Can you help me simplify this query, if possible??

[Fabian]

Hi all there,

I have this very complicated query who was good for a local Visual
basic software program, but now that we have moved onto the Internet is
far to slow to be displayed onto an ASP webpage. :-(

It's in an Access 2000 file and It's about the list of the best
athletes - ever - on a certain Competition type in order of score.

The best score is represented by the max score *first* reached for each
athlete.

In other words, if a single athlete reaches the same score in May 2001
and April 2002, then his/her own best score is the May 2001 score.

So, I have an Atheles table, a Competitions and a Scores table.

Fields names should be understandable, I hope.

Now, take a big breath , here it comes:

SELECT A.AthleteName, Score, CompetitionDate, CompetitionPlace, Notes
FROM Competitions AS C INNER JOIN (Athletes AS A INNER JOIN Scores AS
S ON A.AthleteID = S.AthleteID) ON C.CompetitionID = S.CompetitionID
WHERE S.Score = (SELECT MAX(S1.Score) FROM Scores S1 INNER JOIN
Competitions C2 ON C2.CompetitionID=S1.CompetitionID WHERE
S1.AthleteID = A.AthleteID AND CompetitionTypeID=@T) AND
C.CompetitionDate = (SELECT MIN(C1.CompetitionDate) FROM Competitions
C1 INNER JOIN Scores S1 ON S1.CompetitionID = C1.CompetitionID WHERE
S1.AthleteID = A.AthleteID AND S1.Score = S.Score
ORDER BY S.score DESC,competitiondate;

I believe that if I manage to replace the "WHERE S.Score=(SELECT...)"
with a further "INNER JOIN ([SELECT...]) the execution should be
quicker, but my brain does not work today and I cannot manage to
get it into work properly...

Could any of you help, pleeeese ?

Thanks
Fab.

 

[Tom Ellison]

SELECT A.AthleteName, Score, CompetitionDate, CompetitionPlace, Notes
FROM Competitions AS C
INNER JOIN (Athletes AS A
INNER JOIN Scores AS S
ON A.AthleteID = S.AthleteID)
ON C.CompetitionID = S.CompetitionID
WHERE S.Score =
(SELECT MAX(S1.Score)
FROM Scores S1
INNER JOIN Competitions C2
ON C2.CompetitionID=S1.CompetitionID
WHERE S1.AthleteID = A.AthleteID
AND CompetitionTypeID = @T
AND C.CompetitionDate =
(SELECT MIN(C1.CompetitionDate)
FROM Competitions C1
INNER JOIN Scores S1
ON S1.CompetitionID = C1.CompetitionID ))
WHERE S1.AthleteID = A.AthleteID
AND S1.Score = S.Score
ORDER BY S.score DESC,competitiondate;

I have added two right parens after "C1.CompetitionID" because:

1. the parens in your query were unbalanced

2. this seemed the right place, because the following WHERE clause involves
table aliased S which is in your outermost query

There is something peculiar about: CompetitionTypeID = @T

Are you writing for SQL Server using a variable you have defined, or is this
an error? What is @T?

When you post a query for us to review, I recommend you paste it in from
what is, if possible, a working query. It is a hinderance to understanding
what you are doing to have errors in the SQL you post unless those errors
are the reason for the post.

I'd like to try to help you, but need these things cleared up first.

Tom Ellison

Hi all there,

I have this very complicated query who was good for a local Visual
basic software program, but now that we have moved onto the Internet is
far to slow to be displayed onto an ASP webpage. :-(

It's in an Access 2000 file and It's about the list of the best
athletes - ever - on a certain Competition type in order of score.

The best score is represented by the max score *first* reached for each
athlete.

In other words, if a single athlete reaches the same score in May 2001
and April 2002, then his/her own best score is the May 2001 score.

So, I have an Atheles table, a Competitions and a Scores table.

Fields names should be understandable, I hope.

Now, take a big breath , here it comes:

SELECT A.AthleteName, Score, CompetitionDate, CompetitionPlace, Notes
FROM Competitions AS C INNER JOIN (Athletes AS A INNER JOIN Scores AS
S ON A.AthleteID = S.AthleteID) ON C.CompetitionID = S.CompetitionID
WHERE S.Score = (SELECT MAX(S1.Score) FROM Scores S1 INNER JOIN
Competitions C2 ON C2.CompetitionID=S1.CompetitionID WHERE
S1.AthleteID = A.AthleteID AND CompetitionTypeID=@T) AND
C.CompetitionDate = (SELECT MIN(C1.CompetitionDate) FROM Competitions
C1 INNER JOIN Scores S1 ON S1.CompetitionID = C1.CompetitionID WHERE
S1.AthleteID = A.AthleteID AND S1.Score = S.Score
ORDER BY S.score DESC,competitiondate;

I believe that if I manage to replace the "WHERE S.Score=(SELECT...)"
with a further "INNER JOIN ([SELECT...]) the execution should be
quicker, but my brain does not work today and I cannot manage to
get it into work properly...

Could any of you help, pleeeese ?

Thanks
Fab.

 

[Amy Blankenship]

Try breaking this into two stored queries:

1) A query that returns the maximum score for each athlete
2) A query that returns the competition details for the minimum date where
the score is the same as what was in the first query.

This is pretty straightforward if you use the query builder. Then from the
ASP page, just execute the query you made.

I use this type of query all the time and it runs pretty fast. You may also
want to look at how you're making the ASP page. Dreamweaver, for example,
adds about 5 sec to the load time of any ASP page just because of the way it
handles database connectivity in the background. Also if you have lots of
large (file size) graphics or you've used a program like Fireworks to make
graphics that are sliced into a lot of tiny pieces this will add to load
time.

HTH;

Amy

 

[Fabian]

There is something peculiar about: CompetitionTypeID = @T

Are you writing for SQL Server using a variable you have defined, or is this
an error? What is @T?

It is an external variable, yes and I use Ms Access 2003. It's the type
of competition anthlete competes for:

100mt running, jump, penthatlon, etc.

When you post a query for us to review, I recommend you paste it in from
what is, if possible, a working query. It is a hinderance to understanding
what you are doing to have errors in the SQL you post unless those errors
are the reason for the post.

You are right, but I had to translate the fieldnames from the italian
to make it better understandable for you. Apparently, a parentesis has
fallen.

I'd like to try to help you, but need these things cleared up first.

Of course, thank you so much ad sorry forthe belated reply. I just
missed your message.

Fab.

 

[Fabian]

There is something peculiar about: CompetitionTypeID = @T

Are you writing for SQL Server using a variable you have defined, or is this
an error? What is @T?

It is an external variable, yes and I use Ms Access 2003. It's the type
of competition anthlete competes for:

100mt running, jump, penthatlon, etc.

When you post a query for us to review, I recommend you paste it in from
what is, if possible, a working query. It is a hinderance to understanding
what you are doing to have errors in the SQL you post unless those errors
are the reason for the post.

You are right, but I had to translate the fieldnames from the italian
to make it better understandable for you. Apparently, a parentesis has
fallen.

I'd like to try to help you, but need these things cleared up first.

Of course, thank you so much ad sorry forthe belated reply. I just
missed your message.

Fab.

 

[Fabian]

Try breaking this into two stored queries:

1) A query that returns the maximum score for each athlete
2) A query that returns the competition details for the minimum date where
the score is the same as what was in the first query.

