Interesting observation about the DG performance of two queries

Below are two queries. They do the same thing, and both result in 0 rows. The result is correct. So...from DataGrip...the first query consistently returns results in less than a second. Time and time, again. The second query consistently returns results in 5 or 6 seconds. Time and time, again.

I took both queries to SSMS and both queries consistently return results in about 60/70ms, which is consistent with the first query in DataGrip. I did an EXPLAIN against the queries and the visual execution plans are roughly identical. The first join has an extra filter step, and the hash match is a right outer join. The second subquery has a hash match right anti semi join (whatever that is.) All else is identical.

So what is happening in DG that takes so much more time to process the second query? It is not SqlServer performance, given the SSMS results. It's weird. 

dvn

 

SELECT mst.*
FROM I93FILE.dbo.PYCHKH mst
left join I93FILE.dbo.tbPychkhProcessed p on P.PychkhId = mst.id
WHERE CHCKDT >= I93FILE.dbo.DateToJulian(convert(date,'2017-09-06'))
AND CHCKDT < I93FILE.dbo.DateToJulian(dateadd(dd, 4, convert(date,'2017-09-06') ))
AND p.PychkhId is null
ORDER BY CHEMP#;
-- less than a second

SELECT *
FROM I93FILE.dbo.PYCHKH
WHERE CHCKDT >= I93FILE.dbo.DateToJulian(convert(date,'2017-09-06'))
AND CHCKDT < I93FILE.dbo.DateToJulian(dateadd(dd, 4, convert(date,'2017-09-06') ))
AND ID NOT IN (SELECT PYCHKHId FROM I93File.dbo.tbPyChkHProcessed)
ORDER BY CHEMP#;
-- about 5 or 6s
0

Please sign in to leave a comment.