Access 2000 Adding subquery 16 to query causes massive slowdown

[Guest]

On building a query based on 10 subqueries, the time to run is pretty fast.
On adding 5 more for a total of 15, it's about 20 seconds. If I add even one
more, the query doesn't come back. I only need 22 subqueries and my work is
done...

Changed the 'name autocorrect' option and it helped when running up to 15
subqueries. For 16 subqueries, gave up waiting after 3 minutes.

Help.

 

[Guest]

I used the term subquery, but I probably should describe it as a query based

 

[Allen Browne]

There are many possible approaches to this.

If nothing else works, the solution will probably be to create a temporary
table. Pre-process much of the data into that table, and then build the
queries based on that. Depending on your data, this could be orders of
magnitude faster.

Before you go that route, you might like to consider whether there is an
alternative way to preprocess the data in your stacked queries. You can't
tell Access what execution plan to use, but redesigning the SQL/queries can
have the effect of getting it to take a different path.

In some cases, you can avoid the whole problem by normalizing the tables. As
a basic example, some novices use 22 different tables to store the
information on 22 different companies, and then run into this kind of
problem.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Guest]

I just posted add'l info, but received an error on a busy server...

Sorry if this is duplicate.

I put race results into a table, each race date is a number, each car class
is a number, each driver is numbered. As a driver signs-in to a car class, a
record is added to the table for each race date. For forms and reports, with
joins I bring out the driver name, race date and car class name.

I built a query for each race date to show only those records for that date,
and to massage the finish position to give the correct points, including 0
for disqualifications, etc.

To show the accumulating points in a report, I build a horizontal, based on
queries that have the individual race date results--query Race 1 has the race
1 heat and feature finish, and associated points. So horizontally you see
data from race 1, then 2, then 3, and so on.

I built 6 races. The report looks fine. Have the logic for the various
points situations working great.

On expanding to have all 22 races, I added race 7-10 queries...okay. On
adding 11-15, small wait. Now, can't add race 16.

Thanks for your help.

 

[Allen Browne]

There will certainly be a better way to build this.

I don't know enough about racing, but it seems likely that one driver will
enter many races over time, and that one race will have many drivers enter.
That implies 3 tables:
- Race table (one record for each race), with fields such as:
RaceID primary key
RaceDate date/time
LocationID where the race is held

- Driver table (one record for each driver), with fields:
DriverID primary key
Surname Text
FirstName Text

- Entry table (one record for each entry in each race), with fields:
RaceID relates to Race.RaceID
DriverID relates to Driver.DriverID
EntryDate when the driver's entry for this race was received.

Then you probably have another table for race results, with fields such as:
RaceID relates to Race.RaceID
DriverID relates to Driver.DriverID
RaceTime Number of (seconds?) this driver completed this race in.
(Blank if DNF.)

With that kind of structure, you can query the races and get the results
without the need to use 22 subqueries.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Guest]

This is sort of what I have, except how do you show a report with this sort
of format:

car class 1 race date 1 race date 2 race date
3... total

driver1 finish position/pts finish positions/pts finish
positions/pts... yr pts
driver 2 ......... .........
........... .....
....

car class 2 race date 1 race date 2 race date
3.... total

driver x finish pos/pts finish pos/pts ........
yr pts
driver y .........



thanks.

 

[Allen Browne]

You can flip the races into column headings with a crosstab query using
RaceID as the Column Heading.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Guest]

I thought of a crosstab query...but I need to display more than one field per
race.... heat position, points, feature position, pts and total pts.

 

[Guest]

Your suggestion I used to search for crosstab in the discussions...found the
hints on how to use cartesian+crosstab tricks...managed to get it to work.

Thanks for your time. Here's my sql:

TRANSFORM First(Choose([mytable for crosstab].[5 fields],[Races Data With
Points].[Heat Result],[Races Data With Points].[HPoints],[Races Data With
Points].[Feature Result],[Races Data With Points].[FPoints],[Races Data With
Points].[One Race Points])) AS Expr1
SELECT [Car Classes].CarID, Drivers.DriverID, [Car Classes].[Car Class],
Drivers.[Driver Name]
FROM [mytable for crosstab], (([Races Data With Points] INNER JOIN RaceDates
ON [Races Data With Points].[Race Number] = RaceDates.RaceDateID) INNER JOIN
[Car Classes] ON [Races Data With Points].CarID = [Car Classes].CarID) INNER
JOIN Drivers ON [Races Data With Points].DriverID = Drivers.DriverID
GROUP BY [Car Classes].CarID, Drivers.DriverID, [Car Classes].[Car Class],
Drivers.[Driver Name]
ORDER BY [Car Classes].CarID, Drivers.DriverID
PIVOT [Races Data With Points].[Race Number] & Choose([mytable for
crosstab].[5 fields],"Heat Pos","Heat Pts","Feature Pos","Feature Pts","Pts");

 

[Allen Browne]

Good news. Well done.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news[email protected]...