Query optimization

[James B]

I am doing the following query for a game stats page in
ASP and Access mdb. Is there another way I can rewrite
it to execute faster?

SELECT
GAMES.ID as GameID, GAMES.DisplayName as DisplayName,
STATS.ScoreLevel as ScoreLevel, STATS.SecPlayed as
SecPlayed, STATS.LevelNum as LevelNum, STATS.Difficulty
as Difficulty
FROM
GAMES, STATS
WHERE
GAMES.ID = STATS.GameID AND STATS.PackageCRC=328161819
ORDER BY
STATS.SecPlayed ASC, STATS.ScoreTotal DESC,
STATS.LevelNum ASC, STATS.ID;

All WHERE and ORDER BY fields are already indexed. FYI
GAMES contains about 1k records and STATS 10k.

Is it faster to join the two tables using another type of
JOIN rather than a multiple SELECT?

Also I only need the TOP X results starting at N, I tried
using the SQL TOP command for this but couldn't figure
out to control where it started at, any suggestions to do
that?

 

[Guest]

I would use an inner join here, with a Where clause on the PackageCRC.

FROM GAMES INNER JOIN STATS ON GAMES.GamesID = STATS.GamesID
WHERE STATS.PackageCRC=328161819

Depending upon the cardinality of the PackageCRC field, it may be useful to
create a subquery of STATS before joining on the GameID (to limit the size of
the Cartesian Product Table being built):

FROM GAMES INNER JOIN
(SELECT STATS.* FROM STATS WHERE STATS.PackageCRC=328161819) STATSOuter ON
GAMES.GamesID = STATSOuter.GamesID

Or another variation of the above that performs the WHERE during the join:

FROM GAMES INNER JOIN STATS ON GAMES.GamesID = STATS.GamesID AND
STATS.PackageCRC=328161819

-----

It isn't always easy to know how the query optimizer is will plan the query.
Sometimes you have to try a few scenarios.

David Atkins, MCP

 

[Guest]

I got all of them to work except for the following one, I think there may be
typo in it

FROM GAMES INNER JOIN
(SELECT STATS.* FROM STATS WHERE STATS.PackageCRC=328161819) STATSOuter ON
GAMES.GamesID = STATSOuter.GamesID

Here is the query I am using

SELECT GAMES.ID as GameID, GAMES.DisplayName as DisplayName,
STATS.ScoreLevel as ScoreLevel, STATS.SecPlayed as SecPlayed, STATS.LevelNum
as LevelNum, STATS.Difficulty as Difficulty FROM GAMES INNER JOIN (SELECT
STATS.* FROM STATS WHERE STATS.PackageCRC=328161819) STATS OUTER JOIN ON
GAMES.ID = STATS.GamesID ORDER BY STATS.ScoreLevel DESC, STATS.SecPlayed ASC,
STATS.LevelNum ASC, STATS.ID;

It says there is an error in the FROM clause.

Also I wish there was an easy way to bookmark or view my current treads,
finding them with the page arrows is no fun.