Query performance damaged due to an OR operator

Hi,
I am running the following query:
declare
@.SiteLogId int
set @.SiteLogId = 2833
SELECT
PCT_RPT_Mashines_V.MashineRemarks,
PCT_RPT_Mashines_V_1.MashineSDPDescription
PCT_RPT_Mashines_V_1.MashineRemarks
PCT_RPT_Mashines_V.PerId,
PCT_RPT_Mashines_V.PerOrderNumber
FROM
PCT_RPT_Mashines_V LEFT OUTER JOIN
PCT_RPT_Mashines_V PCT_RPT_Mashines_V_1 ON
PCT_RPT_Mashines_V.MashinePrevMashineId =
PCT_RPT_Mashines_V_1.MashineId
WHERE
PCT_RPT_Mashines_V.MashineSiteLogId = @.SiteLogId OR @.SiteLogId = -1
This query takes about 50 seconds to run. If I remove the OR part from the
WHERE clause, the running time is reduced to 5 seconds, although there
shouldn't be any significance to the OR, since @.SiteLogId was assigned a
value (2833).
Do you have any explanation for this ?
How can I use the OR part, and still keep a good performance ?
And another thing:
If I remove these two lines:
PCT_RPT_Mashines_V_1.MashineSDPDescription
PCT_RPT_Mashines_V_1.MashineRemarks
From the SELECT clause, the query takes 50 seconds to run, with or without
the OR. Why is that happening ?
Thanks,
AvishayAvishay,shalom
> If I remove these two lines:
> PCT_RPT_Mashines_V_1.MashineSDPDescription
> PCT_RPT_Mashines_V_1.MashineRemarks
Take a look at an execution plan . Probably , you don't have indexes on
these columns and SQL Server is not be able to use
"covering indexes" to create a more efficient plan.
Can you try
IF @.SiteLogId = -1
--Here is your SELECT
What happened ?
"Avishay Ben-Zvi" <avishay@.community.nospam> wrote in message
news:706D354E-8C96-465C-A951-DEAE0B5DE433@.microsoft.com...
> Hi,
> I am running the following query:
> declare
> @.SiteLogId int
> set @.SiteLogId = 2833
> SELECT
> PCT_RPT_Mashines_V.MashineRemarks,
> PCT_RPT_Mashines_V_1.MashineSDPDescription
> PCT_RPT_Mashines_V_1.MashineRemarks
> PCT_RPT_Mashines_V.PerId,
> PCT_RPT_Mashines_V.PerOrderNumber
>
> FROM
> PCT_RPT_Mashines_V LEFT OUTER JOIN
> PCT_RPT_Mashines_V PCT_RPT_Mashines_V_1 ON
> PCT_RPT_Mashines_V.MashinePrevMashineId =
> PCT_RPT_Mashines_V_1.MashineId
> WHERE
> PCT_RPT_Mashines_V.MashineSiteLogId = @.SiteLogId OR @.SiteLogId = -1
>
> This query takes about 50 seconds to run. If I remove the OR part from the
> WHERE clause, the running time is reduced to 5 seconds, although there
> shouldn't be any significance to the OR, since @.SiteLogId was assigned a
> value (2833).
> Do you have any explanation for this ?
> How can I use the OR part, and still keep a good performance ?
> And another thing:
> If I remove these two lines:
> PCT_RPT_Mashines_V_1.MashineSDPDescription
> PCT_RPT_Mashines_V_1.MashineRemarks
> From the SELECT clause, the query takes 50 seconds to run, with or without
> the OR. Why is that happening ?
> Thanks,
> Avishay
>|||examnotes (avishay@.community.nospam) writes:
> declare
> @.SiteLogId int
> set @.SiteLogId = 2833
> SELECT
> PCT_RPT_Mashines_V.MashineRemarks,
> PCT_RPT_Mashines_V_1.MashineSDPDescription
> PCT_RPT_Mashines_V_1.MashineRemarks
> PCT_RPT_Mashines_V.PerId,
> PCT_RPT_Mashines_V.PerOrderNumber
>
> FROM
> PCT_RPT_Mashines_V LEFT OUTER JOIN
> PCT_RPT_Mashines_V PCT_RPT_Mashines_V_1 ON
> PCT_RPT_Mashines_V.MashinePrevMashineId =
> PCT_RPT_Mashines_V_1.MashineId
> WHERE
> PCT_RPT_Mashines_V.MashineSiteLogId = @.SiteLogId OR @.SiteLogId
= -1
>
> This query takes about 50 seconds to run. If I remove the OR part from the
> WHERE clause, the running time is reduced to 5 seconds, although there
> shouldn't be any significance to the OR, since @.SiteLogId was assigned a
> value (2833).
I matters a whole lot, because SQL Server builds the query plan for the
entire batch, and it has no idea of what the actual run-time value
@.SiteLogId will have. Thus, it must assume the worst.
If you want the index to be used if you have a value for @.SiteLogId,
the you need to write two queries, one with and one without the condition.
This changes in SQL 2005. Here you can add the hint OPTION (RECOMPILE)
which forces a statement recompile, and this enables SQL Server to look
at the actual parameter value.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Avishay,
SELECT
PCT_RPT_Mashines_V.MashineRemarks,
PCT_RPT_Mashines_V_1.MashineSDPDescription
PCT_RPT_Mashines_V_1.MashineRemarks
PCT_RPT_Mashines_V.PerId,
PCT_RPT_Mashines_V.PerOrderNumber
FROM
PCT_RPT_Mashines_V LEFT OUTER JOIN
PCT_RPT_Mashines_V PCT_RPT_Mashines_V_1 ON
PCT_RPT_Mashines_V.MashinePrevMashineId =
PCT_RPT_Mashines_V_1.MashineId
WHERE
@.SiteLogId = -1
union all
SELECT
PCT_RPT_Mashines_V.MashineRemarks,
PCT_RPT_Mashines_V_1.MashineSDPDescription
PCT_RPT_Mashines_V_1.MashineRemarks
PCT_RPT_Mashines_V.PerId,
PCT_RPT_Mashines_V.PerOrderNumber
FROM
PCT_RPT_Mashines_V LEFT OUTER JOIN
PCT_RPT_Mashines_V PCT_RPT_Mashines_V_1 ON
PCT_RPT_Mashines_V.MashinePrevMashineId =
PCT_RPT_Mashines_V_1.MashineId
WHERE
PCT_RPT_Mashines_V.MashineSiteLogId = @.SiteLogId
Ilya
"Avishay Ben-Zvi" <avishay@.community.nospam> wrote in message
news:706D354E-8C96-465C-A951-DEAE0B5DE433@.microsoft.com...
> Hi,
> I am running the following query:
> declare
> @.SiteLogId int
> set @.SiteLogId = 2833
> SELECT
> PCT_RPT_Mashines_V.MashineRemarks,
> PCT_RPT_Mashines_V_1.MashineSDPDescription
> PCT_RPT_Mashines_V_1.MashineRemarks
> PCT_RPT_Mashines_V.PerId,
> PCT_RPT_Mashines_V.PerOrderNumber
>
> FROM
> PCT_RPT_Mashines_V LEFT OUTER JOIN
> PCT_RPT_Mashines_V PCT_RPT_Mashines_V_1 ON
> PCT_RPT_Mashines_V.MashinePrevMashineId =
> PCT_RPT_Mashines_V_1.MashineId
> WHERE
> PCT_RPT_Mashines_V.MashineSiteLogId = @.SiteLogId OR @.SiteLogId = -1
>
> This query takes about 50 seconds to run. If I remove the OR part from the
> WHERE clause, the running time is reduced to 5 seconds, although there
> shouldn't be any significance to the OR, since @.SiteLogId was assigned a
> value (2833).
> Do you have any explanation for this ?
> How can I use the OR part, and still keep a good performance ?
> And another thing:
> If I remove these two lines:
> PCT_RPT_Mashines_V_1.MashineSDPDescription
> PCT_RPT_Mashines_V_1.MashineRemarks
> From the SELECT clause, the query takes 50 seconds to run, with or without
> the OR. Why is that happening ?
> Thanks,
> Avishay
>|||Hi Uri,
Thanks for the prompt reply.
I tried also using IF on the beginning of the query. This way it took about
10 seconds (still quite long compared to omitting the OR). In addition, it
will be very hard to manage the query this way (every line of code will be
written twice - or more if I do the same for each one of the parameters).
Other suggestions?
Avishay
"Uri Dimant" wrote:

