Optimization Help

[amolbehl]

Hi,

I have had to write a software with VisualBasic 6.0 and MS Access.

Now the database that I deal with has some 1 million records and I perform operations on this and based on certain criteria I need to look at a part of this table and generate a bunch of insert statements.

Overall there are some 100 thousand inserts followed by records getting deleted from the 1 million record table. The operation seems to fail because the MAX LOCKS are reached.

I solved this by editing the registry entry.

However the operation takes a few hours to complete. The main reason for this is the frequent INSERTS.

I just want to know if there is any way of optimizing this.

[Killer42]

You'll probably get more useful ideas on this from the Access forum. But one or two things which comes to mind are...

1. Write out your new records to a text file, then import it at the end.
2. In a similar vein, INSERT them to a temporary table in the database (or another database) and then do one big copy operation at the end. Depending on how you do the copy, maybe this would save time.
3. Just save all the new info in a huge array, then INSERT them all in one big operation.
4. One optimisation which was pointed out to me in the Access forum a while back is to issue SQL commands directly against a connection, rather than opening a recordset all the time. It did seem to help. Um... I think that was an ADO connection.

Of these. my guess is that a combination of #1 and #4 might provide the best times. But definitely try some searching with our search box up the top, and/or ask about it in the Access forum.

[QVeen72]

Hi,

Create indexes on the fields which you use in search criteria or Where conditions.
Creating proper indexes will definitely increase the performance.
Do you also have a PK created?

Regards
Veena

[Killer42]

One thing to keep in mind, though. The indexes have to be updated to keep them current, each time you update a record. So more indexes means slower updates. Obviously, as Veena pointed out, an appropriate index will make a big difference to performance on record retrieval. But don't just index everything indiscriminatel y, or you'll slow down your updates too much.

[lotus18]

Hi,

I have had to write a software with VisualBasic 6.0 and MS Access.

Now the database that I deal with has some 1 million records and I perform operations on this and based on certain criteria I need to look at a part of this table and generate a bunch of insert statements.

Overall there are some 100 thousand inserts followed by records getting deleted from the 1 million record table. The operation seems to fail because the MAX LOCKS are reached.

I solved this by editing the registry entry.

However the operation takes a few hours to complete. The main reason for this is the frequent INSERTS.

I just want to know if there is any way of optimizing this.

I guess its time for you to learn sqlserver for dealing million of records. :)

[amolbehl]

Hmmm well Index wont work coz I have huge number of inserts and they are the one which kill the system.

Anything can be done to fasten up inserts ... presently I am working on storing like a few thousand insert statements in an array and then sourcing them in one go ... do you think this helps ... ???

[QVeen72]

Hi,

One more way is :
Create a Temparory Table with Same Table Structure as Main...
Delete all recs from Temp..
Insert Recs into Temp (Since no recs, Inserts are faster) through loop..
At the End, Insert everything from Temp To Main Table in one go..

Regards
Veena

[Killer42]

It sounds worth a try, Veena.

If anyone needs a hand building the SQL for the copy from Temp to Main, just tell Access to create an Append Query, then copy the SQL.

[amolbehl]

Hmmmm my major concern now is getting rid of "System resources exceeded" error.

Do you think I could reset the connection and do it??

But I have a global which I start in the module .... and I do not know how to switch it on and off.
this is the function

gstrDBPath = "c:\dvp\bulk"
Set gdbFML = OpenDatabase(gstrDBPath & "\common.mdb")  
Load frmPalletComplete
frmPalletComplete.Show vbModal