MDB - ADO - Slow Runtime after multiple users....

[Irish_Griffin]

Hey guys,
I've got a problem with my run time on a updater I run onto my database.

Background :
- My MDB file is updated hourly via a macro run on the server. This macro takes data from our 30 YEAR OLD software system, and applies it into my MDB file. Then it does some basic data manipulations.

- This updater function takes around 5 minutes to run.

Problem :
When a user sends data to be saved into the MDB while the updater is running, something strange happens.... The updater then take 30-40min to complete !!!

Details:
- Win XP
- Excel 2003
- ADO 2.8

Code

Here is the connection string I'm using both in the updater and in the user interface :

Dim SQL_Text As String
Dim Connection_String as String

Dim RS As ADODB.Recordset
Set RS = New ADODB.Recordset
    
SQL_Text = "SELECT * FROM [dropdown_lists]"
RS.Open SQL_Text, Connection_String, adOpenStatic, adLockOptimistic

I'm hoping people can offer input to my adOpenStatic and adLockOptimistic method ..... is there a more suited method ???


Many Thanks
- Griff

 

[Irish_Griffin]

Here is my connection string BTW

Connection_String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=R:\xxxxx\yyyyyy\zzzzzzz\Database_v01.mdb;"

 

[Irish_Griffin]

Bump -

Any input guys ???

This database update macro is becoming a major problem.

I want to make sure I've done the basics correctly.

Is my ADO connection string setup right for multiple users ?

 

[Norie]

Are you sure this is where the problem lies?

Do you even need to have multiple users run this update?

What sort of data are you dealing with?

Sorry for all the questions but I'm just wondering what's going on.

In my dealings with 'legacy' systems the main problem has been getting the data in the first place.

 

[Irish_Griffin]

Are you sure this is where the problem lies?

Do you even need to have multiple users run this update?

What sort of data are you dealing with?

Sorry for all the questions but I'm just wondering what's going on.

In my dealings with 'legacy' systems the main problem has been getting the data in the first place.

Thanks for the reply Norie !! I hate this stupid legacy system, my code would be 20 times better if I didn't have to deal with it


Well, I found the updater to be using this method:

 SQL_Text = "UPDATE change_details INNER JOIN pdm_change_data ON (change_details.geographic_id = .......bla, bla......data!ec_initiator WHERE (((change_details.pdm_change_status)<>'Closed'));"

Dim ADO_Connection As ADODB.Connection
Set ADO_Connection = New ADODB.Connection
ADO_Connection.Open (Connection_String)

'On Error Resume Next
    ADO_Connection.Execute SQL_Text

I think I'm going to remove these .Execute comands and replace the SQL with record by record data manipulation.



I'm also looking into using the adOpenForwardOnly cursor type ( replaces adOpenStatic ). I've read this cursor works faster, but I'm still not clear the differences yet......

Will update this thread after I learn more

Thanks,
-Griff

 

[Norie]

Griff

Unless you are dealing with individual records I'm not quite sure doing things record by record will speed things up.

Who knows though, perhaps I've just got some ingrained idea that using only a few lines of code has got to be faster than looping/whatever.

It'll be interested to know the outcome.

 

[Irish_Griffin]

So I have determined that the .EXECUTE commands are very quick, typically under a second. This is good considering I have alot of them.

I isolated these .EXECUTE commands and loop them 100 times on a seperate computer, then from my computer played with the user interface at the same time.

The user intereaction with the database had little effect on the run time of the .EXECUTE commands..... Thus this is not my problem.......


I'm going to continue to isolate different peices of this update code and repeat the same process untill I find the section that slows way down when the user interfaces with the DB.

 

[Irish_Griffin]

Ok, so I've trimmed it up a bit.

I identified a function that had nested recordset. IE, I create a recordset, and then for each item in this RS I generate 7 different temp record sets. What a mess


I found the .OPEN command to be what caused the runtime slow down. During the RS.OPEN ( before RS.CLOSE) , if any other user accessed the same table, the remaining .UPDATE's run much slower ( 3 times slower )

I replaced some of the .OPEN with .EXECUTE and simplified the SQL as much as I could.

Got the run time down 2.5 min and if a user access the database in this time, the max runtime is 8 mins.

In conclusion, take your time, code it right the first time .... and

AVOID USING LEGACY DATABASES LIKE THE PLAGUE !!!!


Peace,
-Griff

 

[Norie]

Griff

But it's impossible to avoid legacy databases - I think I've seen them in most places I've worked where historic data was involved.

Think when I first saw them and how they were structured I wondered what the developers were up to (on) back then.

Then I kind of realised that was how they had to do things.