Speeding Up Access 2016 Query

user_12925123 0
Viewed 78

I have a query that contains amongst other things batsmanIDs and League names (extract below). I have put together another query to return all records where a batsman has played in each of two Leagues.

The query works but it is very, very slow.

There are 48,000 records returned in the first query but when I use that it runs instantly.

Can anyone suggest how to speed the new query up?

Thanks

Query:

SELECT *
FROM AddDateLeagueSeasontoScorecardBatting
WHERE League ="Indian Premier League" AND batsmanId IN (SELECT batsmanId FROM AddDateLeagueSeasontoScorecardBatting WHERE League ="Big Bash League") OR League ="Big Bash League" AND batsmanId IN (SELECT batsmanId FROM AddDateLeagueSeasontoScorecardBatting WHERE League ="Indian Premier League");

Table: enter image description here

ms-access sql performance
4 Answers

So, you are interested to get batsman who played in both leagues. There is a design problem in your database. Think about your query: you are searching by string values, which is very slow, particularly in the case when there are other possible values for those strings, which are very similar to the one you are searching for. You would need to create a table for leagues, where you would store the name of the league, possibly along with other attributes and a numeric id.

Create a foreign key in your AddDateLeagueSeasontoScorecardBatting referring to the id of the newly created other table for leagues. Index your foreign key, so searches will dramatically quicken up. You should know that you will have a price to pay in performance when records are inserted or the indexed key changes at an update. Now that you have a league table and an indexed foreign key referring to it, try this query:

SELECT *
FROM AddDateLeagueSeasontoScorecardBatting MAIN
JOIN League IPL
on IPL.Name = 'Indian Premier League'
JOIN League BBL
on BBL.Name = 'Big Bash League'
WHERE EXISTS (
    SELECT 1
    FROM AddDateLeagueSeasontoScorecardBatting FIRST
    JOIN AddDateLeagueSeasontoScorecardBatting SECOND
    ON FIRST.batsmanId = SECOND.batsmanId AND
       FIRST.batsmanId = MAIN.batsmanId AND
       FIRST.LeagueId = IPL.LeagueId AND
       SECOND.LeagueId = BBL.LeagueId
);

Note: You will probably want to use the distinct keyword or to group by, but that's another story.

user_4365600

You could accomplish this using inner joins such that the query will return only those records with a batmans appearing in both subqueries:

select t1.* from 
(
    AddDateLeagueSeasontoScorecardBatting t1 inner join
    (
        select distinct batsmanid 
        from AddDateLeagueSeasontoScorecardBatting 
        where League ="Indian Premier League"
    ) t2 on t1.batsmanid = t2.batsmanid
) 
inner join
(
    select distinct batsmanid 
    from AddDateLeagueSeasontoScorecardBatting 
    where League ="Big Bash League"
) t3 on t1.batsmanid = t3.batsmanid

EDIT: To select batsmen who have only played in the two leagues in question (and no others), you might try:

select t1.* from 
AddDateLeagueSeasontoScorecardBatting t1 inner join
(
    select batsmanid
    from AddDateLeagueSeasontoScorecardBatting 
    group by batsmanid
    having 
        sum(League not in ("Indian Premier League", "Big Bash League")) = 0 and
        sum(League = "Indian Premier League") < 0 and 
        sum(League = "Big Bash League") < 0
) t2 on t1.batsmanid = t2.batsmanid
user_75315980

Try with this where clause:

WHERE batsmanId IN 
    (SELECT batsmanId
    FROM AddDateLeagueSeasontoScorecardBatting 
    GROUP BY batsmanId
    HAVING (League IN ("Indian Premier League","Big Bash League")) AND Count(*) = 2)
user_35272970

You need to simplify your query. Try this:

SELECT * FROM AddDateLeagueSeasontoScorecardBatting WHERE League = "Indian Premier League" or League = "Big Bash League"

user_25702770
Related