SUPER, DUPER SLOW QUERY!

G

Glint

Hi All,
I built a select query based on one table (AssetsGeneral) that looks like a
behemoth thus:

SELECT AssetsGeneral.ItemID, AssetsGeneral.ItemName,
AssetsGeneral.ModelNumber, AssetsGeneral.SerialNumber,
AssetsGeneral.AssetCategory, AssetsGeneral.DateAcquired,
AssetsGeneral.PurchasePrice, AssetsGeneral.EstimatedValue,
AssetsGeneral.SalvageValue,
IIf(IsNull(DLookUp("[DepreciationRate]","AssetsGeneralCategory","[CategoryID]="
&
[AssetCategory])),Null,CInt(365*(100/DLookUp("[DepreciationRate]","AssetsGeneralCategory","[CategoryID]="
& [AssetCategory]))*([EstimatedValue]-[SalvageValue])/[EstimatedValue])) AS
DepreciableLife,
IIf([EstimatedValue]-NZ(DateDiff("d",[DateAcquired],Date()),0)*[EstimatedValue]*NZ(DLookUp("[DepreciationRate]/365","AssetsGeneralCategory","[CategoryID]="
& [AssetCategory]),0)>=DLookUp("[SalvageValue]","AssetsGeneral","[ItemID]=" &
[ItemID]),NZ(DateDiff("d",[DateAcquired],Date()),0)*[EstimatedValue]*NZ(DLookUp("[DepreciationRate]/365","AssetsGeneralCategory","[CategoryID]="
&
[AssetCategory]),0),[EstimatedValue]-DLookUp("[SalvageValue]","AssetsGeneral","[ItemID]="
& [ItemID])) AS DTD, [EstimatedValue]-[DTD] AS NBV,
IIf(IsNull(Forms!TB!SDate),Null,IIf(IsNull(DLookUp("[DepreciationRate]","AssetsGeneralCategory","[CategoryID]="
&
[AssetCategory])),0,IIf([DateAcquired]>Forms!TB!SDate,0,IIf(DateDiff("d",[DateAcquired],Forms!TB!SDate)>=[DepreciableLife],[EstimatedValue]-[SalvageValue],NZ(DateDiff("d",[DateAcquired],Forms!TB!SDate),0)*[EstimatedValue]*NZ(DLookUp("[DepreciationRate]/36500","AssetsGeneralCategory","[CategoryID]="
& [AssetCategory]),0))))) AS DTDTBS,
IIf(IsNull(Forms!TB!EDate),Null,IIf(IsNull(DLookUp("[DepreciationRate]","AssetsGeneralCategory","[CategoryID]="
&
[AssetCategory])),0,IIf([DateAcquired]>Forms!TB!EDate,0,IIf(DateDiff("d",[DateAcquired],Forms!TB!EDate)>=[DepreciableLife],[EstimatedValue]-[SalvageValue],NZ(DateDiff("d",[DateAcquired],Forms!TB!EDate),0)*[EstimatedValue]*NZ(DLookUp("[DepreciationRate]/36500","AssetsGeneralCategory","[CategoryID]="
& [AssetCategory]),0))))) AS DTDTBE, [DTDTBE]-[DTDTBS] AS DTDTBNet,
[EstimatedValue]-[DTDTBE] AS NBVTBE
FROM AssetsGeneral
WHERE (((AssetsGeneral.Disposed) Is Null));

This query works fine and returns records on the click of the mouse.

Then I based two fields of another query on this query thus:

Value:
IIf([BKID]=1,Format(NZ(DSum("[DTDTBNet]","vCurrentAssetsDepBK"),0),"Standard"),Format(-NZ(DSum("[DTDTBNet]","vCurrentAssetsDepBK"),0),"Standard"))

And Cum:
IIf([BKID]=1,Format(NZ(DSum("[DTDTBE]","vCurrentAssetsDepBK"),0),"Standard"),Format(-NZ(DSum("[DTDTBE]","vCurrentAssetsDepBK"),0),"Standard"))

This time, it ages to return results; you could almost finish a coffee, if
it takes you up to a minute to drink one. I cannot figure what is wrong. When
I remove the fields, the query if fast enough; but with either of them, it is
slow (more with the two together). Can you help me? Is there another way I
could accomplish the task?
 
O

OfficeDev18 via AccessMonster.com

There is a quicker way, although maybe not as quick as you'd like. Instead of
a SELECT query, make it a Make Table query (and delete the table each time
you run it) or an APPEND query (and delete all records each time you run it).
After running it, base your other queries on the new table. I suspect you'll
save a significant amount of time.

Sam
Hi All,
I built a select query based on one table (AssetsGeneral) that looks like a
behemoth thus:

