Hi, I have an Access 2010 with an SQL Server 2008 back end.

A couple of times a week I have cause to append from a local table between 100 and 6000 records to a table in the SQL Server

The SQL server table has a number of fields and each append requires different combinations of these. Sometimes I append to fields x,y & z and other times a,b & z.

In addition to this, dependent on criteria I may add an incremented integer value to certain records when I append them. Others I dont.

Originally (to get it working) I simply used a DAO recordset and looped through the local table adding records to a criteria to mapped fields in the SQL Server table. This is ok on the small runs but for 6k records it takes between 30-50 minutes.

I am looking to improve performance and have tried:


Looping local table and using QueryDef to change the statement in a Passthrough then executing it.
Creating a Stored Procedure, opening a ADODB connection, looping the local table changing the statement and excuting it.
Exporting the local table to SQL Server and then using a Stored Procedure to append to the Main table. This worked quickly but I didnt know how to add the conditional incremented value.

I have attempted these on a test version of the table for 100 records and the response in both cases are still not particularly quick.

My questions are.


For what I am trying to do which approach would be best.
Should I be looking at my tables for the append performance improvements.
Is it possible to do this without the loop with a server function (not that I have experience of these)

I have searched the forum but not found a specific answer that helps point me in the right direction. If I have missed a post then appologies and please point me too it.

Many thanks in advance,

Dan

The SQL server table has a number of fields and each append requires different combinations of these. Sometimes I append to fields x,y & z and other times a,b & z.

This has me thinking structural issues with the table but many of us here are paranoid because we see so many problems caused by the underlying data structure.

I think we need more infomation.

How do you decide the field that are being appended?

Hi thanks for the reply.

Hopefully this will clarify a bit more. There are the following contact name fields


dat_Title
dat_Initial
dat_Firstname
dat_Lastname
dat_Fullname
dat_Salutation_Letter
dat_Salutation_Envelope

Depending on who has sent the data not all of these are supplied and the names of the fields could be anything and I map them to our standard fields. Currently I iterate through my map for each supplied table and build the required append SQL for either Passthrough or Jet query.

All supplied records are appended.

This is the bit that has me stumped other than looping through each record client side and adding record by record. If there is a telephone number in any field supplied telephone field I need to add a reference number that increments each time.

Regards

Dan

Other than pointing out that dat_Initial and dat_Fullname are denormalized fields I don't think have much to suggest because I am still trying to get my head around it.

How is the data supplied to you?

Presumably the field names are consistent within the one supplied dataset and your append query is built to add all the records at a time rather than individually for a particular dataset.

Where do the phone numbers fit in?

The data is supplied by various charities and they choose to record in differing fields for contact names. Therefore one may collect Firstname and Surname and another might just have a fullname field or an initial and lastname, and so on. Add to that the same charity might send their their data one way one month and another the next. Therefore there are fields in our table to cater for all potential types provided. We need to return their processed data in the same format once we are through with it.

They also supply various telephone fields, again maybe just one but sometimes up to three. We only need to know a number exists so I look across each of the telephone fields they supply for each record and identify if a number exists in any of these fields. If there is a telephone number we need to give the record a unique value for this batch of data (Starting at 1 for each import)


This all works ok on a record by record basis where I loop through each record locally and then add the single record to the server via one of the methods I mentioned above.

I am wondering if I can do this in one hit or server side rather than locally.

thanks again. Hopefully thats clearer.

Dan

Seems to me the process is convoluted by nature and you have had a pretty good effort making it work at all.

The only suggestion I could make that *might* be worth a try is to write the data to a disconnected ADO recordset. ADO like this is held entirely in your machine's RAM (until it can hold any more of course and it writes to the local disk).

With a connected recordset each record is being transmitted back to the server as it is added and written to disk. With the disconnected recordset the processing is done locally without disk or network activity. Then when your data is complete, reconnect it to the SQL server and it is written in a batch.