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));