SELECT AssetsGeneral.ItemID, AssetsGeneral.ItemName,
AssetsGeneral.ModelNumber, AssetsGeneral.SerialNumber,
AssetsGeneral.AssetCategory, AssetsGeneral.DateAcquired,
AssetsGeneral.PurchasePrice, AssetsGeneral.EstimatedValue,
AssetsGeneral.SalvageValue,
IIf(IsNull(DLookUp("[DepreciationRate]","AssetsGeneralCategory","[CategoryID]="
&
[AssetCategory])),Null,CInt(365*(100/DLookUp("[DepreciationRate]","AssetsGeneralCategory","[CategoryID]="
& [AssetCategory]))*([EstimatedValue]-[SalvageValue])/[EstimatedValue])) AS
DepreciableLife,
IIf([EstimatedValue]-NZ(DateDiff("d",[DateAcquired],Date()),0)*[EstimatedValue]*NZ(DLookUp("[DepreciationRate]/365","AssetsGeneralCategory","[CategoryID]="
& [AssetCategory]),0)>=DLookUp("[SalvageValue]","AssetsGeneral","[ItemID]=" &
[ItemID]),NZ(DateDiff("d",[DateAcquired],Date()),0)*[EstimatedValue]*NZ(DLookUp("[DepreciationRate]/365","AssetsGeneralCategory","[CategoryID]="
&
[AssetCategory]),0),[EstimatedValue]-DLookUp("[SalvageValue]","AssetsGeneral","[ItemID]="
& [ItemID])) AS DTD, [EstimatedValue]-[DTD] AS NBV,
IIf(IsNull(Forms!TB!SDate),Null,IIf(IsNull(DLookUp("[DepreciationRate]","AssetsGeneralCategory","[CategoryID]="
&
[AssetCategory])),0,IIf([DateAcquired]>Forms!TB!SDate,0,IIf(DateDiff("d",[DateAcquired],Forms!TB!SDate)>=[DepreciableLife],[EstimatedValue]-[SalvageValue],NZ(DateDiff("d",[DateAcquired],Forms!TB!SDate),0)*[EstimatedValue]*NZ(DLookUp("[DepreciationRate]/36500","AssetsGeneralCategory","[CategoryID]="
& [AssetCategory]),0))))) AS DTDTBS,
IIf(IsNull(Forms!TB!EDate),Null,IIf(IsNull(DLookUp("[DepreciationRate]","AssetsGeneralCategory","[CategoryID]="
&
[AssetCategory])),0,IIf([DateAcquired]>Forms!TB!EDate,0,IIf(DateDiff("d",[DateAcquired],Forms!TB!EDate)>=[DepreciableLife],[EstimatedValue]-[SalvageValue],NZ(DateDiff("d",[DateAcquired],Forms!TB!EDate),0)*[EstimatedValue]*NZ(DLookUp("[DepreciationRate]/36500","AssetsGeneralCategory","[CategoryID]="
& [AssetCategory]),0))))) AS DTDTBE, [DTDTBE]-[DTDTBS] AS DTDTBNet,
[EstimatedValue]-[DTDTBE] AS NBVTBE
FROM AssetsGeneral
WHERE (((AssetsGeneral.Disposed) Is Null));

This query works fine and returns records on the click of the mouse.

Then I based two fields of another query on this query thus:

Value:
IIf([BKID]=1,Format(NZ(DSum("[DTDTBNet]","vCurrentAssetsDepBK"),0),"Standard"),Format(-NZ(DSum("[DTDTBNet]","vCurrentAssetsDepBK"),0),"Standard"))

And Cum:
IIf([BKID]=1,Format(NZ(DSum("[DTDTBE]","vCurrentAssetsDepBK"),0),"Standard"),Format(-NZ(DSum("[DTDTBE]","vCurrentAssetsDepBK"),0),"Standard"))

This time, it ages to return results; you could almost finish a coffee, if
it takes you up to a minute to drink one. I cannot figure what is wrong. When
I remove the fields, the query if fast enough; but with either of them, it is
slow (more with the two together). Can you help me? Is there another way I
could accomplish the task?
Click to expand...
 
M

Marshall Barton

Glint said:
I built a select query based on one table (AssetsGeneral) that looks like a
behemoth thus:

SELECT AssetsGeneral.ItemID, AssetsGeneral.ItemName,
AssetsGeneral.ModelNumber, AssetsGeneral.SerialNumber,
AssetsGeneral.AssetCategory, AssetsGeneral.DateAcquired,
AssetsGeneral.PurchasePrice, AssetsGeneral.EstimatedValue,
AssetsGeneral.SalvageValue,
IIf(IsNull(DLookUp("[DepreciationRate]","AssetsGeneralCategory","[CategoryID]="
&
[AssetCategory])),Null,CInt(365*(100/DLookUp("[DepreciationRate]","AssetsGeneralCategory","[CategoryID]="
& [AssetCategory]))*([EstimatedValue]-[SalvageValue])/[EstimatedValue])) AS
DepreciableLife,
IIf([EstimatedValue]-NZ(DateDiff("d",[DateAcquired],Date()),0)*[EstimatedValue]*NZ(DLookUp("[DepreciationRate]/365","AssetsGeneralCategory","[CategoryID]="
& [AssetCategory]),0)>=DLookUp("[SalvageValue]","AssetsGeneral","[ItemID]=" &
[ItemID]),NZ(DateDiff("d",[DateAcquired],Date()),0)*[EstimatedValue]*NZ(DLookUp("[DepreciationRate]/365","AssetsGeneralCategory","[CategoryID]="
&
[AssetCategory]),0),[EstimatedValue]-DLookUp("[SalvageValue]","AssetsGeneral","[ItemID]="
& [ItemID])) AS DTD, [EstimatedValue]-[DTD] AS NBV,
IIf(IsNull(Forms!TB!SDate),Null,IIf(IsNull(DLookUp("[DepreciationRate]","AssetsGeneralCategory","[CategoryID]="
&
[AssetCategory])),0,IIf([DateAcquired]>Forms!TB!SDate,0,IIf(DateDiff("d",[DateAcquired],Forms!TB!SDate)>=[DepreciableLife],[EstimatedValue]-[SalvageValue],NZ(DateDiff("d",[DateAcquired],Forms!TB!SDate),0)*[EstimatedValue]*NZ(DLookUp("[DepreciationRate]/36500","AssetsGeneralCategory","[CategoryID]="
& [AssetCategory]),0))))) AS DTDTBS,
IIf(IsNull(Forms!TB!EDate),Null,IIf(IsNull(DLookUp("[DepreciationRate]","AssetsGeneralCategory","[CategoryID]="
&
[AssetCategory])),0,IIf([DateAcquired]>Forms!TB!EDate,0,IIf(DateDiff("d",[DateAcquired],Forms!TB!EDate)>=[DepreciableLife],[EstimatedValue]-[SalvageValue],NZ(DateDiff("d",[DateAcquired],Forms!TB!EDate),0)*[EstimatedValue]*NZ(DLookUp("[DepreciationRate]/36500","AssetsGeneralCategory","[CategoryID]="
& [AssetCategory]),0))))) AS DTDTBE, [DTDTBE]-[DTDTBS] AS DTDTBNet,
[EstimatedValue]-[DTDTBE] AS NBVTBE
FROM AssetsGeneral
WHERE (((AssetsGeneral.Disposed) Is Null));

This query works fine and returns records on the click of the mouse.

Then I based two fields of another query on this query thus:

Value:
IIf([BKID]=1,Format(NZ(DSum("[DTDTBNet]","vCurrentAssetsDepBK"),0),"Standard"),Format(-NZ(DSum("[DTDTBNet]","vCurrentAssetsDepBK"),0),"Standard"))

And Cum:
IIf([BKID]=1,Format(NZ(DSum("[DTDTBE]","vCurrentAssetsDepBK"),0),"Standard"),Format(-NZ(DSum("[DTDTBE]","vCurrentAssetsDepBK"),0),"Standard"))

This time, it ages to return results; you could almost finish a coffee, if
it takes you up to a minute to drink one. I cannot figure what is wrong. When
I remove the fields, the query if fast enough; but with either of them, it is
slow (more with the two together). Can you help me? Is there another way I
could accomplish the task?
Click to expand...


The first thing to do with this monster is to get rid of the
DLookup and DSum functions.

Instead of using DLookup in the first query, Join the
AssetsGeneralCategory table to the AssetsGeneral table on
the ItemID field. This will also remove the need for the
some/most/all of the IIf function calls.

Then modify the second query to be a Totals type query so
you can use Sum instead of DSum.

Depending on how the results of the second query are used,
you should postpone formatting the field values until they
are displayed in a form or report, by using a text box's
format property.
 
G

Glint

Thanx Marshal, your suggestion could not have come at a better time;
converting the behemoth to a make-table query was not helping much as it took
ages to make the table.
But when I joined the AssetsCategory as you suggested so that I could avoid
DLookup function, I found that there were other utterly unnecessary DLookups
in the query; I had been using DLookup when the table was right there! The
moment I removed them, presto!
Thanks for your assistance.
--
Glint


Marshall Barton said:
Glint said:
I built a select query based on one table (AssetsGeneral) that looks like a
behemoth thus:

SELECT AssetsGeneral.ItemID, AssetsGeneral.ItemName,
AssetsGeneral.ModelNumber, AssetsGeneral.SerialNumber,
AssetsGeneral.AssetCategory, AssetsGeneral.DateAcquired,
AssetsGeneral.PurchasePrice, AssetsGeneral.EstimatedValue,
AssetsGeneral.SalvageValue,
IIf(IsNull(DLookUp("[DepreciationRate]","AssetsGeneralCategory","[CategoryID]="
&
[AssetCategory])),Null,CInt(365*(100/DLookUp("[DepreciationRate]","AssetsGeneralCategory","[CategoryID]="
& [AssetCategory]))*([EstimatedValue]-[SalvageValue])/[EstimatedValue])) AS
DepreciableLife,
IIf([EstimatedValue]-NZ(DateDiff("d",[DateAcquired],Date()),0)*[EstimatedValue]*NZ(DLookUp("[DepreciationRate]/365","AssetsGeneralCategory","[CategoryID]="
& [AssetCategory]),0)>=DLookUp("[SalvageValue]","AssetsGeneral","[ItemID]=" &
[ItemID]),NZ(DateDiff("d",[DateAcquired],Date()),0)*[EstimatedValue]*NZ(DLookUp("[DepreciationRate]/365","AssetsGeneralCategory","[CategoryID]="
&
[AssetCategory]),0),[EstimatedValue]-DLookUp("[SalvageValue]","AssetsGeneral","[ItemID]="
& [ItemID])) AS DTD, [EstimatedValue]-[DTD] AS NBV,
IIf(IsNull(Forms!TB!SDate),Null,IIf(IsNull(DLookUp("[DepreciationRate]","AssetsGeneralCategory","[CategoryID]="
&
[AssetCategory])),0,IIf([DateAcquired]>Forms!TB!SDate,0,IIf(DateDiff("d",[DateAcquired],Forms!TB!SDate)>=[DepreciableLife],[EstimatedValue]-[SalvageValue],NZ(DateDiff("d",[DateAcquired],Forms!TB!SDate),0)*[EstimatedValue]*NZ(DLookUp("[DepreciationRate]/36500","AssetsGeneralCategory","[CategoryID]="
& [AssetCategory]),0))))) AS DTDTBS,
IIf(IsNull(Forms!TB!EDate),Null,IIf(IsNull(DLookUp("[DepreciationRate]","AssetsGeneralCategory","[CategoryID]="
&
[AssetCategory])),0,IIf([DateAcquired]>Forms!TB!EDate,0,IIf(DateDiff("d",[DateAcquired],Forms!TB!EDate)>=[DepreciableLife],[EstimatedValue]-[SalvageValue],NZ(DateDiff("d",[DateAcquired],Forms!TB!EDate),0)*[EstimatedValue]*NZ(DLookUp("[DepreciationRate]/36500","AssetsGeneralCategory","[CategoryID]="
& [AssetCategory]),0))))) AS DTDTBE, [DTDTBE]-[DTDTBS] AS DTDTBNet,
[EstimatedValue]-[DTDTBE] AS NBVTBE
FROM AssetsGeneral
WHERE (((AssetsGeneral.Disposed) Is Null));

This query works fine and returns records on the click of the mouse.

Then I based two fields of another query on this query thus:

Value:
IIf([BKID]=1,Format(NZ(DSum("[DTDTBNet]","vCurrentAssetsDepBK"),0),"Standard"),Format(-NZ(DSum("[DTDTBNet]","vCurrentAssetsDepBK"),0),"Standard"))

And Cum:
IIf([BKID]=1,Format(NZ(DSum("[DTDTBE]","vCurrentAssetsDepBK"),0),"Standard"),Format(-NZ(DSum("[DTDTBE]","vCurrentAssetsDepBK"),0),"Standard"))

This time, it ages to return results; you could almost finish a coffee, if
it takes you up to a minute to drink one. I cannot figure what is wrong. When
I remove the fields, the query if fast enough; but with either of them, it is
slow (more with the two together). Can you help me? Is there another way I
could accomplish the task?
Click to expand...


The first thing to do with this monster is to get rid of the
DLookup and DSum functions.

Instead of using DLookup in the first query, Join the
AssetsGeneralCategory table to the AssetsGeneral table on
the ItemID field. This will also remove the need for the
some/most/all of the IIf function calls.

Then modify the second query to be a Totals type query so
you can use Sum instead of DSum.

Depending on how the results of the second query are used,
you should postpone formatting the field values until they
are displayed in a form or report, by using a text box's
format property.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

QUERY PARAMETER 11

Top