topic Database SELECT performance in Application Development and Automation Discussions

Database SELECT performance

Former Member — Tue, 27 Feb 2007 10:15:56 GMT

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?

Re: Database SELECT performance

Former Member — Tue, 27 Feb 2007 10:18:02 GMT

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

Re: Database SELECT performance

Former Member — Tue, 27 Feb 2007 10:21:23 GMT

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

Re: Database SELECT performance

Former Member — Tue, 27 Feb 2007 10:23:40 GMT

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.

Re: Database SELECT performance

Former Member — Tue, 27 Feb 2007 10:31:48 GMT

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.

Re: Database SELECT performance

Former Member — Tue, 27 Feb 2007 10:38:53 GMT

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

Re: Database SELECT performance

Former Member — Tue, 27 Feb 2007 13:19:37 GMT

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

Re: Database SELECT performance

Former Member — Tue, 27 Feb 2007 14:02:33 GMT

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.

Re: Database SELECT performance

Former Member — Tue, 27 Feb 2007 14:03:26 GMT

instead of using SELECT *

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