Help with making a table act faster

[BPhilb]

Hi,

I'm having a problem producing a table in a timely fashion and it's an essiential table for a report I am building. I'm pulling records from an ODBC connection and the table I'm pulling from is nearly a million records. I have a very basic code trying to pull the applicable data I need into a table and was wondering if anyone had any tips on what I might be able to try to get through the recordset quicker. My current code is:

Function OpenUnits()
Dim db As Database
Dim qry As DAO.QueryDef
Dim qry1 As DAO.QueryDef
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset, rst2 As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("WM241BASD_ChCART31")
Set rst2 = db.OpenRecordset("OpenUnits")
rst.MoveFirst
Do Until rst.EOF
If rst("CHSTAT") < 25 Then
rst("CHCASN") = rst2("Carton")
rst("CHPCTL") = rst2("PickTicket")
Else
rst.MoveNext
End If
Loop
End Function

Thanks in advance for any help you can provide.

 

[MajP]

Can't this be done in an update query?

 

[MajP]

Does this code even do what you want? You loop through rst, but not through rst2.

rst2("Carton"), and rst2("PickTicket") always return the same value I think. I would declare as static before the loop. But unless I am missing somethin this looks like it could easily be done in sql.

 

[BPhilb]

Hi MajP,

Will an update query work since I am working from an ODBC table? Essiently when the program runs I will call this function to wipe out the current table and pull brand new data into the new table.

 

[BPhilb]

SQL will work also and I'm ok with running it that way, but I'm still having the problem of the program taking almost 30 minutes to run. I'm just seeing if there is anythink I can do to get through the records faster. I know just enough about VB to be dangerous and was wondering if there are some steps I should try such as filters or bookmarks that might be able to help me. Thanks for your replies.

 

[MajP]

Are you saying that you did this in SQL and it still takes 30 minutes? Personally, I know little about ODBC so hopefully some of the experts chime in. But, I have rarely seen a time when a query was not substantially faster than looping through a recordset.

 

[BPhilb]

Yes..I was running an append query initially and it was taking 30 minutes there. I might just be out of luck because the table is so big and I was hoping that VB might give me a few more options to get through the recordset quicker. I appreciate your time in trying help.

 

[MajP]

There was a good FAQ on optimizing queries. Search around in here and google. There are a lot of tips and tricks especially when you are working with client server setups. I would post this in the queries forum, but I personally would not go down the ado/dao path. I just do not believe you can ever out pace SQL.

 

[BPhilb]

Thanks MajP. I'm looking through the FAQ's now. There is a ton of cool information here.

 

[PHV]

Just my 2 cents:
A recordset approach is, IMHO, never faster than a well optimized passthrough query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

PHV,
That is what I said, but pass through queries are not my strong area. Can you help him out? I know that this is one of your many strong points.

 

[PHV]

In order to help him out I've to know what it want to do ...

 

[BPhilb]

Is there more information I can give you? I'm not versed well at all in pass through queries. I did find a logical recordset in my tables last night that has helped speed the process up some but it still is taking me much longer than I like.

 

[PHV]

What is the SQL code of the slow (but working) query ?
What is the backend RDBMS ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BPhilb]

INSERT INTO Header SELECT FROM WM241BASD_CDCART15 INNER JOIN WM241BASD_CHCART02 ON WM241BASD_CDCART15.CDCASN = WM241BASD_CHCART02.CHCASN
WHERE (((WM241BASD_CHCART02.CHPCTL) Not Like "W*") AND ((WM241BASD_CHCART02.CHSTAT)<"25"))
GROUP BY WM241BASD_CDCART15.CDSTYL;

The data is coming from a client access system. It's a make table query at this time, but will be an append query once integrated in the program.

 

[PHV]

Are you sure the above SQL code belongs to a working query ? I doubt.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BPhilb]

SELECT WM241BASD_CHCART02.CHPCTL, WM241BASD_CHCART02.CHSTAT, WM241BASD_CHCART02.CHCASN, WM241BASD_CDCART15.CDSTYL, WM241BASD_CDCART15.CDTBPB INTO Header
FROM WM241BASD_CDCART15 INNER JOIN WM241BASD_CHCART02 ON WM241BASD_CDCART15.CDCASN = WM241BASD_CHCART02.CHCASN
WHERE (((WM241BASD_CHCART02.CHPCTL) Not Like "W*") AND ((WM241BASD_CHCART02.CHSTAT)<"25"));


Sorry...just noticed that. The query works and the data matches what I am looking for it's just forever in getting the data to come over.

 

[PHV]

Some guesses to improve performance.
Make sure that the following fields are indexed:
WM241BASD_CDCART15.CDCASN
WM241BASD_CHCART02.CHCASN
WM241BASD_CHCART02.CHSTAT

You may use the following WHERE clause:
WHERE Left(WM241BASD_CHCART02.CHPCTL,1) <> 'W' AND WM241BASD_CHCART02.CHSTAT < "25"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886