Prev: Building a string out of table columns values
Next: A new approach to learning from Knowledge Horizon
From: tshad on 30 Mar 2010 04:11
I have a Where clause that is doing 2 selects that I want to combine into
one.

The statement would be something like

SELECT Name, Address
FROM Work ws
WHERE
(
WS.SpaceID = (SELECT FixedSpaceID
FROM Work_Defaults
WHERE EID = @EID)
OR WS.SpaceID = (SELECT NonFixedSpaceID
FROM Work_Defaults
WHERE EID = @EID)
)

I was trying to figure out how to replace it with something like:

WS.WorkSpaceID = SELECT CASE WHEN FixedSpaceID IS NOT NULL
THEN
FixedSpaceID
WHEN
NonFixedSpaceID IS NOT NULL
THEN
NonFixedSpaceID END
FROM Work_Defaults
WHERE EID = @ID

The problem is that I want to compare ws.WorkSpaceID with both FixedSpaceID
and NonFixedSpaceID and if either match than the test is true.

I am trying to prevent the 2 SELECTs as I am getting about 150,000 reads on
the Work_Defaults.

Thanks,

Tom


From: Stefan Hoffmann on 30 Mar 2010 05:32
hi,

On 30.03.2010 10:11, tshad wrote:
> The problem is that I want to compare ws.WorkSpaceID with both FixedSpaceID
> and NonFixedSpaceID and if either match than the test is true.
>
> I am trying to prevent the 2 SELECTs as I am getting about 150,000 reads on
> the Work_Defaults.
Not sure if it is the better approach, you need to inspect its costs:

SELECT Name, Address
FROM Work ws
WHERE ws.SpaceID IN
(
SELECT wd.FixedSpaceID FROM Work_Defaults wd
WHERE wd.EID = @EID
UNION
SELECT wd.NonFixedSpaceID FROM Work_Defaults wd
WHERE wd.EID = @EID
)

or

SELECT Name, Address
FROM Work ws
WHERE EXISTS
(
SELECT 1 FROM Work_Defaults wd
WHERE
(wd.FixedSpaceID = ws.SpaceID
OR wd.NonFixedSpaceID = ws.SpaceID
)
AND wd.EID = @EID
)

I think the second approach with an covering index on EID using the
include clause for FixedSpaceID and NonFixedSpaceID should be
interesting for you:

http://msdn.microsoft.com/en-us/library/ms190806.aspx


mfG
--> stefan <--
From: Gert-Jan Strik on 30 Mar 2010 16:04
How about this?

SELECT Name, Address
FROM Work ws
WHERE EXISTS (
SELECT *
FROM Work_Defaults
WHERE EID = @EID
AND ws.SpaceID IN (FixedSpaceID, NonFixedSpaceID)
)

--
Gert-Jan


tshad wrote:
>
> I have a Where clause that is doing 2 selects that I want to combine into
> one.
>
> The statement would be something like
>
> SELECT Name, Address
> FROM Work ws
> WHERE
> (
> WS.SpaceID = (SELECT FixedSpaceID
> FROM Work_Defaults
> WHERE EID = @EID)
> OR WS.SpaceID = (SELECT NonFixedSpaceID
> FROM Work_Defaults
> WHERE EID = @EID)
> )
>
> I was trying to figure out how to replace it with something like:
>
> WS.WorkSpaceID = SELECT CASE WHEN FixedSpaceID IS NOT NULL
> THEN
> FixedSpaceID
> WHEN
> NonFixedSpaceID IS NOT NULL
> THEN
> NonFixedSpaceID END
> FROM Work_Defaults
> WHERE EID = @ID
>
> The problem is that I want to compare ws.WorkSpaceID with both FixedSpaceID
> and NonFixedSpaceID and if either match than the test is true.
>
> I am trying to prevent the 2 SELECTs as I am getting about 150,000 reads on
> the Work_Defaults.
>
> Thanks,
>
> Tom
From: tshad on 30 Mar 2010 16:25

"Stefan Hoffmann" <ste5an(a)ste5an.de> wrote in message
news:ueEipv%23zKHA.5936(a)TK2MSFTNGP04.phx.gbl...
> hi,
>
> On 30.03.2010 10:11, tshad wrote:
>> The problem is that I want to compare ws.WorkSpaceID with both
>> FixedSpaceID
>> and NonFixedSpaceID and if either match than the test is true.
>>
>> I am trying to prevent the 2 SELECTs as I am getting about 150,000 reads
>> on
>> the Work_Defaults.
> Not sure if it is the better approach, you need to inspect its costs:
>
> SELECT Name, Address
> FROM Work ws
> WHERE ws.SpaceID IN
> (
> SELECT wd.FixedSpaceID FROM Work_Defaults wd
> WHERE wd.EID = @EID
> UNION
> SELECT wd.NonFixedSpaceID FROM Work_Defaults wd
> WHERE wd.EID = @EID
> )
>
> or
>
> SELECT Name, Address
> FROM Work ws
> WHERE EXISTS
> (
> SELECT 1 FROM Work_Defaults wd
> WHERE
> (wd.FixedSpaceID = ws.SpaceID
> OR wd.NonFixedSpaceID = ws.SpaceID
> )
> AND wd.EID = @EID
> )
>
> I think the second approach with an covering index on EID using the
> include clause for FixedSpaceID and NonFixedSpaceID should be interesting
> for you:
>

Would do the trick.

Thanks,

Tom
> http://msdn.microsoft.com/en-us/library/ms190806.aspx
>
>
> mfG
> --> stefan <--


From: tshad on 30 Mar 2010 16:25
This would work.

Thanks,

Tom
"Gert-Jan Strik" <sorrytoomuchspamalready(a)xs4all.nl> wrote in message
news:4BB2596A.2440CA5B(a)xs4all.nl...
> How about this?
>
> SELECT Name, Address
> FROM Work ws
> WHERE EXISTS (
> SELECT *
> FROM Work_Defaults
> WHERE EID = @EID
> AND ws.SpaceID IN (FixedSpaceID, NonFixedSpaceID)
> )
>
> --
> Gert-Jan
>
>
> tshad wrote:
>>
>> I have a Where clause that is doing 2 selects that I want to combine into
>> one.
>>
>> The statement would be something like
>>
>> SELECT Name, Address
>> FROM Work ws
>> WHERE
>> (
>> WS.SpaceID = (SELECT FixedSpaceID
>> FROM Work_Defaults
>> WHERE EID = @EID)
>> OR WS.SpaceID = (SELECT NonFixedSpaceID
>> FROM Work_Defaults
>> WHERE EID = @EID)
>> )
>>
>> I was trying to figure out how to replace it with something like:
>>
>> WS.WorkSpaceID = SELECT CASE WHEN FixedSpaceID IS NOT NULL
>> THEN
>> FixedSpaceID
>> WHEN
>> NonFixedSpaceID IS NOT NULL
>> THEN
>> NonFixedSpaceID END
>> FROM Work_Defaults
>> WHERE EID = @ID
>>
>> The problem is that I want to compare ws.WorkSpaceID with both
>> FixedSpaceID
>> and NonFixedSpaceID and if either match than the test is true.
>>
>> I am trying to prevent the 2 SELECTs as I am getting about 150,000 reads
>> on
>> the Work_Defaults.
>>
>> Thanks,
>>
>> Tom


 |  Next  |  Last
Pages: 1 2
Prev: Building a string out of table columns values
Next: A new approach to learning from Knowledge Horizon