Case in a Where clause

Hi all,
I have the following sql statement (it's ugly and it's not my work!!!).
UPDATE abo_tags SET locked = @.PrimaryGroupFilter, locking_user = @.user,
lockingdate = @.sDate
FROM abo_tags
INNER JOIN abo ON abo_tags.id_no_abo = abo.id_no
INNER JOIN products ON abo.id_no_product = products.id_no
LEFT OUTER JOIN abo_tags_subsidies ON abo_tags.id_no =
abo_tags_subsidies.id_no_abo_tag
WHERE abo_tags.id_no_invoice_type = 2 and abo_tags.locked in ('0', '',
NULL)
and abo_tags.validity_date >= @.mdatInvoiceFrom
and abo_tags.validity_date <= @.mdatInvoiceTo
and abo.id_no_primarygroup in (select primchild from invoicehelp where
parentkey = @.PrimaryGroupFilter)
and abo.id_no_product = case when @.mlngProduct >0 then @.mlngProduct else
abo.id_no_product end
and abo.id_no_school = case when @.mlngSchool > 0 then @.mlngSchool else
abo.id_no_school end
and abo_tags_subsidies.id_no_costrepresentative = case when @.mlngCostRep>0
then @.mlngCostRep else abo_tags_subsidies.id_no_costrepresentative end
I noticed that it takes a while to update records (over 20sec) on a table on
500K records. It looks like the problem lies in the CASE statements in the
WHERE clause. Is there a way to do it better than this?
Thanks,
IvanIvan,
Supposing that are int datatype, then try:
WHERE
abo_tags.id_no_invoice_type = 2 and abo_tags.locked in ('0', '')
and abo_tags.validity_date >= @.mdatInvoiceFrom
and abo_tags.validity_date <= @.mdatInvoiceTo
and abo.id_no_primarygroup in (
select primchild
from invoicehelp
where
parentkey = @.PrimaryGroupFilter)
and abo.id_no_product between coalesce(nullif(@.mlngProduct, 0), 1) and
coalesce(nullif(@.mlngProduct, 0), 2147483647)
and abo.id_no_school between coalesce(nullif(@.mlngSchool, 0), 1) and
coalesce(nullif(@.mlngSchool, 0), 2147483647)
and abo_tags_subsidies.id_no_costrepresentative between
coalesce(nullif(@.mlngCostRep, 0), 1) and coalesce(nullif(@.mlngCostRep, 0),
2147483647)
)
also, take off the null value from the list used with first IN operator.

> abo_tags.id_no_invoice_type = 2 and abo_tags.locked in ('0', '', null)
abo_tags.id_no_invoice_type = 2 and abo_tags.locked in ('0', '')
Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
AMB
"Ivan Debono" wrote:

> Hi all,
> I have the following sql statement (it's ugly and it's not my work!!!).
> UPDATE abo_tags SET locked = @.PrimaryGroupFilter, locking_user = @.user,
> lockingdate = @.sDate
> FROM abo_tags
> INNER JOIN abo ON abo_tags.id_no_abo = abo.id_no
> INNER JOIN products ON abo.id_no_product = products.id_no
> LEFT OUTER JOIN abo_tags_subsidies ON abo_tags.id_no =
> abo_tags_subsidies.id_no_abo_tag
> WHERE abo_tags.id_no_invoice_type = 2 and abo_tags.locked in ('0', '',
> NULL)
> and abo_tags.validity_date >= @.mdatInvoiceFrom
> and abo_tags.validity_date <= @.mdatInvoiceTo
> and abo.id_no_primarygroup in (select primchild from invoicehelp where
> parentkey = @.PrimaryGroupFilter)
> and abo.id_no_product = case when @.mlngProduct >0 then @.mlngProduct else
> abo.id_no_product end
> and abo.id_no_school = case when @.mlngSchool > 0 then @.mlngSchool else
> abo.id_no_school end
> and abo_tags_subsidies.id_no_costrepresentative = case when @.mlngCostRep>
0
> then @.mlngCostRep else abo_tags_subsidies.id_no_costrepresentative end
> I noticed that it takes a while to update records (over 20sec) on a table
on
> 500K records. It looks like the problem lies in the CASE statements in the
> WHERE clause. Is there a way to do it better than this?
> Thanks,
> Ivan
>
>|||hi Ivan
This is the equivalent after removing the CASE in Where Clause:
UPDATE abo_tags
SET locked = @.PrimaryGroupFilter,
locking_user = @.user,
lockingdate = @.sDate
FROM abo_tags
INNER JOIN abo ON abo_tags.id_no_abo = abo.id_no
INNER JOIN products ON abo.id_no_product = products.id_no
LEFT OUTER JOIN abo_tags_subsidies ON abo_tags.id_no =
abo_tags_subsidies.id_no_abo_tag
WHERE
abo_tags.id_no_invoice_type = 2 and abo_tags.locked in ('0', '',NULL)
and abo_tags.validity_date >= @.mdatInvoiceFrom
and abo_tags.validity_date <= @.mdatInvoiceTo
and abo.id_no_primarygroup in (select primchild from invoicehelp where
parentkey = @.PrimaryGroupFilter)
and ((@.mlngProduct > 0 AND abo.id_no_product=@.mlngProduct) OR (@.mlngProduct
<= 0))
and ((@.mlngSchool > 0 AND abo.id_no_school=@.mlngSchool) OR (@.mlngSchool <= 0
))
and ((@.mlngCostRep > 0 AND abo_tags_subsidies.id_no_costrepresentative =
@.mlngCostRep) OR (@.mlngCostRep <= 0) )
I dont thin, there is a problem with the CASE. I believe its because of the
in (select primchild from invoicehelp where
parentkey = @.PrimaryGroupFilter)
please let me if this answers your question.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ivan Debono" wrote:

