SQL Query taking ages to execute

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

General SQL Server Forums

New to SQL Server Programming

SQL Query taking ages to execute

Author

Topic

samirkopal
Starting Member

5 Posts

Posted - 2011-06-28 : 17:07:31

I have the following query which i run on a table containing 170000 records and it takes about 2-3 minutes. Can you please suggest on how I can Improve this query

SELECT * FROM(
SELECT ROW_NUMBER() OVER(
ORDER BY Property3 DESC)
AS rownum,* FROM(
SELECT TOP 170000 Table1.Property1,Table1.Property2,Table1.Property3
FROM Table1 ORDER BY Property3 DESC)
AS Tmp)
AS Temp1 WHERE rownum between 169990 AND 170000.

Additional info: This query causes a problem only the very first time i run it, after that it seems to work fine.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-28 : 17:53:05

SELECT * FROM(
SELECT ROW_NUMBER() OVER(ORDER BY Property3 DESC) AS rownum,
Table1.Property1,Table1.Property2,Table1.Property3
FROM Table1) AS Temp1
WHERE rownum between 169990 AND 170000

Do you have an id on the table then
with cte as
(select ROW_NUMBER() OVER(ORDER BY Property3 DESC), id, seq from Table1 )
select Table1.Property1,Table1.Property2,Table1.Property3
FROM Table1
where id in (select id from cte where rownum between 169990 AND 170000)

with
index on Property3 include id
index on id include Property1, Property2, Property3

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

samirkopal
Starting Member

5 Posts

Posted - 2011-06-28 : 17:59:02

quote:
Originally posted by nigelrivett

SELECT * FROM(
SELECT ROW_NUMBER() OVER(ORDER BY Property3 DESC) AS rownum,
Table1.Property1,Table1.Property2,Table1.Property3
FROM Table1) AS Temp1
WHERE rownum between 169990 AND 170000

Do you have an id on the table then
with cte as
(select ROW_NUMBER() OVER(ORDER BY Property3 DESC), id, seq from Table1 )
select Table1.Property1,Table1.Property2,Table1.Property3
FROM Table1
where id in (select id from cte where rownum between 169990 AND 170000)

with
index on Property3 include id
index on id include Property1, Property2, Property3

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Thanks for your reply. But since I am very new to SQL. it would be helpful if you could tell me what is a cte and seq?

samirkopal
Starting Member

5 Posts

Posted - 2011-06-28 : 18:07:02

quote:
Originally posted by nigelrivett

SELECT * FROM(
SELECT ROW_NUMBER() OVER(ORDER BY Property3 DESC) AS rownum,
Table1.Property1,Table1.Property2,Table1.Property3
FROM Table1) AS Temp1
WHERE rownum between 169990 AND 170000

Do you have an id on the table then
with cte as
(select ROW_NUMBER() OVER(ORDER BY Property3 DESC), id, seq from Table1 )
select Table1.Property1,Table1.Property2,Table1.Property3
FROM Table1
where id in (select id from cte where rownum between 169990 AND 170000)

with
index on Property3 include id
index on id include Property1, Property2, Property3

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

I changed the query to this and restarted SQL server. It still took more than 1 minute.
SELECT * FROM(
SELECT ROW_NUMBER() OVER(ORDER BY Property3 DESC) AS rownum,
Table1.Property1,Table1.Property2,Table1.Property3
FROM Table1) AS Temp1
WHERE rownum between 169990 AND 170000

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-06-28 : 18:17:30

[code]
select row_number() over(order by property3 desc) as rownum, *
from (select top(11) table1.property1, table1.property2, table1.property3
from table1
order by property 3 asc) t
[/code]

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-28 : 18:41:49

If there is a unique id on the table then try the second one I gave and add the indexes - should be seconds.
for ctes see
http://www.simple-talk.com/sql/t-sql-programming/sql-server-2005-common-table-expressions/

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

samirkopal
Starting Member

5 Posts

Posted - 2011-06-28 : 18:57:38

quote:
Originally posted by nigelrivett

If there is a unique id on the table then try the second one I gave and add the indexes - should be seconds.
for ctes see
http://www.simple-talk.com/sql/t-sql-programming/sql-server-2005-common-table-expressions/

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Thanks again. This is what I am exactly looking for.

1. The user is displayed a grid with the first 10 records and the grid has a page control below.
2. the user can go to any page, and hence i use the rownum in this case.
Can you please help me write a CTE for this case?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-29 : 06:22:19

First of all add the indexes I suggested - that might cure your problem.

Are you using a stored procedure to produce this? If not then it would make things a lot easier if you did.
The input to the SP would be the page number and maybe the number of rows per page.
Then you can chenge the query easily and test it.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

samirkopal
Starting Member

5 Posts

Posted - 2011-06-29 : 16:16:59

quote:
Originally posted by nigelrivett

First of all add the indexes I suggested - that might cure your problem.

Are you using a stored procedure to produce this? If not then it would make things a lot easier if you did.
The input to the SP would be the page number and maybe the number of rows per page.
Then you can chenge the query easily and test it.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

I did all the steps as mentioned by you and it resolved the issue. Thank you so much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources