topic Re: select * ... in Application Development and Automation Discussions

select * ...

Former Member — Mon, 06 Aug 2007 11:09:34 GMT

Hi,

There are some queries in my program where select * is used.

Are they harmful performance wise ?

Shall I use each and every field instead of this, as the no. of fields is 10.

Thanks,

Binay.

Re: select * ...

seshatalpasai_madala — Mon, 06 Aug 2007 11:14:10 GMT

Hi,

It depends on how many fields you want to use in your program.

If you are not making use of all the fields of the table then it is better to mention them in the select unless there are to many fields too mention..

If you are using allmost all of the fields then go for *.

But * will take more time as it fetches more data than selecing few fields.

Generally its because of the lazyness of typing all the fields that most of us use *

<b>Hint: goto SE30 and there click on Tips& Tricks button to see more of the performance realted tips.</b>

Regards,

Sesh

Re: select * ...

Former Member — Mon, 06 Aug 2007 11:20:41 GMT

Hi,

Yes, select * will all ways effects the performance of the program. Instead u can use specific fields in the select query which are only required.

Out of 10 u may not require all fields. So, select only required fields in the select query.

Regards,

Sankar

Re: select * ...

Former Member — Mon, 06 Aug 2007 11:30:11 GMT

Hii BSM,

Yes u said it rightly.

You should avoid the use of <b>select *</b> and as you said there are only 10 fields,

its ok in this case.

But imagine the situation where you have a big table(Eg.Mara,EKKo etc.)

and you need only 10-15 fields, So how much overhead yiu r giving to the system.

So its gud practice to give the name of columns than select *.....

Hope it helps in ur understanding..

Reward points if helpful..

Regards,

Ashish

Re: select * ...

Former Member — Mon, 06 Aug 2007 11:39:10 GMT

hi,

go through the following links for performance

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/cccad390-0201-0010-5093-fd9ec8157802

http://www.asug.com/client_files/Calendar/Upload/ASUG%205-mar-2004%20BW%20Performance%20PDF.pdf

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/1955ba90-0201-0010-d3aa-8b2a4ef6bbb2

reward points if it helps..

regards,

Omkar.

Re: select * ...

Former Member — Mon, 06 Aug 2007 12:42:39 GMT

IF YOU USE SELECT * IN UR PROGRAM WITH PROPER WHERE CONDITION (ON PRIMARY KEYS OR SECONDRY KEYS ) IT WILL BE OK OTHER WISE

IT WILL GO INTO PERFORMANCE PROBLEMS

REWARD IF USEFULL

Re: select * ...

Former Member — Mon, 06 Aug 2007 12:46:52 GMT

Performance problems on database selects arise from problems with indexs and not from the usage of select * or not.

You can improve a statement by specifying a field list:

+ However you should only do it, if you can reduce the number of fields by a factor of 2.

+ And you should not create statement which differ only in the field list, where clause etc identical). Better create a long enough field list which can be used by

all users.

Siegfried

Re: select * ...

Former Member — Mon, 06 Aug 2007 13:34:08 GMT

The transfer of data from the database to the application server is done in blocks whose size depends on the environment / database / etc. When you do a select then the database server runs the SQL and gets the matching records, and then passes them in blocks to the application server.

This can be seen in SQL trace for any large select:

select *

from trdir into table t_data up to p_cnt rows.

3.255 TRDIR REOPEN 0 SELECT WHERE ROWNUM <= 5000

5.572 TRDIR FETCH 721 0

5.149 TRDIR FETCH 721 0

5.184 TRDIR FETCH 721 0

5.275 TRDIR FETCH 721 0

5.616 TRDIR FETCH 721 0

5.338 TRDIR FETCH 721 0

4.709 TRDIR FETCH 674 0

In the above select 721 records are returned from TRDIR in each block using select *, with 674 in the last fetch

changing it to select a few fields:

SELECT name sqlx edtx varcl dbapl

FROM trdir INTO TABLE t_data UP TO p_cnt ROWS.

3.366 TRDIR REOPEN 0 SELECT WHERE ROWNUM <= 5000

1.796 TRDIR FETCH 2.645 0

3.936 TRDIR FETCH 2.355 0

Here 2645 records fetched first time, 2355 second fetch.

Less trips between the database and the application server will improve program performance, especially in production environments where the two are often separate physical servers. This can be seen by totalling the duration of the above two examples (first column) - 40,098 versus 9,098.

Andrew

Re: select * ...

Former Member — Tue, 07 Aug 2007 07:49:18 GMT

Hi,

Definitely ‘Select*’ statement makes the performance issue so that you can choose the following ways which it better for performance issues.

- Write the select statement with required field names instead of all fields (*) which avoids the performance issue.

- Along with selected field names you may also take care in where condition: you should use only key fields in the where conditions and also maintain the sequence of key fields which they occurred in table if possible.

- Suppose if you use the non-key fields in where condition it makes lot of performance issues and there is no options to use key-fields then you have to create the secondary index for those non-key fields.

Hope these points will be useful to you avoid the performance issues.

Regards,

Vijay

Re: select * ...

Former Member — Tue, 07 Aug 2007 09:12:21 GMT

> Definitely ‘Select*’ statement makes the performance issue

defintely not, it improves a bit, but this is not an issue

The other recommendation you are making could lead to an issue.

The where should always contain ALL fields which are known, independent of the fact whether they are in the primary key or in a seconday key!

The order of the fields is actually irrelevant, but it is still recommended to use the order of the optimal index. The index design must take care that the indices are not too similar, otherwise the index determination of the database can go wrong and a suboptimal index is issued.

=> Database access which are not optimally supported by indexes are the performance issue, making statement 10, 100 or even several thousand times slower than necessary.

The improvement of a field list for a table with 10 (not extremely long, no string) fields is in the range of 10 maybe 20%, i.e. a nice to have.

Siegfried

Re: select * ...

Neslinn — Tue, 07 Aug 2007 09:31:07 GMT

Hi,

Upto 16 fields u can specify it in the select query and its always better to use it.

Regards,

Neslin.

Re: select * ...

Former Member — Tue, 07 Aug 2007 13:26:53 GMT

The general recommendation is to avoid SELECT *, but the savings are minimal. There are far more important things to take care of first.

Please see:

<a href="/people/rob.burbank/blog/2006/11/16/performance--what-will-kill-you-and-what-will-leave-you-with-only-a-flesh-wound">Performance - what will kill you and what will leave you with only a flesh wound</a>

Rob