Upsizing question

[Guest]

I have a BE I am converting to SQL Server. One of my routines links to an
Excel file and then takes the data from the excel file and inserts it into
one of my BE tables. The table I am inserting into has about 50 fields, most
of which are lookups to other tables. The Excel spreadsheet has user
friendly values supplied by the user. During the Insert, I must translate
the values given by the user in the spreadsheet into the appropriate IDs in
each of the supplemental tables.

I have done this both as a loop with .Addnew/.Update, and with a complicated
Append Query with lots of joins.

In the .mdb world, the Query worked OK, but I used the .Addnew/.Update
mechanism because I could show a progress bar on the screen and it was not
all that much slower than the query.

Now that I am going to SQL Server, the .Addnew/.Update loop is WAY too slow,
and the Query is not much better. I am thinking of converting it to a stored
procedure.

My question is, do I pass each excel record in turn to the SP (with each
field as a separate parameter)? Or is there a way to pass the entire
contents of the spreadsheet to the SP? In either case is there a limit to
the number of parameters that can be passed? Or to the type of parameters:
some are long comment fields?

 

[Ron Weiner]

Bill

Without having any real knowledge of what you are doing, how far along you
are in the conversion process, or how the rest of the application is
performing, it is difficult to know what is making your routines WAY to slow
when updating data from a spreadsheet to the Sql Back end. Could be
anything from inappropriate or missing indexes, a slow running update / add
trigger on the new table, to some problem with your server or network.

As a workaround / test, you might consider leaving this one table and
process alone in your backend Access database and use your old routines to
update it. Then when the old table is updated, real "quick like a bunny"
update the Sql table directly from the Access table. Should only take a
second to run an update query from a table in the Access backend with 50
columns and a couple hundred rows to the Sql server. If you discover that
updating the Access table (linked to lookups in the Sql side) takes a long
time in this scenario, I'd start snooping around Sql looking at the current
indexes to see if they are appropriate.

While this does not answer your main question (I do not know if / how Sql
can link to or import data from a excel spreadsheet.), it does allow you to
move on to the "next thing" on your "To-Do" list. Here are some of the
answers to your other specific questions.

Max number parameters = 2100
Max size of a Stored Proc = 128Mb
Parameters can be any Sql type including Text, Image or even user defined
types.