Slow Query - SQL Server Forums

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

Slow Query

Author

Topic

PeteLeHoq
Starting Member

37 Posts

Posted - 2011-03-21 : 13:28:43

Hey all,

I've been using this SQL but it takes a good 2-3 seconds to get a response.

strSQL = "SELECT DISTINCT TOP 20 Otbl.ID, Mtbl.name, Mtbl.folname, Mtbl.url FROM Otbl LEFT JOIN Mtbl ON Otbl.ID=Mtbl.ID WHERE otbl.id NOT IN ( SELECT DISTINCT TOP 20 otbl.id FROM otbl WHERE Otbl.[end_date] >= #" & date() & "# AND [code] IS NOT NULL ORDER BY otbl.id ASC) AND Otbl.[end_date] >= #" & date() & "# AND [code] IS NOT NULL ORDER BY otbl.id ASC;"

It selects the records 20 - 40 from a result set, where a end_date is greater than todays date, and the field code is not empty.

Anyone think of a faster way of doing this? at the moment it repeats the
"WHERE Otbl.[end_date] >= #" & date() & "# AND [code] IS NOT NULL" line, there must be a better way of doing this?

Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-21 : 13:40:25

If you are using SQL 2005 or higher, you can use row_number function. See an example here: http://www.mssqltips.com/tip.asp?tip=1175 or google for "row_number function for paging"

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-21 : 13:44:04

Want to post the DDL of the tables and the indexes it has?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-21 : 13:48:52

I don't see why you are doing the IN


	SELECT DISTINCT TOP 20 Otbl.ID, Mtbl.name, Mtbl.folname, Mtbl.url 
	  FROM Otbl 
 LEFT JOIN Mtbl 
		ON Otbl.ID=Mtbl.ID 
	 WHERE otbl.id NOT IN ( 
			  SELECT DISTINCT TOP 20 otbl.id 
				FROM otbl 
			   WHERE Otbl.[end_date] >= GETDATE()
			     AND [code] IS NOT NULL 
			ORDER BY otbl.id ASC) 
	   AND Otbl.[end_date] >= GETDATE()
	   AND [code] IS NOT NULL
  ORDER BY otbl.id ASC;

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

PeteLeHoq
Starting Member

37 Posts

Posted - 2011-03-21 : 14:00:30

The NOT IN is used, so that the query selects id where it's 'NOT IN' the first 20 records found. This all started when I was trying to select rows from a result set, e.g rows 20 to 40. The projects in SQL 2000.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-21 : 15:02:47

Something similar to what I was suggesting using row_number function (which is not available in SQL 2000) can be implemented like this in SQL 2000

-- test data
create table #test (id int, price float);

declare @i int; set @i = 0;
while (@i < 20) 
begin
   set @i = @i+1;
   insert into #test values (@i,rand()*100.0);
end

-- see the data in the table.
select * from #test order by price

-- get the 3 lowest prices excluding the lowest 3.
-- (prices ranked 4 thru 6).
select top 3 * from
(
   select top 6 * from #test order by price
) a order by price desc

drop table #test

PeteLeHoq
Starting Member

37 Posts

Posted - 2011-03-24 : 17:36:09

Just to confirm, the row_number function, looks at the results of the query and not the actual rows in the table?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-24 : 20:30:57

I am afraid I didnt completely understand your question. SQL 2000 does not have the row_number function. So code I posted does not use row_number function. It does look at the results of the query to determine ordering.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-03-25 : 12:49:05

Also, try getting rid of the dynamic sql. that should speed it up too.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources