September 19, 2002 at 2:31 pm
Hi !
The situation that I have is like this:
I have a sql statement that returns say more than 300,000 records. I am interested in only the first 25 for the display but I also want to know the total number of records that qualify for that sql statement. Is there an efficient way of doing this ? The statement has an order by clause as well as a result of which getting the first 25 records becomes slower since it has to sort the result-set first.
Normally, what is the suggessted way of doing paging in the UI application ? The requirment is that the sql statement that gets generated is dynamic and not static...we want to see the first 25 records fast and also want to get a count of the total records in order to indicate the number of pages available.
Any suggesstions or feedback will be highly appreciated.
Thanks,
Rahul
September 19, 2002 at 5:14 pm
Hi, I think you should place a Clustered Index on that column. The index will place all the rows in order in new pages in and index structure.
This way your transaction will run faster because it will access the columns by the index.
With Select Top 25 Column1 From table ....
You will get the first 25 rows in order from your table.
If you execute select count(*) from table... to know how much records you have in the table it will navigate trough the index.
Also check: select rows from sysindexes where id=object_id(Index_name)... you can get the how many rows are in the index.
If you never used indexes, you should read in BOL DBCC showcontig and other DBCC to check from fragmentation and other things that can low performance.
September 19, 2002 at 5:39 pm
Please re-read the original question:
1) The sql statement is totally dynamic, it's not static, the search criteria can be totally different, the joining tables could be totally different....in order to select top 25 records either top or set rowcount can be used however, since an order by is included, the records are processed and then only the top 25 records are selected.
2) The count(*) issue cannot be resolved by looking at sysindexes because the sql is totally dynamic....
September 20, 2002 at 12:05 am
I think you should use dynamic sql to achive this.
Secondly sysindexes is not always return correct result so do a select count(*) and probably use a union to join these two recordset
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
September 20, 2002 at 3:58 am
Usually if you know the data is going to be moved thru then use ADO with a server side cursor and setup into pages using the proper syntax (a bit long for here and varies based on the app, can find examples all over the net). When you do this it will containt a count of records returned and number of pages in the buffers. This is about the only way to accomplish, especially with complete dnamic sql needs.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply