Logical OR comparison between two queries hangs

• mahrajmca

  SSC-Addicted

  Points: 400

  More actions

  March 14, 2017 at 1:08 pm

  #321340

  Hi,

  Faced an interesting problem.
  Following is the Query am using.

  SELECT 1 WHERE(
  ((SELECT count(1) FROM View1 WHERE field1 = 'Y') > 0)
  OR
  ((SELECT count(1) FROM View2 WHERE field1 = 'Y') > 0)
  )

  This keeps running though there are only few thousand records in both views. Interestingly, if I use AND instead of OR am getting the result instantly.
  Any thoughts?

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  March 14, 2017 at 1:31 pm

  #1933129

  maybe it could be simplified like this instead of physically counting all the records?
  
SELECT 1 WHERE(
EXISTS (SELECT NULL FROM View1 WHERE field1 = 'Y')
OR
EXISTS (SELECT NULL FROM View2 WHERE field1 = 'Y')


• mahrajmca

  SSC-Addicted

  Points: 400

  More actions

  March 14, 2017 at 1:39 pm

  #1933131

  Chris Harshman - Tuesday, March 14, 2017 1:31 PM

  maybe it could be simplified like this instead of physically counting all the records?
  
SELECT 1 WHERE(
EXISTS (SELECT NULL FROM View1 WHERE field1 = 'Y')
OR
EXISTS (SELECT NULL FROM View2 WHERE field1 = 'Y')


  Thanks for the reply Chris. But no change in result. The query hangs still in use of OR.

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  March 14, 2017 at 1:46 pm

  #1933133

  mahrajmca - Tuesday, March 14, 2017 1:39 PM

  Chris Harshman - Tuesday, March 14, 2017 1:31 PM

  maybe it could be simplified like this instead of physically counting all the records?
  
SELECT 1 WHERE(
EXISTS (SELECT NULL FROM View1 WHERE field1 = 'Y')
OR
EXISTS (SELECT NULL FROM View2 WHERE field1 = 'Y')


  Thanks for the reply Chris. But no change in result. The query hangs still in use of OR.

  Is there an index on field1 for both views?
  Check this article to get better help instead of simple guesses

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 14, 2017 at 1:50 pm

  #1933134

  Each of these run?
  
SELECT 1 WHERE
EXISTS (SELECT NULL FROM View1 WHERE field1 = 'Y')


  
SELECT 1 WHERE
EXISTS (SELECT NULL FROM View2 WHERE field1 = 'Y')


• mahrajmca

  SSC-Addicted

  Points: 400

  More actions

  March 14, 2017 at 2:00 pm

  #1933137

  Lynn Pettis - Tuesday, March 14, 2017 1:50 PM

  Each of these run?
  
SELECT 1 WHERE
EXISTS (SELECT NULL FROM View1 WHERE field1 = 'Y')


  
SELECT 1 WHERE
EXISTS (SELECT NULL FROM View2 WHERE field1 = 'Y')


  No delay when executed separately.

• mahrajmca

  SSC-Addicted

  Points: 400

  More actions

  March 14, 2017 at 2:01 pm

  #1933138

  Luis Cazares - Tuesday, March 14, 2017 1:46 PM

  Is there an index on field1 for both views?
  Check this article to get better help instead of simple guesses

  Looks like this article may help. Let me go through completely.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 14, 2017 at 2:06 pm

  #1933141

  I did the following and all run fine:
  
create view dbo.view1 as
select * from sys.objects;
go
create view dbo.view2 as
select * from sys.objects;
go

  
  select 1 where
  exists(select 1 from dbo.view1 where type = 'P')

  select 1 where
  exists(select 1 from dbo.view2 where type = 'P')

  select 1 where
  exists(select 1 from dbo.view1 where type = 'P')
  or
  exists(select 1 from dbo.view2 where type = 'P')

  Run your code again that hangs and in another window run sp_who2 and see if anything is being blocked.
  

• Sergiy

  SSC Guru

  Points: 110206

  More actions

  March 20, 2017 at 4:48 am

  #1933954

  SELECT 1 WHERE EXISTS (
SELECT 1 FROM View1 WHERE field1 = 'Y'
union all
SELECT 1 FROM View2 WHERE field1 = 'Y'
)

  _____________
  Code for TallyGenerator

• whenriksen

  SSCarpal Tunnel

  Points: 4752

  More actions

  March 28, 2017 at 3:16 pm

  #1935394

  Sergiy - Monday, March 20, 2017 4:48 AM

  SELECT 1 WHERE EXISTS (
SELECT 1 FROM View1 WHERE field1 = 'Y'
union all
SELECT 1 FROM View2 WHERE field1 = 'Y'
)

  Would getting Top 1 from each view minimize how much the Union All has to concatenate?

  SELECT 1 WHERE EXISTS (
SELECT TOP (1) 1 FROM View1 WHERE field1 = 'Y'
union all
SELECT TOP (1) 1 FROM View2 WHERE field1 = 'Y'
)

  Wes
  (A solid design is always preferable to a creative workaround)