Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Database SELECT performance

Former Member
0 Kudos

I am doing some work that involves a large table view (COVP). The view has somewhere in the region of 7 million entries and growing.

I have been doing some tests on selecting data from the view.

If I use the statement

SELECT * FROM covp INTO TABLE t_covp
    WHERE objnr EQ 'OR000002000571'.

the select has a duration of 183,411,192 (accoring to the trace log).

If I use the statement

SELECT * FROM covp INTO TABLE t_covp
    WHERE objnr EQ 'OR000002000571'
      AND lednr EQ '00'.

the select only has a duration of 24,591 (according to the trace).

Both select statements return the same result set (6 records).

Obviously, the first select takes far to long. My question is, why is there such a large difference in run times. Neither of the fields are key fields.

Am I missing something?

8 REPLIES 8

Former Member
0 Kudos

Hi,

Since you have given both the key fields(not always) in the where condition of the Select, the second one took less time.

It's always good to give all the key fields(Major data fields) in the where clause.

Regards,

anji

Message was edited by:

Anji Reddy Vangala

former_member181962
Active Contributor
0 Kudos

Hi Stuart,

The more key fields(Primary or otherwise) you pass to the select statement, it easier it becomes for the processor. Hence it is an important guideline in most performance improvement docuemants to provide the complete key for data retrievals.

Regards,

Ravi

Former Member
0 Kudos

Hi Stuart,

There might be several reasons: if the selects are performed shortly after another the results of the second run will be influenced by the first run (buffering). Furthermore there might be indexes defined for one of the underlying tables.

To be sure: switch on the SQL-trace to find out the reason.

Regards,

John.

Former Member
0 Kudos

Just to make sure whether buffering is happening, try to run those statements in reverse order and see if you can see the same amoutn of time. Alternatively SQL trace also tells you whether buffer was used or not.

I dont see much of a difference by using LEDNR, so the improvement must be due to buffering effect.

Former Member
0 Kudos

Hmm. I don't think it's a buffering effect. If this was the case then surely only the second select statement would benefit.

I actually performed these selects the other way round (the slower one was done second).

Message was edited by:

Stuart Mayor

Former Member
0 Kudos

Hi Stuart,

The Tables COEP and COBK has been joined to form the View COVP.

Please check the Indexes of the transparent table COEP in which the fields OBJNR and LEDNR have been marked which may increase the performance of DB fetch.

Thanks and Regards,

Siva

0 Kudos

The main criterion which drives performane is the number of fields you select and the number of where condition you specify in your select statemnt ( Primary or otherwise)

The two together are directly proportional to the performance.

0 Kudos

instead of using SELECT *

select only those fields which you specifically need for the rest of the program this would even increase your performance.