Enhancing ADO import query (1 Viewer)

[LEXCERM]

Morning all,

I have the following snippet of code which connects to our sqlServer via ADO:-

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String

    sConnString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security " & _
                    "Info=True;Initial Catalog=PostTripDM;Data Source=SERVER5;" & _
                    "Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;" & _
                    "Tag with column collation when possible=False"

    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    conn.Open sConnString
    
    Set rs = conn.Execute("SELECT TEST_ID, ClientID FROM dimTEST;")
    
    Do Until rs.EOF
    CurrentDb().Execute ("INSERT INTO tbl_DboDimTESTLocal VALUES (""" & rs("TEST_ID") & """,""" & rs("ClientID") & """);")
    rs.MoveNext
    Loop

I appreciate that network connectivity/speed will vary for many different reasons. At present, importing 140,000 records is taking around 20/30 minutes using this method.

I have had to create a routine that first imports the data to the users own Front-End and then transfer the data to the Back-End table. This seems to speed up the process a little.

My question is can the above code be changed so that it works more efficiently, thus speeding up the process?

Thanks in advance for advice and feedback.

 

[Minty]

Have you considered doing the update on the SQL server by calling a Stored Procedure?
Or is this data sent to you via a Excel spreadsheet or similar hence doing it locally on the front end ?

 

[LEXCERM]

Thanks for replying Minty.

I have worked with Stored Procedures in a previous life lol (last job where our sql server man would assist me on this), but it's different now.

At present, I am only able to connect to the SQL tables as per code above and import the data as shown.

 

[Minty]

Okay, lets look from another angle - I'm not sure you need to do the loop in your SQL?
You have loaded a record set that may be unnecessary? If the table is already loaded locally simply run the insert SQL based on your imported table?

Not sure if I'm over simplifying it or missing a stage?

 

[Galaxiom]

You have not shown the code or context you use to do the job directly. Just the local table technique.

Is there a good reason why you don't simply link the source and destination tables and use a query?

Writing temporary data to the FE is often done but it really isn't a good idea. If you must use a temp table it is better to put it in a separate linked database. This avoids bloating the FE.

When a record is added to a table, any indexes must be updated. This can take longer than inserting the record so avoid having any indexes on your temporary table.

Repeatedly executing an SQL string is considerably slower than executing a saved query because a query plan must be built each time. A saved query with parameters should be faster.

 

[LEXCERM]

Thanks Minty and Galaxiom for your replies and apologies for not getting back to you sooner.

Read both your posts with interest. Due to laggy connectivity issues, we're going to schedule output files from SQL each morning. This will only happen once a day and these files will be imported just the once on first opening of the database.

Much cleaner and quicker.

Thanks again to the both of you.

 

[arnelgp]

are you deleteting the records of tbl_DboDimTESTLocal and re-inserting new, updated records? i'm sure most of the records are dormant, why not employ a tactic to update records only and insert new ones, that way your processing time will be much less.