Unanswered: Out of options, Need performance help.
hey guys, i need your help please.
here is the scenario:
1. I need to return a data back to client (result set varies 20-10,000)
2. I only want to show 20 records at a time
3. To get info i need to display i need to join 10 tables
When there are small #s of records it works but when i get over 8000 then it becomes a problem:
1. The first version was:
Get all data using big query and return everything back to client and display only 20 at a time (not very proficient).
Takes around 15 seconds to view 20 records.
2. Inspired by 4GuysFromRolla (http://www.4guysfromrolla.com/webtech/062899-1.shtml)
Use Stored Procedure w/ server side paging logic to get 20 records at the time. I had to pass every filter parameter and stuff. SP had to sort resultset and return only 20 records i need to display.
Takes around 5 seconds to view 20 records.
I still think it's slow, i know this is a very broad question but is there any other way to do it, logically?
You coud try running a few instances of the search in anticipation of the Next/Prev scenario.
While users are reading 20 in their window the application is downloading a further 20 behind the scenes asynchronously. I couldn't give an example. At least they wouldn't be aware of the 5 seconds if it took longer to read 20.
The only other example I have is for Delphi, sorry.
Well...you need to order by something....and you need to know what page your on...
DECLARE @Page int, @sql varchar(8000)
SELECT @Page = 2
SELECT @sql = 'SELECT * FROM (SELECT TOP 20 * FROM (SELECT TOP '
+ CONVERT(varchar(10),@Page * 20)
+ ' * FROM Orders ORDER by OrderId) AS A '
+ ' ORDER By OrderID Desc) AS B '
+ ' ORDER BY OrderId'
If you are concerned about performance NOW, then you need to look into indexes NOW. I wondered what kind of indexes you had when I read that it started getting slow at 8000 records. 8000 records is peanuts.
Doing a select count(*) to get the rowcount should not be too expensive. Another method is to select the rowcount from the sysindexes table.
If it's not practically useful, then it's practically useless.