Passing in a recordset to SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Passing in a recordset to SQL

Author

Topic

gmetaj
Starting Member

33 Posts

Posted - 2004-09-07 : 16:36:31

I have a storedprocedure AddToHolding that is being called from inside VB code thousands and thousands of time. All this sproc consist of is an insert statement that inserts one row into a table. Doing this numerous times slows my application significantly. I am considering doing one huge insert that will have all the data inside. to be able to do that I need to be able to pass somehow a VB 6 recordset to microsoft sql. I know that I can not pass in a recordset as a parameter, but is there a way around it. all i need to do is to write the content of the recordset into the table.

Thank you for all your help and suggestions.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-07 : 16:50:25

You can pass the values into a stored procedure comma separated. Have a look at the CSV article or check the forums for examples.

Someone else might have a better answer for you though.

Tara

danielhai
Yak Posting Veteran

50 Posts

Posted - 2004-09-07 : 18:14:48

how much data is being sent in per row? If it's ID-based, you can get up to 8000 characters - try using this function

<code>

CREATE FUNCTION dbo.fn_GetIDsFromString (@String varchar(8000))
RETURNS @Return Table (ID int, RowID int identity PRIMARY KEY) WITH SCHEMABINDING
AS
BEGIN

declare @Current smallint,
@TempString varchar(20)

set @Current = 1

while (CHARINDEX(',',@String,@Current) > 0)
begin
set @tempstring = CHARINDEX(',',@String,@Current) - @Current

if @tempstring <> 0
begin
insert into @Return (ID) values (substring(@String,@Current, cast(@tempstring as int)) )
end

set @Current = CHARINDEX(',',@String,@Current) + 1
end

if substring(@String,@Current, len(@String)) <> ''
insert into @Return (ID) values (substring(@String,@Current, len(@String)) )

return
END
then you could do a procedure like:

CREATE PROCEDURE sp_test

@Input varchar(8000)

AS

insert into table (
column1,
column2
) select value1,
[ID],
from dbo.fn_getIDsFromString(@Input)

</code>

hope that helps.

danielhai
Yak Posting Veteran

50 Posts

Posted - 2004-09-07 : 18:16:14

another way is to write a csv file or something, move the file to the db server, and bulk insert - i think it would all depend on the size of your inserts though.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-07 : 23:10:23

if you're talking about one row insert and it's slowing down your application, better check if the sproc needs tuning, check if there is a need for an index or if there are indexes that you don't need.

gmetaj
Starting Member

33 Posts

Posted - 2004-09-08 : 09:07:57

The size of what's being inserted is larger than 8000 characters. It is about 300 records. From my experience and from what I have read online it seems that doing

300 Inserts is significantly slower than doing one INSERT that has 300 selects insides. What we are currently doing is

INSERT INTO TABLENEW
SELECT DATA FROM TABLEINFO

this is being repeated 300 times. I am thinking about putting all those 300 different selects (SELECT Data from TABLEINFO) into a VB6 recordset and than do a batch insert of all that info.

Thank you for your help.

Gent

quote:
Originally posted by danielhai

how much data is being sent in per row? If it's ID-based, you can get up to 8000 characters - try using this function

<code>

CREATE FUNCTION dbo.fn_GetIDsFromString (@String varchar(8000))
RETURNS @Return Table (ID int, RowID int identity PRIMARY KEY) WITH SCHEMABINDING
AS
BEGIN

declare @Current smallint,
@TempString varchar(20)

set @Current = 1

while (CHARINDEX(',',@String,@Current) > 0)
begin
set @tempstring = CHARINDEX(',',@String,@Current) - @Current

if @tempstring <> 0
begin
insert into @Return (ID) values (substring(@String,@Current, cast(@tempstring as int)) )
end

set @Current = CHARINDEX(',',@String,@Current) + 1
end

if substring(@String,@Current, len(@String)) <> ''
insert into @Return (ID) values (substring(@String,@Current, len(@String)) )

return
END
then you could do a procedure like:

CREATE PROCEDURE sp_test

@Input varchar(8000)

AS

insert into table (
column1,
column2
) select value1,
[ID],
from dbo.fn_getIDsFromString(@Input)

</code>

hope that helps.

danielhai
Yak Posting Veteran

50 Posts

Posted - 2004-09-08 : 13:59:35

maybe what you could do is run the procedure only when you have surpassed the 8000 character point - maybe get it down to 5 or so transactions rather then 300 ... i use the prior function to insert 100's of records at the same time, but the records are numbers delimited by commas ... you could modify the function to allow for strings ... delimited by some other character ...

Hommer
Aged Yak Warrior

808 Posts

Posted - 2004-09-08 : 14:58:38

Instead of sending recordset to SQL Server, another option is to send the source data from server directly to SQL server using DTS/bcp, or xml/web service.

I suspect you have to load the recordset from some kind of database source (Oracle/db2/Sybase server?), then manipulate the data through buz logic, then save them back to SQL Server.

If those buz logics are not too complcate to be replaced with T-SQL, my option will save you a trip to the server.

Hommer

gmetaj
Starting Member

33 Posts

Posted - 2004-09-08 : 15:07:43

Thank you for all your feedback. I solved this problem using a batch update approach using the UpdateBatch command inside the ADODB.Recordset.

Thanks!

Gent

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources