Summary: More efficient data grid sorting logic is needed when sorting all table rows, similar to what is provided in Toad for Oracle.
Current Problem: When you click a grid col header, the tool prompts whether you want to sort all the rows in the table or just what's in the grid. If you pick just what's in the grid, it's a fast sort, however, the sorting is not applied to the rest of the remaining rows to be listed in the results grid, rendering the initial sort useless.
Possible solutions:
- When user clicks on grid col header, prompt "Sort only the N rows already loaded into the grid, or sort all rows for table (issue new query to DB)?" Have 2 buttons there for user to click.
- If user clicks "sort data already in grid", run a fast local sort
- If user clicks "sort all rows in table (new DB query)", then it's a new query to DB with ORDER BY (with ASC or DESC as appropriate), and then display top N rows
If the user really wants to load the full table to the Results grid, they can drag the scroll bar to the bottom of the data results and the tool will already prompt them whether they want to load all data to the grid. If they do, then the grid of course now contains the full table. So after that point, if the user clicks a grid col header, the tool could skip the prompt and just sort locally.
This will be a considerable time saver for large queries.
Customer did additional tests and these are the observations:
Summary: More efficient data grid sorting logic is needed when sorting all table rows, similar to what is provided in Toad for Oracle. Specifically, sort the data on DB side and do NOT fetch all rows back to the grid.
Current Problem: When you click a grid col header, the tool prompts whether you want to sort all the rows in the table or just what's in the grid.
If you pick just what's in the grid, TDS does a local sort which is typically fast because the grid typically does not have very many rows (typically only 500 or 1000, initially).
However, if you pick “all rows”, then TDS fetches all rows from DB to the grid (this is the issue!) and then does a local sort.
The fetching of all rows from DB to grid is a showstopper for large tables (it could take hours, depending on table size and network speed).
There is no need to fetch all rows from DB to the grid to display sorted data in the grid – we can simply query the DB using an ORDER BY and then display the first N rows in the grid.
Generally, the sort on the DB side will be relatively quick (typically seconds, depending on table size and indexes). This is how Toad for Oracle works.
Possible solutions:
- When a user clicks on the grid col header, prompt "Sort only the N rows already loaded into the grid, or sort all rows for the table (issue a new query to DB)?" Have 2 buttons there for the user to click.
- If the user clicks "sort data already in the grid", run a fast local sort
- If the user clicks "sort all rows in the table (new DB query)", then it's a new query to DB with ORDER BY (with ASC or DESC as appropriate), and then displays top N rows
Note: If the user wants to load the full table to the Results grid, they can drag the scroll bar to the bottom of the data results and the tool will already prompt them whether they want to load all data to the grid.
If they do, then the grid of course now contains the full table. So after that point, if the user clicks a grid col header, the tool could skip the prompt and just sort locally.
This will be a considerable time saver for large tables.