Select to view content in your preferred language

SDE database slow.

1315
7
05-22-2013 06:52 AM
MarkPaulson
Occasional Contributor
First, let me say I'm noob to SQL Server, but I have installed SQL Express for workgroups on my local machine with no other users other than myself. I have a table that has 197368 records including lat lon fields. I add that table to create  and xy event layer. If I export the event layer back to the SQL database it take a couple of minutes. If I export to a file geodatabase it is almost instantaneous. To carry this further, for a web app I have to use the add XY coordinates tool. When I run this using the SQL feature class it takes about 44 minutes. If I run the tool using the feature class from the file geodatabase it takes 44 seconds. Is this normal or do I have a configuration problem?
0 Kudos
7 Replies
VinceAngelo
Esri Esteemed Contributor
It's hard to tell what the issue might be without further clarification --
What version of SQL-Server are you using?  Express, Standard, or Enterprise?
What version of ArcGIS are you using?
Did you load the point data into an ArcSDE layer?  What storage format
Do you have a spatial index built?

- V
0 Kudos
MarkPaulson
Occasional Contributor
It's hard to tell what the issue might be without further clarification --
What version of SQL-Server are you using?  Express, Standard, or Enterprise?
What version of ArcGIS are you using?
Did you load the point data into an ArcSDE layer?  What storage format
Do you have a spatial index built?

- V


I'm Running Express 2008 R2 and Desktop Advanced 10.1 sp1.
I don't know what you mean by and ArcSDE Layer. Like I said I'm a noob. I drug a table created by querying another SQL database  on another machine which created the local table on my Machine.

--TRUNCATE TABLE Temp_Prod_Data;

DROP TABLE Temp_Prod_Data;
CREATE TABLE [dbo].[Temp_Prod_Data](
 [client] [varchar](50) NULL,
 [ownerno] [varchar](25) NULL,
 [propertyno] [varchar](25) NULL,
 [lease] [varchar](50) NULL,
 [API] [varchar](25) NULL,
 [LPD_ID] [varchar](25) NULL,
 [well_name] [varchar](50) NULL,
 [WELL_NUM] [varchar](15) NULL,
 [COUNTY] [varchar](50) NULL,
 [STATE] [varchar](2) NULL,
 [OPERATOR] [varchar](50) NULL,
 [YYYYMM] [varchar](10) NULL,
 [ProdDateT] [datetime] NULL,
 [ProdDate] [varchar](25) NULL,
 [GAS] [float] NULL,
 [OIL] [float] NULL,
 [Latitude] [float] NULL,
 [Longitude] [float] NULL,
 [DEPTH] [int] NULL
) ON [PRIMARY]

GO
INSERT INTO Temp_Prod_Data (client, ownerno, propertyno, lease, API, LPD_ID, well_name, WELL_NUM, COUNTY, STATE, OPERATOR, YYYYMM, ProdDate, GAS, OIL, Latitude, Longitude, DEPTH) Exec [trinitylt\SQL2008].[LandTracker].dbo.sp_Prod_Data_5_year_Export;

SELECT * FROM Temp_Prod_Data;


The above creates the table that I use to create the XY event layer which is then exported back into my database as a feature class. Which is what I use to add the run the XY coordinate tool. Hope this makes sense.

I have no idea about the Spatial index.

Thanks
0 Kudos
VinceAngelo
Esri Esteemed Contributor
An event layer has nothing at all to do with ArcSDE.  It's just a table with
X and Y columns that are calculated into a point on the fly.  They will have
awful draw performance, since the full table must be scanned for every
draw request (though if you had an index on the X and Y columns, there's
a chance it might work faster).

An ArcSDE layer is the table behind an ArcGIS feature class with a geometry
(shape) column.  Geometry columns come in many flavors (storage types),
but they all share the capability of using a spatial index, which allows for
efficient spatial searching.

It sounds like you need to export this event layer into a feature class.

- V
0 Kudos
MarkPaulson
Occasional Contributor


It sounds like you need to export this event layer into a feature class.

- V


That is exactly what I am doing. I then process that feature class with the add XY tool. This part takes about 44 minutes. If I export it to a file geodatabase and process that feature class the same way, it only takes about 40 seconds. While I understand it shoul take a little longer, I think taking 60 plus times longer means something is wrong...

Thanks for the responses.
0 Kudos
VinceAngelo
Esri Esteemed Contributor
Yes, something is wrong.  File geodatabase should be a bit faster, but not by
two orders of magnitude.  Full table queries should always be bound by I/O
access to the table blocks.

- V
0 Kudos
MarkPaulson
Occasional Contributor
Yes, something is wrong.  File geodatabase should be a bit faster, but not by
two orders of magnitude.  Full table queries should always be bound by I/O
access to the table blocks.

- V


Just heard back from ESRI support and they are experiencing the same issue with the data I sent them. I will post findings.
0 Kudos
User35489
Frequent Contributor
Just heard back from ESRI support and they are experiencing the same issue with the data I sent them. I will post findings.


Hi,

Is there any update for the above issue.... ?

Thanks for sharing
-AS
0 Kudos