Need to make qry faster

[audrey]

Ok, here's the layout: 1 table: Trainer Detail

Is there any way I can make this PITA query faster? Can i break this down into more smaller queries??? do ANYTHING to it to make it run in less time??? ANY and ALLLLL ideas are SO appreciated, you have no idea, lol. Here it is:

SELECT [T].[ID], [T].[Trainer], [T].[Date], [T].[Horse], [T].[Race], [T].[Fin_Pos], [T].[Odds], [T].[RACECLASS], [T].[Race_Sbred], [T].[Race_Age], [T].[Track_Cond], ([T].[Odds] * 2 + 2) AS AvgPay, (SELECT COUNT([TW].[ID]) FROM [Trainer Detail] [TW] WHERE [TW].[ID] <> [T].[ID] AND [TW].[Date] BETWEEN ([T].[Date] - 1) AND ([T].[Date] - [DaysBack]) AND [TW].[Trainer] = [T].[Trainer]) AS Starts, (SELECT COUNT([TW].[ID]) FROM [Trainer Detail] [TW] WHERE [TW].[ID] <> [T].[ID] AND [TW].[Date] BETWEEN ([T].[Date] - 1) AND ([T].[Date] - [DaysBack]) AND [TW].[Trainer] = [T].[Trainer] AND [TW].[Fin_Pos] = 1) AS Wins, (SELECT COUNT([TW].[ID]) FROM [Trainer Detail] [TW] WHERE [TW].[ID] <> [T].[ID] AND [TW].[Date] BETWEEN ([T].[Date] - 1) AND ([T].[Date] - [DaysBack]) AND [TW].[Trainer] = [T].[Trainer] AND [TW].[Fin_Pos] = 2) AS Places, (SELECT COUNT([TW].[ID]) FROM [Trainer Detail] [TW] WHERE [TW].[ID] <> [T].[ID] AND [TW].[Date] BETWEEN ([T].[Date] - 1) AND ([T].[Date] - [DaysBack]) AND [TW].[Trainer] = [T].[Trainer] AND [TW].[Fin_Pos] = 3) AS Shows, ROUND ((((SELECT COUNT([TW].[ID]) FROM [Trainer Detail] [TW] WHERE [TW].[ID] <> [T].[ID] AND [TW].[Date] BETWEEN ([T].[Date] - 1) AND ([T].[Date] - [DaysBack]) AND [TW].[Trainer] = [T].[Trainer] AND [TW].[Fin_Pos] = 1) / (SELECT COUNT([TW].[ID]) FROM [Trainer Detail] [TW] WHERE [TW].[ID] <> [T].[ID] AND [TW].[Date] BETWEEN ([T].[Date] - 1) AND ([T].[Date] - [DaysBack]) AND [TW].[Trainer] = [T].[Trainer])) * 100), 0) AS [Win Percentage], ROUND ((((SELECT COUNT([TW].[ID]) FROM [Trainer Detail] [TW] WHERE [TW].[ID] <> [T].[ID] AND [TW].[Date] BETWEEN ([T].[Date] - 1) AND ([T].[Date] - [DaysBack]) AND [TW].[Trainer] = [T].[Trainer] AND [TW].[Fin_Pos] <= 2) / (SELECT COUNT([TW].[ID]) FROM [Trainer Detail] [TW] WHERE [TW].[ID] <> [T].[ID] AND [TW].[Date] BETWEEN ([T].[Date] - 1) AND ([T].[Date] - [DaysBack]) AND [TW].[Trainer] = [T].[Trainer])) * 100), 0) AS [Win/Place Percentage], ROUND ((((SELECT COUNT([TW].[ID]) FROM [Trainer Detail] [TW] WHERE [TW].[ID] <> [T].[ID] AND [TW].[Date] BETWEEN ([T].[Date] - 1) AND ([T].[Date] - [DaysBack]) AND [TW].[Trainer] = [T].[Trainer] AND [TW].[Fin_Pos] <= 3) / (SELECT COUNT([TW].[ID]) FROM [Trainer Detail] [TW] WHERE [TW].[ID] <> [T].[ID] AND [TW].[Date] BETWEEN ([T].[Date] - 1) AND ([T].[Date] - [DaysBack]) AND [TW].[Trainer] = [T].[Trainer])) * 100), 0) AS [ITM Percentage]
FROM [Trainer Detail] AS T
WHERE ([T].Track_Cond IN ("fst", "gd", "tf", "fm"))
AND ([T].Odds BETWEEN 4.499 And 8.901)
AND (SELECT COUNT([TW].[ID]) FROM [Trainer Detail] [TW] WHERE [TW].[ID] <> [T].[ID] AND [TW].[Date] BETWEEN ([T].[Date] - 1) AND ([T].[Date] - [DaysBack]) AND [TW].[Trainer] = [T].[Trainer] AND [TW].[Fin_Pos] <= 3) > 1
ORDER BY [T].[Fin_Pos];

Thanks guys

audrey

 

[llkhoutx]

Multiple simple queries run in some meaningful sequence always runs faster that one large complex query.

 

[audrey]

i tried breaking this down but with all the criteria it's still slow as ever. I ultimately conceeded to access and let it win this round

Thanks for the reply!

audrey

 

[shaggy]

I'm working with an Oracle DB using Access for Reports. I've found that the order of the Tables and Field criteria makes a huge difference.

It's a little hard to explain and will take a little tinkering, but I have one table in the DB that has 2.8 million records and I initially wrote a query against it and other tables in my DB. It took close to 2 minutes to run. After getting tired of it running so slowly I re-wrote the query and now it take less than 5 seconds.

Put the Tables and Field criteria first that eliminate the most records. Then those that eliminate fewer records.

The order the information displayed in the query doesn't matter if you are putting it on a report since you can re-organize it there. If you need the information on the query in a particular order then I'm not sure how you would do that.

It's hard for anyone to help you even with the query unless they know the structure and relative sizes of your tables.

Hope this helps.

 

[audrey]

Kevin, thanks... i have tried running "sub queries" to eliminate most of the data (i.e. first query eliminates the most records, second query a little more and so on) i ended up with 3 sub queries and the final query... which all that [for me] took longer to run than the union query. The problem lies, or so i believe, with the table design. It is in least normal form, but the owner claims "he HAS to have it that way". And yes the table is also very large 24,000 records. I thought i might make an append query to create a normalized table then run the queries off the new table. I'll try that and let you (and anyone else with the same problem) know if i get any results.

thanks

audrey

 

[Jon K]

Just a thought. Running subqueries (i.e. Select statements in brackets) on a large table can be very time-consuming. Since most of the subqueries in your query are repetitive, maybe replacing them with aliases in the percentage calculations can speed up performance.

ROUND(Wins/Starts*100,0) AS [Win Percentage],
ROUND((Wins + Places)/Starts*100,0) AS [Win/Place Percentage],
ROUND((Wins + Places + Shows)/Starts*100,0) AS [ITM Percentage]


Hope it helps.

 

[audrey]

Thanks Jon - i'll give it a go!

 

[Mabena]

I also believe that your table design makes a difference. I had one particular query, accessing fewer records from fewer fields than yours, but it could take 3+ minutes to run. I tried to redesign my DB especially tables, in my new design I made sure that my records are indexed, reduce field sizes from the defaults especially if I knew the record would never be 255 for text. Also make sure that your combo boxes are limited to the list because other text with wrong spellings could be added to your list.

Reason for redesigning was of course to make sure all existing records are affected by the new rules. I do not have any slow query any more.