inserting array into SQL DB using ASP and ADO

[Christopher]

I would like to know how I can improve performance in inserting data
from an XLS sheet into SQL using a Stored Proc.

Steps:

1) Upload XLS file to server
2) SELECT sheet from XLS file and inserting it into an array
3) Do a FOR loop through the XLS import array and executes a
parameterized connection to the SQL server using a new
ADODB.Connection & ADODB.Recordset for each record.

It works, but is it the best way of doing this? The results are
horrible in my opinion. The act of importing almost 1,100 records
from the XLS file takes about 8 minutes--- refreshing SQL Enterprise
Manager shows a guessed average of about 5 records every 2 seconds.

Anyone enlighten me? I will eventually be moving my code over to .NET
but not for another >3 months as our team builds up knowledge on it.

---- CODE SNIP ----
Set connTemp = Server.CreateObject("ADODB.Connection")
connTemp.ConnectionTimeout = Session("connTemp_ConnectionTimeout")
connTemp.CommandTimeout = Session("connTemp_CommandTimeout")
connTemp.Open DSNTemp

For I = LBound(Session("dataExcel"),2) to
UBound(Session("dataExcel"),2)
Set cmdTemp = Server.CreateObject("ADODB.Command")
Set rsObj = Server.CreateObject("ADODB.Recordset")
cmdTemp.CommandText = "sp_Insert_XLS"
cmdTemp.CommandType = adCmdStoredProc

..... extra parameters snipped for space ...

Set cmdTemp.ActiveConnection = connTemp
Set tmpParam = cmdTemp.CreateParameter("Web", adVarChar,adParamInput,
255, Session("dataExcel")(15,I))
cmdTemp.Parameters.Append tmpParam

..... extra parameters snipped for space ...

set rsObj = cmdTemp.Execute()
set rsObj = nothing
Set cmdTemp = Nothing
Next
set connTemp = Nothing
---- CODE SNIP ----


Thanks much in advance!

Christopher Lemon

[Bob Barrows [MVP]]

Why not use SQL Server's builtin Import functionality (Data Transformation
Services). It's bound to be much faster than anything you could code
together. Go to [url]www.sqldts.com[/url] for details. They have a few tutorials there.

Bob Barrows
Christopher wrote:

> I would like to know how I can improve performance in inserting data
> from an XLS sheet into SQL using a Stored Proc.
>

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Jeff Cochran]

On 20 Jul 2004 06:57:40 -0700, [email]clemon@apwa.net[/email] (Christopher) wrote:

>I would like to know how I can improve performance in inserting data
>from an XLS sheet into SQL using a Stored Proc.
>
>Steps:
>
>1) Upload XLS file to server
>2) SELECT sheet from XLS file and inserting it into an array
>3) Do a FOR loop through the XLS import array and executes a
>parameterized connection to the SQL server using a new
>ADODB.Connection & ADODB.Recordset for each record.
>
>It works, but is it the best way of doing this?

I wouldn't think so. SQL's DTS is made for this job, can be scheduled
and should be almost instantaneous compared to looping through a
record set. Look in BOL for Data Transformation, or just walk through
the wizard, it's pretty simple to configure it.

Jeff

[Christopher]

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message news:<O0fvAjmbEHA.368@TK2MSFTNGP10.phx.gbl>...

> Why not use SQL Server's builtin Import functionality (Data Transformation
> Services). It's bound to be much faster than anything you could code
> together. Go to [url]www.sqldts.com[/url] for details. They have a few tutorials there.
>
> Bob Barrows
> Christopher wrote:

> > I would like to know how I can improve performance in inserting data
> > from an XLS sheet into SQL using a Stored Proc.
> >

Yeah I could, but I would rather not because the end result is an
employee doing the work and making it as seamless as possible. I had
been digging around a it and found that if I do a INTO statement I can
tunnel my data from the Excel Spreadsheet right into SQL Server
without having do do the Array & FOR loop. But of course I am having
difficulties with that ;) Our servers are not linked, but I am trying
to do a DSN call and route it through there...

The Web Sever AND the SQL server are seperate boxes too.

Any thoughts on how to accomplish this?



Phfft.

[Bob Barrows [MVP]]

Christopher wrote:

> "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
> news:<O0fvAjmbEHA.368@TK2MSFTNGP10.phx.gbl>...

>> Why not use SQL Server's builtin Import functionality (Data
>> Transformation
>> Services). It's bound to be much faster than anything you could code
>> together. Go to [url]www.sqldts.com[/url] for details. They have a few
>> tutorials there.
>>
>> Bob Barrows
>> Christopher wrote:

>>> I would like to know how I can improve performance in inserting data
>>> from an XLS sheet into SQL using a Stored Proc.
>>>

>
> Yeah I could, but I would rather not because the end result is an
> employee doing the work

??

DTS can be automated. Go to the site and check out the diffierent methods
for executing a DTS package (including from ASP).

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Christopher]

Thanks for both of your replies. Although I didn't want to use DTS I
can see that it seems this is the best route.

What annoyes me is I was trying to do an INTO using a OPENDATASOURCE
connecting to the XLS file and I kept getting errors and no matter
what KB's I do it still doesn't work.

Thanks for taking the time to reply!

[email]jeff.nospam@zina.com[/email] (Jeff Cochran) wrote in message news:<40fe4ab3.183385534@msnews.microsoft.com>...

> On 20 Jul 2004 06:57:40 -0700, [email]clemon@apwa.net[/email] (Christopher) wrote:
>

> >I would like to know how I can improve performance in inserting data
> >from an XLS sheet into SQL using a Stored Proc.
> >
> >Steps:
> >
> >1) Upload XLS file to server
> >2) SELECT sheet from XLS file and inserting it into an array
> >3) Do a FOR loop through the XLS import array and executes a
> >parameterized connection to the SQL server using a new
> >ADODB.Connection & ADODB.Recordset for each record.
> >
> >It works, but is it the best way of doing this?

>
> I wouldn't think so. SQL's DTS is made for this job, can be scheduled
> and should be almost instantaneous compared to looping through a
> record set. Look in BOL for Data Transformation, or just walk through
> the wizard, it's pretty simple to configure it.
>
> Jeff