Exporting Large Query, Kills Access

  • Thread starter Thread starter Chris.Buske
  • Start date Start date
C

Chris.Buske

I'm trying to export a query that has 35K+ results, with calculation on
a few of the fields for each, and everytime i try to export it it dies,
is there anway to speed it up, or possibly write a function in vba that
would break it apart and append it to a file?

access 2003
xp pro
1 gig ram
P4 2.4gig
Local DB
 
Here's the SQL for it, it's a monster...

SELECT Orders.OrderNumber, Orders.OrderDate, Orders.OrderTime,
IIf([ordertime]>#12/30/1899 14:0:0#,[orderdate]+1,[orderdate]) AS
LADate, DFirst("[datetime]","[AppDatesTimes]","numerickey=" &
[ordernumber]) AS ApproveDate,
DFirst("[ladate]","[AppDatesTimes]","numerickey=" & [ordernumber]) AS
[LA Approve], Tracking.DateAdded AS DateShipped,
IIf([dateadded]-[ladate]<0,0,[dateadded]-[ladate]) AS [Days-Order
Date], IIf(DateDiff("d",CVDate([la
approve]),CVDate([dateadded]))<0,0,DateDiff("d",CVDate([la
approve]),CVDate([dateadded]))) AS [Days-Approval],
NZ(DSum("[quantityshipped]","[orderdetailsqueryloc]","[adjustment]=false
and dropship=false and [dateshipped] is not null and location<>'Old
Bridge' and ordernumber=" & [ordernumber]),0) AS [#Items],
CCur([#items]*0.73) AS PickCost
FROM Orders, Tracking
WHERE (((Orders.OrderNumber)=Val([ordernum])) AND
((Tracking.DateAdded)>=#10/26/2005#) AND
((NZ(DSum("[quantityshipped]","[orderdetailsqueryloc]","[adjustment]=false
and dropship=false and [dateshipped] is not null and location<>'Old
Bridge' and ordernumber=" & [ordernumber]),0))>0) AND
((Orders.Cancelled)=False))
ORDER BY Tracking.DateAdded;
 
In what format are you exporting the data and how are you exporting it? If
Excel, you may be trying to stuff more records into a worksheet than the
selected version of Excel allows. If I remember correctly OutputTo by default
exports spreadsheets to Excel 3 which allows only 16K records.

The SQL is ugly. How does it run by itself? Looking at it I sure hope that
the ordernum in
WHERE (((Orders.OrderNumber)=Val([ordernum]))
is part of the Tracking table. Otherwise you might have a Cartiesian joint
there.

Also make sure that Tracking.DateAdded and Orders.OrderNumber are both
indexed.

Speaking of indexing, if you are using a function for something in the Where
clause, Access will most likely NOT use a index. If you can avoid functions
in a Where clause, things will work much better. The overuse of functions is
often a sign of poor table normalization.
 
It's a hack job i know, mark of youth, but it gets the job done, kind
of. I've tried exporting to text and to excel, using both
docmd.transfertext for the text and transferspreadsheet for excel
(running now, going on about an hour i think), i've also tried using
file | export for both formats, just hangs, i'm betting if i let it sit
for a day or 2 it would finish, but that's not exactly feasible,
luckily i can run it on the server where it's not pulling across a
network, tried that first day and nearly killed myself. ordernum is
part of the tracking table and while it is indexed, it's a text field
in tracking and a number field in orders.. function you're talking
about i'm guessing is the dsum? if it is i don't know another way
around it, the count isn't stored anywhere, Tracking.DateAdded and
Orders.OrderNumber are both indexed, but just noticed tracking.ordernum
is not... should i remove
WHERE (((Orders.OrderNumber)=Val([ordernum]))
and create a join instead and add the val() part?
 
should i remove
WHERE (((Orders.OrderNumber)=Val([ordernum]))
and create a join instead and add the val() part?
Click to expand...

I doubt if that would work in OrderNumber is a number and ordernum is text.
You best bet is to change ordernum to a Number data type and then ditch the
Val. Also index ordernum.

Also consider putting a copy of everything on your hard drive and running
everything there including the export to hard drive. Test it. Might just be a
network problem.

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


CkanoaB said:
It's a hack job i know, mark of youth, but it gets the job done, kind
of. I've tried exporting to text and to excel, using both
docmd.transfertext for the text and transferspreadsheet for excel
(running now, going on about an hour i think), i've also tried using
file | export for both formats, just hangs, i'm betting if i let it sit
for a day or 2 it would finish, but that's not exactly feasible,
luckily i can run it on the server where it's not pulling across a
network, tried that first day and nearly killed myself. ordernum is
part of the tracking table and while it is indexed, it's a text field
in tracking and a number field in orders.. function you're talking
about i'm guessing is the dsum? if it is i don't know another way
around it, the count isn't stored anywhere, Tracking.DateAdded and
Orders.OrderNumber are both indexed, but just noticed tracking.ordernum
is not... should i remove
WHERE (((Orders.OrderNumber)=Val([ordernum]))
and create a join instead and add the val() part?
Click to expand...
 
i'll mirror the db and change the datatype, everything's already on a
local drive, hopefully that change will speed up the process
 
Those domain functions are going to be killers
too, if there are many records. And in the criteria!
That will probably run much better if you use a
join or a correlated subquery. Or If this is just
something you run yourself, I would make a temp
table for that dsum - you can use an append query
to get the values.

For the DFirst, you would be better off using
a user defined function like this,
http://easyweb.easynet.co.uk/~trevor/downloads/baslookup.zip
but in the criteria, even that will be slow.

Also, TransferText will be a bit slower than using
the equivalent append or create table query to write
to XLS or Text, but in this case i think that is
the least of your worries :~)

(david)
 
Back
Top