Performace issue "SELECT *" vs "SELECT col1, col2, col3....." (ORACLE)
Posted: 05.03.2015, 11:11
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.
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
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;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
