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

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?

avatar image By tbrickle 1 asked Apr 19 at 08:22 PM
more ▼
(comments are locked)
avatar image SDyckes2 Apr 19 at 08:31 PM

Could you please attach the PESession file? Thanks!

avatar image tbrickle Apr 19 at 08:55 PM

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).

avatar image tbrickle Apr 19 at 09:04 PM

I entered the same issue with the file attached.

10|10000 characters needed characters left

1 answer: sort voted first

--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

avatar image By tbrickle 1 answered Apr 19 at 08:48 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x494
x116
x30
x19

asked: Apr 19 at 08:22 PM

Seen: 23 times

Last Updated: Apr 19 at 09:04 PM