Toad World® Forums

More efficient grid sorting of all rows

More efficient data grid sorting logic is needed when sorting all table rows, similar to what is provided in Toad for Oracle. In Toad for Oracle, if you click a grid column header, even if it's a huge table with millions of records, it will simply issue the appropriate ORDER BY query behind the scenes, and then display only the first 1000 rows or whatever your preferences are set to. I.e. if the DB can sort the data in a couple of seconds, that's all you'll have to wait. The key point is that the tool does not need to bring back all rows from the DB.

In Toad Data Point, however, it doesn't work like this. 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, and this is a nice feature (to be able to sort just what's there). However, if you pick all rows in a table, then the tool actually pulls all the data back from DB into the grid, before it sorts it. This is a showstopper if the table is large (millions of records). It would be better for it to just issue the query with ORDER BY and then return only the top N rows, based on your preferences. In fact, if you go to the SQL editor and write your own query with ORDER BY, it runs as quickly as DB can sort the data, and returns only the top N rows into a grid, so is very fast. This should be what happens when you click on the grid col header to sort.

Maybe the behavior could be changed as follows:
• When you click 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", then it's just 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 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.