Passing long text strings to a stored procedure

Hello!

I am attempting to insert a group of records into a SQL Server 2000 database table. The data for each of these records is the same, with the exception of a foreign key (hereafter known as the 'RepKey') and the generated primary key. To improve performance and cut down on the network traffic, I pack the RepKeys in a comma-delimited string and send it as a single parameter, with the intention of parsing it in the stored proicedure to obtain each individual RepKey, or to use it as a list in an 'WHERE RepKey IN (' + @.RepKeyString + ')' type of query.

My problem is that there may be 1000's of items in this string. Using a varchar(8000) as the parameter type is too short, while using the 'text' data type does not allow me to perform any string operations on it. Any ideas on how to make one network call and insert multiple records that breaks the 8000 character barrier?

One thing that I cannot do is add a table so that the record is stored once, then mapped to each individual rep key. The database structure cannot change. Other solutions that I may not have considered are welcome. Thanks!Can you add a global temp table?
You could write all the data to a disk file then bcp it in to the global table.
Could also create a disconnected recordset on the golobal temp table, diconnect it, populate it then connect it to commit the records then use that.

You could use a text datatype then use substring to parse it in chunks and use char functions on it.|||Nigelrivett idea of a text file sounds interesting. What if the parameter used in your stored procedure was the path to a file containing the list of RepKeys? Once in your procedure you use BULK INSERT into a temporay table (Globle table if needed like nigelrivett suggested) then perform the same looping as you would have done before.

sp_MyProc (RepKeyFile AS varchar(50), ....)

CREATE TABLE #temptable ...
BULK INSERT #temptable FROM @.RepKeyFile
.
.
.
CREATE CURSOR on #temptable
loop through

The only problem is your point on:
or to use it as a list in an 'WHERE RepKey IN (' + @.RepKeyString + ')' type of query.

I thought that you could create a local text variable and while looping append the RepKey to the local text field, SET @.txt = @.txt + ',' + @.RepKey. However I got an error when trying to create a local variable as type text.

Msg 2739, Level 16, State 1, Server ATLAS, Line 1
The text, ntext, and image data types are invalid for local variables.|||Thanks guys - I ended up using the substring procedure to break off chunks, then used an INSERT..SELECT statement that looks like the following:

SET @.query = "INSERT INTO RepContact([fields])"
SET @.query = @.query + "SELECT RepKey, [@.vars] FROM Rep WHERE RepKey IN (" + @.currentString + ")"
exec(@.query)

@.CurrentString is the current list of keys. Each time through the loop, as long as there are still items, the query is run.

Thanks again - if anyone has any ideas on speeding this up, it would be greatly appreciated. (The insert runs a bit slower than I would have hoped).

Everett|||I was thinking that the text file would hold the keys delimitted by crlf so that the bcp would insert them into separate rows and you wouldn't have to do any further processing.|||I think that I would prefer to leave it as it is and avoid writing to and reading from disk. Wouldn't this make it slower, not faster? Anyways, thanks again.|||>> Wouldn't this make it slower, not faster?

Depends on the data and environment.
the bcp will be non-logged so the inser will be faster. It will reduce the handshaking across the network and reduce the amount of manipulation needed before the insert into the production tables.
It would probably end up slower but maybe not. It does give an automatic record of the dta inserted from the text files and makes it easy to make the insert asynchronous if you need to.|||I'll try it during the week and advise you of the outcome.

Thanks again for all of your help.

Passing an array of strings to a Stored Procedure

Well, Imanaged to write a Stored procedure that updates some records in the Northwind Database based on the ProductIDs passed to the SP as a list of strings. This is the Alter version of the SP:

USE [Northwind]

GO

/****** Object: StoredProcedure [dbo].[gv_sp_UpdatePOs] Script Date: 06/10/2007 12:07:54 ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

ALTERPROC [dbo].[gv_sp_UpdatePOs]

(

@.IDListvarchar(500),

@.ReorderLevelint,

@.ProductNamenvarchar(30)

)

AS

BEGIN

SETNOCOUNTON

EXEC('Update dbo.Products

SET ReorderLevel = ('+ @.ReorderLevel+') ,ProductName = ('''+ @.ProductName+''')

WHERE ProductID IN ('+ @.IDList+')')

END

-------

THis works fine inside Sql Server 2005 Query analyser.

But when I setup an aspx page with an objectDataSource inside the page attached to an xsd file where the Products table is located. When I try to add new query to the tableadapter inside the Products table and point to the stored procedure in the wizard I get this error: " the wizard detected the following problems when configuring TableAdapter query "Products" Details: Generated SELECT statement. Incorrect suntax near ')'.

Any help would be appreciated

And can someone convert it to support XML instead of list of strings. thanks.

Hello my friend,

It would be better to do the following. First, run the following SQL: -

CREATE FUNCTION dbo.StringArrayIntoTable
(
@.String VARCHAR(8000),
@.Separator VARCHAR(1)
)
RETURNS @.tblStrings TABLE(Item VARCHAR(8000))

AS

BEGIN

DECLARE @.pos INT,
@.SubStr VARCHAR(800)

SET @.pos = CHARINDEX(@.Separator, @.String)

WHILE @.pos > 0
BEGIN
SET @.SubStr = SUBSTRING(@.String, 0, @.pos)

INSERT INTO @.tblStrings (Item) VALUES (@.SubStr)

SET @.String = SUBSTRING(@.String, LEN(@.SubStr) + 2, LEN(@.String) - LEN(@.SubStr) + 1)
SET @.pos = CHARINDEX(@.Separator, @.String)
END

INSERT INTO @.tblStrings (Item) VALUES (@.String)
RETURN

END

Test this function via the following: -

SELECT Item FROM dbo.StringArrayIntoTable('red,blue,yellow', ',')
SELECT Item FROM dbo.StringArrayIntoTable('USA|Germany|Russia|UK', '|')

Now change your procedure to the following: -

ALTER PROC [dbo].[gv_sp_UpdatePOs]
(
@.IDList varchar(500),
@.ReorderLevel int,
@.ProductName nvarchar(30)
)
AS

BEGIN

SET NOCOUNT ON

UPDATE dbo.Products SET
ReorderLevel = @.ReorderLevel,
ProductName = @.ProductName

WHERE ProductID IN
(
SELECT Item FROM dbo.StringArrayIntoTable(@.IDList, ',')
)

END

Kind regards

Scotty

|||

USE [Northwind]GO/****** Object: StoredProcedure [dbo].[gv_sp_UpdatePOs] Script Date: 06/10/2007 12:07:54 ******/SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOALTER PROC [dbo].[gv_sp_UpdatePOs](@.IDListvarchar(500),@.ReorderLevelint,@.ProductNamenvarchar(30) )ASBEGINSET NOCOUNT ON EXEC('Update dbo.ProductsSET ReorderLevel = (' +CAST( @.ReorderLevelas varchar(20) ) +') ,ProductName = (''' + @.ProductName +''')WHERE ProductID IN (' + @.IDList +')')END

Hello,
Try this.|||

Scotty, nice trick thanks.

Hasan, thanks. It is working now with casting.

pass several records at once for insert ?

If I was to pass several xml elements into a stored procedure (as an xml parameter), how could I then insert all the items into a table ?

Are you wanting to do something similar to what is done in this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1501246&SiteID=1

Here is an example that uses NODES:

Code Snippet

declare @.myXml xml

set @.myXml = N'<marketData>
<date>19-Apr-2007 17:08:55</date>
<Rates>
<Rate code="USDCAD">
<values>
<value type="BID">1.1276</value>
<value type="ASK">1.1277</value>
<value type="MID">1.127649997783</value>
</values>
</Rate>
<Rate code="EURUSD">
<values>
<value type="BID">1.3607</value>
<value type="ASK">1.3608</value>
<value type="MID">1.36075</value>
</values>
</Rate>
</Rates>
</marketData>'

select r.value ('http://@.code', 'varchar(20)') as rateCode,
r.value ('./@.type', 'varchar(20)') as type,
r.value ('.', 'varchar(20)') as value
from @.myXml.nodes('/marketData/Rates/Rate/values/value') as x(r)

pasing a set of records to SP

hi
I need to send a set of records to a SP in order to make some comparisos and
calculations and return back from the SP the same records but modified
What is the best (faster,optimal) way to send the records from my client App
via internet to the SQLDB in order to the SP reading those records?
I just discover that a SP does not accept arrays as input parameters.
thks
kenhttp://www.sommarskog.se/arrays-in-sql.html
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:F69119F4-7020-4B87-9BCA-414F3A446AFC@.microsoft.com...
> hi
> I need to send a set of records to a SP in order to make some comparisos
and
> calculations and return back from the SP the same records but modified
> What is the best (faster,optimal) way to send the records from my client
App
> via internet to the SQLDB in order to the SP reading those records?
>
> I just discover that a SP does not accept arrays as input parameters.
> thks
> ken
>

Partitions and performance

I copied 5million records into a table that has no indexes and partitions and
it took 90mins.I then truncated this table and setup a Partition and created
clustered index based on the Partitioning Column. I copied the same 5million
records into this table but it took only 60mins.
Shouldnâ't the copy in the second case be taking more time because of the
presence of the Clustered Index? Or did the created partition increase the
performance?
Later, I subjected the table to a set of queries and compared the
performance against the non-partitioned table and I could not see much of a
performance gain.
Considering all else equal, ie the query is based on the Partitioned Column
which is inturn based on one of the keys in the Clustered Index, I expected
good performance gain. But I was disappointed to see that it did not offer
much.
I just wanted to hear from others and see what their experiences are.
Thanks,
rgnHi
You don't give information about the partitioning or the clustered index and
what the data is that you are inserting. Posting DDL and an example would
help. You may answer the first question yourself by loading into the table
with the clustered index and no partition.
Check out the query plans to see if your partioning is working.
John
"rgn" wrote:
> I copied 5million records into a table that has no indexes and partitions and
> it took 90mins.I then truncated this table and setup a Partition and created
> clustered index based on the Partitioning Column. I copied the same 5million
> records into this table but it took only 60mins.
> Shouldnâ't the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned Column
> which is inturn based on one of the keys in the Clustered Index, I expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>|||rgn
I don't think that Partition is designed to gain performance benefit, in my
opinion it is more for structuring the data , however , I'm sure that
performance plays its role as well
Read Nigel's great article
http://www.simple-talk.com/sql/sql-server-2005/partitioned-tables-in-sql-server-2005/
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:ED3E72A3-962E-439B-A6E3-60DA95C529FB@.microsoft.com...
>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
> Shouldn?t the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of
> a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned
> Column
> which is inturn based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>|||>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
I think there must be something else going on here. Was the database space
pre-allocated? Were the non-partitioned and partitioned tables on the same
filegroups and physical disks? Is the source data in the same sequence as
the clustered index?
> Considering all else equal, i.e. the query is based on the Partitioned
> Column
> which is intern based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
Partitioning is more for manageability than performance. For example,
partitioning can reduce intermediate space requirements for (re)building a
clustered index or allow you to place historical read-only data on different
filegroups. Partitioning can improve performance of certain queries through
partition elimination but this mostly helps scan operations.
Indexing is the real key to performance of both partitioned and
non-partitioned tables. The real performance sweet spot for partitioning is
when a design allows mass data load/archive using SWITCH.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:ED3E72A3-962E-439B-A6E3-60DA95C529FB@.microsoft.com...
>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
> Shouldnâ't the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of
> a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned
> Column
> which is inturn based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>

Partitions and performance

I copied 5million records into a table that has no indexes and partitions and
it took 90mins.I then truncated this table and setup a Partition and created
clustered index based on the Partitioning Column. I copied the same 5million
records into this table but it took only 60mins.
Shouldn’t the copy in the second case be taking more time because of the
presence of the Clustered Index? Or did the created partition increase the
performance?
Later, I subjected the table to a set of queries and compared the
performance against the non-partitioned table and I could not see much of a
performance gain.
Considering all else equal, ie the query is based on the Partitioned Column
which is inturn based on one of the keys in the Clustered Index, I expected
good performance gain. But I was disappointed to see that it did not offer
much.
I just wanted to hear from others and see what their experiences are.
Thanks,
rgn
Hi
You don't give information about the partitioning or the clustered index and
what the data is that you are inserting. Posting DDL and an example would
help. You may answer the first question yourself by loading into the table
with the clustered index and no partition.
Check out the query plans to see if your partioning is working.
John
"rgn" wrote:

> I copied 5million records into a table that has no indexes and partitions and
> it took 90mins.I then truncated this table and setup a Partition and created
> clustered index based on the Partitioning Column. I copied the same 5million
> records into this table but it took only 60mins.
> Shouldn’t the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned Column
> which is inturn based on one of the keys in the Clustered Index, I expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>
|||rgn
I don't think that Partition is designed to gain performance benefit, in my
opinion it is more for structuring the data , however , I'm sure that
performance plays its role as well
Read Nigel's great article
http://www.simple-talk.com/sql/sql-server-2005/partitioned-tables-in-sql-server-2005/
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:ED3E72A3-962E-439B-A6E3-60DA95C529FB@.microsoft.com...
>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
> Shouldnt the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of
> a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned
> Column
> which is inturn based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>
|||>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
I think there must be something else going on here. Was the database space
pre-allocated? Were the non-partitioned and partitioned tables on the same
filegroups and physical disks? Is the source data in the same sequence as
the clustered index?

> Considering all else equal, i.e. the query is based on the Partitioned
> Column
> which is intern based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
Partitioning is more for manageability than performance. For example,
partitioning can reduce intermediate space requirements for (re)building a
clustered index or allow you to place historical read-only data on different
filegroups. Partitioning can improve performance of certain queries through
partition elimination but this mostly helps scan operations.
Indexing is the real key to performance of both partitioned and
non-partitioned tables. The real performance sweet spot for partitioning is
when a design allows mass data load/archive using SWITCH.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:ED3E72A3-962E-439B-A6E3-60DA95C529FB@.microsoft.com...
>I copied 5million records into a table that has no indexes and partitions
>and
> it took 90mins.I then truncated this table and setup a Partition and
> created
> clustered index based on the Partitioning Column. I copied the same
> 5million
> records into this table but it took only 60mins.
> Shouldn’t the copy in the second case be taking more time because of the
> presence of the Clustered Index? Or did the created partition increase the
> performance?
> Later, I subjected the table to a set of queries and compared the
> performance against the non-partitioned table and I could not see much of
> a
> performance gain.
> Considering all else equal, ie the query is based on the Partitioned
> Column
> which is inturn based on one of the keys in the Clustered Index, I
> expected
> good performance gain. But I was disappointed to see that it did not offer
> much.
> I just wanted to hear from others and see what their experiences are.
> Thanks,
> rgn
>