Tune up my sql query for better perfomance

Question

select top 1 col1 
from table1 
order by CreatedOn desc

This query takes 6 minutes on SQL Server 2005 to execute.

The table contains about 25,00,000 rows and number of total columns is 36, but as shown above I am just retrieving a single column whose datatype is uniqueidentifier.

So, please help me out on optimization and do let me know if any more details are required.

Well, let me tell you that I cannot change my table structure or cannot do any changes to table like index, etc. Suggest changes to query only or changes which won't have an impact on table structure.

if you can't create an index then you cannot improve your query. — Mitch Wheat, Nov 26 '10 at 08:42
@"oracle certified professional" how should i go ahead with creating new table ?? — Pratik, Nov 26 '10 at 08:47
BTW, adding indexes is not really changing a Table (although I'm not suggesting it is a trival free-for-all operation!); an index is not a Domain object (unless its a unique constraint). It's a non-functional concern — Mitch Wheat, Nov 26 '10 at 08:47
@Pratik I was thinking that you could create a new table in which you would cache the last record added to your main table (if this would be the one with the latest CreatedOn date) when it is added, then you simply query this single-row table instead of the main table — , Nov 26 '10 at 08:50
As said earlier, due to some reasons, we are not allowed to make changes to database, in case when we get permissions to do so, i would definately consider "creating indexes" but as of now i dont have option for that. — Pratik, Nov 26 '10 at 08:53
y down vote for ?? i just asked : Other than indexing how to achieve perfomance, atleast to some extent. Indexes are trivial answers i wanted something different. Don't understand down vote ?? — Pratik, Nov 26 '10 at 08:56
@Pratik could you do something daft like use a field that you know _will_ be indexed? Such as the primary key? If, say, your primary key is auto-incremented, would the latest CreatedOn record have the highest ID? — , Nov 26 '10 at 08:59
What @oracle means is that if ordering by the primary key gives you the same result as ordering by CreatedOn, you could order by primary key and make use of the index on that (if it exists). — Lieven Keersmaekers, Nov 26 '10 at 09:03
the column which i am reteriving is a primary key itself, which is uniqueidentifier type & can't order by that, as i want ascending order of data inserted on createddate only — Pratik, Nov 26 '10 at 09:06
How about an indexed view? http://technet.microsoft.com/en-us/library/cc917715.aspx#XSLTsection127121120120 — , Nov 26 '10 at 09:20
@oracle, your idea was sound, I would have upvoted it. If Col1 *is* the primary key and there's no other indexed column to order by, OP is out of luck and Mitch's comment stands. — Lieven Keersmaekers, Nov 26 '10 at 09:29
I'm voting to close this question as off-topic because it belongs on the DBA site. — juunas, Jan 08 '17 at 16:06

score 3 · Accepted Answer · edited Nov 29 '10 at 14:39

3

Are u test it :

SELECT col1
FROM   table1
WHERE CreatedOn in (SELECT MAX(CreatedOn) AS col1 FROM table1)

edited Nov 29 '10 at 14:39

Samuel Neff

73,278
17
138
182

answered Nov 26 '10 at 09:38

Ali Jazayeri

40
3

1

this works well (atleast faster, than anything mentioned here) !! my 6min query is done in 27sec..wow! – Pratik Nov 26 '10 at 10:33
You should still have a `TOP 1` on it. It's possible for multiple records to have the same `CreatedOn` value. – Samuel Neff Nov 27 '10 at 04:45
@Lieven, sure you do. The inner subquery will only return one result. But if the `MAX(CreatedOn)` value is duplicated in multiple rows in `table` then you could end up with multiple result rows. – Samuel Neff Nov 29 '10 at 14:38
@Lieven, I don't understand why you think the accepted answer isn't what the OP requested. The OP's original query returns one value from the row with the max `CreatedOn`. The accepted answer returns the same one value from the same one row with the max `CreatedOn` value. The queries provide the same result. – Samuel Neff Nov 29 '10 at 16:45
@Sam, seems to be a case of typing while my brain has entered sleep mode. I'll delete my comments later this evening to spare me the long term embarassement. – Lieven Keersmaekers Nov 29 '10 at 17:06
@Lieven, yeah, the original answer obviously had a typo but the basic structure and approach was correct, so I fixed the typo. – Samuel Neff Nov 29 '10 at 18:21
Comments deleted, I can rest in peace. – Lieven Keersmaekers Nov 29 '10 at 18:33

Tom 'Blue' Piddock · Answer 2 · 2010-11-26T09:28:58.400

2

What you could try is to pray to Prequel (the god and creator of SQL code everywhere) to grant you magical access rights and indexing abilities to combine not only clustered but non clustered indexes in one beautifully structured table...

edited Nov 26 '10 at 09:28

answered Nov 26 '10 at 09:04

Tom 'Blue' Piddock

2,131
1
21
36

2

And just *poof* out of nothing there was +1 – littlegreen Nov 26 '10 at 09:18

score 1 · Answer 3 · answered Nov 26 '10 at 08:35

1

set index on CreatedOn

answered Nov 26 '10 at 08:35

x2.

9,554
6
41
62

Thanks but due to privilages i can't do that, can you recommend something else. – Pratik Nov 26 '10 at 08:37

score 1 · Answer 4 · answered Nov 26 '10 at 08:36

1

Have you created index for this table. Index increase to find data quickly when a query is processed.

answered Nov 26 '10 at 08:36

Pankaj Agarwal

11,191
12
43
59

No but i cant change it now, no privilages for creating index.some other tip ?? – Pratik Nov 26 '10 at 08:42

score 1 · Answer 5 · answered Nov 26 '10 at 08:37

1

create an index for CreatedOn . the source of delay is caused by the time it takes to order by CreatedOn, creating an index on it will solve your problem

answered Nov 26 '10 at 08:37

Ali Tarhini

5,278
6
41
66

Thanks.Looking forward for something different than indexes. – Pratik Nov 26 '10 at 08:41
1

@Pratik: there isn't anything. No amount of asking will help. – Mitch Wheat Nov 26 '10 at 08:43
either you need to create the index or change the query – Ali Tarhini Nov 26 '10 at 08:49
1

one option is add a where clause to limit the results(per year for example) but it depends on the business needs and if it feasible. – Ali Tarhini Nov 26 '10 at 09:00

Mitch Wheat · Answer 6 · 2010-11-26T08:52:49.830

1

Create a non-clustered index on CreatedOn and include col1:

CREATE NONCLUSTERED INDEX NC_Table1_CreatedOn_I_col1
    ON Table1(CreatedOn)
        INCLUDE (col1)

If you can't get an index created, then NO you cannot speed your query up! You could do a different query, but that wasn't your question...

edited Nov 26 '10 at 08:52

answered Nov 26 '10 at 08:39

Mitch Wheat

295,962
43
465
541

Thanks.Looking forward for something different than indexes. – Pratik Nov 26 '10 at 08:42
Thanks, still in case if you have some other solution,do let me know. – Pratik Nov 26 '10 at 08:49

score 1 · Answer 7 · answered Nov 26 '10 at 09:16

1

Create your own (temp) table on which you have ALL rights
Create clustered index on this table on CreatedOn
Copy over all data
Happy querying ever after

answered Nov 26 '10 at 09:16

littlegreen

7,290
9
45
51

score 1 · Answer 8 · answered Nov 26 '10 at 09:46

1

select min(CreatedOn) from table1

If you're lucky, SQL will have this in its statistics and the answer will be quick.

If you're unlucky, then indexing as suggested above is your only hope.

answered Nov 26 '10 at 09:46

smirkingman

6,167
4
34
47

select min(CreatedOn) from table1 --- 34sec select top 1 col1 from table1 order by CreatedOn desc --- 7min36sec – Pratik Nov 26 '10 at 10:23
You're lucky :) My mistake, it should have read MAX(CreatedOn). Out of curiosity, is MAX faster then MIN? – smirkingman Nov 26 '10 at 13:18

Tune up my sql query for better perfomance

8 Answers8