Asp Forum - Dynamic SQL Query - microsoft.public.sqlserver.programming

Cylix

3/23/2007 2:22:00 AM

I am going to update a list of records accroding a Unique ID,
like
Update TBL SET thedate=GETDATE() WHERE TBL_ID IN ('1', '3', '4' ....)

The ID List may be very long ...
But for a stored procedure, VARCHAR(8000) seems the longest string I
can declare.
Any datatype I can define for more than 8000 charaters for a dynamic
query string?

Thanks.

5 Answers

masri999

3/23/2007 2:57:00 AM

On Mar 23, 7:22 am, "Cylix" <cylix2...@gmail.com> wrote:
> I am going to update a list of records accroding a Unique ID,
> like
> Update TBL SET thedate=GETDATE() WHERE TBL_ID IN ('1', '3', '4' ....)
>
> The ID List may be very long ...
> But for a stored procedure, VARCHAR(8000) seems the longest string I
> can declare.
> Any datatype I can define for more than 8000 charaters for a dynamic
> query string?
>
> Thanks.

Is it possible put your tbl_id's in #temp table instead of delimited
string and execute dynamic script

Tony Rogerson

3/23/2007 8:13:00 AM

Hi Cylix,

If you are using SQL 2005 then look at varchar(max) or the xml type.

Otherwise, you can use multiple varchar(8000) parameters and exec them...

