CODEWITHSUNDEEP
sqlms-accessjoinoptimizationms-access-2010
Theo Babilon
Theo Babilon

Reputation: 661

SQL query Optimisation JOIN multiple column

I have two tables on Microsoft Access: T_DATAS (about 200 000 rows) and T_REAF (about 1000 rows).

T_DATAS has a lot of columns (about 30 columns) and T_REAF has about 10 columns.

I have to tell you that I am not allowed to change those tables nor to create other tables. I have to work with it.

Both tables have 6 columns that are the same. I need to join the tables on these 6 columns, to select ALL the columns from T_DATAS AND the columns that are in T_REAF but not in T_DATAS.

My query is :

SELECT A.*, B.CARROS_NEW, B.SEGT_NEW, B.ATTR INTO FINALTABLE FROM T_DATAS A LEFT JOIN T_REAF B ON A.REGION LIKE B.REGION AND A.PAYS LIKE B.PAYS AND A.MARQUE LIKE B.MARQUE AND A.MODELE LIKE B.MODELE AND A.CARROS LIKE B.CARROS AND A.SEGT LIKE B.SEGT

I have the result I need but the problem is that this query is taking way too long to give the result (about 3 minutes). I know that T_DATAS contains a lot of rows (200 000) but I think that 3 minutes is too long for this query.

Could you please tell me what is wrong with this query?

Thanks a lot for your help

Upvotes: 1

Views: 117

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Two steps for this. One is changing the query to use =. I'm not 100% sure if this is necessary, but it can't hurt. The second is to create an index.

So:

SELECT D.*, R.CARROS_NEW, R.SEGT_NEW, R.ATTR
INTO FINALTABLE
FROM T_DATAS D LEFT JOIN
     T_REAF R
     ON D.REGION = R.REGION AND
        D.PAYS = R.PAYS AND
        D.MARQUE = R.MARQUE AND
        D.MODELE = R.MODELE AND
        D.CARROS = R.CARROS AND
        D.SEGT = R.SEGT;

Second, you want an index on T_REAF:

CREATE INDEX IDX_REAF_6 ON T_REAF(REGION, PAYS, MARQUE, MODELE, CARROS, SEGT);

MS Access can then use the index for the JOIN, speeding the query.

Note that I changed the table aliases to be abbreviations for the table names. This makes it easier to follow the logic in the query.

Upvotes: 3

Pedram
Pedram

Reputation: 6508

I assume that those 6 columns are same may have same datatype also.

Note: Equals (=) operator is a comparison operator - that compares two values for equality. So in your query replace LIKE with = and see the result time. 

SELECT A.*
      ,B.CARROS_NEW
      ,B.SEGT_NEW
      ,B.ATTR
       INTO FINALTABLE
FROM   T_DATAS A
       LEFT JOIN T_REAF B
            ON  A.REGION = B.REGION
                AND A.PAYS = B.PAYS
                AND A.MARQUE = B.MARQUE
                AND A.MODELE = B.MODELE
                AND A.CARROS = B.CARROS
                AND A.SEGT = B.SEGT

Upvotes: 2

Related Questions