Logical OR comparison between two queries hangs

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

  • 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')

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

  • 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
  • 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')

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

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

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

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

    _____________
    Code for TallyGenerator

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply