SQL Server - how to increase query speed of simple query
Hi..
I have a simple stored procedure that looks like this
SELECT * FROM CUSTOMERS ORDER BY LASTNAME , FIRSTNAME
The customer table has over 100 fields.
It returns 20K records, I have an index on lastname, firstname.
The execution plan say 46% Sort, 54% table scan.
It appears to take 25 -45 seconds to run . When called and traced in Activity Monitor from a web app. In Query analyzer it takes 10 seconds. Any suggestions how I can speed it up.
thx
I have a simple stored procedure that looks like this
SELECT * FROM CUSTOMERS ORDER BY LASTNAME , FIRSTNAME
The customer table has over 100 fields.
It returns 20K records, I have an index on lastname, firstname.
The execution plan say 46% Sort, 54% table scan.
It appears to take 25 -45 seconds to run . When called and traced in Activity Monitor from a web app. In Query analyzer it takes 10 seconds. Any suggestions how I can speed it up.
thx
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Nem Schlecht
Agreed, the Query is running decently quickly. Either transferring the data (network) or parsing it on the client side (client application) is what is slowing things down.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Yes.. showing all records - but using paging 15 at a time.
Yes.. have index on lastname, firstname and a clustered index on ID
I guess the issue is with the app or network?
Yes.. have index on lastname, firstname and a clustered index on ID
I guess the issue is with the app or network?
Why don't you fetch 15 records at a time,
ASKER
I grab all the records into a cache object - that is later used for other queries and filters.
So I only make 1 call to the DB throughout the app. So I need all the records upfront.
It's a readonly / reporting app. I have contacted my ISP
So I only make 1 call to the DB throughout the app. So I need all the records upfront.
It's a readonly / reporting app. I have contacted my ISP
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
My ISP says I'm reaching their 200MG memory limit and the app pool is being constantly recycled. I think that my problem.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.