> Avishay,shalom
> Take a look at an execution plan . Probably , you don't have indexes on
> these columns and SQL Server is not be able to use
> "covering indexes" to create a more efficient plan.
> Can you try
> IF @.SiteLogId = -1
> --Here is your SELECT
>
> What happened ?
>
>
> "Avishay Ben-Zvi" <avishay@.community.nospam> wrote in message
> news:706D354E-8C96-465C-A951-DEAE0B5DE433@.microsoft.com...
>
>|||Hi Ilya,
Unfortunately, putting these two queries in Union, executes even slower than
using IF on the beginning of the query (>40 seconds). It also forces
duplicate management of the query and having this method for lots of
parameters is not an option.
Thanks for your reply.
Avishay
"Ilya Margolin" wrote:

> Avishay,
> SELECT
> PCT_RPT_Mashines_V.MashineRemarks,
> PCT_RPT_Mashines_V_1.MashineSDPDescription
> PCT_RPT_Mashines_V_1.MashineRemarks
> PCT_RPT_Mashines_V.PerId,
> PCT_RPT_Mashines_V.PerOrderNumber
>
> FROM
> PCT_RPT_Mashines_V LEFT OUTER JOIN
> PCT_RPT_Mashines_V PCT_RPT_Mashines_V_1 ON
> PCT_RPT_Mashines_V.MashinePrevMashineId =
> PCT_RPT_Mashines_V_1.MashineId
> WHERE
> @.SiteLogId = -1
> union all
> SELECT
> PCT_RPT_Mashines_V.MashineRemarks,
> PCT_RPT_Mashines_V_1.MashineSDPDescription
> PCT_RPT_Mashines_V_1.MashineRemarks
> PCT_RPT_Mashines_V.PerId,
> PCT_RPT_Mashines_V.PerOrderNumber
>
> FROM
> PCT_RPT_Mashines_V LEFT OUTER JOIN
> PCT_RPT_Mashines_V PCT_RPT_Mashines_V_1 ON
> PCT_RPT_Mashines_V.MashinePrevMashineId =
> PCT_RPT_Mashines_V_1.MashineId
> WHERE
> PCT_RPT_Mashines_V.MashineSiteLogId = @.SiteLogId
>
> Ilya
> "Avishay Ben-Zvi" <avishay@.community.nospam> wrote in message
> news:706D354E-8C96-465C-A951-DEAE0B5DE433@.microsoft.com...
>
>|||Union all? Those aren't cummulative conditions. Plain old "union" will do.
ML|||ML,
Union without all option forces distinct values.
Ilya
"ML" <ML@.discussions.microsoft.com> wrote in message
news:FEF8E63F-394A-4509-9410-8D635649240F@.microsoft.com...
> Union all? Those aren't cummulative conditions. Plain old "union" will do.
>
> ML|||Let me demonstrate:
use pubs
go
-- OR
select *
from dbo.sales
where (stor_id = 7067 or title_id = 'PS2091')
-- UNION ALL
select *
from dbo.sales
where (stor_id = 7067)
union all
select *
from dbo.sales
where (title_id = 'PS2091')
-- UNION
select *
from dbo.sales
where (stor_id = 7067)
union
select *
from dbo.sales
where (title_id = 'PS2091')
Compare the results. :)
ML|||> In addition, it
> will be very hard to manage the query this way (every line of code will be
> written twice - or more if I do the same for each one of the parameters).
you can create a viewsql