topic Re: SDE database slow. in Data Management Questions

SDE database slow.

MarkPaulson — Wed, 22 May 2013 13:52:06 GMT

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?

Re: SDE database slow.

VinceAngelo — Wed, 22 May 2013 22:23:16 GMT

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

Re: SDE database slow.

MarkPaulson — Sun, 12 Dec 2021 07:26:15 GMT

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

Re: SDE database slow.

VinceAngelo — Wed, 22 May 2013 23:43:38 GMT

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

Re: SDE database slow.

MarkPaulson — Thu, 23 May 2013 00:59:04 GMT

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.

Re: SDE database slow.

VinceAngelo — Thu, 23 May 2013 02:20:40 GMT

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

Re: SDE database slow.

MarkPaulson — Thu, 23 May 2013 02:34:55 GMT

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.

Re: SDE database slow.

User35489 — Thu, 30 May 2013 20:59:45 GMT

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