Re: Slow queries

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: david epsom dot com dot au (david_at_epsomdotcomdotau)
Date: 07/23/04 

Date: Fri, 23 Jul 2004 13:37:08 +1000

It's not normally the sum that slows it down, it's the grouping,
but FWIW, SUM ignores nulls: abs(Sum([QtyShip]*[IsPosted])), and
Jet recalculates referenced fields, so the query gets slower
every time you use that calculated value.

(david)

"Michael S. Montoya" <blank@blank.com> wrote in message
news:ggWLc.201$4x.164@newssvr27.news.prodigy.com...
> I have a database that has many save queries to give me inventory on hand
> balances. A rail car comes in with multiple inventory items. I have a
> CarHeader and CarDetail, with the Detail listing the various items and qty
> received of each item. I do not have a place for OnHand or TotalSold as I
> am using queries to get these values. I opted to go with the query method
> to insure data integrity (what if something happend while the sale was
being
> made and the CarDetail's onhand didn't get updated, or what happens if two
> users access the same inventory/CarDetail record at the same time)
>
> The only problem is the database is getting slower and slower as rail cars
> are entered and sales are made.
>
> I am going though the many queries and I noticed I had several dlookup()
and
> dsums() in some queries and have gotten rid of those by adding another
query
> to the sql.
>
> I also see that I use the nz() function many times in the queries. I
> believe I need this for instances where there are no sales on the joined
> table, I will get zero instead of a null. as well as a function like
> Shipped: Sum(Nz([QtyShip],0)*Abs(nz([IsPosted],0))) (this will only deduct
> from inventory only those order that are posted.
>
> Do the NZ() or ABS() functions slow down queries drasticaly? Is there a
> work around. In your guys' opinion, should I just write the QtySold back
to
> the CarDetail table? If I need to write the sale back to the Detail
table,
> how do I insure data integrity for issues like multiple users posting
> different orders at the same time?
>
> Thanks in advance for help on this one.
>
>
>

Relevant Pages

  • RE: Dynamically referencing a recordset?
    ... through that recordset to set the query parameters in VBA. ... to be the one who had to modify 1440 queries if there is a change in the ... different table schema, then you need 3, one for each record type. ... This is a database that tracks the production on records in another ...
    (microsoft.public.access.modulesdaovba)
  • Re: Was: what does "serialization" mean?
    ... > the specific queries up front. ... that the NEXT time the query is run the query is fast, ... data base, don't know squat" when in fact the Donald clone doesn't ... >> WHATEVER would probably be a separate query to summarize total sales. ...
    (comp.programming)
  • Re: Official Status of SQLServer 2005 ADP
    ... I have said that the support for SQL passthrough ... queries under MDB was bad and worst than the one offered by ADP while you ... > attempt to "pass through" every Access query against a linked ODBC ...
    (microsoft.public.access.adp.sqlserver)
  • Re: "Query Too Complex" Errors
    ... few dozens of queries, in the middle of which there's a long chain of ... we've been having a lot of those "Query Too ... some of the complexity in the SQL ... SQL statement you are working on. ...
    (microsoft.public.access.forms)
  • Re: Cluster synchronize
    ... queries per unit time. ... CPU is the ONLY bottleneck. ... increase in query capacity. ... queries that perform sequential I/O or queries performing random I/O. ...
    (microsoft.public.sqlserver.clustering)