topic Re: Poor query performance when using date range in Application Development and Automation Discussions

Poor query performance when using date range

Former Member — Wed, 26 Jan 2011 00:47:46 GMT

Hello,

We have the following ABAP code:

select sptag werks vkorg vtweg spart kunnr matnr periv volum_01 voleh

into table tab_aux

from s911

where vkorg in c_vkorg

and werks in c_werks

and sptag in c_sptag

and matnr in c_matnr

that is translated to the following Oracle query:

SELECT

"SPTAG" , "WERKS" , "VKORG" , "VTWEG" , "SPART" , "KUNNR" , "MATNR" , "PERIV" , "VOLUM_01" ,"VOLEH" FROM SAPR3."S911" WHERE "MANDT" = '003' AND "VKORG" = 'D004' AND "SPTAG" BETWEEN 20101201 AND 20101231 AND "MATNR" BETWEEN 000000000100000000 AND 000000000999999999;

Because the field SPTAG is not enclosed by apostropher, the oracle query has a very bad performance. Below the execution plans and its costs, with and without the apostrophes. Please help me understanding why I am getting this behaviour.

##WITH APOSTROPHES

SQL> EXPLAIN PLAN FOR

2 SELECT

3 "SPTAG" , "WERKS" , "VKORG" , "VTWEG" , "SPART" , "KUNNR" , "MATNR" , "PERIV" , "VOLUM_01" ,"VOLEH" FROM SAPR3."S911" WHERE "MANDT" = '003' AND "VKORG" = 'D004' AND "SPTAG" BETWEEN '20101201' AND '20101231' AND "MATNR" BETWEEN '000000000100000000' AND '000000000999999999';

Explained.

SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT

Operation

Name

Rows

Bytes

Cost (%CPU)

0	SELECT STATEMENT		932	62444	150 (1)
1	TABLE ACCESS BY INDEX ROWID	S911	932	62444	149 (0)
2	INDEX RANGE SCAN	S911~VAC	55M		5 (0)

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

1 - filter("VKORG"='D004' AND "SPTAG">='20101201' AND

"SPTAG"<='20101231')

2 - access("MANDT"='003' AND "MATNR">='000000000100000000' AND

"MATNR"<='000000000999999999')

##WITHOUT APOSTROPHES

SQL> EXPLAIN PLAN FOR

2 SELECT

3 "SPTAG" , "WERKS" , "VKORG" , "VTWEG" , "SPART" , "KUNNR" , "MATNR" , "PERIV" , "VOLUM_01" ,"VOLEH" FROM SAPR3."S911" WHERE "MANDT" = '003' AND "VKORG" = 'D004' AND "SPTAG" BETWEEN 20101201 AND 20101231 AND "MATNR" BETWEEN '000000000100000000' AND '000000000999999999';

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

Explained.

SQL>

PLAN_TABLE_OUTPUT

Operation

Name

Rows

Bytes

Cost (%CPU)

0	SELECT STATEMENT		2334	152K	150 (1)
1	TABLE ACCESS BY INDEX ROWID	S911	2334	152K	149 (0)
2	INDEX RANGE SCAN	S911~VAC	55M		5 (0)

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

1 - filter("VKORG"='D004' AND TO_NUMBER("SPTAG")>=20101201 AND

TO_NUMBER("SPTAG")<=20101231)

2 - access("MANDT"='003' AND "MATNR">='000000000100000000' AND

"MATNR"<='000000000999999999')

Best Regards,

Daniel G.

Re: Poor query performance when using date range

volker_borowski2 — Wed, 26 Jan 2011 18:04:37 GMT

Hi,

the reason for the observed is fairly simple. Your filter condition applys a DB-funtion to a DB column,

which means all retrieved data needs to be converted by the function.

So in writing the query, the comparrison would be

where

CHAR_DB_FIELD = to_char_conversion( number_value )

which is very good, because the conversion is only done once, against

where

to_number_conversion(CHAR_DB_FIELD) = number_value

which is very bad, because the conversion needs to be applied against ALL retrieved records.

But the VERY question is: Why do you get SQL Statements without bind variables ?

You should get statements wit :A0 , :A1 , ... and so on.

There are options to force the kernel to generate such statements to be able to make use of histograms.

Do you have such parameters in place?

Volker

Re: Poor query performance when using date range

Former Member — Wed, 26 Jan 2011 18:14:15 GMT

Hello Volker Borowski,

Those execution plans were retrieved directly from the Oracle. In ABAP the queries are generated with bind variables. I meant to ask why is the conversion from opensql (ABAP) to the oracle SQL done without enclosing the char variables with apostrophes.

Regards,

Daniel Garrido

Re: Poor query performance when using date range

HermannGahm — Wed, 26 Jan 2011 18:24:29 GMT

Hi,

additionally to Volker's comments:

how is c_sptag defined?

obviously it is stored as varchar at the database, since it is not converted when you compare it with strings

and not as a number since it is converted when you compare it with numbers.

Make sure to use the same data types.

e.g. define c_sptag as a range for s911-sptag....

Kind regards,

Hermann

Re: Poor query performance when using date range

volker_borowski2 — Wed, 26 Jan 2011 19:42:54 GMT

OK,

so what is the plan for the statement with bind variables like

(get it from ST05 with entering the statement with variables).

The ST05 output from the trace with variables filled in is actually not the one for which the plan is generated.

The filled in values are just for better reading of the trace.

