By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,129 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

Database Efficiency

P: n/a
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.MyDate,
qryDataDaily.Week, qryDataDaily.Period, qryDataDaily.MonthYear,
qryDataDaily.PeriodName, qryINindent.InIndentTEUsWater,
qryINindent.InIndentTEUsYard, qryFCin.fcINteusyard,
qryINindent.InIndentTEUsReceived, qryFCin.fcINteusreceived,
qryINindent.InIndentPalletsDevanned, qryFCin.fcInindentpallets,
qryINlocal.InLocalPalletsReceived, qryFCin.fcInlocalpallets,
[InIndentPalletsDevanned]+[InLocalPalletsReceived] AS
InTotalPalletsReceived, [fcInindentpallets]+[fcInlocalpallets] AS
fcINtotalPallets, qryINDIRECTfinancials.ReceivedValue,
qryWHoffice.WHAllocations, qryWHoffice.WHShortSweep,
iif([OUTcmergeCartonsSorted]>0,[OUTcmergeCartonsSorted],0)+iif([WHncSortOMs]>0,[WHncSortOMs],0)+iif([WHsecurityOMs]>0,[WHsecurityOMs],0)
AS TotalOMsProcessed, qryFCwh!fcWHcOms+qryFCwh!fcWHncOMs AS
fcOMsProcessed, (iif(qryOUTcWrap!OUTcWrapPallets>0,qryOUTcWrap!OUT cWrapPallets,0))+(iif(qryWHncSort!WHncSortPalletsB uilt>0,qryWHncSort!WHncSortPalletsBuilt,0))
AS TotalPalletsBuilt, qryINDIRECTfinancials.ProductionValue,
qryWHoffice.WHOrderbank, qryOUTdDespatch.OutDespTotalPallets,
qryFCout.fcOutpallets, qryWHinvCC.WHinvPalletsDC,
qryWHinvCC.WHinvPallets3PL, qryWHinvCC.WHinvPalletsonhand,
qryINDIRECTfinancials.SOHvalue,
qryOUTdDespatch.OutDespTotalPalletsDock, qryFCretail.fcCostofSales
FROM ((((((((((((((qryDataDaily LEFT JOIN qryINindent ON
(qryDataDaily.MyDate=qryINindent.MyDate) AND
(qryDataDaily.DC=qryINindent.DC)) LEFT JOIN qryINlocal ON
(qryDataDaily.MyDate=qryINlocal.MyDate) AND
(qryDataDaily.DC=qryINlocal.DC)) LEFT JOIN qryOUTcMerge ON
(qryDataDaily.MyDate=qryOUTcMerge.MyDate) AND
(qryDataDaily.DC=qryOUTcMerge.DC)) LEFT JOIN qryWHncSort ON
(qryDataDaily.MyDate=qryWHncSort.MyDate) AND
(qryDataDaily.DC=qryWHncSort.DC)) LEFT JOIN qryWHsecurity ON
(qryDataDaily.MyDate=qryWHsecurity.MyDate) AND
(qryDataDaily.DC=qryWHsecurity.DC)) LEFT JOIN qryOUTcWrap ON
(qryDataDaily.MyDate=qryOUTcWrap.MyDate) AND
(qryDataDaily.DC=qryOUTcWrap.DC)) LEFT JOIN qryOUTdDespatch ON
(qryDataDaily.MyDate=qryOUTdDespatch.MyDate) AND
(qryDataDaily.DC=qryOUTdDespatch.DC)) LEFT JOIN qryINoffice ON
(qryDataDaily.MyDate=qryINoffice.MyDate) AND
(qryDataDaily.DC=qryINoffice.DC)) LEFT JOIN qryWHoffice ON
(qryDataDaily.MyDate=qryWHoffice.MyDate) AND
(qryDataDaily.DC=qryWHoffice.DC)) LEFT JOIN qryWHinvCC ON
(qryDataDaily.MyDate=qryWHinvCC.MyDate) AND
(qryDataDaily.DC=qryWHinvCC.DC)) LEFT JOIN qryFCout ON
(qryDataDaily.DC=qryFCout.DC) AND
(qryDataDaily.MyDate=qryFCout.MyDate)) LEFT JOIN qryFCin ON
(qryDataDaily.DC=qryFCin.DC) AND (qryDataDaily.MyDate=qryFCin.MyDate))
LEFT JOIN qryFCretail ON (qryDataDaily.DC=qryFCretail.DC) AND
(qryDataDaily.MyDate=qryFCretail.MyDate)) LEFT JOIN qryFCwh ON
(qryDataDaily.DC=qryFCwh.DC) AND (qryDataDaily.MyDate=qryFCwh.MyDate))
LEFT JOIN qryINDIRECTfinancials ON
(qryDataDaily.DC=qryINDIRECTfinancials.DC) AND
(qryDataDaily.MyDate=qryINDIRECTfinancials.MyDate) ;
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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" <na**************@hotmail.com> wrote in message
news:4b**************************@posting.google.c om...
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.MyDate,
qryDataDaily.Week, qryDataDaily.Period, qryDataDaily.MonthYear,
qryDataDaily.PeriodName, qryINindent.InIndentTEUsWater,
qryINindent.InIndentTEUsYard, qryFCin.fcINteusyard,
qryINindent.InIndentTEUsReceived, qryFCin.fcINteusreceived,
qryINindent.InIndentPalletsDevanned, qryFCin.fcInindentpallets,
qryINlocal.InLocalPalletsReceived, qryFCin.fcInlocalpallets,
[InIndentPalletsDevanned]+[InLocalPalletsReceived] AS
InTotalPalletsReceived, [fcInindentpallets]+[fcInlocalpallets] AS
fcINtotalPallets, qryINDIRECTfinancials.ReceivedValue,
qryWHoffice.WHAllocations, qryWHoffice.WHShortSweep,
iif([OUTcmergeCartonsSorted]>0,[OUTcmergeCartonsSorted],0)+iif([WHncSortOMs]0,[WHncSortOMs],0)+iif([WHsecurityOMs]>0,[WHsecurityOMs],0)
AS TotalOMsProcessed, qryFCwh!fcWHcOms+qryFCwh!fcWHncOMs AS
fcOMsProcessed, (iif(qryOUTcWrap!OUTcWrapPallets>0,qryOUTcWrap!OUT cWrapPallets,0))+(iif(qryW
HncSort!WHncSortPalletsBuilt>0,qryWHncSort!WHncSor tPalletsBuilt,0)) AS TotalPalletsBuilt, qryINDIRECTfinancials.ProductionValue,
qryWHoffice.WHOrderbank, qryOUTdDespatch.OutDespTotalPallets,
qryFCout.fcOutpallets, qryWHinvCC.WHinvPalletsDC,
qryWHinvCC.WHinvPallets3PL, qryWHinvCC.WHinvPalletsonhand,
qryINDIRECTfinancials.SOHvalue,
qryOUTdDespatch.OutDespTotalPalletsDock, qryFCretail.fcCostofSales
FROM ((((((((((((((qryDataDaily LEFT JOIN qryINindent ON
(qryDataDaily.MyDate=qryINindent.MyDate) AND
(qryDataDaily.DC=qryINindent.DC)) LEFT JOIN qryINlocal ON
(qryDataDaily.MyDate=qryINlocal.MyDate) AND
(qryDataDaily.DC=qryINlocal.DC)) LEFT JOIN qryOUTcMerge ON
(qryDataDaily.MyDate=qryOUTcMerge.MyDate) AND
(qryDataDaily.DC=qryOUTcMerge.DC)) LEFT JOIN qryWHncSort ON
(qryDataDaily.MyDate=qryWHncSort.MyDate) AND
(qryDataDaily.DC=qryWHncSort.DC)) LEFT JOIN qryWHsecurity ON
(qryDataDaily.MyDate=qryWHsecurity.MyDate) AND
(qryDataDaily.DC=qryWHsecurity.DC)) LEFT JOIN qryOUTcWrap ON
(qryDataDaily.MyDate=qryOUTcWrap.MyDate) AND
(qryDataDaily.DC=qryOUTcWrap.DC)) LEFT JOIN qryOUTdDespatch ON
(qryDataDaily.MyDate=qryOUTdDespatch.MyDate) AND
(qryDataDaily.DC=qryOUTdDespatch.DC)) LEFT JOIN qryINoffice ON
(qryDataDaily.MyDate=qryINoffice.MyDate) AND
(qryDataDaily.DC=qryINoffice.DC)) LEFT JOIN qryWHoffice ON
(qryDataDaily.MyDate=qryWHoffice.MyDate) AND
(qryDataDaily.DC=qryWHoffice.DC)) LEFT JOIN qryWHinvCC ON
(qryDataDaily.MyDate=qryWHinvCC.MyDate) AND
(qryDataDaily.DC=qryWHinvCC.DC)) LEFT JOIN qryFCout ON
(qryDataDaily.DC=qryFCout.DC) AND
(qryDataDaily.MyDate=qryFCout.MyDate)) LEFT JOIN qryFCin ON
(qryDataDaily.DC=qryFCin.DC) AND (qryDataDaily.MyDate=qryFCin.MyDate))
LEFT JOIN qryFCretail ON (qryDataDaily.DC=qryFCretail.DC) AND
(qryDataDaily.MyDate=qryFCretail.MyDate)) LEFT JOIN qryFCwh ON
(qryDataDaily.DC=qryFCwh.DC) AND (qryDataDaily.MyDate=qryFCwh.MyDate))
LEFT JOIN qryINDIRECTfinancials ON
(qryDataDaily.DC=qryINDIRECTfinancials.DC) AND
(qryDataDaily.MyDate=qryINDIRECTfinancials.MyDate) ;

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.