cancel
Showing results for 
Search instead for 
Did you mean: 

slow query,it is this normal?

Former Member
0 Kudos

Hello,

i am getting information from 4 tables with a join the tables are:

-CLIENTES, 404678 records

-DIRECCION, 366777 records

-TELEFONO, 432637 records

-CLIENTE_GRUPO_RELACION, 405976

i am doing this query:

SELECT C.CODIGO_CLIENTE, C.NOMBRE, C.APELLIDO1, C.APELLIDO2, C.NUMERO_TARJETA, T.NUMERO_TELEFONO, D.CODPOSTAL, D.PROVINCIA, D.POBLACION, D.TIPO_VIA, D.NOMBREVIA, D.NUMERO, D.PISO, CG.NOMBRE AS NOMBRE_GRUPO, C.EMAIL FROM SKUDA.CLIENTE AS C LEFT JOIN SKUDA.DIRECCION AS D ON C.CLIENTE_ID=D.ID_CODIGO_ORIGEN AND D.TIPO_ORIGEN='CLIENTE' LEFT JOIN SKUDA.TELEFONO AS T ON C.CLIENTE_ID=T.ID_CODIGO_ORIGEN AND T.TELEFONO_PRINCIPAL=TRUE AND T.TIPO_ORIGEN='CLIENTE' LEFT JOIN SKUDA.CLIENTE_GRUPO_RELACION AS CGR ON C.CLIENTE_ID=CGR.ID_CLIENTE JOIN SKUDA.CLIENTE_GRUPOS AS CG ON CGR.ID_GRUPO=CG.GRUPO_ID AND CGR.PRIMARIO=TRUE WHERE C.CODIGO_CLIENTE LIKE '30%'

it takes 18 seconds and gives me 63812 records. i get this explain output:

CG TABLE SCAN 1

CGR ID_GRUPO JOIN VIA KEY RANGE 1407

C CLIENTE_ID JOIN VIA KEY COLUMN 9631

D CODIGO_ORIGEN_INDEX JOIN VIA MULTIPLE INDEXED COLUMNS 7469

ID_CODIGO_ORIGEN (USED INDEX COLUMN)

TIPO_ORIGEN (USED INDEX COLUMN)

T UQ_ID_PRINCIPAL_TIPO JOIN VIA MULTIPLE INDEXED COLUMNS 4562

TELEFONO_PRINCIPAL (USED INDEX COLUMN)

ID_CODIGO_ORIGEN (USED INDEX COLUMN)

TIPO_ORIGEN (USED INDEX COLUMN)

NO TEMPORARY RESULTS CREATED

RESULT IS COPIED , COSTVALUE IS 148503

and this explain join output:

CG 108 1 1 2.91891891891892 108 1

JOIN VIA MORE THAN ONE FIELD CGR 405216 2402 1 7011.24324324324 259416 7912.99324324324

JOIN VIA SINGLE KEY C 75222 1 1 28824 259416 42913.5646718147

JOIN VIA MORE THAN ONE FIELD D 365981 1 1 43236 259416 93032.0299779371

JOIN VIA MORE THAN ONE FIELD T 428828 1 1 51883.2 259416 148502.898063043

i have updated stadistics but i think this it is too long? any visible problem with my query? i am not sql expert but i have this other query joins 2 tables with 55000 and 20000 records (much less i know but too much difference):

SELECT MO.CODIGO_INTERNO, MO.CODIGO_PROVEEDOR, MO.COLOR, TA.PRECIO, MO.NOMBRE FROM SKUDA.MODELO AS MO LEFT JOIN SKUDA.TARIFA_MODELO_PRECIO AS TA ON MO.MODELO_ID=TA.ID_MODELO WHERE TA.ID_TARIFA=3 AND MO.CODIGO_INTERNO LIKE '02%'

and this give me this explain:

MO UQ_CODIGO_OTEROS_INDEX RANGE CONDITION FOR INDEX 1444

CODIGO_OTEROS (USED INDEX COLUMN)

TA JOIN VIA MULTIPLE KEY COLUMNS 563

ID_MODELO (USED KEY COLUMN)

ID_TARIFA (USED KEY COLUMN)

NO TEMPORARY RESULTS CREATED

RESULT IS COPIED , COSTVALUE IS 661

this is normal behaviour or i am doing something wrong, all the columns involved in the join or select query are indexed.

