Why does the same query inside a IF EXISTS statement not perform the same.

tbrickle 2018-04-19 20:22:20

The query outside the if exists, returns in 1 seconds.

The query inside the if exists, takes 1.5 minutes to complete.

Can you help me understand what is the possible issue and how I might correct this issue?

SDyckes2 2018-04-19 20:31:46
Could you please attach the PESession file? Thanks!
tbrickle 2018-04-19 20:55:48
It is not letting attach the file.

We're sorry, but you are not allowed to answer multiple times to a question.
We're sorry, but you do not have permission to do the activity you attempted. If you believe this to be in error, please contact the site administrator(s).

tbrickle 2018-04-19 21:04:19
I entered the same issue with the file attached.
tbrickle 2018-04-19 20:48:54
–This runs is seconds below, but when put within and if exist statement never returns.

(SELECT distinct
gc.Contract_Code
FROM dbo.Grower_Contract GC
INNER JOIN dbo.Grower_Contract_Amendment GCA
ON GCA.Grower_Contract_Key = GC.Grower_Contract_Key
INNER JOIN dbo.Grower_Contract_Line_Amendment GCLA
ON GCLA.Grower_Contract_Amendment_Key = GCA.Grower_Contract_Amendment_Key
INNER JOIN dbo.Grower_Contract_Line_Amendment_SYN GCLAS
ON GCLAS.Grower_Contract_Line_Amendment_Key = GCLA.Grower_Contract_Line_Amendment_Key
INNER JOIN dbo.uvw_Grower_Contract_Amendment_Latest_Status STAT
ON STAT.Grower_Contract_Amendment_Key = GCA.Grower_Contract_Amendment_Key
INNER JOIN dbo.Contract_Status CS
ON CS.Contract_Status_Key = STAT.Contract_Status_Key
WHERE gc.Crop_Year_Key IN (5, 7)
GROUP BY gc.Contract_Code, cs.Contract_Status, gca.grower_Contract_amendment_Key, gca.Acres_Contracted
HAVING SUM(gclas.Acres_Contracted) <> gca.Acres_Contracted)

–It comes back immediately

–If I run the next, it never returns

IF EXISTS
(SELECT distinct
gc.Contract_Code
FROM dbo.Grower_Contract GC
INNER JOIN dbo.Grower_Contract_Amendment GCA
ON GCA.Grower_Contract_Key = GC.Grower_Contract_Key
INNER JOIN dbo.Grower_Contract_Line_Amendment GCLA
ON GCLA.Grower_Contract_Amendment_Key = GCA.Grower_Contract_Amendment_Key
INNER JOIN dbo.Grower_Contract_Line_Amendment_SYN GCLAS
ON GCLAS.Grower_Contract_Line_Amendment_Key = GCLA.Grower_Contract_Line_Amendment_Key
INNER JOIN dbo.uvw_Grower_Contract_Amendment_Latest_Status STAT
ON STAT.Grower_Contract_Amendment_Key = GCA.Grower_Contract_Amendment_Key
INNER JOIN dbo.Contract_Status CS
ON CS.Contract_Status_Key = STAT.Contract_Status_Key
WHERE gc.Crop_Year_Key IN (5, 7)
GROUP BY gc.Contract_Code, cs.Contract_Status, gca.grower_Contract_amendment_Key, gca.Acres_Contracted
HAVING SUM(gclas.Acres_Contracted) <> gca.Acres_Contracted)
–BEGIN
PRINT '1'
–END