March 14, 2017 at 1:08 pm
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?
March 14, 2017 at 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')
March 14, 2017 at 1:39 pm
Chris Harshman - Tuesday, March 14, 2017 1:31 PMmaybe 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.
March 14, 2017 at 1:46 pm
mahrajmca - Tuesday, March 14, 2017 1:39 PMChris Harshman - Tuesday, March 14, 2017 1:31 PMmaybe 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
March 14, 2017 at 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')
March 14, 2017 at 2:00 pm
Lynn Pettis - Tuesday, March 14, 2017 1:50 PMEach 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.
March 14, 2017 at 2:01 pm
Luis Cazares - Tuesday, March 14, 2017 1:46 PMIs 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.
March 14, 2017 at 2:06 pm
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.
March 20, 2017 at 4:48 am
SELECT 1 WHERE EXISTS (
SELECT 1 FROM View1 WHERE field1 = 'Y'
union all
SELECT 1 FROM View2 WHERE field1 = 'Y'
)
_____________
Code for TallyGenerator
March 28, 2017 at 3:16 pm
Sergiy - Monday, March 20, 2017 4:48 AMSELECT 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