Sorting through millions of records

[BPhilb]

I'm running a query in which I'm pulling data of an AS400 system. The table I'm pulling from has probably about 5 million records in it dateing back to early September. I've been running an append query that took about 4 to 5 minutes to update everyday but has now gotten to the point where it's taking hours to do. The update is just looking for certain fields in which the date [THDATS] is equal to a text box date (usually date()-1). I know an append query probably isn't the best way to go about this but would anyone have any suggestions as to how I can just pull the dates I need from the table in question in a quciker manner? I can provide more information if possible. Thanks for your assistance.

Brad

 

[JoanneM]

Hi & good morning

Just been sat thinking over your dilema and I had one passing idea -
create a select query that uses a parameter ( in the criteria line put
= SetMonthforQuery()

which calls the function beleow returning a month name
so that the recordset to be appended is only a fraction of the size - causing a fraction of the work

as your append query is sourced from that select query

so in pseudo code

set a public variable to hold the month name/number
dim myMonthToFilter

for monthname = sept to Thismonth
MyMonthTofilter = Monthname
runappendquery
doevents
next monthname


function SetMonthforQuery
SetMonthforQuery = MyMonthTofilter
end function

No guarantees here as you have to take into account your network limitations and the version of access you are using.


The Doevents command will ensure that the last process is completed before allowing the loop to continue.

this has a double benefit -
you could write some timer code and find out which area is taking longest so you are aware of it

it could also help pinpoint data anomolies as lengthy processing may mean a discrepancy in your input processes


if you need the full VBA let me know

hope this is of some help.

regards

jo

 

[MichaelRed]

As long as it is in the JET dbEngine, it is going to take a while. It takes a LOT longer to do it on a nework (w/ a split db), as the Jet engine will copy the entire 'cross product' to the local system for processing. One (short term) help would be to simply copy the 5Meg records to your local machine as a temporary recordset before running the process. Having the recordset instantiated locally will improve the performance quite a bit, but at some point in the growth even this will prove inadequate.

A better soloution would be to get an 'industrial strength' dbEngine (SQL Server ... ) for the back end. Fortunatly, this can be accomplished without to much upheaval with Ms. A. (2K +), by using the MSDE which is a (limited) SQL Server dbEngine (missing some of the nice to have front end tools, like T-SQL), and which has only a few licenses (5?). A simplistic conversion can be done almost in automatic (conversion of the TABLES), but then you need to create the stored procedures which relieve the network traffic (only processing the affected records over the network).


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BPhilb]

Thanks for your input here guys. Our company recently obtained an SQL server but it's goign to be a while before I will have access to using it. I do like jo's idea of pulling out the month that I am looking for and running against it. I will try that it see if that helps speed up the process any. I appreciate you time in helping me here. I've been beating myself up over this one for a couple of days now.

Brad