Query is running very slowly.

Topic

New Reply

The query is running very slowly, about a minute to run, when normally the query shows in a almost instantaneously.
OneDrive link:https://onedrive.live.com/redir?resid=B220B890CEE8154E!821&authkey=!AMW_iJYMqWLf4o0&ithint=file%2czip
This should link to the database, and a word document showing the SQL of the query: [Most_FT_2g_v22_Query] and the queries it links to.
Your help and ideas are greatly appreciated. I am hoping to expand this query/line to 3 or more games, and use for other ideas like most points, most rebounds,etc.
In advance, thank you.

Reply | Quote

Replies

The problem is with the sorting. If you remove the sorting from the query, it will return results almost immediately (at least on my system). It will take a bit more time, with the sorting.

Considering you are using a query within a query and the problem is with the sorting with a calculated column within the query, no indexing will help. Probably what can make sense is to create a table to store these results, index the desired fields and then query such table.

This, of course, requires a bit more of thought. You could create the table structure, have the 1st query run as an append query, including the calculated field being used for the sort, which would be indexed in the created table structure and then have the 2nd query get the sorted data from the table.

This would also require some code to coordinate query execution and delete any records in the table, prior to the 1st query execution.

Reply | Quote

I don’t know about anyone else, but when you present a convoluted situation like this (one query is a source for another query which in return is a source for yet another query), it would help me if you could explain the logic behind all the queries. That is, what does each query do and what are you expecting out of it.

Reply | Quote

Why do you characterize “a minute to run” as “very slow”? Not in my world it isn’t!

Reply | Quote

My query should have about 1000 rows with about a dozen columns. When the queries are running ‘correctly’, the results pop up in a couple of seconds (or instantaneously). How do I stop/crash a query that appears to be on an infinite loop?
Recently I was running a query that would ‘crash’ after a couple of minutes for lack of space. When I changed the links from choice 1 to choice 2, the results immediately popped up. How couple I stop the earlier query when it became ‘obvious’ (30 seconds) that it was only going to crash after a couple of minutes? (Same question)
Yes I am running ACCESS with less than 1000 players and less than 30 games a season. I realize that I am not working with a global company with stores in every county of the United States.
Mark, my first level is to find the results from their first game of making a free throw. I can then build the Roster and Game ID’s for the second game. At the second level I am using the new ID’s to find the free throws made for the second game. And, the 3rd level (as Rui/ruirib helped me with before) is when I add the first game’s free throws made and the second game’s free throws made and then to sort to find the player with the most free throws made (total).
Yes, Rui I remove the Order By/sorting and the query runs its results in a snap.

Reply | Quote

My query should have about 1000 rows with about a dozen columns. When the queries are running ‘correctly’, the results pop up in a couple of seconds (or instantaneously). How do I stop/crash a query that appears to be on an infinite loop?
Recently I was running a query that would ‘crash’ after a couple of minutes for lack of space. When I changed the links from choice 1 to choice 2, the results immediately popped up. How couple I stop the earlier query when it became ‘obvious’ (30 seconds) that it was only going to crash after a couple of minutes? (Same question)
Yes I am running ACCESS with less than 1000 players and less than 30 games a season. I realize that I am not working with a global company with stores in every county of the United States.
Mark, my first level is to find the results from their first game of making a free throw. I can then build the Roster and Game ID’s for the second game. At the second level I am using the new ID’s to find the free throws made for the second game. And, the 3rd level (as Rui/ruirib helped me with before) is when I add the first game’s free throws made and the second game’s free throws made and then to sort to find the player with the most free throws made (total).
Yes, Rui I remove the Order By/sorting and the query runs its results in a snap.

Where do I start?

The problem with this is that you run a multitude of complex queries, with vb functions such as IIF, which make the resulting queries not the most efficient, performance wise. I don’t know enough about the Access query engine to know what is happening here and I don’t know enough about your problem to tell you whether there was a more efficient way for you to do the querying.

I did one thing, though. I tried my suggested approach and created an intermediate table to keep the results, indexed the desired sorted field and, obviously, the results are sorted almost instantaneously. Of course, getting the desired records to create the table took a bit of time.

I am at a big disadvantage here, which is not being familiarized with the nature of your problem and your table structure. I will note, though, that on a quick look, considering you keep what I think are stats per player per game, any aggregated stats should be easy to calculate, by simple aggregation. I noticed that you base the stats on roster, though, and not on player, which means another indirection (join) would be required to aggregate by player.

Unfortunately, I don’t have much more advice to offer, per the lack of knowledge mentioned before. You may well have to materialize the results of some of queries (that is, get those results into properly indexed tables), to get performance improvements. I suppose that as the number of games increases, performance will only get worse, unless you materialize results and take advantage of indexing.

Reply | Quote

Where do I start?

The problem with this is that you run a multitude of complex queries, with vb functions such as IIF, which make the resulting queries not the most efficient, performance wise. I don’t know enough about the Access query engine to know what is happening here and I don’t know enough about your problem to tell you whether there was a more efficient way for you to do the querying.

I did one thing, though. I tried my suggested approach and created an intermediate table to keep the results, indexed the desired sorted field and, obviously, the results are sorted almost instantaneously. Of course, getting the desired records to create the table took a bit of time.

I am at a big disadvantage here, which is not being familiarized with the nature of your problem and your table structure. I will note, though, that on a quick look, considering you keep what I think are stats per player per game, any aggregated stats should be easy to calculate, by simple aggregation. I noticed that you base the stats on roster, though, and not on player, which means another indirection (join) would be required to aggregate by player.

Unfortunately, I don’t have much more advice to offer, per the lack of knowledge mentioned before. You may well have to materialize the results of some of queries (that is, get those results into properly indexed tables), to get performance improvements. I suppose that as the number of games increases, performance will only get worse, unless you materialize results and take advantage of indexing.

Thank you for your insights. I use Roster ID for each season. A player can play many different seasons.

Reply | Quote

I understand, but you could still store stats under playerID. You could then keep a table maintaining associations between playerID and season through rosterID. In any case, it would just remove an indirection, maybe allowing aggregation of stats directly over the table that keeps stats.

I think you have a problem that is not the simplest. Probably some thought should be given to table structure, coding and querying, depending on your needs. Unfortunately that can be really hard for someone who doesn’t know the problem.

Reply | Quote