OraDirect && SDO_GEOMETRY == poor performance?

OraDirect && SDO_GEOMETRY == poor performance?

Postby mpaul42 » Thu 17 Aug 2006 11:25

OraDirect && SDO_GEOMETRY == slow??

Indeed I hope the answer to this question is NO. Could you guys please test out the below sample code and tell me if I am doing sth wrong or if OraDirect simply is ugly slow with these kinds of queries?

My Doubts:

- why is it faster to select all columns (*) than only one column?
- why are you adding additional fields to the ADO.NET datatable, fields which are not included in my select (fields are SDO_GTYPE, SDO_SRID, ..)? Is there any way to deactivate this behavior?
- to may understanding, selecting a single polygon should be as fast as selecting from dual table. The last test demonstrates this, when selecting the X,Y coordinate of the point. This query returns the decimal data type instead of an Oracle Object and is as fast as the first query (select 'a' from dual).

Of course, the current example is extremely simplified. My real problem is that performing a select of data stored in Oracle Spatial currently needs 4s for a table containing 5.000 rows, whereas the same select which stores the geometry inside of a blob field in WKB format only needs 0.3s (so fetching SDO_GEOMETRY is 13 times slower!).

---------------------------------------

setup.sql:
CREATE TABLE cola_markets (
mkt_id NUMBER PRIMARY KEY,
name VARCHAR2(32),
shape SDO_GEOMETRY);
INSERT INTO cola_markets VALUES(
1,
'cola_a',
SDO_GEOMETRY(
2003, -- two-dimensional polygon
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior)
SDO_ORDINATE_ARRAY(1,1, 5,7) -- only 2 points needed to
-- define rectangle (lower left and upper right) with
-- Cartesian-coordinate data
)
);
INSERT INTO USER_SDO_GEOM_METADATA
VALUES (
'cola_markets',
'shape',
SDO_DIM_ARRAY( -- 20X20 grid
SDO_DIM_ELEMENT('X', 0, 20, 0.005),
SDO_DIM_ELEMENT('Y', 0, 20, 0.005)
),
NULL -- SRID
);
CREATE INDEX cola_spatial_idx
ON cola_markets(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;

test a select 'a' from dual (time: first 25 iterations 2.7s, subsequent 0.08s):
DateTime now = DateTime.Now;
DataTable dt = new DataTable();
for (int i = 0; i < 25; i++)
{
OracleDataAdapter oda = new OracleDataAdapter("select 'performance test' from dual", "Data Source=oraserver;Password=tiger;User ID=scott");
oda.Fill(dt);
}
Console.WriteLine("time needed:" + DateTime.Now.Subtract(now));

test b select shape from cola_markets (time: first 25 iterations 2.7s, subsequent 0.5s):

DateTime now = DateTime.Now;
DataTable dt = new DataTable();
for (int i = 0; i < 25; i++)
{
OracleDataAdapter oda = new OracleDataAdapter("select shape from cola_markets", "Data Source=oraserver;Password=tiger;User ID=scott");
oda.Fill(dt);
}
Console.WriteLine("time needed:" + DateTime.Now.Subtract(now));

test c select * from cola_markets (time: first 25 iterations 2.7s, subsequent 0.22s):

DateTime now = DateTime.Now;
DataTable dt = new DataTable();
for (int i = 0; i < 25; i++)
{
OracleDataAdapter oda = new OracleDataAdapter("select * from cola_markets", "Data Source=oraserver;Password=tiger;User ID=scott");
oda.Fill(dt);
}
Console.WriteLine("time needed:" + DateTime.Now.Subtract(now));

test d select a.shape.sdo_point.x from cola_markets a (suqsequent: 0.08s):

DateTime now = DateTime.Now;
DataTable dt = new DataTable();
for (int i = 0; i < 25; i++)
{
OracleDataAdapter oda = new OracleDataAdapter("select a.shape.sdo_point.x as x, a.shape.sdo_point.y as y from cola_markets a", "Data Source=oraserver;Password=tiger;User ID=scott");
oda.Fill(dt);
}
Console.WriteLine("time needed:" + DateTime.Now.Subtract(now));
mpaul42
 
Posts: 3
Joined: Wed 01 Mar 2006 17:32

Postby Alexey » Fri 18 Aug 2006 09:59

We are investigating this behaviour. You will be notified on results as soon as possible.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby mpaul42 » Tue 12 Sep 2006 12:03

OK, if you need any further details and/or assistance please let me know.
mpaul42
 
Posts: 3
Joined: Wed 01 Mar 2006 17:32

Postby Alexey » Tue 19 Sep 2006 06:22

The deceleration is seemed to be caused by the Fill() method. Try to use data reader instead.
Anyway we are going to increase performance in the next major version.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for Oracle