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)
 query taking 6 seconds to load
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

esthera
Flowing Fount of Yak Knowledge

1392 Posts

Posted - 05/05/2013 :  08:18:23  Show Profile  Reply with Quote
i'm trying to run the following query on a table with 3000 records and it takes 6 seconds to load - is there a way to make this quicker

select participantid, lastname, firstname from participants order by lastname, firstname

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2055 Posts

Posted - 05/05/2013 :  08:34:15  Show Profile  Visit jackv's Homepage  Reply with Quote
could you supply the execution plan?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1392 Posts

Posted - 05/05/2013 :  09:15:35  Show Profile  Reply with Quote
what do you mean the execution plan - it's just a query I am running often and i'm wondering if I can optimize it
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2055 Posts

Posted - 05/05/2013 :  14:15:26  Show Profile  Visit jackv's Homepage  Reply with Quote
The execution plan gives details about the data retrieval methods decided by the SQL Server query optimizer. You can view it graphically or read out SET SHOWPLAN in the BOL.
Other things to think about about are _ statistics being up to date,

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3633 Posts

Posted - 05/05/2013 :  21:00:52  Show Profile  Reply with Quote
To add to what Jack suggested, in SSMS, press control-m and then run the query. You will see an additional tab with the graphical execution plan. However, since you are doing a select against the table with no where clauses or joins of any kind, I suspect all you would see in the query plan is a table scan.

For a table with only 3,000 rows and 3 or four columns, six seconds sounds way too high. It could be that the table is blocked by another spid trying to update rows in the table, or it could be that there is network delays. You can run sp_who2 or sp_whoisactive to see other processes that are active and may be blocking your query.
Go to Top of Page

esthera
Flowing Fount of Yak Knowledge

1392 Posts

Posted - 05/06/2013 :  02:21:04  Show Profile  Reply with Quote
select 0%
sort 18%
table scan 82%

How can I optimize this or find where the issue is
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3633 Posts

Posted - 05/06/2013 :  08:23:58  Show Profile  Reply with Quote
quote:
Originally posted by esthera

select 0%
sort 18%
table scan 82%

How can I optimize this or find where the issue is


This is what I would have expected. There is nothing you can do in the T-SQL code to make it faster (unless you change the requirement to filter out some rows etc.)

The ratio of table scan to sorting time may be indicative of a general slowness. Is this a busy server with a lot of queries hitting it? If that is the case, it just may be that your hardware resources are not sufficient. If the server indeed is busy, try running the same query on a test server where there is not much activity.

What I said above is just an educated guess - don't go out and buy more memory or cores or whatever before you do more investigation and pinpoint the cause of the slowness.
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 05/06/2013 :  10:27:50  Show Profile  Reply with Quote
If you're executing the query on the server, then yes, it is very high (for 3000 records). However if you're trying to execute it from some other machine over the network then compare this time with the time when you execute the same query on server machine. If there's any differnce in time, then its network slowness issue.

Else as James mentioned.

Other thing that I might think of could be index defregmentation if there's any on the participants table. Also, my understanding could be incorrect (and I might get a straight "not correct" from an expert on this), but what I think you may reduce the at least the sorting by creating a clustered index on the table (if there isn't any extent one). Since according to BOL Clustered index store values in order so extra efforts made for soring (18%) can be reduced. However the scan would remain as it is since there is no where clause.

Cheers
MIK

Edited by - MIK_2008 on 05/06/2013 10:28:26
Go to Top of Page

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 05/06/2013 :  20:00:28  Show Profile  Reply with Quote
It is doing a Table Scan which means you most likly have no suitable indexes...

-- Run the statistics parts
set statistics time on
go
set statistics IO on
go

-- Run your code
select participantid, lastname, firstname from participants order by lastname, firstname
go
-- look in messages tab for the time is took and the logical and physical reads.
-- copy the information seen somewhere

-- create the following index
CREATE NONCLUSTERED INDEX [NonClusteredIndex-participants_1] ON [dbo].participants
(
lastname ASC,
firstname ASC
)
INCLUDE ( participantid) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


-- run your code again
select participantid, lastname, firstname from participants order by lastname, firstname
go

-- compare the time taken and the logical and physical reads to the information previously obtained
-- any improvement is a plus.
-- Review the Showplan. There should be no sort time anymore.
-- if no improvement at all, delete the index created

DROP INDEX[NonClusteredIndex-participants_1] ON [dbo].participants
GO


Edited by - UnemployedInOz on 05/06/2013 20:19:44
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 05/07/2013 :  05:24:54  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
it's doing a table scan because you are asking for all of the table. Simple as that.

An index on the sorting columns will help you fetch TOP records or filter (where) on those columns but there's not much optimization to make if you are asking for all of the data...

Just in case though -- that is a base table and not a view you are hitting right?

Transact Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
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.08 seconds. Powered By: Snitz Forums 2000