So the statement parsed (the one that is responsible for the plan) is actually the one with variables.

Get some of the DB-stats as well please.

Personally I always like to have

- # of blocks in table

- # of rows in table,

- name of fields in Index(es)

- number of distinct keys from all columns in where clause

- level(blevel) of indexes involved

We'll see later what else might be needed.

Volker

Re: Poor query performance when using date range

Former Member — Wed, 26 Jan 2011 23:52:33 GMT

Hello Hermann,

Answering your question, c_sptag is defined the following way:

data: dt_range type range of s911-sptag with header line.

...

perform f_perio_data tables:i_sptag ix_sptag.

....

&----

*& Form F_PERIO_DATA

&----

form f_perio_data tables: range structure dt_range

x_range structure per_range.

data: dia_ini type s911-sptag.

data: dia_fim type s911-sptag.

refresh range.

clear range.

loop at x_range.

...

dia_ini(6) = x_range-low.

dia_ini+6(2) = '01'.

dia_fim4(2) = dia_ini4(2) + 1.

if dia_fim+4(2) = '13'.

dia_fim+4(2) = '01'.

dia_fim0(4) = dia_ini0(4) + 1.

else.

dia_fim0(4) = dia_ini0(4).

endif.

dia_fim+6(2) = '01'.

dia_fim = dia_fim - 1.

range-low = dia_ini.

range-high = dia_fim.

range-sign = 'I'.

range-option = 'BT'.

append range.

endloop.

endform. " F_PERIO_DATA

Re: Poor query performance when using date range

Former Member — Thu, 27 Jan 2011 00:29:42 GMT

Volker,

Answering your question, regarding the explain from ST05. As a quick work around I created an index (S911~Z9), but still I'd like to solve this issue without this extra index, as primary index would work ok, as long as date was correctly sent to oracle as string and not as number.

SELECT

"SPTAG" , "WERKS" , "VKORG" , "VTWEG" , "SPART" , "KUNNR" , "MATNR" ,

"PERIV" , "VOLUM_01" , "VOLEH"

FROM

"S911"

WHERE

"MANDT" = :A0 AND "VKORG" = :A1 AND "SPTAG" BETWEEN :A2 AND :A3 AND "MATNR"

BETWEEN :A4 AND :A5

A0(CH,3) = 003

A1(CH,4) = D004

A2(NU,8) = 20101201 (NU means number correct?)

A3(NU,8) = 20101231

A4(CH,18) = 000000000100000000

A5(CH,18) = 000000000999999999

SELECT STATEMENT ( Estimated Costs = 10 , Estimated #Rows = 6 )

5 3 FILTER

Filter Predicates

5 2 TABLE ACCESS BY INDEX ROWID S911

( Estim. Costs = 10 , Estim. #Rows = 6 )

Estim. CPU-Costs = 247.566 Estim. IO-Costs = 10

1 INDEX RANGE SCAN S911~Z9

( Estim. Costs = 7 , Estim. #Rows = 20 )

Search Columns: 4

Estim. CPU-Costs = 223.202 Estim. IO-Costs = 7

Access Predicates Filter Predicates

The table originally includes the following indexes:

###S911~0

MANDT

SSOUR

VRSIO

SPMON

SPTAG

SPWOC

SPBUP

VKORG

VTWEG

SPART

VKBUR

VKGRP

KONDA

KUNNR

WERKS

MATNR

###S911~VAC

MANDT

MATNR

Number of entries: 61.303.517

DISTINCT VKORG: 65

DISTINCT SPTAG: 3107

DISTINCT MATNR: 2939

Re: Poor query performance when using date range

HermannGahm — Thu, 27 Jan 2011 07:30:18 GMT

Hi,

did you create a function based index? How does your index look like?

Rigth now i find it quite confusing that we see this conversion...

Kind regards,

Hermann

Re: Poor query performance when using date range

Former Member — Thu, 27 Jan 2011 09:32:03 GMT

No, the existing indexes are the standard ones. No function based index has been created.

So it's not normal that the ABAP type DATS is converted to number oracle type?

Regards,

Daniel Garrido

Re: Poor query performance when using date range

volker_borowski2 — Thu, 27 Jan 2011 17:48:50 GMT

Hello again,

no, it is not normal, but I doubt that it is done that way. I think it is only displayed that way in ST05.

The plan is parsed for bind variables and the corresponding plan should show no function being applied in the filter.

Due to lack of the corresponding S911 tabel I got myself a standard Table with DATS and the ST05 EXPLAIN compiles to:

select * from TPCDATE where to_date = :A1

Filter Predicates

"TO_DATE"=:A1

So no "to_char" at all. The thesis is, that the missing quotes are only applied to the ST05 display because

of the NUMC format of the DATS datatype, which is in fact VARCHAR 2.

For your given condition and the structure so far, I think the PK is useles for a good access.

MATNR with that range as a qualifier is missleading for the optimizer as it is unselective.

Try to ommit it completely.

Fill in your values into an SE16 selection and SQL Trace the SE16 call with the values.

This will have more information than the transformed statement for a direct Oracle Explain.

I assume you'll see a range scan on PK with only MANDT beinig used for access and with this

numer of rows it is worse than a full table scan. I think you might get no success without an additional index.

What type of access do you think will perform good on the PK as long as you have unqualified columns

between MANDT and your first selective column. I even think a SKIP SCAN will do no good for this access.

Volker