473,320 Members | 2,004 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Database Efficiency

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
1 1916
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

18
by: mountain man | last post by:
Greetings to all database professionals and laymen, Let us make a bold assumption that we have developed a software tool for the SQL Server environment which simply acts as an interface between...
1
by: Scott Chapman | last post by:
I am working with Python (psycopg). I have HTML with embedded Python that I'm inserting into a database and it could contain any character. Single quotes, at least, must be escaped (to two...
13
by: MLH | last post by:
I have a RDBMS app consisting of 3 primary mdb's... 1) a front-end with a few STATIC tables and the other menagerie of objects 2) a back-end with most of my DYNAMIC tables. I'll call it my main...
335
by: extrudedaluminiu | last post by:
Hi, Is there any group in the manner of the C++ Boost group that works on the evolution of the C language? Or is there any group that performs an equivalent function? Thanks, -vs
19
by: vamshi | last post by:
Hi all, This is a question about the efficiency of the code. a :- int i; for( i = 0; i < 20; i++ ) printf("%d",i); b:- int i = 10;
10
by: Jay | last post by:
In C# I can set up an Enum so that number are represented as keywords, which is very useful. Is there such a datatype in a database? I suppose I could use an extra table, with the ID column as...
10
by: ircmaxell | last post by:
Ok, I have a program that reads the contents of a file (1 line, 5 '|' seperated items). Every so often (between twice a day, and 200 times a day), it rewrites the contents of that file. I also do...
3
Chrisjc
by: Chrisjc | last post by:
I was told the other day that I was able to place HTML in my database.. So I thought to my self that be grate for my feature points so I can use bullets... so I put the following code in one of my...
9
by: OldBirdman | last post by:
Efficiency I've never stumbled on any discussion of efficiency of various methods of coding, although I have found posts on various forums where individuals were concerned with efficiency. I'm...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.