[VB6] Save routine too slow

[Arachnid13]

I have a VB6 program in which my save routine is very slow... At runtime the data is stored in a class/collection structure and shown using a treeview.
The save routine runs throught the class/collection structure, executing an sql statment for each part of the data which corresponds to a record in my database.
The looping through the structure is fine, the speed problem seems to be down to the fact that the more complex documents my program deals require about 25000 records to be written to the database, currently I am writing each record using a seperate connection.Execute "some SQL" statement and so the save can take upto a few minutes, with the sql statements commented out it takes literally seconds.
Would it be quicker to join all the sql with semi-colons and do one execute at the end? and if so is there a limit to the length of the string executed or the number of queries?

[RhinoBull]

... currently I am writing each record using a seperate connection.

If you're looping then why are you reconnecting every time?

- open connection
- loop through whatever and insert each record
- close connection

[zeezee]

What is your DB? and what are the sructures for the tables. I think it may be problem of your DB. It may not be tuned for speed. If it invoves tables with composite keys and many references, time may be higher.
And some DBs have options to support to run big multiple querries quickly.

[zeezee]

If you're looping then why are you reconnecting every time?

- open connection
- loop through whatever and insert each record
- close connection

Rhino, I think you misunderstood . OP means seperate Exectute for each insert. Like.
Connection.Execute "Insert INTO ....."

[RhinoBull]

I think OP was quite clear - separate connection means ... well ... separate connection.

[Arachnid13]

rhino, thanks for the input but zeezee was right.. i said a seperate "connection.execute" not a seperate "connection".. the connection is opened at the start of the loop and closed at the end... im not an idiot!

zeezee, the database is an access database, the structure is as follows:

table 1 is the "document" table
table 2 is the "sections" table (contains a reference to table 1)
table 3 is the "rows" table (contains a reference to table 2)
table 4 is the "functions" table (also contains a reference to table 2)
table 5 is the "items" table (contains a reference to table 3)
table 6 is the "parameters" table (contains a reference to table 5)
table 7 is the "triggers" table (contains a reference to table 4)

as far as i can see the database structure is as efficient as it can be.. imagine the loop like this:

open connection

for each section in document

insert relevant info about the section into the database
for each function in section

insert relevant info about the function into the database
for each trigger in function

insert relevant info about the trigger into the database

next trigger

next function
for each row in section

insert relevant info about the row into the database
for each item in row

insert relevant info about the item into the database
for each parameter in row

insert relevant info about the parameter into the database

next parameter

next item

next row

next section

close connection

[Arachnid13]

maybe this post should be in database development?
can an admin give their opinion please?

[zeezee]

well as I thought the references may be the problem. You see , for an example, when you are inserting to table2, it has to check for the table1 for the correct reference ,right? If the there is a FK violation , the db should check that.

so thats what I meant by tuning the DB, not its structure, by having proper indexes , etc , ... Tune it for performance. But I m not a DB expert and better to PM a moderator and ask to put it there. Or you can start a new thread also.

Another thing is in MySQL (which I use) , there is an option to turn off FK checks. We use this particularly when backing up using DB dumps. There are other options to enable large quarries also. I m not sure about Access though.

Since you are using class/Collections ,the code seems to fine. So I guess the problem is the DB.