Complex Query

[ChewDoggie]

Hello All,

I've developed a VB app that scores motorcycle races. One function that the software has to deliver is that I have to do a "back-to-back" racer check, where I have to check to see if there are any racers who are racing in back-to-back racenums.

I have an Access DB with the following tables:

Registration table with fields:
eventid
racerid
classid
comp

EventsClasses table with fields:
eventid
classid
racenum

The challenge comes into play when you consider that the racenums may not be successive. That is, you might have racenum "1", then racenum "3", then racenum "5" OR you might have racenum "1", then racenum "2", then racenum "3", etc.

The way I'm handling it right now is a very labor intensive process. I grab all racers for 1st racenum (which might be "1" or "2" or "3"...who knows!), then grab all racers for 2nd racenum (which might be "2" or "3" or "10"...who knows!) and loop thru each recordset to grab all common racerid's. This can take a while when you have 50+ racenums and 1500+ racerids.

Is there a way to do it with a single query?

Access Query Experts UNITE !

Thanks!


AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[lespaul]

why are you storing the classid in both tables? you should be able to store just the eventid in the registration table and extract the classid from there....

How do you know what order the race numbers are actually in?

Leslie

Essential for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[ChewDoggie]

Sorry, lesPaul, I was out yesterday.

The classid is stored in both tables to reduce the number of table joins. The registration and the eventsclasses tables are the most often accessed tables in the system. I often have to join 5 tables for reports and whatnot. I duplicated some of the data (de-normalized) to reduce the number of table joins.

To answer your other question, the user creates the race (number) order and it's always in ascending order and I only want to work with race numbers that are greater than zero. Beyond that, I can't make any assumptions about the race numbers.

It's a pickle !

Thanks!






AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[PHV]

reduce the number of table joins
For which benefits ????

 

[ChewDoggie]

This is getting a little off topic, but for the benefit of speed of output. When we tested the application with 500+ events and 1,000,000+ racers (and 25,000 classes), we found that we had to do (sometimes) eight table joins to get at the 'classid' and this meant that the user might have to wait 10 seconds for a report to be generated. This was entirely unacceptable. By duplicating the 'classid' (and sometimes the 'racerid') in various tables, we were able to reduce the number of table joins and reduce the time it took to produce the same report to just 1 second (or less).






AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[KennethH]

Why couldn't you use the general approach below

Select Id, fk from table where fk = @Race1
AND Id In (Select ID from table where fk = @Race2)

Id is the field for the racer, fk is the field for the race numbers, @Race1 and @Race2 are replaceble parameters.

If you wanted to do an entire days races in one batch you would need to create a temp table or resultset allowing the following select approach (or modify your existing tables to track order of races):

Select t.Id,fk from table t where t.RaceOrder = @Race
And t.Id In (Select t2.ID from table t2 where t2.RaceOrder = t.RaceOrder +1)

Ken

 

[ChewDoggie]

Thanks, Ken.

I don't have the flexibility to modify the existing tables, so I would like to try your approach, with some modifications. I adapted your suggestion to work in a loop, and it works pretty fast and has to be faster than the current code.

sql = "select r,racerid, e.runclassid from registration r INNER JOIN eventsclasses e on e.eventid = r.eventid and e.classid = r.classid where r.eventid = " & entId & " and r.status = 'Y' and e.runclassid = " & ThisRaceNum & " and r.racerid in (select distinct re.racerid from registration re INNER JOIN eventsclasses ev on re.eventid = ev.eventid and re.classid = ev.classid where re.eventid = " & entId & " and re.status = 'Y' and ev.runclassid = " & NextRaceNum & ")"

I'll give it a whirl in my VB6 form, see how it performs and report back on Monday.

Thanks again!




AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[lespaul]

I'm glad Ken is able to help you with this issue, but the bigger is for me is the denormalization.

Since your reasoning was increased speed in the query, before you decided to de-normalize did you try adding indexes to the tables on the necessary fields to see if that improved your query speed?