Hi, I'm trying to retreive data from ArcSDE using an 'attribute only' query. Doing so it takes a huge amount of time to get an answer back with large tables (+ 4M entities) using the ArcSDE reader. But the same query is ok if I do it using Geodatabase (ArcSDE) reader. On the other hand if I modified the query (with ArcSDE reader) to use a spatial envelope and an attribute value it works fine. But again, I have to pay attention to "search method order" parameter. If I set it to "optimize" or "spatial_first" it's fine but if I set it to "attribute_first" it's slow again. The problem with ArcSDE reader comes from a bad select statement. I trapped it and we can see, thru the explan plan, that it's doing a full table scan of the table. So when the table is large it becomes very slow. It's not a problem of not having indexes because it's working well with Geodatabase reader. The problem seems to come from either ESRI C API or from FME. My work around for now is to use Geodatabase reader but in the end I would like to use ArcSDE instead because it's faster (when queries are done right) and it doesn't consume an ArcGIS licence. I know that I could use ArcSDE with a spatial envelope but my process is more seamless with attribute only and I assume that ArcSDE reader should work as well as the Geodatabase reader.
I sent this problem three time to FME but I never received a ticket from
support. Maybe my e-mail has problem receiving mail from Safe (if it's the
case excuse me for harassing you with this!). Maybe I'm not the only one
having this problem, maybe I forgot setting some parameters, etc.
Attached with this e-mail are: the SQL query, a screenshot of Oracle
Performance Manager showing the explain plan and the full table scan (marked
in blue) causing the wait and a very simple workbench showing what I did to
get this SQL.
Thanks!
Jean-Marc Pr�vost
I'm using:
- ArcGIS 9, ArcSDE 9, Oracle 9.2.0.6
- Using Direct connect
- OS: Windows 2000 sp3
- Fme build: 1650
SELECT objectid, gid, prim_fc, snrc, edition, version, theme, atc, atg, atz,
ate, precision, type, generation, elevation, shape,
v__267.shape se_row_id, shape.fid, shape.numofpts, shape.entity,
shape.points, shape.rowid
FROM (SELECT b.objectid, b.gid, b.prim_fc, b.snrc, b.edition, b.version,
b.theme, b.atc, b.atg, b.atz, b.ate, b.precision, b.type,
b.generation, b.elevation, b.shape
FROM courbe_de_niveau_1 b
WHERE NOT b.objectid IN (SELECT /*+ HASH_AJ INDEX (d267
d267_pk)
*/ sde_deletes_row_id
FROM bndt.d267
WHERE deleted_at IN (SELECT /*+ USE_HASH (l) */
l.lineage_id
FROM sde.state_lineages l
WHERE l.lineage_name = :lineage_name
AND l.lineage_id <= :state_id1))
UNION ALL
SELECT a.objectid, a.gid, a.prim_fc, a.snrc, a.edition, a.version,
a.theme, a.atc, a.atg, a.atz, a.ate, a.precision, a.type,
a.generation, a.elevation, a.shape
FROM bndt.a267 a, sde.state_lineages sl
WHERE NOT (a.objectid, a.sde_state_id) IN (SELECT /*+ HASH_AJ
INDEX
(d267
d267_pk)
*/
sde_deletes_row_id,
sde_state_id
FROM bndt.d267
WHERE deleted_at IN (SELECT /*+ USE_HASH (l) */
l.lineage_id
FROM sde.state_lineages l
WHERE l.lineage_name = :lineage_name
AND l.lineage_id <= :state_id1))
AND a.sde_state_id = sl.lineage_id
AND sl.lineage_name = :lineage_name
AND sl.lineage_id <= :state_id1) v__267, bndt.f232 shape
WHERE snrc = '031H01'
AND shape.fid (+) = v__267.shape
<<slow-query.gif>> <<ArcSDESelect.fmw>>
Get the maximum benefit from your FME, FME Objects, or SpatialDirect via our
Professional Services team. Visit www.safe.com/services for details.
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/fme/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
<<attachment: slow-query.gif>>
ArcSDESelect.fmw
Description: Binary data
