Performace issue "SELECT *" vs "SELECT col1, col2, col3....." (ORACLE)

The official tester's forum for ZeosLib 7.1. Ask for help, post proposals or solutions.
Post Reply
wiwiechris
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 08.05.2014, 14:58

Performace issue "SELECT *" vs "SELECT col1, col2, col3....." (ORACLE)

Post by wiwiechris »

Hallo @All
I'am facing a strange performance problem (ORACLE) with ZEOS 7.1.4

My test-setup:
* Have an ORACLE-table "MyTable" with 154 columns including over ten varchar2(200) columns (2000000 records)
* "MyTable_INDEX" is a unique index on a NUMBER-column
* Use TZQuery in ReadOnly mode
* Test1: Make a "select" with "*" from table "MyTable" for one record
* Test2: Make a "select" naming all columns from table "MyTable" for one record
* Repeated the test several times to avoid ORACLE block caching effects.

Code: Select all

    for I := 0 to 10000 do
    begin    
        Query.Sql.Clear;
        
        // Use the "*" option for selection
        Query.Sql.Add('SELECT * FROM MyTable WHERE MyTable_INDEX=:IDX'); 

        // List all the column names in the select
        Query.Sql.Add('SELECT ColName1, ColName2,  ...  ColName153, ColName154 FROM MyTable WHERE MyTable_INDEX=:IDX'); 

        Query.params[0].AsInteger := random(1000000);
        Query.Open;
    end;
Observation:
1. Throughput in Test2 with naming all column names is >> 3 << times faster than Test1 (ZEOS 7.1.4)
2. For Test2 there is no obvious speed difference between ZEOS 7.1.3 and ZEOS 7.1.4
3. In ZEOS 7.1.3 Test1 is about 15 Percent faster than Test2

Is there any explanation especially for (1)?

Best regards
Chris

ZEOS Version 7.1.4-Stable
ORACLE 11.2.0.1
Delphi 2010

Christoph Wiwie
http://www.wiwie.de
Certified Tester/ Certified Requirements Engineer
miab3
Zeos Test Team
Zeos Test Team
Posts: 1310
Joined: 11.05.2012, 12:32
Location: Poland

Re: Performace issue "SELECT *" vs "SELECT col1, col2, col3....." (ORACLE)

Post by miab3 »

@wiwiechris,

Could you see if the same thing happens for ZEOS 7.2 svn Revision 3619(3621):
http://svn.code.sf.net/p/zeoslib/code-0 ... sting-7.2/
?

Michal
wiwiechris
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 08.05.2014, 14:58

Re: Performace issue "SELECT *" vs "SELECT col1, col2, col3....." (ORACLE)

Post by wiwiechris »

Hi Michal,

seems to be OK. ;-)
Thanks for your advice.

Best regards
Chris

ZEOS Version 7.1.4-Stable
ORACLE 11.2.0.1
Delphi 2010

Christoph Wiwie
http://www.wiwie.de
Certified Tester/ Certified Requirements Engineer
Post Reply