question

technette avatar image
technette asked

Optimize a long running query

I have a query that is taking 285 minutes to run. The table size is approximately 130,000 records. How can I optimize the following? SELECT SampleTable.RecID FROM dbo.SampleTable Order BY SampleTable.RecID
queryoptimization
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

If you can post the execution plan, that would help troubleshoot. I wouldn't imagine that a table of that size taking that long to return the records.
0 Likes 0 ·
Thank you for responding John. I am unable to get the execution plan for this query because of my rights to this particular server. For some reason, according to another team member, its doing an entire table scan. The RecID is an auto generated ID when ever a record is inserted. don't know if this makes a difference.
0 Likes 0 ·
A table scan would be expected since there isn't a WHERE clause. If the RecID is a unique identifier, I agree with @binodbabu in removing the ORDER BY. If it isn't a unique identifier, then I would guess there is something else going on here.
0 Likes 0 ·
130,000 rows is (ordinarily) not a lot of rows, regardless of the the ORDER BY. There must be more to this than meets the eye (like some really, really fat columns, or maybe it's a VM on an old laptop). So we need more information. Do all queries run slowly or just this? What version of SQL Server are you running? Can you post the table definition (all data types, sizes and nullability, constraints, keys)? Any indexes? What server hardware are you running this query on (processor, RAM, storage access)? What DBA resources are there at your organsation to help you gather metrics on this?
0 Likes 0 ·
binodbabu avatar image
binodbabu answered
if RECID is auto generated ID then just remove order by SampleTable.Recid it will be must faster. SELECT SampleTable.RecID FROM dbo.SampleTable
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Lukasz8519 avatar image
Lukasz8519 answered
when You delete order by clause you can add clustered index
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image
Scot Hauder answered
Important lesson here: don't store large amounts of unstructured data in your database. you have large amounts of XML/text in a varchar(max) or large amounts of data in varbinary(max)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered
Are you absolutely sure that it's actually a table and not some godforsaken view or a synonym to a table on a linked server?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.