> Hi all,
> I have the following sql statement (it's ugly and it's not my work!!!).
> UPDATE abo_tags SET locked = @.PrimaryGroupFilter, locking_user = @.user,
> lockingdate = @.sDate
> FROM abo_tags
> INNER JOIN abo ON abo_tags.id_no_abo = abo.id_no
> INNER JOIN products ON abo.id_no_product = products.id_no
> LEFT OUTER JOIN abo_tags_subsidies ON abo_tags.id_no =
> abo_tags_subsidies.id_no_abo_tag
> WHERE abo_tags.id_no_invoice_type = 2 and abo_tags.locked in ('0', '',
> NULL)
> and abo_tags.validity_date >= @.mdatInvoiceFrom
> and abo_tags.validity_date <= @.mdatInvoiceTo
> and abo.id_no_primarygroup in (select primchild from invoicehelp where
> parentkey = @.PrimaryGroupFilter)
> and abo.id_no_product = case when @.mlngProduct >0 then @.mlngProduct else
> abo.id_no_product end
> and abo.id_no_school = case when @.mlngSchool > 0 then @.mlngSchool else
> abo.id_no_school end
> and abo_tags_subsidies.id_no_costrepresentative = case when @.mlngCostRep>
0
> then @.mlngCostRep else abo_tags_subsidies.id_no_costrepresentative end
> I noticed that it takes a while to update records (over 20sec) on a table
on
> 500K records. It looks like the problem lies in the CASE statements in the
> WHERE clause. Is there a way to do it better than this?
> Thanks,
> Ivan
>
>|||Hi
Here is the query after removing the IN
UPDATE abo_tags
SET locked = @.PrimaryGroupFilter,
locking_user = @.user,
lockingdate = @.sDate
FROM abo_tags
INNER JOIN abo ON abo_tags.id_no_abo = abo.id_no
INNER JOIN products ON abo.id_no_product = products.id_no
INNER JOIN invoicehelp ON primchild = abo.id_no_primarygroup
LEFT OUTER JOIN abo_tags_subsidies ON abo_tags.id_no =
abo_tags_subsidies.id_no_abo_tag
WHERE
abo_tags.id_no_invoice_type = 2 and abo_tags.locked in ('0', '',NULL)
and abo_tags.validity_date >= @.mdatInvoiceFrom
and abo_tags.validity_date <= @.mdatInvoiceTo
and invoicehelp.parentkey = @.PrimaryGroupFilter
and ((@.mlngProduct > 0 AND abo.id_no_product=@.mlngProduct) OR (@.mlngProduct
<= 0))
and ((@.mlngSchool > 0 AND abo.id_no_school=@.mlngSchool) OR (@.mlngSchool <= 0
))
and ((@.mlngCostRep > 0 AND abo_tags_subsidies.id_no_costrepresentative =
@.mlngCostRep) OR (@.mlngCostRep <= 0) )
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Chandra" wrote:
> hi Ivan
>
> This is the equivalent after removing the CASE in Where Clause:
> UPDATE abo_tags
> SET locked = @.PrimaryGroupFilter,
> locking_user = @.user,
> lockingdate = @.sDate
> FROM abo_tags
> INNER JOIN abo ON abo_tags.id_no_abo = abo.id_no
> INNER JOIN products ON abo.id_no_product = products.id_no
> LEFT OUTER JOIN abo_tags_subsidies ON abo_tags.id_no =
> abo_tags_subsidies.id_no_abo_tag
> WHERE
> abo_tags.id_no_invoice_type = 2 and abo_tags.locked in ('0', '',NULL)
> and abo_tags.validity_date >= @.mdatInvoiceFrom
> and abo_tags.validity_date <= @.mdatInvoiceTo
> and abo.id_no_primarygroup in (select primchild from invoicehelp where
> parentkey = @.PrimaryGroupFilter)
> and ((@.mlngProduct > 0 AND abo.id_no_product=@.mlngProduct) OR (@.mlngProduc
t
> <= 0))
> and ((@.mlngSchool > 0 AND abo.id_no_school=@.mlngSchool) OR (@.mlngSchool <=
0
> ))
> and ((@.mlngCostRep > 0 AND abo_tags_subsidies.id_no_costrepresentative =
> @.mlngCostRep) OR (@.mlngCostRep <= 0) )
> I dont thin, there is a problem with the CASE. I believe its because of th
e
> in (select primchild from invoicehelp where
> parentkey = @.PrimaryGroupFilter)
> please let me if this answers your question.
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Ivan Debono" wrote:
>|||Never heard about the Coalesce... need to read more about it.
Is there an alternative for the Subselect (select primchild...)'
Thanks,
Ivan
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> schrieb im
Newsbeitrag news:7F407540-37EF-4341-946A-78FB74EB0E91@.microsoft.com...
> Ivan,
> Supposing that are int datatype, then try:
> WHERE
> abo_tags.id_no_invoice_type = 2 and abo_tags.locked in ('0', '')
> and abo_tags.validity_date >= @.mdatInvoiceFrom
> and abo_tags.validity_date <= @.mdatInvoiceTo
> and abo.id_no_primarygroup in (
> select primchild
> from invoicehelp
> where
> parentkey = @.PrimaryGroupFilter)
> and abo.id_no_product between coalesce(nullif(@.mlngProduct, 0), 1) and
> coalesce(nullif(@.mlngProduct, 0), 2147483647)
> and abo.id_no_school between coalesce(nullif(@.mlngSchool, 0), 1) and
> coalesce(nullif(@.mlngSchool, 0), 2147483647)
> and abo_tags_subsidies.id_no_costrepresentative between
> coalesce(nullif(@.mlngCostRep, 0), 1) and coalesce(nullif(@.mlngCostRep, 0),
> 2147483647)
> )
> also, take off the null value from the list used with first IN operator.
>
> abo_tags.id_no_invoice_type = 2 and abo_tags.locked in ('0', '')
>
> Dynamic Search Conditions in T-SQL
> http://www.sommarskog.se/dyn-search.html
>
> AMB
> "Ivan Debono" wrote:
>|||I would go thru the DDL and change the blank strings to '0', add a
DEFAULT and a check constraint to prevent this problem in the future.