"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
>
>
>

"Alejandro Mesa" <AlejandroM***@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', '', 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
>>
>>
>>

"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
>
>
>

"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 (@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
> >
> >
> >