slow query in SQL-Server 2005 - 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

SQL Server 2005 Forums

Transact-SQL (2005)

slow query in SQL-Server 2005

Author

Topic

Gyte
Starting Member

23 Posts

Posted - 2007-02-12 : 08:08:13

Hello, I'm migrating from SQL-Server 2000 to SQL-Server 2005 and one query seems very slow. The below query is fast in SQL-Server 2000, but in SQL-Server 2005 the same query is slow.

SELECT tblContact.V8000 AS Companyname,
tblContact.V91000 AS Companynumber,
tblV.Vcyclusnumber AS cyclusnumber,
tblReport.B231 AS SalesData
FROM tblContact
INNER JOIN tblV ON tblV.Vclientnumber = tblContact.V91000
LEFT JOIN tblReport ON tblV.Vcyclusnumber = tblReport.Rcyclusnumber
WHERE tblReport.ID_tblReport IS NULL
OR tblReport.ID_tblReport IN (SELECT MAX(ID_tblReport)
FROM tblReport
GROUP BY Rcyclusnumber)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-12 : 08:10:56

What does the execution plan and IO statistics looks like on both SQL 2000 and 2005?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 08:16:17

Do you have the same indexes in the tables?
Are there the same amount of records?

Peter Larsson
Helsingborg, Sweden

Gyte
Starting Member

23 Posts

Posted - 2007-02-12 : 08:40:37

Dear Harsh Athalye,
I don't know where to find the execution plan and IO statistics.

Dear Peter Larsson,
All tables are indexed, but none of the tables has a primary key defined.
I tried your query in SQL-Server 2005 and there was the following error message : "The TOP N WITH TIES clause is not allowed without a corresponding ORDER BY clause."

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-12 : 08:45:06

To see execution plan along with IO statistics run following commands:

SET STATISTICS IO ON
GO
SET SHOWPLAN_TEXT ON
GO
-- Your query here
SELECT ....
GO
SET STATISTICS IO OFF
GO
SET SHOWPLAN_TEXT OFF
GO

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 08:58:36

Try this, since you are using SQL Server 2005

SELECT		c.V8000 AS Companyname,
		c.V91000 AS Companynumber,
		v.Vcyclusnumber AS cyclusnumber,
		r.B231 AS SalesData
FROM		tblContact AS c
INNER JOIN	tblV AS v ON v.Vclientnumber = c.V91000
LEFT JOIN	(
			SELECT	B231,
				Rcyclusnumber
			FROM	(
					SELECT	B231,
						Rcyclusnumber,
						ROW_NUMBER() OVER (PARTITION BY Rcyclusnumber ORDER BY ID_tblReport DESC) AS rownum,
						ID_tblReport
					FROM	tblReport
				) AS x
			WHERE	x.rownum = 1
				OR x.ID_tblReport IS NULL
		) AS r ON r.Rcyclusnumber = v.Vcyclusnumber

Peter Larsson
Helsingborg, Sweden

Gyte
Starting Member

23 Posts

Posted - 2007-02-12 : 10:27:54

Thank you Peter Larsson.
The query you send me did the job.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 10:42:56

What is the speed difference now?

Peter Larsson
Helsingborg, Sweden

Gyte
Starting Member

23 Posts

Posted - 2007-02-12 : 10:48:41

The speed difference is huge. The first query was very slow and sometimes generated a time out. The new query generates the results in about 10 seconds. This is quite an improvement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-12 : 11:16:56

Try this one on the SQL 2000 server and compare speed

SELECT		c.V8000 AS Companyname,
		c.V91000 AS Companynumber,
		v.Vcyclusnumber AS cyclusnumber,
		r.B231 AS SalesData
FROM		tblContact AS c
INNER JOIN	tblV AS v ON v.Vclientnumber = c.V91000
LEFT JOIN	(
			SELECT	x.B231,
				x.Rcyclusnumber
			FROM	(
					SELECT TOP 1 WITH TIES	B231,
								Rcyclusnumber,
								ID_tblReport
					FROM			tblReport
					ORDER BY		ID_tblReport DESC
					UNION
					SELECT	B231,
						Rcyclusnumber,
						NULL
					FROM	tblReport
					WHERE	ID_tblReport IS NULL
				) AS x
		) AS r ON r.Rcyclusnumber = v.Vcyclusnumber

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources