March 17, 2017 at 10:15 am
This is somethign that has been running in our Production system for many years and the more I look at it the more I realize I need to correct it.
I prefer to run such stuff to see what it does and make it better, but this take over 14 minutes, before I cancelled it, and I don't know yet would it would return.
Here is the SQL that I speak of and it is of the last WHERE statement that causes my concern.
SELECT
c.ID
FROM Tbl1 c
JOIN Tbl2 w ON c.ParentID=w.ParentID
WHERE w.Fld1 = 'Process'
AND c.Fld2 NOT LIKE '%INFO'
AND c.Fld3 NOT IN (0,4)
AND (SELECT COUNT(*) FROM Tbl1 WHERE Tbl1.ID=c.ID ) = 0
If someone could please what they know about using code like this and am I correct in thinking that it should be replaced with a LEFT JOIN WHERE LEFT JOIN ID is NULL ??
Thanks..
March 17, 2017 at 10:26 am
It will never return any rows, because a record will always match itself, so the COUNT() will always be at least 1 (assuming that Tbl1.ID is not nullable). Assuming that Tbl1.ID has a unique/primary key, then the COUNT(*) will always be 1.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 17, 2017 at 10:33 am
Budd - Friday, March 17, 2017 10:15 AMThis is somethign that has been running in our Production system for many years and the more I look at it the more I realize I need to correct it.
I prefer to run such stuff to see what it does and make it better, but this take over 14 minutes, before I cancelled it, and I don't know yet would it would return.
Here is the SQL that I speak of and it is of the last WHERE statement that causes my concern.
SELECT
c.ID
FROM Tbl1 c
JOIN Tbl2 w ON c.ParentID=w.ParentID
WHERE w.Fld1 = 'Process'
AND c.Fld2 NOT LIKE '%INFO'
AND c.Fld3 NOT IN (0,4)
AND (SELECT COUNT(*) FROM Tbl1 WHERE Tbl1.ID=c.ID ) = 0
If someone could please what they know about using code like this and am I correct in thinking that it should be replaced with a LEFT JOIN WHERE LEFT JOIN ID is NULL ??
Thanks..
Sorry I miss labeled Tbl3 as Tbl1 - This is what I meant to post, and you should understand that Tbl3 is (I'm not sure how to describe this perfectly) a reporting table build from Tbl1 through a view and many scalar functions
SELECT
c.ID
FROM Tbl1 c
JOIN Tbl2 w ON c.ParentID=w.ParentID
WHERE w.Fld1 = 'Process'
AND c.Fld2 NOT LIKE '%INFO'
AND c.Fld3 NOT IN (0,4)
AND (SELECT COUNT(*) FROM Tbl3 WHERE Tbl1.ID=c.ID ) = 0
March 17, 2017 at 10:47 am
Budd - Friday, March 17, 2017 10:33 AMBudd - Friday, March 17, 2017 10:15 AMThis is somethign that has been running in our Production system for many years and the more I look at it the more I realize I need to correct it.
I prefer to run such stuff to see what it does and make it better, but this take over 14 minutes, before I cancelled it, and I don't know yet would it would return.
Here is the SQL that I speak of and it is of the last WHERE statement that causes my concern.
SELECT
c.ID
FROM Tbl1 c
JOIN Tbl2 w ON c.ParentID=w.ParentID
WHERE w.Fld1 = 'Process'
AND c.Fld2 NOT LIKE '%INFO'
AND c.Fld3 NOT IN (0,4)
AND (SELECT COUNT(*) FROM Tbl1 WHERE Tbl1.ID=c.ID ) = 0
If someone could please what they know about using code like this and am I correct in thinking that it should be replaced with a LEFT JOIN WHERE LEFT JOIN ID is NULL ??
Thanks..Sorry I miss labeled Tbl3 as Tbl1 - This is what I meant to post, and you should understand that Tbl3 is (I'm not sure how to describe this perfectly) a reporting table build from Tbl1 through a view and many scalar functions
SELECT
c.ID
FROM Tbl1 c
JOIN Tbl2 w ON c.ParentID=w.ParentID
WHERE w.Fld1 = 'Process'
AND c.Fld2 NOT LIKE '%INFO'
AND c.Fld3 NOT IN (0,4)
AND (SELECT COUNT(*) FROM Tbl3 WHERE Tbl3.ID=c.ID ) = 0
March 19, 2017 at 8:42 pm
AND NOT EXISTS (SELECT * FROM Tbl3 WHERE Tbl3.ID=c.ID )
_____________
Code for TallyGenerator
March 20, 2017 at 8:15 am
Budd - Friday, March 17, 2017 10:33 AMBudd - Friday, March 17, 2017 10:15 AMThis is somethign that has been running in our Production system for many years and the more I look at it the more I realize I need to correct it.
I prefer to run such stuff to see what it does and make it better, but this take over 14 minutes, before I cancelled it, and I don't know yet would it would return.
Here is the SQL that I speak of and it is of the last WHERE statement that causes my concern.
SELECT
c.ID
FROM Tbl1 c
JOIN Tbl2 w ON c.ParentID=w.ParentID
WHERE w.Fld1 = 'Process'
AND c.Fld2 NOT LIKE '%INFO'
AND c.Fld3 NOT IN (0,4)
AND (SELECT COUNT(*) FROM Tbl1 WHERE Tbl1.ID=c.ID ) = 0
If someone could please what they know about using code like this and am I correct in thinking that it should be replaced with a LEFT JOIN WHERE LEFT JOIN ID is NULL ??
Thanks..Sorry I miss labeled Tbl3 as Tbl1 - This is what I meant to post, and you should understand that Tbl3 is (I'm not sure how to describe this perfectly) a reporting table build from Tbl1 through a view and many scalar functions
SELECT
c.ID
FROM Tbl1 c
JOIN Tbl2 w ON c.ParentID=w.ParentID
WHERE w.Fld1 = 'Process'
AND c.Fld2 NOT LIKE '%INFO'
AND c.Fld3 NOT IN (0,4)
AND (SELECT COUNT(*) FROM Tbl3 WHERE Tbl1.ID=c.ID ) = 0
The COUNT(*) requires you to read all of the relevant records before making a determination, whereas the NOT EXISTS version allows you to short circuit as soon as you find ANY record.
That being said, the real problem is more likely to lie in the "many scalar functions."
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
March 20, 2017 at 8:34 am
Thank you for all replies. (especially after all my mistakes in posting this)
Tbl3 is a table based on data from Tbl1 and a view with many scalar functions, so although there are functions they are not directly involved in this process.
I’m looking for confirmation that this (which Runs a long time)
SELECT
c.ID
FROM Tbl1 c
JOIN Tbl2 w ON c.ParentID=w.ParentID
WHERE w.Fld1 = 'Process'
AND c.Fld2 NOT LIKE '%INFO'
AND c.Fld3 NOT IN (0,4)
AND (SELECT COUNT(*) FROM Tbl3 WHERE Tbl3.ID=c.ID ) = 0
Is essentially the same as this (which is done in less than 1 Second)
SELECT
c.ID
FROM Tbl1 c
JOIN Tbl2 w ON c.ParentID=w.ParentID
LEFT JOIN Tbl3 ON c.ID = Tbl3.ID
WHERE w.Fld1 = 'Process'
AND c.Fld2 NOT LIKE '%INFO'
AND c.Fld3 NOT IN (0,4)
AND Tbl3.ID IS NULL
March 28, 2017 at 3:11 pm
Budd - Monday, March 20, 2017 8:34 AMThank you for all replies. (especially after all my mistakes in posting this)Tbl3 is a table based on data from Tbl1 and a view with many scalar functions, so although there are functions they are not directly involved in this process.
I’m looking for confirmation that this (which Runs a long time)
SELECT
c.ID
FROM Tbl1 c
JOIN Tbl2 w ON c.ParentID=w.ParentID
WHERE w.Fld1 = 'Process'
AND c.Fld2 NOT LIKE '%INFO'
AND c.Fld3 NOT IN (0,4)
AND (SELECT COUNT(*) FROM Tbl3 WHERE Tbl3.ID=c.ID ) = 0Is essentially the same as this (which is done in less than 1 Second)
SELECT
c.ID
FROM Tbl1 c
JOIN Tbl2 w ON c.ParentID=w.ParentID
LEFT JOIN Tbl3 ON c.ID = Tbl3.ID
WHERE w.Fld1 = 'Process'
AND c.Fld2 NOT LIKE '%INFO'
AND c.Fld3 NOT IN (0,4)
AND Tbl3.ID IS NULL
I think they would output the same result, but using a join for an existence check is generally not the best way to go.
If you are only returning a column from the C table, you may want to consider removing the join to W as well.SELECT c.ID
FROM Tbl1 AS c
WHERE EXISTS ( SELECT *
FROM Tbl2 AS w
WHERE c.ParentID = w.ParentID
AND w.Fld1 = 'Process'
)
AND NOT EXISTS (SELECT * FROM Tbl3 WHERE c.ID = Tbl3.ID)
AND c.Fld2 NOT LIKE '%INFO'
AND c.Fld3 NOT IN ( 0, 4 );
Wes
(A solid design is always preferable to a creative workaround)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply