summarizing query of Date Diff query running really slow

[weeblebiker]

Hi.
The Question is "How many times has a [Serial #] been repaired and come back for repair in 0 to 90 days, 91 to 180 days, in a date range". The purpose is to quantify design or process changes impact on longevity.

so I have a repair table that has all the info in it.

Then a simple Date Range select query that returns all fields Between begin date and end date.

than the date diff query:

SELECT [XRS-3 Repair Date Range Query].[Serial #], [XRS-3 Repair Date Range Query].[Date in], (SELECT Max([Date in]) FROM [XRS-3 Repair Date Range Query] AS Alias WHERE Alias.[Date in] < [XRS-3 Repair Date Range Query].[Date in] AND Alias.[Serial #] = [XRS-3 Repair Date Range Query].[Serial #]) AS PrevEnd, DateDiff("d",[PrevEnd],[Date in]) AS [Date Diff], [XRS-3 Repair Date Range Query].[Customer #], [XRS-3 Repair Date Range Query].Customer
FROM [XRS-3 Repair Date Range Query]
ORDER BY [XRS-3 Repair Date Range Query].[Serial #], [XRS-3 Repair Date Range Query].[Date in];

Then the date diff sum query:

SELECT Sum(IIf([Date Diff] Between 1 And 90,1,0)) AS [under 90 days], Sum(IIf([Date Diff] Between 91 And 180,1,0)) AS [3 to 6 Months], Sum(IIf([Date Diff] Between 181 And 365,1,0)) AS [6 Months to 1 Year], Sum(IIf([Date Diff] Between 366 And 730,1,0)) AS [1 to 2 Years], Sum(IIf([Date Diff]>730,1,0)) AS [2 Plus years]
FROM [XRS-3 Repair Date Diff];

The date diff query runs within 2 seconds regardless of date range.

when I run the date diff sum query it take around 16 seconds to return results fora 1 year period and over 30 second to return results over a 2 year period. :banghead:

I suspect there is some looping back through every record in the select date range query for every sum statement thingy going on, or worse, even going back to the table for every query term in every layer of queries. how do I not do this?
do I need to make a "virtual" table of the query output for the next query to look at?

and how?

 

[Brianwarnock]

I dont know how the compiler will handle the cascading queries, one would hope that it would optimise the final code and if that was best using a virtual temp table then it would do so.

You can make the Date Diff query into a make table query and then run the sum query against that, an easy to do test.

I would change the Datadiff query to only output the data required into the table, i think, but cannot test, that the following should do it.

I think that you have to Select PrevEnd as well as [Date Diff], but not when it has Null value. you don't need any other fields nor does the order matter.

you should also have an index on Datein, dups allowed, as there is a search on the field.

Brian

SELECT (SELECT Max([Date in]) FROM [XRS-3 Repair Date Range Query] AS Alias WHERE Alias.[Date in] < [XRS-3 Repair Date Range Query].[Date in] AND Alias.[Serial #] = [XRS-3 Repair Date Range Query].[Serial #]) AS PrevEnd, DateDiff("d",[PrevEnd],[Date in]) AS [Date Diff]
FROM [XRS-3 Repair Date Range Query]
Where ((SELECT Max([Date in]) FROM [XRS-3 Repair Date Range Query] AS Alias WHERE Alias.[Date in] < [XRS-3 Repair Date Range Query].[Date in] AND Alias.[Serial #] = [XRS-3 Repair Date Range Query].[Serial #]) Is Not Null) ;

 

[weeblebiker]

Thanks for the input,
however your revised code takes approx 6 seconds to run, the original [Date Diff] code takes approx 2 seconds to run.
and the extra 4 seconds is carried over to the [Date Diff Sum] query. :banghead::banghead:

I think I need to do temporary tables. most of the articles I've found so far seem to be for creating permanent tables.

what is the proper term for the table I want to create? I suspect I'm searching for my own made up terms
Any recommended resources for doing this?

 

[Brianwarnock]

WOW I'm amazed at the change in run time, I took out a couple of fields, dropped the sorts and added the check on PrevEnd Is Not Null.

I would do the temp table test, as I only used temporary tables when forced to and that was rare and I don't remember more than 1 per project I just called it TempTable, all other tables started Tbl and thus it did not get lost amongst them.

The annoying thing is having to run 2 queries, but you can write code or a macro to do this, code allows for error checking, but i've forgotten how to do that>

Brian

 

[gemma-the-husky]

one other option

if you want dates splitting in fixed blocks try the little known PARTITION function in your query.

that's what it does. splits data into fixed bands.

here's a link


http://bytes.com/topic/access/insights/708740-partition-function

 

[Brianwarnock]

Dave unfortunately his block sizes are not equal.

Do you know, or think, that if you have a subquery in the Where clause as I had that the query reruns the subquery, ie the compiler doen't think . hey I need that info so i'll keep it.
I think I was a bit naive.

Brian

 

[gemma-the-husky]

Dave unfortunately his block sizes are not equal.

Do you know, or think, that if you have a subquery in the Where clause as I had that the query reruns the subquery, ie the compiler doen't think . hey I need that info so i'll keep it.
I think I was a bit naive.

Brian

Brian

I thought he wanted to split his data into 90 day groups

 

[weeblebiker]

I want to split the difference of the data into 90 day groups.. as in unit comes in for repair [Date in] and IF comes back for repair between initial and next [Date in] in x days. group x in 0-90, 91-180, etc,,,

only the [date in] is in a table, all manipulation after that is in a series of 3 queries, 1 a select query to limit the table to data to a date range, then the datediff( query on the date range query, then the sum(IIf( on the datediff( query.

so it is called "Temp Table" ?

 

[Brianwarnock]

TempTable no space

An earlier post showed bands of 90,90,186,365 days

Brian

 

[weeblebiker]

Is this what I want to do? (method 3)

http://www.experts-exchange.com/Mic...ing-Temporary-Tables-in-Microsoft-Access.html

 

[Brianwarnock]

At the moment we are merely trying to see if it solves the performance issue, so I would use a simple make table ie #2 . You can compact your database at any time. I have not used approach 3 so cannot comment as the article states compact on close was a temporary problem for 2007 and I was not on that.

Brian

 

[weeblebiker]

Dave unfortunately his block sizes are not equal.

Do you know, or think, that if you have a subquery in the Where clause as I had that the query reruns the subquery, ie the compiler doen't think . hey I need that info so i'll keep it.
I think I was a bit naive.

Brian

what are my "block sizes"?

 

[weeblebiker]

Thanks, I'll work on it and get back in the next day or two, have a wires melting issue to help figure out first.

Oh if only everything could be fixed at a keyboard,,,,,

 

[weeblebiker]

so sometimes walking away fixes things.
it now runs under 10 seconds with a 2 year date range. slow, but acceptable, I suspect I wrote enough garbage code during the creation of the topic queries, that the back end db and server needed to be restarted to clear things out.