Select querry - performence

[Former Member]

Hi all,

I want to clarify one thing...........

1. select F1 F2 F3

from X

into table itab

where F1 in s_F1

and F2 in s_F2.

2. Select * from X into table itab.

here itab type table of X.

which select statement will give less perfomence and is there any relation betwwen table size and perfomence of select querry?

If there is a relation let me know how to find it..........

Thanks,

kk.

[Former Member]

Hi all,

I want to clarify one thing...........

1. select F1 F2 F3

from X

into table itab

where F1 in s_F1

and F2 in s_F2.

2. Select * from X into table itab.

here itab type table of X.

which select statement will give less perfomence and is there any relation betwwen table size and perfomence of select querry?

If there is a relation let me know how to find it..........

Thanks,

kk.

[Former Member]

hi,

The second select statement gives less performance as you are selecting all the entries with * without any where condition. Yes performance of a statement depends on number of entries in the table. If there are many records then the table has to go through all the records to find the suitable record which we are looking for.

Regards,

Santosh

[Former Member]

Hi santhosh,

Thanks for ur reply.....

if we have more table entries then i think we should use second select querry...........

then that select querry will give less performence right.........

Is there any way to avoid this problem?

Thanks,

kk.

[Former Member]

Hi,

The number of records and the number of fields you cfetch classify into 2 issues.

If the table has many entries, WHERE condition will influence the performance of the select query.

if the table has say 100+ fields and you require say abt 10-15 fields for processing in your program. in this case if you specify select *, it might impact the performance of your program. then it is better u specify the fields.

if you have select * for a huge table with lots of fields, you have to be careful in writing an efficient select keeping in mind the number of fields to be selected and the WHERE condition.

regards,

madhumitha

[Former Member]

Hi,

I can highly recommend you the read more general information about selecting from databases.

About performance:

+ the WHERE condition is much more important than the field list. It is important than you restrict the number of wanted records in the WHERE condition. It is important that your in-clauses are filled!

And it is important than you fields of the WHERE condition are in indexes.

+ Using fieldlist is nice to have and play a role if the table has really a lot of columns, but not it is rather slim. Use it if you can reduce the fields at least by a factor of 2, and don't create new statements which are identical in everything but not in the fieldlist.

Siegfried

[Former Member]

hiii,,

as per your qustion i just wanna say tht the performance of the prg depends heavily on the select queries tht we use.

if we use select... end select statements for our open sql statement the 1 data at a time is being fetched.its time consumeing.

then again we should never do any type of calculations,negations staements etc in the select query,beacuse it gets executed in the database it self.....

now if the data is huge the useing * is useful but we must have a correct where clause.

but if the data set is less the fully qualified clause for it.. or the fully qualified key for the query or the table in question.

reward points if helpful..

thankss....

[Former Member]

Hi Kusuma,

the answer is, it depends (as you already have read in the other postings):

If both ranges are filled and you have an index on both fields (as the two first columns of the index - may be with the client in the first place), the first statement will probably run faster than the second in most cases, because it has to read less blocks from disk. This could be also incorrect if the selectivity of your ranges is bad and your table is huge.

If your ranges in the query are not filled, than both statements are equivalent and read the same amount from disk. If the table has more columns than three, then the first query may be a bit faster because it has not as many data to transfer between database and application.

If only the second range is filled and you have no suitable index on column F2, than the first query could run even longer than the second because some databases (Oracle and DB2, may be MS SQL Server too) could create execution plans which reads the table multiple times. It depends on your indexes and their statistics if it happens or not. In most cases, it will run not longer than the second statements, but in rare situation it could run considerably longer.

You see, SQL is complex and the execution depends heavily on the implementation in the database management system itself.

One additional comment. If your ranges contains only single values, it would be much better for the stability of your coding if you use FOR ALL ENTRIES. The database interface could create suitable junks of values if you use FOR ALL ENTRIES. If you use IN range, this could not be done and if the range gets to big (bigger than the maximum statement size), you'll get a dump. For simple queries with only two ranges this is not very likely, but if your example grows, it's getting more and more likely.

Best regards

Ralph Ganszky

[Former Member]

Please read my answer again, and give more information if you really want a useful answer. Without specifying the indexes all answers here are useless.

And don't give points to wrong answers as the last posting. The database works different.

Siegfried