Loading is slow when querying large amounts of data

[mxtao12345]

If the query data volume is relatively large, such as 200,000 entries or more, and I have set the default pagination to 200 entries, the loading is very slow. I think it should be optimized, for example, by loading the first 200 entries first to quickly display the results.

[mxtao12345]

I am currently using the Oracle database, and the plugin version is the latest version.

[ChandraSaiKarthik]

i see the 2.3 lakh records data loading within 1.2 secs

[mikeburgh]

Yes I need to revisit this.. I initially planned to use streaming to deal with large result sets, to get the data displaying sooner.. however I ran into two issues.. not all drivers support streaming, and then the performance of serializing them from the extension to the web view (where they are show in VSCode terms) was not efficient and resulted in the overall results taking longer to be passed.

I don't want to edit or wrap SQL that you are executing in any way so I cannot add limit directives or CTE/nested selects to align it to the pagination.

I will revisit it to see if I can conditionally support streaming for the drivers that do provide it, and if there is a better way to get the data to the web view (or maybe the initial couple of pages) and then let the bulk move over once ready.

[ccarbonaro-GSU]

Running into the same problem; is there any plan to revisit this?

Being unable to quickly preview a query which returns many records is the only thing keeping me from using DBCode as my primary RDBMS workstation. I'm using Oracle, and while DBCode has a ton of amazing features, the slow return time on a lot of our queries is probably a deal-breaker at the moment. Would love to see some sort of functionality for this implemented.

[mikeburgh]

You can fix it now by just adding a limit to your query.. that will resolve it for you. I still plan to look at it, but it's not in the short term, as its a much more complex issue that needs to be addresses per driver and also deal with the issue when one driver supports it and another does not.. so adding a limit statement to your query is the only current solution.

[ccarbonaro-GSU]

This may still be worth implementing at some point, since:

1. Having to manually add a LIMIT statement is a point of friction that will frustrate over time, especially when:
   • The user is not the original author of the query, or
   • The SQL implementation is non-standard e.g. Oracle SQL forgoes LIMIT in favor of the absolutely lovely FETCH NEXT X ROWS ONLY syntax
2. Using a LIMIT clause means that other features of DBCode no longer work properly e.g. displaying the total number of returned rows, being able to apply filters to the results, etc.

Understand why this is not a priority, just putting down some thoughts about why this might still be worth eventually supporting.

[mikeburgh]

Yeah appreciate it, and it will be addressed..

Point 2 is part of the complication.. in a lot of cases, I am going to need to run the full query to ge the total result count.. I don't think too many engines/drivers provide the total count but then only a subset of the results.. I can certianlly only render a sub set, but the full query would still need to be executed..

There's just a lot of complexity to it, and agree Oracle syntax is not great for this, but only option for now until I start dealing with all that complexity to not modify SQL but handle only small parts of a response, and still get all the detail needed.

[ccarbonaro-GSU]

Don't doubt that there's complexity under the hood which makes this a non-trivial feature request. Seems like you'd have to do something asynchronously and await details related to things like the number of rows. Appreciate you taking the time to comment on it, and hope to see this addressed at some point!