Database Efficiency

**Larry Linson** · Nov 13 '05, 02:48 AM

Re: Database Efficiency

The devil, Ross Perot said, is in the details.

If you could be a bit more explicit... for example, regarding "compatible
with Excel". If you are exporting the results of a query to Excel, the NZ
function will not be a factor... it will already have performed its duty and
only the result will be in the exported data.

Is this a split Access - Jet database engine database, an Access client to a
server database, or ??? I assume it is likely one of these, since the
network seems to be a factor, which it would not be if the user interface
(front-end) and datatables (back-end) were on the same computer, or were
together in a monolithic database on the user's machine.

Under what circumstances are you using this SQL... as the RecordSource of a
Form or Report, or to define a Recordset you are reading/writing in VBA, or
to display the results in Datasheet view? Or, are you perhaps planning to
run this SQL _from_ Excel?

Just for the record, that is rather complicated and long SQL for a new
database developer.

The best collection of links and information on performance that I have seen
is at MVP Tony Toews' site, http://www.granite.ab.ca/accsmstr.htm.

Larry Linson
Microsoft Access MVP

"Nathan Bloomfield" <nathanbloomfie ld@hotmail.com> wrote in message
news:4bbc1902.0 408130340.4a650 034@posting.goo gle.com...[color=blue]
> Hi,
>
> I am a new database developer and would appreciate some advice on
> improving the efficiency of my database.
>
> The code below is an example of one of the more complex queries in the
> database (very basic in the scheme of things).
>
> Can anyone offer advice in improving the structure considering this
> database resides on a network with limited bandwidth. The query runs
> fine with 2 weeks worth of data (1000 records), however, I am
> concerned that the query will slow to a crawl once the database grows
> in size (eventually containing 50,000+)
>
> It is not possible, in the short term, to modify the structure of the
> underlying queries & tables. Also, the data must be fully compatible
> with excel (so no "nz" functions or the like).
>
> Any feedback is much appreciated.
>
> SELECT qryDataDaily.FY , qryDataDaily.DC , qryDataDaily.My Date,
> qryDataDaily.We ek, qryDataDaily.Pe riod, qryDataDaily.Mo nthYear,
> qryDataDaily.Pe riodName, qryINindent.InI ndentTEUsWater,
> qryINindent.InI ndentTEUsYard, qryFCin.fcINteu syard,
> qryINindent.InI ndentTEUsReceiv ed, qryFCin.fcINteu sreceived,
> qryINindent.InI ndentPalletsDev anned, qryFCin.fcInind entpallets,
> qryINlocal.InLo calPalletsRecei ved, qryFCin.fcInloc alpallets,
> [InIndentPallets Devanned]+[InLocalPalletsR eceived] AS
> InTotalPalletsR eceived, [fcInindentpalle ts]+[fcInlocalpallet s] AS
> fcINtotalPallet s, qryINDIRECTfina ncials.Received Value,
> qryWHoffice.WHA llocations, qryWHoffice.WHS hortSweep,
>[/color]
iif([OUTcmergeCarton sSorted]>0,[OUTcmergeCarton sSorted],0)+iif([WHncSortOMs][color=blue]
>0,[WHncSortOMs],0)+iif([WHsecurityOMs]>0,[WHsecurityOMs],0)
> AS TotalOMsProcess ed, qryFCwh!fcWHcOm s+qryFCwh!fcWHn cOMs AS
> fcOMsProcessed,[/color]
(iif(qryOUTcWra p!OUTcWrapPalle ts>0,qryOUTcWra p!OUTcWrapPalle ts,0))+(iif(qry W
HncSort!WHncSor tPalletsBuilt>0 ,qryWHncSort!WH ncSortPalletsBu ilt,0))[color=blue]
> AS TotalPalletsBui lt, qryINDIRECTfina ncials.Producti onValue,
> qryWHoffice.WHO rderbank, qryOUTdDespatch .OutDespTotalPa llets,
> qryFCout.fcOutp allets, qryWHinvCC.WHin vPalletsDC,
> qryWHinvCC.WHin vPallets3PL, qryWHinvCC.WHin vPalletsonhand,
> qryINDIRECTfina ncials.SOHvalue ,
> qryOUTdDespatch .OutDespTotalPa lletsDock, qryFCretail.fcC ostofSales
> FROM ((((((((((((((q ryDataDaily LEFT JOIN qryINindent ON
> (qryDataDaily.M yDate=qryINinde nt.MyDate) AND
> (qryDataDaily.D C=qryINindent.D C)) LEFT JOIN qryINlocal ON
> (qryDataDaily.M yDate=qryINloca l.MyDate) AND
> (qryDataDaily.D C=qryINlocal.DC )) LEFT JOIN qryOUTcMerge ON
> (qryDataDaily.M yDate=qryOUTcMe rge.MyDate) AND
> (qryDataDaily.D C=qryOUTcMerge. DC)) LEFT JOIN qryWHncSort ON
> (qryDataDaily.M yDate=qryWHncSo rt.MyDate) AND
> (qryDataDaily.D C=qryWHncSort.D C)) LEFT JOIN qryWHsecurity ON
> (qryDataDaily.M yDate=qryWHsecu rity.MyDate) AND
> (qryDataDaily.D C=qryWHsecurity .DC)) LEFT JOIN qryOUTcWrap ON
> (qryDataDaily.M yDate=qryOUTcWr ap.MyDate) AND
> (qryDataDaily.D C=qryOUTcWrap.D C)) LEFT JOIN qryOUTdDespatch ON
> (qryDataDaily.M yDate=qryOUTdDe spatch.MyDate) AND
> (qryDataDaily.D C=qryOUTdDespat ch.DC)) LEFT JOIN qryINoffice ON
> (qryDataDaily.M yDate=qryINoffi ce.MyDate) AND
> (qryDataDaily.D C=qryINoffice.D C)) LEFT JOIN qryWHoffice ON
> (qryDataDaily.M yDate=qryWHoffi ce.MyDate) AND
> (qryDataDaily.D C=qryWHoffice.D C)) LEFT JOIN qryWHinvCC ON
> (qryDataDaily.M yDate=qryWHinvC C.MyDate) AND
> (qryDataDaily.D C=qryWHinvCC.DC )) LEFT JOIN qryFCout ON
> (qryDataDaily.D C=qryFCout.DC) AND
> (qryDataDaily.M yDate=qryFCout. MyDate)) LEFT JOIN qryFCin ON
> (qryDataDaily.D C=qryFCin.DC) AND (qryDataDaily.M yDate=qryFCin.M yDate))
> LEFT JOIN qryFCretail ON (qryDataDaily.D C=qryFCretail.D C) AND
> (qryDataDaily.M yDate=qryFCreta il.MyDate)) LEFT JOIN qryFCwh ON
> (qryDataDaily.D C=qryFCwh.DC) AND (qryDataDaily.M yDate=qryFCwh.M yDate))
> LEFT JOIN qryINDIRECTfina ncials ON
> (qryDataDaily.D C=qryINDIRECTfi nancials.DC) AND
> (qryDataDaily.M yDate=qryINDIRE CTfinancials.My Date);[/color]

Database Efficiency

Database Efficiency

Comment