SQL Anywhere database - Performance question...

Author

Performance question...

Troy Coombs

2005-06-29, 7:23 am

Using ASA 8.0.2.

Based on the below Select statement does the extra

"AND (manufacturer = :ls_manufacturer OR :ls_manufacturer IS NULL)

AND (rooftop = :ll_rooftop OR :ll_rooftop IS NULL)"

in the WHERE clause result in a performance hit then if it wasn't there?
It's already built into the view 'view_dev_security_p
ermissions' but
unfortuately got also included in the where clause on many dw's throughout
the system.

We don't have enough data in the related tables yet to get any clear stats
from the ISQL plan.

SELECT servs_id, servs_name, servs_oemrt_id

FROM services, view_dev_security_pe
rmissions

WHERE employee = :ls_employee

AND (manufacturer = :ls_manufacturer OR :ls_manufacturer IS NULL)

AND (rooftop = :ll_rooftop OR :ll_rooftop IS NULL)

AND servs_oemrt_id = oemrt_id

ORDER BY servs_name ASC

Troy

Ani Nica

2005-06-29, 9:23 am

A predicate containing only host variables and constants (e.g., the
predicate ":ls_manufacturer IS NULL" ) maybe evaluated before the query
optimization. For example, the predicate "(rooftop = :ll_rooftop OR
:ll_rooftop IS NULL)" will be dropped if ":ll_rooftop" is set to NULL; if
the variable ":ll_rooftop" is not NULL the OR predicate is replaced by the
sargable predicate "(rooftop = :ll_rooftop)". Hence, the performance of
your query should not be affected by the two extra OR predicates.

--
Ani Nica
Research and Development, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
"Troy Coombs" <coombst@quorumis.com> wrote in message
news:42c2780f$1@foru
ms-1-dub...
> Using ASA 8.0.2.
>
> Based on the below Select statement does the extra
>
> "AND (manufacturer = :ls_manufacturer OR :ls_manufacturer IS NULL)
>
> AND (rooftop = :ll_rooftop OR :ll_rooftop IS NULL)"
>
> in the WHERE clause result in a performance hit then if it wasn't there?
> It's already built into the view 'view_dev_security_p
ermissions' but
> unfortuately got also included in the where clause on many dw's throughout
> the system.
>
> We don't have enough data in the related tables yet to get any clear stats
> from the ISQL plan.
>
> SELECT servs_id, servs_name, servs_oemrt_id
>
> FROM services, view_dev_security_pe
rmissions
>
> WHERE employee = :ls_employee
>
> AND (manufacturer = :ls_manufacturer OR :ls_manufacturer IS NULL)
>
> AND (rooftop = :ll_rooftop OR :ll_rooftop IS NULL)
>
> AND servs_oemrt_id = oemrt_id
>
> ORDER BY servs_name ASC
>
>
>
>
>
>
>
> Troy
>
>

Sponsored Links