October 29, 2008 at 7:45 am
I know this topic has been hit on a lot but came across an article and wanted to get some more input. The article is describing a 'cross join' I put it in quotes because it really isn't and even more interesting when re-written with an inner join the query plan was identicle. Then I wanted to see if I could improve and know for sure many here may even do better and wasn't sure about scalability of either. So the link http://www.sql-server-performance.com/articles/per/Compare_Dates_p1.aspx I re-wrote it with inner join syntax SELECT *,(SELECT CASE WHEN (SUM(CASE WHEN B.DEDate =1
THEN 1 ELSE 0 END AS IsnotProper from PersonRecord B INNER JOIN PersonRecord C ON C.PersonId = B.PersonId
WHERE B.PersonID=A.PersonID AND B.VERSION<>C.VERSION
AND B.VERSION<C.VERSION) AS [Is Not In Proper Order]
from PersonRecord A
GO
to prove that it is in fact an inner join. Then of course we all want to improve on code :hehe: so I tried this where the execution plan was slightly better.SELECT PersonRecord.*, COALESCE(NotProperOrderIds.NotInOrder, 0) [Is Not In Proper Order]
FROM PersonRecord
LEFT OUTER JOIN (
SELECT NotProperOrderRs.PersonId, 1 NotInOrder
FROM PersonRecord NotProperOrderRs
WHERE EXISTS(
SELECT *
FROM PersonRecord ex
WHERE
ex.PersonId = NotProperOrderRs.PersonId AND
ex.version > NotProperOrderRs.version AND
ex.DEDate < NotProperOrderRs.DEDate
)
GROUP BY PersonId
) NotProperOrderIds ON
NotProperOrderIds.PersonId = PersonRecord.PersonId
GO
and in case no one really reads the article or is lost, the original code from the site:
In the output it will display all the records with a flag called Is Im Proper. If the flag is 1 that means there is a problem with the record. Otherwise, the PersonID versions are in a proper order.
So, instead of using loops and cursors it handles everything in a single query.
IF OBJECT_ID('PersonRecord') IS NOT NULL
DROP TABLE PersonRecord
GO
CREATE TABLE PersonRecord ( PersonID int, Version int,DEDate datetime)
go
INSERT INTO PersonRecord VALUES(1,0,'03/10/2000')
INSERT INTO PersonRecord VALUES(1,1,'03/16/2000')
INSERT INTO PersonRecord VALUES(1,2,'03/19/2000')
INSERT INTO PersonRecord VALUES(1,3,'03/18/2000')
INSERT INTO PersonRecord VALUES(1,4,'03/17/2000')
INSERT INTO PersonRecord VALUES(2,0,'02/10/2000')
INSERT INTO PersonRecord VALUES(2,1,'02/11/2000')
INSERT INTO PersonRecord VALUES(2,2,'02/18/2000')
INSERT INTO PersonRecord VALUES(3,0,'03/25/2000')
INSERT INTO PersonRecord VALUES(3,1,'03/23/2000')
INSERT INTO PersonRecord VALUES(3,2,'03/26/2000')
INSERT INTO PersonRecord VALUES(3,3,'03/30/2000')
INSERT INTO PersonRecord VALUES(4,0,'08/19/2000')
INSERT INTO PersonRecord VALUES(4,1,'08/20/2000')
INSERT INTO PersonRecord VALUES(4,2,'08/24/2000')
INSERT INTO PersonRecord VALUES(4,3,'08/23/2000')
go
SELECT *,(SELECT CASE WHEN (SUM(CASE WHEN B.DEDate =1
THEN 1 ELSE 0 END AS IsnotProper from PersonRecord B CROSS JOIN PersonRecord C
WHERE B.PersonID=A.PersonID AND C.PersonID=A.PersonID AND B.VERSION<>C.VERSION
AND B.VERSION<C.VERSION) AS [Is Not In Proper Order]
from PersonRecord A
GO
I was just interested in some feedback and other ideas for solutions. Enjoy!
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply