Query efficiency, alternatives

[cheshire_smile]

I'm having tryouble running a query on a single table with 100,000 records. It was taking over an hour to run the query, so I tried indexing all the fields. This droped it down to about half an hour. I need it to run faster. Here is the query:

SELECT UpDn_Analysis.Terminal_Name,
Min(UpDn_Analysis.[Date/Time]) AS Down_Time,
UpDn_Analysis.Next_Up_Time AS Up_Time
 
FROM (
SELECT
[Temp_Table].Terminal_Name,
[Temp_Table].[Date/Time],
(SELECT Min(NxtUp.[Date/Time])
FROM [Temp_Table] NxtUp
WHERE NxtUp.[Terminal_Name] = [Temp_Table].[Terminal_Name]
AND NxtUp.[Date/Time] > [Temp_Table].[Date/Time]
AND NxtUP.[Dn Total] <> 0
AND NxtUP.[Up Total] <> 0) AS Next_Up_Time,
(SELECT Max(PrevDn.[Date/Time])
FROM [Temp_Table] PrevDn
WHERE PrevDn.[Terminal_Name] = [Temp_Table].[Terminal_Name]
AND PrevDn.[Date/Time] = [Temp_Table].[Date/Time]
AND PrevDn.[Dn Total] = 0
AND PrevDn.[Up Total] = 0) AS Previous_Dn_Time
FROM Temp_Table) AS UpDn_Analysis
WHERE (((UpDn_Analysis.[Date/Time])=[Previous_Dn_Time]))
GROUP BY UpDn_Analysis.Terminal_Name, UpDn_Analysis.Next_Up_Time) AS Outage_Times

Sample data:

[ID]-[Terminal_Name] ---[Date/Time]--[Up Total]-- [Dn Total] 1-----Terminal1-------7/1/2011 10:52-----1------------1 2-----Terminal1-------7/1/2011 10:53-----1------------1 3-----Terminal1-------7/1/2011 10:54-----1------------1 4-----Terminal1-------7/1/2011 10:55-----0------------0 5-----Terminal1-------7/1/2011 10:56-----1------------1 6-----Terminal1-------7/1/2011 10:57-----1------------1 7-----Terminal1-------7/1/2011 10:58-----1------------1 8-----Terminal1-------7/1/2011 10:59-----0------------0 9-----Terminal1-------7/1/2011 11:00-----0------------0 10----Terminal1-------7/1/2011 11:01-----0------------0 11----Terminal1-------7/1/2011 11:02-----1------------1 12----Terminal1-------7/1/2011 11:03-----1------------1 13----Terminal1-------7/1/2011 11:04-----1------------1 14----Terminal1-------7/1/2011 11:05-----1------------1 15----Terminal1-------7/1/2011 11:06-----1------------1

The purpose of this Query is to find outages. It provides the terminal name, the first timestamp where it shows 0 for both up and down and the next non-zero timestamp.

... I'm not verry good with VBA, I can read it and modify it, but writing it from scratch is a little difficult.

I don't know if splitting the fields up to several tables and creating 1-many relationships would improve it any.

Any help would very much appreciated.

Thx.

 

[VilaRestal]

First step would be to format it a bit to make it easier to read and understand:

SELECT Terminal_Name, 
       Min([Date/Time]) AS Down_Time, 
       Next_Up_Time AS Up_Time
FROM (
      SELECT Terminal_Name, 
             [Date/Time],
             (SELECT Min(NxtUp.[Date/Time]) 
              FROM [Temp_Table] NxtUp
              WHERE NxtUp.[Terminal_Name] = [Temp_Table].[Terminal_Name]
                   AND NxtUp.[Date/Time] > [Temp_Table].[Date/Time]
                   AND NxtUP.[Dn Total] > 0
                   AND NxtUP.[Up Total] > 0
              ) AS Next_Up_Time,
             (SELECT Max(PrevDn.[Date/Time]) 
              FROM [Temp_Table] PrevDn
              WHERE PrevDn.[Terminal_Name] = [Temp_Table].[Terminal_Name]
                   AND PrevDn.[Date/Time] = [Temp_Table].[Date/Time]
                   AND PrevDn.[Dn Total] = 0
                   AND PrevDn.[Up Total] = 0
              ) AS Previous_Dn_Time
      FROM Temp_Table
     ) AS UpDn_Analysis
WHERE [Date/Time] = [Previous_Dn_Time]
GROUP BY Terminal_Name, Next_Up_Time) AS Outage_Times

That Terminal_Name is a Text field isn't going to be brilliant for efficiency. If you could replace that field with an integer that would improve storage efficiency if not this query's speed.

The WHERE [Date/Time] = [Previous_Dn_Time] seems like a strange condition but it makes my head hurt to think about it

But most of all I wonder about the
) AS Outage_Times
at the end. It makes me think there's something missing.

 

[Galaxiom]

Changing Terminal_Name in the data to be represented by an integer as VilaRestal suggested woud make a big difference especially if the names are fairly long. Strings are slow to compare because each character must be compared individually with both upper and lower case. Numbers can be directly compared in one operation.

I suspect this is the real culprit.
Use a JOIN instead of:
WHERE PrevDn.[Terminal_Name] = [Temp_Table].[Terminal_Name]

What you have is a Cartesian Product with a condition. I expect that this will create a number of virtual records equal to 100K times the number of records in the temp table and then apply the conditions.

Maybe Access can optimise this situation anyway but the time it is taking to run suggests that it isn't. I would think it is certainly worth a try.

Joins also support conditions other than equals. For example
ON NxtUp.[Date/Time] > [Temp_Table].[Date/Time]
They just cannot be represented in the query designer.

BTW using a slash in a name is frowned upon by most developers. Similarly spaces in names.

 

[cheshire_smile]

Thank you both for you input. The data is not stored which is why it's in a Temp table. I am using access as a report generation tool. I import the data from a DB (which I only have front end access to) then run the query to generate a usage report along with outage stats.
I know the naming conventions are not really good but, it's not really important since I'm not using it to actually store the data.
VilaRestal: you are correct there is more to the query, the rest simply calculates the duration of the outage by subtracting dn_time from up_time.

I tried splitting the data into two tables, creating a terminal_id vs terminal_name and it still takes way too long.

I'm not sure how to write this as a join. I'm completely open to suggestions and am willing to re-write everything.
How would you guys write the query?

 

[Galaxiom]

The Join in the subquery looks like this:

(SELECT Min(NxtUp.[Date/Time]) 
FROM [Temp_Table] 
INNER JOIN NxtUp
ON NxtUp.[Terminal_Name] = [Temp_Table].[Terminal_Name]
   AND NxtUp.[Date/Time] > [Temp_Table].[Date/Time]
WHERE  NxtUP.[Dn Total] > 0
   AND NxtUP.[Up Total] > 0
) AS Next_Up_Time

Similarly the other subquery.