October 19, 2005 at 10:08 am
You could try covered indexes (index on all necessary fields)
October 19, 2005 at 10:38 am
RGR'us
Very fast not even one second retrieves nearly 10,000 rows
Enthusiast
All indexes are done , before indexing it is more slower
October 19, 2005 at 10:56 am
Krishna -
Now I get it! You need a paging method - returning that many rows to the client, whether a desktop application or web page, isn't a real good idea and will absolutely kill your applications performance. Better to return say 50 rows at a time to the client rather than the entire result set every time.
There are a variety of strategies to accomplish this depending on your development environment/target. Search for "resultset paging" here or on your favorite coders site - asp.net (http://www.asp.net) has a couple of threads on the subject and I've read articles about it, etc. in MSDN before. The best option is going to be a stored procedure that performs a TOP or other limiting operation on the database rather than on the client.
Joe
October 19, 2005 at 1:46 pm
I had this same issue with a query, except a LOT more rows were involved.
Look at your query execution plan....that HASH MERGE is killing your time. First it gets all of the requested data from one table and HASHs the common column (ON......), then it HASHs the common column for ALL of the rows in the other table. Then it compares the HASHed values for matches. This is EXTREMELY slow.
In my situation, the problem was with a Stored Procedure. If I ran the same query with the parameters inserted, the execution plan showed a NESTED LOOP.
Solution: I created a JOIN HINT. In my case it was:
LEFT OUTER LOOP JOIN tablename
And wow did my query become lightning fast. Without the hint the query took over 3 hours. With the hint, the same query took 14 minutes - which was acceptable considering one table has over 160 Million rows and the other has over 1 Billion.
-SQLBill
October 20, 2005 at 11:01 am
Hi Please implement paging. Like decide up on number of rows to be displayed on one page and than provide next and previous to navigate all records.
If you decide to display 50 records to display than just maintain the count and return only those 50 records which needs to be displayed on the page. Just like google hundreds of thousands of google search results are displayed.
Regards,
Naishal
Naishal Topiwala
October 20, 2005 at 11:40 am
Yes the paging is also done i have taken the query into stored procedure there i used the temp table and when my application request me the number of rows and page number i retrieve them, but by using the temp table the performance is the same .. The application is faster as it is loading only requeste rows like say 1000
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply