Skip to Content

SELECT on database table containing strings takes much too much time (Oracle)

Hi there!

I have created a database table with two index fields (key1, key2) of type CHAR2 and a value field of type STRING.

the table contains 3700 entries. The value field contains only short strings (max 30).

The SELECT statement takes about 3 seconds...

Selecting only the key fields is very fast but with string field 3 seconds.

I did an SQL trace but there is only the SELECT statement with this enormous run time.

I know that storing and selecting STRINGS in a database is not the best way, but I see no reason why it takes that much time.

this is from performance trace SAT:

Do you have any hints for me?

Thanks in advance

Enno

ora1.png (56.6 kB)
Add a comment
10|10000 characters needed characters exceeded

Related questions

5 Answers

  • Posted on Sep 04, 2019 at 07:16 AM

    Hello,
    I was just solving this issue yesterday :-)
    We had a (standard abap / open sql) SELECT from Z* DB table where one of the columns is defined as STRING (CLOB in Oracle). Simple SELECT * FROM ztable of about 40k records took ~ 60 seconds!

    I was investigating what could be the problem and pin-poined it also on STRING column. If I run same SELECT but just without selecting the STRING column - it took ~ 1 second (40k records, just without that one string column).

    Could not find any other solution. But I tried "native SQL" from Oracle using "EXEC SQL" in ABAP and selecting same amount of data took now only ~ 10 seconds (FETCH in DO. ENDDO. cycle, different way might be even faster? comment please). So I ended up with using this "workaround" because it is the part of code where we need as much performance as we can. BUT! read below!!!

    If you use this workaround with native SQL - then I strongly recommend to do a detailed testing and comparison of the data you are getting (OPEN SQL vs. Native SQL SELECTs). In my case data were ALMOST identical. Difference was that string data of native SQL select were missing all 'EN SPACE' (U+2002) trailing characters! (no problem for our implementation)

    Please comment / share other ideas if you have any...

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 04, 2019 at 06:02 AM

    This is an answer test.

    Or a Test answer.

    From mobile

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 04, 2019 at 10:15 PM

    Hi Enno,

    if the string is implemented as a CLOB or even BLOB on Oracles side, that would explain some delay. I don't understand this huge delay though.

    But the gerenal problem with [BC]LOBs is, that you need an extra step in the OCI interface to retreive the data.I havent programmed with Oracle OCI since Oracle 10 though, so my knowledge is somehow outdated. In any case Strings are just simply there when you retrieve a row from the data base but for CLOBs you need to open a stream for every selected row and 'String' field in order to retrieve that data. I guess thats what the SAP data base driver does under the hood.

    But I see this is an interesting issue. Especially when it comes to compare to HANA behaviour, so I will have a look myself soon.

    Cheers,
    Peter

    Add a comment
    10|10000 characters needed characters exceeded

    • Thanks Peter! Yes, that matches with my information. As STRINGS seem to be stored outside the database, the access is a little bit more complicated. But, again, it should not be THAT slow... Still searching for some switches or parameters to make this "extra trip" faster.

      Cheers
      Enno

  • Posted on Apr 09 at 05:45 PM

    Hi guys,

    I am having the same problem. I did a test where I did a select on the same table with the field containing the string and one without. The difference is crazy (5 seconds to 12 milliseconds). Execution plan is the same for both (5 cost).

    I cannot find any info on this issue in any official document (notes, kernel release notes).

    Do you have any info for me whether this was fixed in a new kernel. Currently running 753 PL400.

    Oracle Release: 12.1.0.2.0

    Looking forward for your answers. Happy Easter everyone!

    BR
    Fabian

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 03, 2019 at 05:19 PM
    -1

    Hi Helena! There is no specific Trace information for this select.

    I created a second similar table to reproduce this issue and it's the same problem. Replacing STRING by CHAR1024 makes the select fast as ever.

    I also don't remember any problems with string fields in the database

    Add a comment
    10|10000 characters needed characters exceeded

    • Please use the COMMENT button for comments, questions, adding details, etc., ANSWER is only to propose a solution, dixit SAP text at the right of the answer area: "Before answering You should only submit an answer when you are proposing a solution to the poster's problem"

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.