Accepted Solutions (1)

Accepted Solutions (1)

holger_becker
Employee
Employee
0 Kudos

Hi,

from optimizer point of view it looks good now.

Perhaps you could increase your CACHE_SIZE to reduce I/O.

But this will only speed up the execution if the tables are already read by other operations.

Kind regards

Holger

Answers (2)

Answers (2)

holger_becker
Employee
Employee
0 Kudos

Hi,

for me it looks like the transition

C.CLIENTE_ID=CGR.ID_CLIENTE

is not well supported by your key or index definition.

Please try if a single index on CGR.ID_CLIENTE speed up your query.

Kind regards

Holger

Former Member
0 Kudos

Hi,

i have created a single index in CGR.ID_CLIENTE (it is part of the, two columns, primary key), the stats seem to be better and the time it is better but i think it is possible it have any other problem because it is slow yet.

new explain:

C UQ_CODIGO_INDEX RANGE CONDITION FOR INDEX 9631

CODIGO_CLIENTE (USED INDEX COLUMN)

CGR IDCLIENTE_INDEX JOIN VIA INDEXED COLUMN 1407

ID_CLIENTE (USED INDEX COLUMN)

CG GRUPO_ID JOIN VIA KEY COLUMN 1

TABLE HASHED

D CODIGO_ORIGEN_INDEX JOIN VIA MULTIPLE INDEXED COLUMNS 7469

ID_CODIGO_ORIGEN (USED INDEX COLUMN)

TIPO_ORIGEN (USED INDEX COLUMN)

T UQ_ID_PRINCIPAL_TIPO JOIN VIA MULTIPLE INDEXED COLUMNS 4562

TELEFONO_PRINCIPAL (USED INDEX COLUMN)

ID_CODIGO_ORIGEN (USED INDEX COLUMN)

TIPO_ORIGEN (USED INDEX COLUMN)

NO TEMPORARY RESULTS CREATED

RESULT IS COPIED , COSTVALUE IS 33352

new explain sequence:

TABLE No. 1

C

INV RANGE FETCH

strat 0.185E+00

pages searc 0.179E+04

all_pages 9631

whole_IO_pa 1791

recs_per_p 42

rec_len 481

TABLE No. 2

D

KEY RANGE FETCH

strat 0.100E+01

pages searc 0.746E+04

all_pages 7469

whole_IO_pa 7469

recs_per_p 49

rec_len 484

TABLE No. 3

T

KEY RANGE FETCH

strat 0.100E+01

pages searc 0.456E+04

all_pages 4562

whole_IO_pa 4562

recs_per_p 94

rec_len 52

TABLE No. 4

CGR

KEY RANGE FETCH

strat 0.100E+01

pages searc 0.140E+04

all_pages 1407

whole_IO_pa 1407

recs_per_p 288

rec_len 26

TABLE No. 5

CG

KEY RANGE FETCH

strat 0.100E+01

pages searc 0.100E+01

all_pages 1

whole_IO_pa 1

recs_per_p 108

rec_len 148

| FROM 1 2 3 4 5

TO

1 -- SK SK SK ><

2 MJ -- = = =

3 MJ MJ -- MJ MJ

4 MJ = = -- MJ

5 ?? ?? ?? SK --

mt_cnt : 8

TO via multiplier T F J I Used tables

4 FIRST KEY 0.240E+04 1 1 0 0 [ 5 ]

4 KEY 0.100E+01 2 2 0 0 [ 1 5 ]

4 INDEXCOLUMNS 0.100E+01 1 1 2 1 [ 1 ]

3 INDEXCOLUMNS 0.100E+01 1 2 3 1 [ 1 ]

3 INDEX PART 0.216E+06 0 1 4 3 [ ]

3 UNIQUE INDE 0.100E+01 1 3 4 3 [ 1 ]

2 INDEX PART 0.100E+01 1 1 6 5 [ 1 ]

2 INDEXCOLUMNS 0.100E+01 1 2 6 1 [ 1 ]

TO via multiplier costs ji mi

1 SINGLE KEY 0.100E01 0.263446E02 2 0

2 INDEX-FIELDS 0.100E01 0.141666E03 1 8 multiplejoin

3 UNIQUE INDE 0.100E01 0.104807E03 1 6 multiplejoin

4 KEY 0.100E01 0.102403E03 1 2 multiplejoin

5 SINGLE KEY 0.100E01 0.113513E03 4 0

JOIN SEQUENCE SEARCH = AUTOMATIC - LEVEL 9

< COSTVALUE : 0.137507E+05 [ 1 4 5 ]

> COSTVALUE : 0.434198E+05 [ 4 5 1 ]

> COSTVALUE : 0.429135E+05 [ 5 4 1 ]

BUFFER SIZE PER TASK : 131072 BYTES

TABLE 1 : 130835 BYTES (recs est. 75222)

TABLE 4 : 0 BYTES (recs est. 1)

TABLE 5 : 148 bytes (recs est. 1)

TABLE 2 : 2 BYTES (recs est. 1)

TABLE 3 : 87 bytes (recs est. 1)

-


anyway works much better thant before, maybe this is the real best speed of the query?

holger_becker
Employee
Employee
0 Kudos

Hi,

would you mind posting the output of the explain sequence here?

It's hard to read but might give use more information.

Kind regards

Holger

Former Member
0 Kudos

Hi,

this is the explain sequence output:

TABLE No. 1

C

INV RANGE FETCH

strat 0.185E+00

pages searc 0.179E+04

all_pages 9631

whole_IO_pa 1791

recs_per_p 42

rec_len 481

TABLE No. 2

D

KEY RANGE FETCH

strat 0.100E+01

pages searc 0.746E+04

all_pages 7469

whole_IO_pa 7469

recs_per_p 49

rec_len 484

TABLE No. 3

T

KEY RANGE FETCH

strat 0.100E+01

pages searc 0.456E+04

all_pages 4562

whole_IO_pa 4562

recs_per_p 94

rec_len 52

TABLE No. 4

CGR

KEY RANGE FETCH

strat 0.100E+01

pages searc 0.140E+04

all_pages 1407

whole_IO_pa 1407

recs_per_p 288

rec_len 26

TABLE No. 5

CG

KEY RANGE FETCH

strat 0.100E+01

pages searc 0.100E+01

all_pages 1

whole_IO_pa 1

recs_per_p 108

rec_len 148

| FROM 1 2 3 4 5

TO

1 -- SK SK SK ><

2 MJ -- = = =

3 MJ MJ -- MJ MJ

4 = = = -- MJ

5 ?? ?? ?? SK --

mt_cnt : 7

TO via multiplier T F J I Used tables

4 FIRST KEY 0.240E+04 1 1 0 0 [ 5 ]

4 KEY 0.100E+01 2 2 0 0 [ 1 5 ]

3 INDEXCOLUMNS 0.100E+01 1 2 3 1 [ 1 ]

3 INDEX PART 0.216E+06 0 1 4 3 [ ]

3 UNIQUE INDE 0.100E+01 1 3 4 3 [ 1 ]

2 INDEX PART 0.100E+01 1 1 6 5 [ 1 ]

2 INDEXCOLUMNS 0.100E+01 1 2 6 1 [ 1 ]

TO via multiplier costs ji mi

1 SINGLE KEY 0.100E01 0.263446E02 2 0

2 INDEX-FIELDS 0.100E01 0.141666E03 1 7 multiplejoin

3 UNIQUE INDE 0.100E01 0.104807E03 1 5 multiplejoin

4 EQUAL FIELD 0.100E01 0.703612E06 1 0

5 SINGLE KEY 0.100E01 0.113513E03 4 0

JOIN SEQUENCE SEARCH = AUTOMATIC - LEVEL 9

< COSTVALUE : 0.105857E+09 [ 1 4 5 ]

< COSTVALUE : 0.189001E+07 [ 1 5 4 ]

< COSTVALUE : 0.735207E+05 [ 4 1 5 ]

< COSTVALUE : 0.434198E+05 [ 4 5 1 ]

< COSTVALUE : 0.429135E+05 [ 5 4 1 ]

BUFFER SIZE PER TASK : 131072 BYTES

TABLE 5 : 26420 BYTES (recs est. 108)

TABLE 4 : 103229 BYTES (recs est. 2402)

TABLE 1 : 481 bytes (recs est. 1)

TABLE 2 : 855 BYTES (recs est. 1)

TABLE 3 : 87 bytes (recs est. 1)

thanks for help, if this is the speed i can get, i think i should denormalize, should not?