query taking 6 seconds to load

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

query taking 6 seconds to load

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-05-05 : 08:18:23

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
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-05-05 : 08:34:15

could you supply the execution plan?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-05-05 : 09:15:35

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

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-05-05 : 14:15:26

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-05 : 21:00:52

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.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2013-05-06 : 02:21:04

select 0%
sort 18%
table scan 82%

How can I optimize this or find where the issue is

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-06 : 08:23:58

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.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-06 : 10:27:50

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

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-05-06 : 20:00:28

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2013-05-07 : 05:24:54

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources