当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - Painfully Slow DB2 Query

问题描述:

This query is PAINFULLY slow and our team cannot figure out why. We've tried creating views, but it still extremely slow. Any thoughts?

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 ;

网友答案:

For starters, are all foreign key relationships indexed? (e.g., CMSDPR23.JE215000, CP.JEBOA, etc.

Second, LIKE forces a full table search. Can you index NMELASTBUS and NAME_FIRST (etc...) and check for matches?

Third, are fields in your WHERE clause indexed?

网友答案:

If you haven't done so already, try submitting the query to DB2's EXPLAIN utility to determine what the full access path is and which parts of the query are the most expensive. Any part of the explain plan that uses a relation scan (full table scan) to find rows is the most likely to be improved by an index.

Before you add a bunch of indexes, make sure the tables and indexes involved have have accurate statistics for the optimizer to use. If the table has grown substantially since RUNSTATS was last run, the optimizer may be disregarding perfectly good indexes because it doesn't understand how large the tables have grown. Execute a new RUNSTATS if the cardinality and distribution of the data has changed significantly from what was captured during the last RUNSTATS.

Posting the list of indexes that are already defined on the tables, along with the approximate number of rows in each table would help a lot.

A LIKE search does not necessarily force a table scan, but it certainly can result in an index scan if the column specified is indexed. The EXPLAIN utility will show you what is actually happening in those situations.

A foreign key does not always benefit from an index, especially for foreign keys that have very low cardinality throughout the table. Another issue is that the optimizer generally has to select the best index to use, so having a lot of sub-optimal indexes laying around will eventually slow down updates and may not accelerate reads all that much.

Let's assume that no good indexes exist yet on these tables. From the limited information provided, an index built on ( CWARCASNBR, CIPTYSQNBR, CT1FYA ) for table CMSDPR23.JE215000 may reduce the expense of the join from CMSDPL23.JE026001. Similarly, there is hopefully an index already built on (ADDRESSID, ADDIDSEQNO ) for CMSDPR23.CT007000, since that smells like a primary key or at least a unique candidate key.

Your ORDER BY is going to require a sort if a significant number of rows are returned. You may have a cheaper sort if you go after the same columns CP.JEBOA, CP.CT1FYA in the outer table, since it's only going to be scanned once.

网友答案:

Basic principle as mention before use only index keys, the more index keys the faster.

The order by is going to add a good 1 or 2 minute depending on the amount of database records. i usually try to avoid it, because i'm working with millions of records.

分享给朋友:
您可能感兴趣的文章:
随机阅读: