Hi all

The following query works and gives me the desired results. The only problem is that it is very slow, and I suspect it is because of the multiple sub-queries included in it.

The table, RESULT, has multiple records per athlete, per stroke, per distance stored in the order of the events and not in rank order. I am pulling out the top time for each athlete for the selected stroke/distance/age/gender combination and ranking the result. ... In other words, I'm selecting the relevant swimmers' top times for a selected event and ranking those.

Variables are passed to the script - Selected Athlete_Id - 1234 - to generate age and gender for ranking - stroke_id and distance. These are already included in the code below.

The query returns 45 rows, which is correct, and, according to my SQLYog, it takes, on average, 14 seconds to process...

Any suggestions?

Thanks very much!

Rory

select 	a.ATHLETE, 
	rtrim(a.LAST) as Surname, 
	rtrim(a.FIRST) as FirstName,
 
        (select z.SCORE from RESULT z 
         where z.ATHLETE = r.ATHLETE 
            and z.STROKE = 1 
            and z.DISTANCE = 50 
            and z.SCORE > 0 
            and z.COURSE = 'L' 
        order by z.SCORE limit 0,1) as SCORE,  -- Selects top time for selected event & dist.

        (select m.MNAME from RESULT w 
        Join MEET m on (w.MEET = m.MEET) 
        where w.ATHLETE = r.ATHLETE 
                and w.STROKE = 1 
                and w.DISTANCE = 50 
		and w.SCORE > 0 
                and w.COURSE = 'L' 
        order by w.SCORE limit 0,1) as SWMEET  -- Selects gala where the time was achieved

from RESULT r 
Join ATHLETE a on (a.ATHLETE = r.ATHLETE) 
Join TEAM t on (t.TEAM = a.TEAM1) 
Join stroke s on (r.STROKE = s.stroke_id) 
WHERE a.AGE = (select AGE from ATHLETE where ATHLETE = 1234) 
	and a.SEX = (select SEX from ATHLETE where ATHLETE = 1234)
	and r.COURSE = 'L' 
	and r.STROKE = 1 
	and r.DISTANCE = 50 
	and r.SCORE > 0 
	and t.LSC = 'KZ' 
Group by r.ATHLETE
Order by SCORE

Any suggestions?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.