This is pretty straightforward if you use the query builder. Then from the
ASP page, just execute the query you made.

well, I believe this is impossible in my case because apparently, if I
have well understood, I cannot call a query with paramerer in ASP, so
to say,I cannot do:

RS.Open "BestAthlete 'pentathlon'", "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" & DBDirectory&"Athletics.mdb"

where BestAthlete is the query name and pentathlons is the competition
type.

For the same reason, I cannot split the query in two as you mentioned
before. Both the queries would need the same parameter as input.

Or maybe,there is a trick I donot know.

I use this type of query all the time and it runs pretty fast. You may also
want to look at how you're making the ASP page. Dreamweaver, for example,
adds about 5 sec to the load time of any ASP page just because of the way it
handles database connectivity in the background. Also if you have lots of
large (file size) graphics or you've used a program like Fireworks to make
graphics that are sliced into a lot of tiny pieces this will add to load
time.

I see but it's not my case, here. I build simple asp with my ultraedit
text editor.

Thanks for your reply and sorry for my belated reaction.
Fab.

 

[Amy Blankenship]

well, I believe this is impossible in my case because apparently, if I
have well understood, I cannot call a query with paramerer in ASP, so
to say,I cannot do:

RS.Open "BestAthlete 'pentathlon'", "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=" & DBDirectory&"Athletics.mdb"

where BestAthlete is the query name and pentathlons is the competition
type.

For the same reason, I cannot split the query in two as you mentioned
before. Both the queries would need the same parameter as input.

Or maybe,there is a trick I donot know.

This is the code I use:
set conn = OpenConn("AMGAC.mdb") 'OpenConn is a custom function I wrote to
open a connection
SQL = "Exec [getIssueBlurbs] " & IssueID 'getIssueBlurbs is the name of the
query, IssueID is the parameter
set rs = conn.execute(SQL)

To have multiple parameters, use commas between them after the first one.
As always, strings must be in single quotes.

HTH;

Amy