SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 select taking time to load
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

esthera
Flowing Fount of Yak Knowledge

1392 Posts

Posted - 06/16/2013 :  07:00:30  Show Profile  Reply with Quote
I have a query select participantid,lastname,firstname,nameofprogram, status from participants order by lastname,firstname

we are doing this at the top of every page and it's taking time to load
(part of the issue I believe is the shared sql server we are on)
I already have an index on lastname,firstname

is there anyway I can make this query quicker?

alternatively i was thinking to cache this query in a table and when the table is updated - change the updated info == maybe to do this in sql and maybe on the server just in a text file

what do you think?

James K
Flowing Fount of Yak Knowledge

3643 Posts

Posted - 06/16/2013 :  12:42:00  Show Profile  Reply with Quote
Given that your query does not have any WHERE clause or HAVING clause to limit the number of rows, the query is going to do a table scan, so indexes are probably not of going to be much help. If you are doing this select frequently, assuming the table is not very large, it should not result in any physical IO's; just logical IOs. How many rows does the table have?

Your best bet is to turn statistics on to see what is going on. I don't think your idea about caching the table is not going to help much either - but I am only guessing; first you need to find where the bottleneck is. Query plan and statistics will help you do that.
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1392 Posts

Posted - 06/16/2013 :  13:26:29  Show Profile  Reply with Quote
there are 3754 rows

execution plan is

select 0%
sort 17%
table scan 83%

what can I do?

we need this list of the users in many places
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000