Access to SQL Server - Too Slow - Help Please

[hegartyjp]

I would greatly appreciate any help I can get on this.

I have written an enormous amount of code in VBA in an access from which creates a recordset from a table on SQL Server.

It then loops through the recordset and processes each record, i.e. checks all flags etc and then writes a new record back to SQL Server based on the relult of all the processing in the form.

The problem is I have to process 470,000 records and after running for the whole day, I am only up to 11,000.

I am connecting from Access 2000 to SQL Server 2000 using a basic ODBC.

Is there any way to get the speed up without having to transfer all the processing from Access to SQL Server....

As I said, Any help greatly appreciated.

Thanks........

 

[macleod1021]

Short answer: Nope.

Your limitation is that you have to pull the data from the server, process it, then send it back. If you're processing the data for that long and only got 11k records done, then your only choices are to cut back the amount of processing being done, or move it to a stored procedure or function on the SQL server.

 

[hegartyjp]

I have only started using SQL Server and so do not know how to do the processing without putting it in access.

The processing involves a lot of if statements. and further small stored procedures to get further data from some tables based on original values from the first recordset.

I use one stored procedure to bring back all the data from the source table.

would it be faster to bring it back one by one, process and return each record?

would it be faster to bring the whole table into access and create a new table with the result data and then write it all back?

If I used something other than ODBC would it be quicker?

 

[cmmrfrds]

Unless you want to duplicate the code on the sql server side, then it would be faster to put into a local Access table. After the table is created in Access, you could ask your DBA to transfer the Data to sql server through DTS or have them create a linked server to your Access table and then write an sql statement on the sql server side to insert into the sql server table.

 

[niall29]

Hegartyip
I would recommend that you try to write it in SQL. I used to use access alot and then I was forced to change and I wouldn't go back.
I always found access very limited to what it could do and
470,000 records is asking alot.
What are you doing to get your recordset and why would you not write it in SQL.
One of my queries used to take about 3hrs to run in Access but when I wrote it in SQL and put it on the server the same query ran in about 2 to 4 mins

 

[belovedcej]

I would also recommend that you look into adp (Access Projects). Using an ODBC connection, as you have noticed, can be slow! With adp all procedures and views are server side and server-fast. - not that this solves your immediate issue, but it is a tip for the future.

 

[hegartyjp]

Thanks to all of you for the advice.

I have reduced the processing and made some less complicated s procs.

Managede to process 300,000 today so nearly ther.

Thanks

 

[SQLSister]

I also recommend that you never ever use a loop to go through data. If you need to update, then do it in a set based statement, the conditionals can be handles with CASE (look it up in BOL for examples of how to do that).'

Looping is something you should forget you ever heard of. Data sets are very bad things to loop through for the most part.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief