SELECT clause using IN ... very slow?
Solution 1
Lets forget for a moment the empno BETWEEN 1 and 200
suggestion and assume that you have you have t2.empno IN (3,7,...,5209)
(200 entries).
You could also write your query (which is a hidden JOIN query) to the non-equivalent EXISTS query which would show same results (but possibly fewer rows) and should be faster than the JOIN:
SELECT
t1.name
FROM
t1
WHERE EXISTS
( SELECT *
FROM t2
WHERE t2.id = t1.id
AND t2.empno IN (3,7,...,5209)
)
(Wild speculation)
If on the other hand, it's not even t2.empno IN (3,7,...,5209)
but t2.empno IN (SELECT tx.empno FROM tx WHERE someConditions)
and you are using MySQL, then this is the root of your problem (MySQL is known to not handle field IN (SELECT f FROM x)
in the best possible way). So, you could change the query into:
SELECT
t1.name
FROM
t1
JOIN t2
ON t2.id = t2.id
JOIN tx
ON tx.empno = t2.empno
WHERE
someConditions
or even to:
SELECT
t1.name
FROM
t1
WHERE EXISTS
( SELECT *
FROM t2
JOIN tx
ON tx.empno = t2.empno
WHERE t2.id = t1.id
AND someConditions
)
Solution 2
The first thing that is wrong is using implict join syntax. That is a SQL antipattern.
If you have a large list in the IN clause, have you tried putting them in a table instead and using a join?
What database? Have you looked at your explain plan or execution plan to see where the slowdown is?
Solution 3
Don't use the cross-join.
try this
SELECT
t1.name
FROM
t1
JOIN t2
ON t2.id = t1.id
WHERE
t2.empno IN (1,...,200)
EDIT: After you edit, seeing your multiple tables in the cartesian products, it is probably very important that you use proper JOIN
syntax.
Comments
-
Sudhakar over 1 year
Could you guys please review the following query to an Oracle DB and point out what's wrong:
SELECT t1.name FROM t1, t2 WHERE t1.id = t2.id AND t2.empno IN (1, 2, 3, …, 200)
Query statistics:
- Time taken: 10.53 seconds.
Indices:
t2.empno
is indexed.t1.id
is indexed.t2.id
is indexed.
Update
The above query was just a sample replica of the query i use. Here below in a more true form
Explain Plan
Query:
SELECT PRODUCT_REPRESENTATION_SK FROM Product_Representation pr , Design_Object do , Files files ,EPS_STATUS epsStatus ,EPS_ERROR_CODES epsError ,VIEW_TYPE viewTable WHERE pr.DESIGN_OBJECT_SK = do.DESIGN_OBJECT_SK AND pr.LAYER_NAME !='Layer 0' AND epsStatus.EPS_STATUS_SK = pr.EPS_STATUS AND epsError.EPS_ERROR_CODE = pr.EPS_ERROR_CODE AND viewTable.VIEW_TYPE_ID = pr.VIEW_TYPE_ID AND files.pim_id = do.PIM_ID AND do.DESIGN_OBJECT_ID IN ( 147086,149924,140458,135068,145197,134774,141837,138568,141731,138772,143769,141739,149113,148809,141072,141732,143974,147076,143972,141078,141925,134643,139701,141729,147078,139120,137097,147072,138261,149700,149701,139127,147070,149702,136766,146829,135762,140155,148459,138061,138762............................................. 200 such numbers )
Indexed Colums:
pr.DESIGN_OBJECT_SK do.DESIGN_OBJECT_SK do.DESIGN_OBJECT_ID files.pim_id
Table
TABLE "PIM"."DESIGN_OBJECT" ( "DESIGN_OBJECT_SK" NUMBER(*,0) NOT NULL ENABLE, "PIM_ID" NUMBER(*,0) NOT NULL ENABLE, "DESIGN_OBJECT_TYPE_SK" NUMBER(*,0) NOT NULL ENABLE, "DESIGN_OBJECT_ID" VARCHAR2(40 BYTE) NOT NULL ENABLE, "DIVISION_CD" NUMBER(*,0), "STAT_IND" NUMBER(*,0) NOT NULL ENABLE, "STAT_CHNG_TMST" TIMESTAMP (6), "CRTD_BY" VARCHAR2(45 BYTE), "CRT_TMST" TIMESTAMP (6), "MDFD_BY" VARCHAR2(45 BYTE), "CHNG_TMST" TIMESTAMP (6), "UPDATE_CNT" NUMBER(*,0), "GENDER" VARCHAR2(1 BYTE), PRIMARY KEY ("DESIGN_OBJECT_SK") ) TABLESPACE "PIM" ENABLE, FOREIGN KEY ("DESIGN_OBJECT_TYPE_SK") REFERENCES "PIM"."DESIGN_OBJECT_TYPE" ("DESIGN_OBJECT_TYPE_SK") ON DELETE CASCADE ENABLE, FOREIGN KEY ("PIM_ID") REFERENCES "PIM"."FILES" ("PIM_ID") ON DELETE CASCADE ENABLE )
Table 2
CREATE TABLE "PIM"."PRODUCT_REPRESENTATION" ( "PRODUCT_REPRESENTATION_SK" NUMBER(*,0) NOT NULL ENABLE, "DESIGN_OBJECT_SK" NUMBER(*,0) NOT NULL ENABLE, "VIEW_TYPE_ID" NUMBER(*,0) NOT NULL ENABLE, "LAYER_NAME" VARCHAR2(255 BYTE), "STAT_IND" NUMBER(*,0) NOT NULL ENABLE, "STAT_CHNG_TMST" TIMESTAMP (6), "CRTD_BY" VARCHAR2(45 BYTE), "CRT_TMST" TIMESTAMP (6), "MDFD_BY" VARCHAR2(45 BYTE), "CHNG_TMST" TIMESTAMP (6), "UPDATE_CNT" NUMBER(*,0), "EPS_STATUS" VARCHAR2(30 BYTE) NOT NULL ENABLE, "EPS_GENERATED_TIME" TIMESTAMP (6), "EPS_ERROR_CODE" NUMBER, "EPS_ERROR_DETAILS" VARCHAR2(500 BYTE), "DEEPSERVER_ASSET_LAYER_ID" VARCHAR2(255 BYTE), "PRODUCT_REPRESENTATION_LOC" VARCHAR2(255 BYTE), PRIMARY KEY ("PRODUCT_REPRESENTATION_SK") ) TABLESPACE "PIM" ENABLE, FOREIGN KEY ("DESIGN_OBJECT_SK") REFERENCES "PIM"."DESIGN_OBJECT" ("DESIGN_OBJECT_SK") ON DELETE CASCADE ENABLE, FOREIGN KEY ("VIEW_TYPE_ID") REFERENCES "PIM"."VIEW_TYPE" ("VIEW_TYPE_ID") ON DELETE CASCADE ENABLE, CONSTRAINT "EPS_ERROR_CODE_FK" FOREIGN KEY ("EPS_ERROR_CODE") REFERENCES "PIM"."EPS_ERROR_CODES" ("EPS_ERROR_CODE") ON DELETE CASCADE ENABLE, CONSTRAINT "EPS_STATUS_FK" FOREIGN KEY ("EPS_STATUS") REFERENCES "PIM"."EPS_STATUS" ("EPS_STATUS_SK") ON DELETE CASCADE ENABLE )