Advice on VB6 - SQL Express 2005 Script

[jdgti]

I have a SQL Express 2005 Database containing about 40 tables. I have an old VB6 script that runs several queries on the DB. The Script also imports a file from the hard drive, parses the file, then inserts the records as long as the db does not already contain records with that information.

The problem is, the processing done by this script is taking a much longer time than it used to, possibly because the database is becoming so large since the script was originally written.

I am assuming some of the queries I wrote for this program are not optimized.

I am using this style to query the SQL DB:

Dim rsNewOrders As Recordset
Set rsNewOrders = New Recordset
strNewOrdersSQL = "SELECT ordersAll.* FROM ordersAll, ordersNew WHERE ordersNew.[order-id] = ordersAll[order-id]"
With rsNewOrders
    .CursorLocation = adUseClient
    Call .Open(strNewOrdersSQL, conSQL)
End With

Then to compare data, I do things like

quantity = rsNewOrders![quantity]

To set it to a VB variable and then compare it later in the program to another VB variable.

- Is this horribly inefficient? Is there a more optimized way to do this? I am doing SELECTS, UPDATES, INSERTS, JOINS, all of which are done from the VB side using this same style of querying.

- But before I go optimizing all of this, I am wondering if moving to .NET or some other language to accomplish this task (possibly even web based) would be more efficient??

TIA.

 

[gmmastros]

When you are loading data in to the database, you gotta ask yourself.... "Do I have more code in VB, or more code in SQL". If your answer is VB, then yes, you are doing things inefficiently.

In a situation like this, I would bulk load the data in to a temp table. Then, I would write several queries to load the data in to your real tables. Basically.... if the record already exists, then update the real table. If it's a new record, then insert to the real table. It's possible to write these queries in a set based way. It's likely to be many times faster than your current method.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jdgti]

I have two files "ALL.txt" that contains data from the last 2 weeks, and "NEW.txt" which contains data from today.

I DELETE all records from two temp tables (ordersAll, ordersNew) that have already been created. Then I take the data from the two files and bulk load those into their respective tables. The two orders table are like temporary master tables containing a lot of columns which that will be spread around to different tables in the databse after my script is done. They are like a flat view of the relational database before the script is run.

From here I do a lot of querying/checking of different tables in the database to make sure the correct information gets input to the database from the temporary tables. This is done mostly in this format:

For all records in rsOrdersNew:
1.) run a SELECT on one of the tables in the DB
2.) create a recordset from that select (lets call it rsContents)

For all records in rsContents:
3.) set some temp_vars = rsContents fields
4.) compare temp_vars to the rsOrdersNew (current record)
5.) if no match is found, INSERT into this specified table the information to create a new record.

This process is repeated for each table that needs to be updated with the information from the ALL and NEW files.

 

[gmmastros]

All of your numbered steps (last half of most recent post) should be done in the database.

Instead of pulling the data back in to VB so you can loop and check for update vs. delete, do those things in the database using a set based approach.

If you want.... post some sample data from the relevant tables, and I will be able to advise better.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jdgti]

By sample data do you mean like a column list for all of the tables or do you mean actual records in the tables?

I have both of these ready I just don't want to post the wrong thing, since you said you might be able to help.