Deathly Performance with Sql Server Query

Question

Deathly Performance with Sql Server Query

Jimmy123

SSC Veteran

Points: 200
More actions
December 4, 2014 at 1:27 pm

#313639

WITH Sales_CTE (ID, SalesAccountNumber, SalesStartDate, SalesEndDate)
AS
-- Define the CTE query.
(
SELECT ID, SalesAccountNumber, SalesStartDate,SalesEndDate
FROM Sales.SalesOrderHeader
group by ID, SalesAccountNumber, SalesStartDate,SalesEndDate
)
-- Define the outer query referencing the CTE name.
select A.SalesAccountNumber from Sales_CTE A
inner join Sales_CTE B
where A.SalesAccountNumber=B.SalesAccountNumber
and A.SalesStartDate=B.SalesStartDate
and A.SalesEndDate!='30/12/3049'
Now this query takes 15 mins plus with no hope of completing. The table Sales.SalesOrderHeader is 1 million rows and had no indexes.
The Explain plan says the merge join is 91% of the problem.
The ID column is an identity column. What is the solution to getting this query to complete in a reasonable time ?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398712 More actions · Answer 1

I'd put indexes on the table. The best choice, if you can only have a single index, would be a clustered index on the three columns in the WHERE clause.

The query itself, other than not following strict syntax using an ON clause for defining the JOIN criteria, is fine. There's little you can do to adjust that. It really is about getting an index on the table.

Every table, with very few exceptions, should have a clustered index, at minimum. SQL Server data access is designed around the clustered index.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Eddie Wuerch SSChampion Points: 12532 More actions · Answer 2

Try this instead:

SELECT DISTINCT SalesAccountNumber

FROM Sales.SalesOrderHeader

WHERE SalesEndDate!='30/12/3049'

It's the same query with a lot less work.

Eddie Wuerch
MCM: SQL

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

After analyzing your query and Eddie's, I keep wondering what are you trying to accomplish. This can certainly be a major problem with cartesian products. Could you post DDL, sample data and expected results? Actual Execution Plan can help to confirm the problem that I imagine.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

joshdbguy SSCertifiable Points: 7707 More actions · Answer 4

Actual query looks fine. We would need data to determine the reason why it's taking so long. But as Grant said, it's most likely an indexing issue. Another reason could be a data type issue. If you are pulling from a blob field, that could destroy performance.

Grant Fritchey SSC Guru Points: 398712 More actions · Answer 5

Luis Cazares (12/5/2014)
After analyzing your query and Eddie's, I keep wondering what are you trying to accomplish. This can certainly be a major problem with cartesian products. Could you post DDL, sample data and expected results? Actual Execution Plan can help to confirm the problem that I imagine.

I don't know that I'd call this a Cartesian product. The WHERE clause defines join criteria. It's just doing it in ANSI 89 syntax instead of the more modern approach. Not a choice I'd make, but I'll bet if you moved the them to the ON clause the execution plan probably wouldn't change a bit.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning