September 3, 2008 at 2:28 pm
I have the following query and was wondering if anyone can suggest a way to re-organize it so it is more efficient. My T-SQL skills need work... 😉
The table has about 2.5 million rows, and there are no indexes on it, save for the PK defined on an identity column (not part of the query). I have identified a couple of indexes that would address the present query, but my objective here is more one of tuning the query itself, ie. re-writing it so it runs faster (if there is a way of doing so).
Anyway, here it is. Any suggestions would be much appreciated:
SELECT
D.COID
, MIN( D.COTID )
FROM
dbo.tblName AS D
LEFT JOIN (
SELECT
COID
FROM
dbo.tblName
WHERE
CCBID = 100000 AND
MCT = 'Y'
GROUP BY
COID
) AS D2
ON D.COID= D2.COID
WHERE
D.CCBID = 100000 AND
D2.COID IS NULL AND
D.COID IS NOT NULL
GROUP BY
D.COID
Here is the cardinality of the columns in the query (no. of rows commented out on the right):
SELECT COUNT(*) FROM tblName --2402039
SELECT COUNT(DISTINCT (COID)) FROM tblName --88589
SELECT COUNT(DISTINCT (COTID)) FROM tblName --93500
SELECT COUNT(DISTINCT (CCBID)) FROM tblName --136
SELECT COUNT(DISTINCT (MCT)) FROM tblName --2
There is only one index defined on the table; it is the PK, clustered, on the identity column.
Any ideas?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 4, 2008 at 5:42 am
First thing I see, do you reall need to do a left join to that sub-select. If I'm seeing the logic correctly, it's looking for any of the values that have MCT = 'Y' and then eliminating them. Why not simply have that in the WHERE statement of the main query and eliminate the sub-select entirely. That should cut the query execution cost in half.
If COID is the primary key, then this statement, D.COID IS NOT NULL, is redundant. You can't have a null primary key.
After that, I'd need to see the execution plan.
"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
September 4, 2008 at 8:47 am
Try this
SELECTcoID,
MIN(cotID)
FROMdbo.tblName
WHEREccbID = 100000
GROUP BYcoID
HAVINGMAX(CASE WHEN mcT = 'Y' THEN 1 ELSE 0 END) = 0
Also add a covering index on the table like this
CREATE NONCLUSTERED INDEX IX_tblName ON dbo.tblName (ccbID, coID, cotID, mcT)
N 56°04'39.16"
E 12°55'05.25"
September 4, 2008 at 8:51 am
Grant, it is not enough with a WHERE clause.
If the LEFT JOIN find a coID with any of it's record set to mcT = 'Y' the complete coID should be omitted, not just that particular record.
N 56°04'39.16"
E 12°55'05.25"
September 4, 2008 at 9:12 am
Grant Fritchey (9/4/2008)
First thing I see, do you reall need to do a left join to that sub-select. If I'm seeing the logic correctly, it's looking for any of the values that have MCT = 'Y' and then eliminating them. Why not simply have that in the WHERE statement of the main query and eliminate the sub-select entirely. That should cut the query execution cost in half.If COID is the primary key, then this statement, D.COID IS NOT NULL, is redundant. You can't have a null primary key.
After that, I'd need to see the execution plan.
Thanks for the suggestion, I actually thought along the same lines yesterday, after I posted this. It's always good to get an independent confirmation though.
The COID is not the primary key, so we are OK there.
Here is what the query looks like after the changes:
SELECT
D.COID
,MIN( D.COTID )
FROM
dbo.tblName AS D
LEFT JOIN
dbo.tblName AS D2
ON
D.COID = D2.COID
WHERE
D.CCBID = 100000 AND
D.CCBID = D2.CCBID AND
D2.COID IS NULL AND
D.COID IS NOT NULL AND
D2.MCT = 'Y'
GROUP BY
D.COID
I will look at the execution plans next and send an update...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 4, 2008 at 9:25 am
You were using the LEFT JOIN to the derived table to test for existence of at least 1 record for a COID.
Try using NOT EXISTS ?
SELECT
D.COID
, MIN( D.COTID )
FROM
dbo.tblName AS D
WHERE D.CCBID = 100000
AND D.COID IS NOT NULL
AND NOT EXISTS (
SELECT *
FROM dbo.tblName AS D2
WHERE D2.CCBID = 100000
AND D2.MCT = 'Y'
AND D2.COID = D.COID
)
GROUP BY
D.COID
September 4, 2008 at 9:41 am
Marios Philippopoulos (9/4/2008)
SELECTD.COID
,MIN( D.COTID )
FROM
dbo.tblName AS D
LEFT JOIN
dbo.tblName AS D2
ON
D.COID = D2.COID
WHERE
D.CCBID = 100000 AND
D.CCBID = D2.CCBID AND
D2.COID IS NULL AND
D.COID IS NOT NULL AND
D2.MCT = 'Y'
GROUP BY
D.COID
This will NOT produce the same result!
You have a D2 in the WHERE clause, which makes the LEFT JOIN reduntant and act like an INNER JOIN instead.
And you only omit records that have mct = 'Y'.
Other records for same coID are still fetched.
Test my suggestion posted earlier.
N 56°04'39.16"
E 12°55'05.25"
September 4, 2008 at 9:47 am
Peso (9/4/2008)
Try this
SELECTcoID,
MIN(cotID)
FROMdbo.tblName
WHEREccbID = 100000
AND coID IS NOT NULL
GROUP BYcoID
HAVINGMAX(CASE WHEN mcT = 'Y' THEN 1 ELSE 0 END) = 0
N 56°04'39.16"
E 12°55'05.25"
September 4, 2008 at 11:20 am
Peso (9/4/2008)
Marios Philippopoulos (9/4/2008)
SELECTD.COID
,MIN( D.COTID )
FROM
dbo.tblName AS D
LEFT JOIN
dbo.tblName AS D2
ON
D.COID = D2.COID
WHERE
D.CCBID = 100000 AND
D.CCBID = D2.CCBID AND
D2.COID IS NULL AND
D.COID IS NOT NULL AND
D2.MCT = 'Y'
GROUP BY
D.COID
This will NOT produce the same result!
You have a D2 in the WHERE clause, which makes the LEFT JOIN reduntant and act like an INNER JOIN instead.
And you only omit records that have mct = 'Y'.
Other records for same coID are still fetched.
Test my suggestion posted earlier.
Oops, thank you, that explains the weird execution plan I got! 🙂
I will try your suggestion as soon as I finish something boring that has just come up, called work...
Back shortly.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 4, 2008 at 11:53 am
Grant Fritchey (9/4/2008)
First thing I see, do you reall need to do a left join to that sub-select. If I'm seeing the logic correctly, it's looking for any of the values that have MCT = 'Y' and then eliminating them. Why not simply have that in the WHERE statement of the main query and eliminate the sub-select entirely. That should cut the query execution cost in half.If COID is the primary key, then this statement, D.COID IS NOT NULL, is redundant. You can't have a null primary key.
After that, I'd need to see the execution plan.
I can't post the exec plan as XML. It shows up as blank on the screen once posted.
Is there another way?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 4, 2008 at 12:03 pm
Sure. Zip it up and attach it.
"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
September 4, 2008 at 12:06 pm
Save it to disk, zip it, and upload the zip file.
😎
September 4, 2008 at 1:30 pm
Here is the plan of the original query (pls see attached).
I will now try the suggested modified query and will post the plan shortly.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 4, 2008 at 3:58 pm
It's just dawned on me that the original query makes no sense!
The D and D2 tables are joined (left join) on the COID column, but then the filter "D2.CORPOID IS NULL AND D.CORPOID IS NOT NULL" negates the join! The query will never return any rows!
I need to talk to the developer to understand what they wanted to get out of this query.
Thanks all for your input!
Here is the original query again:
SELECT
D.COID
, MIN( D.COTID )
FROM
dbo.tblName AS D
LEFT JOIN (
SELECT
COID
FROM
dbo.tblName
WHERE
CCBID = 100000 AND
MCT = 'Y'
GROUP BY
COID
) AS D2
ON D.COID= D2.COID
WHERE
D.CCBID = 100000 AND
D2.COID IS NULL AND
D.COID IS NOT NULL
GROUP BY
D.COID
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 4, 2008 at 4:07 pm
>>but then the filter "D2.CORPOID IS NULL AND D.CORPOID IS NOT NULL" negates the join! The query will never return any rows!
Nope, that's not true in this case.
The check on D2.CORPOID IS NULL is how the query is implementing "NOT EXISTS" functionality.
When you want to perform "NOT EXISTS" in SQL there are several options, here are 3 of them:
1. NOT EXISTS ( subquery )
2. NOT IN ( subquery )
3. LEFT JOIN Table2 and check NULL on 1 of the columns in Table2
The query is written to use method #3. The condition D2.CORPOID IS NULL evaluates to True in cases where the LEFT JOIN does not join to any matching rows.
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply