This question is Shateringly slow and we cannot understand why. We have attempted creating sights, however it still very slow. Any ideas?
SELECT CI . CWARCASNBR AS CASENUMBER , CI . CT1FYA AS COURTAGENCYCODE , CI . CIPTYSQNBR AS PARTYSEQNBR , CI . CIRCDTYPE AS CASETYPECODE , CP . NMELASTBUS AS LASTNAME , CP . NAME_FIRST AS FIRSTNAME , CP . NAME_MID AS MIDDLENAME , CP . NAME_SUFFX AS SUFFIX , CP . CP_SEX AS GENDER , CP . CT1PA AS RACECODE , CP . CP_DOB AS DOB , CP . CP_SSN AS SSN , A . STREETNAME AS ADDRESS1 , A . ADDRLINE2 AS ADDRESS2 , A . CITYPARISH AS CITY , A . ADDRSTATE AS STATE , A . ZIPCODE AS ZIP FROM CMSDPL23 . JE026001 AS CP LEFT OUTER JOIN CMSDPR23 . JE215000 CI ON CP . JEBOA = CI . CWARCASNBR AND CP . CT1FYA = CI . CT1FYA AND CP . CP_SEQ_NBR = CI . CIPTYSQNBR LEFT OUTER JOIN CMSDPR23 . CT007000 A ON CP . ADDRESSID = A . ADDRESSID AND CP . ADDRESSPRI = A . ADDIDSEQNO WHERE CP . NMELASTBUS LIKE 'Durham' || '%' AND CP . NAME_FIRST LIKE 'Roger%' || '%' AND NOT CP . PRTY_TCDE IN ( 'OFF' , 'BEP' ) AND CI . CI_FLAG_1 IN ( 'C' , 'B' ) AND CI . CT1MKA = '23' ORDER BY CI . CWARCASNBR , CI . CT1FYA ;
To begin with, are foreign key associations indexed? (e.g.,
LIKE forces a complete table search. Are you able to index
NAME_FIRST (etc...) and appearance for matches?
Third, are fields inside your
WHERE clause indexed?
Should you haven't done this already, try posting the query to DB2's EXPLAIN utility to determine which the entire access path is and which areas of the query would be the most costly. Any area of the explain plan that utilizes a relation scan (full table scan) to locate rows may be the that appears to be enhanced by a catalog.
Before you decide to add a lot of indexes, make certain the tables and indexes involved have have accurate statistics for that optimizer to make use of. When the table is continuing to grow substantially since RUNSTATS was last run, the optimizer might be neglecting perfectly good indexes since it does not know how large the tables have become. Perform new RUNSTATS when the cardinality and distribution from the data has transformed considerably from that which was taken throughout the final RUNSTATS.
Posting their email list of indexes which are already defined around the tables, together with the approximate quantity of rows in every table would be very convenient.
A LIKE search doesn't always pressure a table scan, however it certainly can lead to a catalog scan when the column specified is indexed. The EXPLAIN utility will highlight what's really happening in individuals situations.
An overseas key doesn't necessarily take advantage of a catalog, specifically for foreign secrets which have really low cardinality through the table. Another problem would be that the optimizer generally needs to choose the very best index to make use of, so getting lots of sub-optimal indexes lounging around will ultimately decelerate updates and could not accelerate reads that much.
Let us think that not good indexes exist yet on these tables. In the limited information provided, a catalog built on ( CWARCASNBR, CIPTYSQNBR, CT1FYA ) for table CMSDPR23.JE215000 may reduce the fee for the join from CMSDPL23.JE026001. Similarly, there's hopefully a catalog already built on (ADDRESSID, ADDIDSEQNO ) for CMSDPR23.CT007000, since that has the aroma of a principal key or at best a distinctive candidate key.
Your ORDER BY will need a sort if your significant quantity of rows are came back. You might have a less expensive sort should you pursue exactly the same posts Clubpenguin.JEBOA, Clubpenguin.CT1FYA within the outer table, becasue it is only likely to be scanned once.