-- Get rid of SQL injection
set @in_list1 = replace( @in_list1, '''', '''''' )
set @in_list2 = replace( @in_list2, '''', '''''' )
set @in_list3 = replace( @in_list3, '''', '''''' )

exec( 'Update TBL SET thedate=GETDATE() WHERE TBL_ID IN (' + @in_list1 + ' '
+ @in_list2 + ' ' + @in_list3 + ')' )

You could use a table and insert them into that first, but if performance is
a factor then that approach will be slow - that approach you have two
options, create one batch with lots of INSERT's (you'd probably hit the max
batch size for SQL Server) or you'd do lots of round trips INSERTing the
rows which would be costly.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/to... (Ramblings from the field from a
SQL consultant)
http://sqlser... (UK SQL User Community)

"Cylix" <cylix2000@gmail.com> wrote in message
news:1174616541.354749.286140@y80g2000hsf.googlegroups.com...
>I am going to update a list of records accroding a Unique ID,
> like
> Update TBL SET thedate=GETDATE() WHERE TBL_ID IN ('1', '3', '4' ....)
>
> The ID List may be very long ...
> But for a stored procedure, VARCHAR(8000) seems the longest string I
> can declare.
> Any datatype I can define for more than 8000 charaters for a dynamic
> query string?
>
> Thanks.
>

Someone Else

3/23/2007 8:54:00 AM

but as is pointed out in here:

http://www.sommarskog.se/dynamic_sql...

dynamic sql is not the best solution to this problem anyway.

On 23 Mar, 08:12, "Tony Rogerson" <tonyroger...@torver.net> wrote:
> Hi Cylix,
>
> If you are using SQL 2005 then look at varchar(max) or the xml type.
>
> Otherwise, you can use multiple varchar(8000) parameters and exec them...
>
> -- Get rid of SQL injection
> set @in_list1 = replace( @in_list1, '''', '''''' )
> set @in_list2 = replace( @in_list2, '''', '''''' )
> set @in_list3 = replace( @in_list3, '''', '''''' )
>
> exec( 'Update TBL SET thedate=GETDATE() WHERE TBL_ID IN (' + @in_list1 + ' '
> + @in_list2 + ' ' + @in_list3 + ')' )
>
> You could use a table and insert them into that first, but if performance is
> a factor then that approach will be slow - that approach you have two
> options, create one batch with lots of INSERT's (you'd probably hit the max
> batch size for SQL Server) or you'd do lots of round trips INSERTing the
> rows which would be costly.
>
> --
> Tony Rogerson, SQL Server MVPhttp://sqlblogcasts.com/blogs/to...(Ramblings from the field from a
> SQL consultant)http://sqlser...(UK SQL User Community)
>
> "Cylix" <cylix2...@gmail.com> wrote in message
>
> news:1174616541.354749.286140@y80g2000hsf.googlegroups.com...
>
>
>
> >I am going to update a list of records accroding a Unique ID,
> > like
> > Update TBL SET thedate=GETDATE() WHERE TBL_ID IN ('1', '3', '4' ....)
>
> > The ID List may be very long ...
> > But for a stored procedure, VARCHAR(8000) seems the longest string I
> > can declare.
> > Any datatype I can define for more than 8000 charaters for a dynamic
> > query string?
>
> > Thanks.- Hide quoted text -
>
> - Show quoted text -

Tony Rogerson

3/23/2007 9:24:00 AM

Are you refering to this?

>> On top of that, for long lists, IN has extremely poor performance - in
>> some tests I did, it took SQL Server 15 seconds to build the query plan
>> for a list with 10000 elements.
>> The correct method is to unpack the list into a table with a user-defined
>> function or a stored procedure

On 2005 this is totally wrong, the IN is significantly more optimised, in
fact if you look at my blog I used an unpack method to unpack the CSV to a
table before using it in the query, the difference between that and the
dynamic SQL ie. IN ( '....' ) is that the dynamic SQL took 27 seconds and
the unpack method took 384 seconds - that is a staggering difference!

Anyway, I blogged about it:
http://sqlblogcasts.com/blogs/to.../archive/2007/03/17/joe-celko-don-t-use-csv-xml-use-1-000-parameters-in...

Erland has a more recent article on arrays
(http://www.sommarskog.se/arrays-in-sql...) that just reading it is
also wrong on the dynamic SQL - I'll ping Erland and bring it to his
attention.

Tony.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/to... (Ramblings from the field from a
SQL consultant)
http://sqlser... (UK SQL User Community)

"Someone Else" <someone_else_of_course@hotmail.co.uk> wrote in message
news:1174640062.955099.136890@n76g2000hsh.googlegroups.com...
> but as is pointed out in here:
>
> http://www.sommarskog.se/dynamic_sql...
>
> dynamic sql is not the best solution to this problem anyway.
>
> On 23 Mar, 08:12, "Tony Rogerson" <tonyroger...@torver.net> wrote:
>> Hi Cylix,
>>
>> If you are using SQL 2005 then look at varchar(max) or the xml type.
>>
>> Otherwise, you can use multiple varchar(8000) parameters and exec them...
>>
>> -- Get rid of SQL injection
>> set @in_list1 = replace( @in_list1, '''', '''''' )
>> set @in_list2 = replace( @in_list2, '''', '''''' )
>> set @in_list3 = replace( @in_list3, '''', '''''' )
>>
>> exec( 'Update TBL SET thedate=GETDATE() WHERE TBL_ID IN (' + @in_list1 +
>> ' '
>> + @in_list2 + ' ' + @in_list3 + ')' )
>>
>> You could use a table and insert them into that first, but if performance
>> is
>> a factor then that approach will be slow - that approach you have two
>> options, create one batch with lots of INSERT's (you'd probably hit the
>> max
>> batch size for SQL Server) or you'd do lots of round trips INSERTing the
>> rows which would be costly.
>>
>> --
>> Tony Rogerson, SQL Server
>> MVPhttp://sqlblogcasts.com/blogs/to...(Ramblings from the field
>> from a
>> SQL consultant)http://sqlser...(UK SQL User Community)
>>
>> "Cylix" <cylix2...@gmail.com> wrote in message
>>
>> news:1174616541.354749.286140@y80g2000hsf.googlegroups.com...
>>
>>
>>
>> >I am going to update a list of records accroding a Unique ID,
>> > like
>> > Update TBL SET thedate=GETDATE() WHERE TBL_ID IN ('1', '3', '4' ....)
>>
>> > The ID List may be very long ...
>> > But for a stored procedure, VARCHAR(8000) seems the longest string I
>> > can declare.
>> > Any datatype I can define for more than 8000 charaters for a dynamic
>> > query string?
>>
>> > Thanks.- Hide quoted text -
>>
>> - Show quoted text -
>
>

Mr Tea

3/23/2007 10:51:00 PM

Hi Tony

I look at your article in a little more detail, the phenomenon you are
seeing here is a query plan reuse on the dynamic sql. If you were to vary
the input parameters on each call, or drop the procedure cache between each
call then the dynamic sql would drop below the table of numbers in terms of
performance.

Erlands article on arrays and lists documents the phenomenon well, check the
section on EXEC$B... You had me excited for a while there :).

Regards
Lee

"Tony Rogerson" <tonyrogerson@torver.net> wrote in message
news:e92cp1SbHHA.3584@TK2MSFTNGP02.phx.gbl...
> Are you refering to this?
>
>>> On top of that, for long lists, IN has extremely poor performance - in
>>> some tests I did, it took SQL Server 15 seconds to build the query plan
>>> for a list with 10000 elements.
>>> The correct method is to unpack the list into a table with a
>>> user-defined function or a stored procedure
>
> On 2005 this is totally wrong, the IN is significantly more optimised, in
> fact if you look at my blog I used an unpack method to unpack the CSV to a
> table before using it in the query, the difference between that and the
> dynamic SQL ie. IN ( '....' ) is that the dynamic SQL took 27 seconds and
> the unpack method took 384 seconds - that is a staggering difference!
>
> Anyway, I blogged about it:
> http://sqlblogcasts.com/blogs/to.../archive/2007/03/17/joe-celko-don-t-use-csv-xml-use-1-000-parameters-in...
>
> Erland has a more recent article on arrays
> (http://www.sommarskog.se/arrays-in-sql...) that just reading it is
> also wrong on the dynamic SQL - I'll ping Erland and bring it to his
> attention.
>
> Tony.
>
> --
> Tony Rogerson, SQL Server MVP
> http://sqlblogcasts.com/blogs/to... (Ramblings from the field from
> a SQL consultant)
> http://sqlser... (UK SQL User Community)
>
> "Someone Else" <someone_else_of_course@hotmail.co.uk> wrote in message
> news:1174640062.955099.136890@n76g2000hsh.googlegroups.com...
>> but as is pointed out in here:
>>
>> http://www.sommarskog.se/dynamic_sql...
>>
>> dynamic sql is not the best solution to this problem anyway.
>>
>> On 23 Mar, 08:12, "Tony Rogerson" <tonyroger...@torver.net> wrote:
>>> Hi Cylix,
>>>
>>> If you are using SQL 2005 then look at varchar(max) or the xml type.
>>>
>>> Otherwise, you can use multiple varchar(8000) parameters and exec
>>> them...
>>>
>>> -- Get rid of SQL injection
>>> set @in_list1 = replace( @in_list1, '''', '''''' )
>>> set @in_list2 = replace( @in_list2, '''', '''''' )
>>> set @in_list3 = replace( @in_list3, '''', '''''' )
>>>
>>> exec( 'Update TBL SET thedate=GETDATE() WHERE TBL_ID IN (' + @in_list1 +
>>> ' '
>>> + @in_list2 + ' ' + @in_list3 + ')' )
>>>
>>> You could use a table and insert them into that first, but if
>>> performance is
>>> a factor then that approach will be slow - that approach you have two
>>> options, create one batch with lots of INSERT's (you'd probably hit the
>>> max
>>> batch size for SQL Server) or you'd do lots of round trips INSERTing the
>>> rows which would be costly.
>>>
>>> --
>>> Tony Rogerson, SQL Server
>>> MVPhttp://sqlblogcasts.com/blogs/to...(Ramblings from the field
>>> from a
>>> SQL consultant)http://sqlser...(UK SQL User Community)
>>>
>>> "Cylix" <cylix2...@gmail.com> wrote in message
>>>
>>> news:1174616541.354749.286140@y80g2000hsf.googlegroups.com...
>>>
>>>
>>>
>>> >I am going to update a list of records accroding a Unique ID,
>>> > like
>>> > Update TBL SET thedate=GETDATE() WHERE TBL_ID IN ('1', '3', '4' ....)
>>>
>>> > The ID List may be very long ...
>>> > But for a stored procedure, VARCHAR(8000) seems the longest string I
>>> > can declare.
>>> > Any datatype I can define for more than 8000 charaters for a dynamic
>>> > query string?
>>>
>>> > Thanks.- Hide quoted text -
>>>
>>> - Show quoted text -
>>
>>
>

microsoft.public.sqlserver.programming

Dynamic SQL Query

Cylix

masri999

Tony Rogerson

Someone Else

Tony Rogerson

Mr Tea

x Login to ForumsZone