PDA

View Full Version : Slow dBase access in VB6

plaidme
10-18-02, 13:55
I am programming a small app that modifies dbase databases created by an outside application. I'm not quite sure what version of dBase it is using. I am using this connection string:

Set dbDBF = CreateObject("ADODB.Connection")

dbDBF.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _
"DriverID=277;" & _
"Dbq=" & Directory

but I have upwards of 900 querys running in the application, and it ends up taking about 8 hours to fully complete it's task. That's a bit too long ;)... It's a wizard, so the first couple boxes just ask some questions, and the last one does all the work... but i notice that even the first couple boxes that only have 1 or 2 queries running before their display take anywhere from 10 to 30 seconds to load. Is there a faster way to do this?

Thanks,
Noah
rnealejr
10-18-02, 16:27
How are you executing the queries ? Do you have other applications that hit the database to compare slowness ? You mention the word "modify" - are you doing updates/deletes/inserts ?
plaidme
10-21-02, 14:09
i don't know the name of the databases until runtime. based on the date the user wants to modify, the databases will be different. There are anywhere from 5000 entries in one table to 35000 entries in one of the more detailed databases. The effect I am looking for is deletion of certain records. I have to basically copy most entries from one database to another. skipping ones they specify, for about 3 different databases. Therefore, what i do is a

I do a while loop setting:
"select * from databases_they_chose.DBF where ID <> " & id_nums_to_delete
so it ends up being something like:

sql = "select * from databases_they_chose.dbf where id<>4 AND id<>7 AND id<>9 AND id<>38"
Set dbRs = CreateObject("ADODB.Recordset")
dbRs.Open sql, dbDBF

then i have to update the ids--so if i deleted 4, then 5 becomes 4 and 6 becomes 5 and since i deleted 7, 8 become 6, etc...

so i loop through that select query and use the same code to "insert into temporary_database (id, col1, col2, etc) values (newid, col1, col2, etc)".

i guess you are probably wondering why i dont just do a delete query. Well... first off i have to update the IDs, but secondly, there is one database of sales, and one of the detail of the sales, so when the ID of the sale 45 gets bumped down to 44, i have to update the detailed sales Sale_ID of each item purchased from 45 to 44 as well.

then after all that is done for all the databases, i copy from temporary db to db and voila--it works! but its REALLY slow.

thanks a lot,
Noah
rnealejr
10-21-02, 14:55
Why are you renumbering the ids ?
plaidme
10-22-02, 13:58
because unfortunately, since this app modifies an outside program's db, i need to update the id's to make the program work correctly. Basically, it has to seem as if nothing as happened in the program, and it was like that all along...

Thanks,
Noah
rnealejr
10-22-02, 15:15
Do you have to have a sequential id with no gaps ? How are you updating the detail sales table ? Does the dbase you use support cascading updates or triggers ?
plaidme
10-22-02, 17:55
i do need sequential ids w/no gaps... basically, in my loop to update the sales table, i have a variable that keeps track of the OLD id, and then i update the detailed sales db: update sales set id=newid where id=oldid... and i basically just loop through that for each item purchased....

noah
plaidme
10-22-02, 18:01
Originally posted by rnealejr
Do you have to have a sequential id with no gaps ? How are you updating the detail sales table ? Does the dbase you use support cascading updates or triggers ?

Sorry, but i am not sure whether it supports those... to tell you the truth, im not sure what cascading updates and triggers are... :(

Noah
rnealejr
10-26-02, 03:09
I need more information about the process. Like when are these queries running - are you rearranging the data while people are using it or is this happening when no one is on ? The deletes that you are filling the gaps - how many do you have to fix - and again is anyone on the db when you are filling the gaps ? Also, how are you currently doing the updates - using a recordset object and the update method ? The way you have described the scenario - I am not surprised how slow the process is. Can you give me more information about what you have to do behind the scenes and how the table structure affects other tables in your process ? Are you having to support an "unknown" application that generates these tables - so basically you are blind ? It almost seems like it would be more cost effective to rewrite the app. What does the application do ?
plaidme
10-28-02, 17:19
this is a side application that is run when nobody is using the system. It works on a Point of Sale software. There is a database for sales and for detailed sales for each mmyy combo. So that would make, say 10 and 10--10 sales databases for 01/02 to 10/02, and 10 detailed sales dbs. Basically, in the item sales, there is a Bill_Number (int), and some sales info (total, taxes, time, etc). The detailed sales is a db with one entry for each item purchased for each sale. So there might be 3 entries for one sale, and 12 for another. Each of those has field Bill_Number that, obviously, has to correlate to the bill number of the general sale.

Because this client is using a number of different computer systems, it is necessary at some times to delete certain sales of a certain payment type for a certain server (etc) from this system. This is where this app comes in--it basically goes in the database and deletes those orders.

Because each database is labeled sales0302.dbf, sales0402.dbf, etc, i do not know what database I am using until the user types in the date they want to edit in the first form.

Because I use database access so much in this program, I actually made Database an Object. I have openDB(dbpath), closeDB(), runSqlQuery(qry), and closeRecordset() methods.

the basic code i use for opening a connection is:
dbDBF.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _
"DriverID=277;" & _
"Dbq=" & Directory

Set OpenDBConnection = dbDBF

and for running a query, basically:

Set dbRs = CreateObject("ADODB.Recordset")

dbRs.Open sql, dbDBF

Set RunSQLQuery = dbRs

and will run queries:

Set dbRsPmt = dbDBF.RunSQLQuery(sql_PMT)

i hope that helps... if you need any more info, just give me a hollar.

Thanks an awful lot,
noah
plaidme
10-28-02, 17:21
thought i would include a comment i have that basically sums up what i do...

' loop through the Sales table and only copy the sales that were not selected
' to the temprary sales table
' then update the detailed sales table to reflect this by copying only the
' unselected bills to the temporary db, and updating the bill numbers to the new ones in the sales table
rnealejr
10-30-02, 18:21
Sorry for the delay but I have been away - but will respond to you by this weekend.