[VB6] Save routine too slow

[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.