Howto inprove performance after timeout exception

Topics: Developer Forum, User Forum
May 23, 2007 at 1:02 PM
Hi,

I'm having serious performance issues using Sharpmap + MssqlSpatial.
I enlarged my database from Bavaria to Germany and experienced an also linearly enlarged waiting time, which leads to SQL timeout exceptions in sharpmap. Now before I engage myself in database splitting and whatsoever I want to ask a couple of questions about issues that I don't understand yet and ask for some tips to increase performance.

I'm using latest SharpMap and MsSqlSpatial sources along with map data for Germany (boundaries, streets, rivers, parks, etc...).
I imported the data from shapefiles using the msscmd utility.
The Envelope columns (thegeomenvelope.... x/y min/max ) were indexed automatically.

I have a zoom level dependent layer display. Streets are only displayed if zoomed in to a small area... city quarter level I would say.

However, I don't understand, why the queries for a bounding box ( SharpMap's GetGeometriesInView(...) ) take that much longer.

1) Doesn't the index on the envelope columns care for a quick discovery of the geometries?
It seems like all 6M street table entries are examined one by one..

2) Since the Germanymap data is complete it's form, is there a way ( or need ) to sort the tables by a certain column?
I guess it could be better if queries could rely on the data being sorted by 'thegeomEnvelope_MinX' for example.

3) I imported the Germany data from 8 sets of shapefiles ( 8 road files, 8 boundary files, ...) into 8 sets of tables and later merged them with INSERTs.
Has that procedure messed up the data?

4) I'm thinking about splitting up the data to the 8 parts the shapefiles were partitioned in and somehow only activating the parts that are interesting for a certain view in the map. Is that a good idea?

5) What else could I try?


Thanks,

Jo
May 23, 2007 at 7:40 PM
You are not trying to render all the data of germany in one view are you? For instance showing all the streets of germany in one small bitmap wouldn't give you any good map anyway, and of course this would create a large response from the database which would take some time. This is where scale-dependent layers come into play.
May 24, 2007 at 6:26 AM
No, as I wrote, the streets only show on "city quarter" zoom level. So actually not that many roads are being loaded at once. Despite that, a request with a result set of 100 objects takes 30 seconds or more..
Coordinator
May 29, 2007 at 9:25 PM
Please, after the timeout, could you run the following query and paste here the output?:
SELECT
 qt.text
,SUBSTRING(qt.text,qs.statement_start_offset/2, 
	(case when qs.statement_end_offset = -1 
	then len(convert(nvarchar(max), qt.text)) * 2 
	else qs.statement_end_offset end -qs.statement_start_offset)/2) 
	as statement
,qs.creation_time
,qs.last_execution_time
,qs.execution_count
,qs.last_worker_time
,qs.last_elapsed_time
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY last_execution_time DESC

Best regards,
Ricardo Stuven.
May 30, 2007 at 7:09 AM
Hi,

executing the query manually with sql management studio express takes 31 seconds..
The enveloping statement is executed twice because in this test I had streets and street labels active.
When turning the labels of, it is faster an mostly within the 30second limit ( about 20 then), but still unreasonable slow compared to the old map data base, which was a lot smaller ( 2-5 seconds all layers included )

Thanks for your effort,

Jo



 
SELECT
 qt.text
,SUBSTRING(qt.text,qs.statement_start_offset/2, 
,qs.creation_time
,qs.last_execution_time
,qs.execution_count
,qs.last_worker_time
,qs.last_elapsed_time
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY last_execution_time DESC
;SELECT
 qt.text
,SUBSTRING(qt.text,qs.statement_start_offset/2, 
,qs.creation_time
,qs.last_execution_time
,qs.execution_count
,qs.last_worker_time
,qs.last_elapsed_time
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY last_execution_time DESC
;2007-05-30 08:46:13.950;2007-05-30 08:54:34.990;1;235738;242331
(@p0 nvarchar(3),@p1 nvarchar(10))SELECT [COLUMN_NAME]
FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE]
WHERE [TABLE_SCHEMA]=@p0
AND [TABLE_NAME]=@p1;)SELECT [COLUMN_NAME]
FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE]
WHERE [TABLE_SCHEMA]=@p0
AND [TABLE_NAME]=@p;2007-05-30 08:43:47.660;2007-05-30 08:53:16.487;5;514;514
(@p0 nvarchar(3),@p1 nvarchar(7))SELECT [COLUMN_NAME]
FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE]
WHERE [TABLE_SCHEMA]=@p0
AND [TABLE_NAME]=@p1;)SELECT [COLUMN_NAME]
FROM [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE]
WHERE [TABLE_SCHEMA]=@p0
AND [TABLE_NAME]=@p;2007-05-30 08:53:15.127;2007-05-30 08:53:16.367;1;2872;6160
(@p0 float(53),@p1 float(53),@p2 float(53),@p3 float(53))SELECT [oid], [the_geom]
FROM [dbo].[AdminBndy1] WITH (INDEX(IDX_the_geom_Envelope))
WHERE 
(   [the_geom_Envelope_MinX] < @p0
AND [the_geom_Envelope_MaxX] > @p1
AND [the_geom_Envelope_MinY] < @p2
AND [the_geom_Envelope_MaxY] > @p3
);)SELECT [oid], [the_geom]
FROM [dbo].[AdminBndy1] WITH (INDEX(IDX_the_geom_Envelope))
WHERE 
(   [the_geom_Envelope_MinX] < @p0
AND [the_geom_Envelope_MaxX] > @p1
AND [the_geom_Envelope_MinY] < @p2
AND [the_geom_Envelope_MaxY] > @p3
;2007-05-30 08:43:49.743;2007-05-30 08:53:14.903;1;3125;32265
(@p0 nvarchar(3),@p1 nvarchar(7),@p2 nvarchar(8))SELECT [SRID]
FROM [ST].[GEOMETRY_COLUMNS]
WHERE [F_TABLE_SCHEMA] = @p0
AND [F_TABLE_NAME] = @p1
AND [F_GEOMETRY_COLUMN] = @p2
;)SELECT [SRID]
FROM [ST].[GEOMETRY_COLUMNS]
WHERE [F_TABLE_SCHEMA] = @p0
AND [F_TABLE_NAME] = @p1
AND [F_GEOMETRY_COLUMN] = @p2
;2007-05-30 08:51:30.723;2007-05-30 08:51:32.870;2;76;76
  SELECT 
  Min([the_geom_Envelope_MinX])
, Max([the_geom_Envelope_MaxX])
, Min([the_geom_Envelope_MinY])
, Max([the_geom_Envelope_MaxY])
FROM [dbo].[Streets]
 
; SELECT 
  Min([the_geom_Envelope_MinX])
, Max([the_geom_Envelope_MaxX])
, Min([the_geom_Envelope_MinY])
, Max([the_geom_Envelope_MaxY])
FROM [dbo].[Streets]
 
;2007-05-30 08:51:30.983;2007-05-30 08:51:32.870;2;584;584
SELECT ST.AsBinary(ST.EnvelopeQueryWhere('Streets', 'the_geom', ''));SELECT ST.AsBinary(ST.EnvelopeQueryWhere('Streets', 'the_geom', '');2007-05-30 08:51:30.723;2007-05-30 08:51:32.870;2;1506;1507
  SELECT 
  Min([the_geom_Envelope_MinX])
, Max([the_geom_Envelope_MaxX])
, Min([the_geom_Envelope_MinY])
, Max([the_geom_Envelope_MaxY])
FROM [dbo].[Streets]
 
; SELECT 
  Min([the_geom_Envelope_MinX])
, Max([the_geom_Envelope_MaxX])
, Min([the_geom_Envelope_MinY])
, Max([the_geom_Envelope_MaxY])
FROM [dbo].[Streets]
 
;2007-05-30 08:50:55.633;2007-05-30 08:50:56.977;2;452;452
SELECT ST.AsBinary(ST.EnvelopeQueryWhere('Streets', 'the_geom', ''));SELECT ST.AsBinary(ST.EnvelopeQueryWhere('Streets', 'the_geom', '');2007-05-30 08:50:55.513;2007-05-30 08:50:56.977;2;938;938
(@p0 nvarchar(3),@p1 nvarchar(7),@p2 nvarchar(8))SELECT [SRID]
FROM [ST].[GEOMETRY_COLUMNS]
WHERE [F_TABLE_SCHEMA] = @p0
AND [F_TABLE_NAME] = @p1
AND [F_GEOMETRY_COLUMN] = @p2
;)SELECT [SRID]
FROM [ST].[GEOMETRY_COLUMNS]
WHERE [F_TABLE_SCHEMA] = @p0
AND [F_TABLE_NAME] = @p1
AND [F_GEOMETRY_COLUMN] = @p2
;2007-05-30 08:50:55.543;2007-05-30 08:50:56.977;2;68;68
(@p0 nvarchar(3),@p1 nvarchar(10),@p2 nvarchar(8))SELECT [SRID]
FROM [ST].[GEOMETRY_COLUMNS]
WHERE [F_TABLE_SCHEMA] = @p0
AND [F_TABLE_NAME] = @p1
AND [F_GEOMETRY_COLUMN] = @p2
;)SELECT [SRID]
FROM [ST].[GEOMETRY_COLUMNS]
WHERE [F_TABLE_SCHEMA] = @p0
AND [F_TABLE_NAME] = @p1
AND [F_GEOMETRY_COLUMN] = @p2
;2007-05-30 08:45:57.677;2007-05-30 08:45:57.677;1;8975;8975
SELECT ST.AsBinary(the_geom) FROM ST.FilterQuery#dbo#Adminbndy3#the_geom(ST.MakeEnvelope(-0.5,-0.295722713864307,0.5,0.295722713864307,4326));SELECT ST.AsBinary(the_geom) FROM ST.FilterQuery#dbo#Adminbndy3#the_geom(ST.MakeEnvelope(-0.5,-0.295722713864307,0.5,0.295722713864307,4326);2007-05-30 08:43:50.883;2007-05-30 08:45:56.947;1;3697;7761
(@1 varchar(8000))SELECT [SRID] FROM [ST].[GEOMETRY_COLUMNS] WHERE [F_TABLE_NAME]=@1;)SELECT [SRID] FROM [ST].[GEOMETRY_COLUMNS] WHERE [F_TABLE_NAME]=@;2007-05-30 08:45:44.557;2007-05-30 08:45:44.557;1;402;6145
(@p0 float(53),@p1 float(53),@p2 float(53),@p3 float(53))SELECT [oid], [the_geom]
FROM [dbo].[AdminBndy4] WITH (INDEX(IDX_the_geom_Envelope))
WHERE 
(   [the_geom_Envelope_MinX] < @p0
AND [the_geom_Envelope_MaxX] > @p1
AND [the_geom_Envelope_MinY] < @p2
AND [the_geom_Envelope_MaxY] > @p3
);)SELECT [oid], [the_geom]
FROM [dbo].[AdminBndy4] WITH (INDEX(IDX_the_geom_Envelope))
WHERE 
(   [the_geom_Envelope_MinX] < @p0
AND [the_geom_Envelope_MaxX] > @p1
AND [the_geom_Envelope_MinY] < @p2
AND [the_geom_Envelope_MaxY] > @p3
;2007-05-30 08:43:51.263;2007-05-30 08:43:51.403;2;54;54
SELECT *, ST.AsBinary(the_geom) AS sharpmap_tempgeometry FROM ST.FilterQuery#dbo#Adminbndy4#the_geom(ST.MakeEnvelope(-0.5,-0.295722713864307,0.5,0.295722713864307,4326));SELECT *, ST.AsBinary(the_geom) AS sharpmap_tempgeometry FROM ST.FilterQuery#dbo#Adminbndy4#the_geom(ST.MakeEnvelope(-0.5,-0.295722713864307,0.5,0.295722713864307,4326);2007-05-30 08:43:51.403;2007-05-30 08:43:51.403;1;1580;1580
SELECT ST.AsBinary(the_geom) FROM ST.FilterQuery#dbo#Adminbndy4#the_geom(ST.MakeEnvelope(-0.5,-0.295722713864307,0.5,0.295722713864307,4326));SELECT ST.AsBinary(the_geom) FROM ST.FilterQuery#dbo#Adminbndy4#the_geom(ST.MakeEnvelope(-0.5,-0.295722713864307,0.5,0.295722713864307,4326);2007-05-30 08:43:51.203;2007-05-30 08:43:51.253;1;5598;9828
(@p0 float(53),@p1 float(53),@p2 float(53),@p3 float(53))SELECT [oid], [the_geom]
FROM [dbo].[Adminbndy3] WITH (INDEX(IDX_the_geom_Envelope))
WHERE 
(   [the_geom_Envelope_MinX] < @p0
AND [the_geom_Envelope_MaxX] > @p1
AND [the_geom_Envelope_MinY] < @p2
AND [the_geom_Envelope_MaxY] > @p3
);)SELECT [oid], [the_geom]
FROM [dbo].[Adminbndy3] WITH (INDEX(IDX_the_geom_Envelope))
WHERE 
(   [the_geom_Envelope_MinX] < @p0
AND [the_geom_Envelope_MaxX] > @p1
AND [the_geom_Envelope_MinY] < @p2
AND [the_geom_Envelope_MaxY] > @p3
;2007-05-30 08:43:51.063;2007-05-30 08:43:51.073;1;189;2602
(@p0 float(53),@p1 float(53),@p2 float(53),@p3 float(53))SELECT [oid], [the_geom]
FROM [dbo].[Adminbndy2] WITH (INDEX(IDX_the_geom_Envelope))
WHERE 
(   [the_geom_Envelope_MinX] < @p0
AND [the_geom_Envelope_MaxX] > @p1
AND [the_geom_Envelope_MinY] < @p2
AND [the_geom_Envelope_MaxY] > @p3
);)SELECT [oid], [the_geom]
FROM [dbo].[Adminbndy2] WITH (INDEX(IDX_the_geom_Envelope))
WHERE 
(   [the_geom_Envelope_MinX] < @p0
AND [the_geom_Envelope_MaxX] > @p1
AND [the_geom_Envelope_MinY] < @p2
AND [the_geom_Envelope_MaxY] > @p3
;2007-05-30 08:43:50.453;2007-05-30 08:43:50.453;1;201;26338
SELECT ST.AsBinary(the_geom) FROM ST.FilterQuery#dbo#Adminbndy2#the_geom(ST.MakeEnvelope(-0.5,-0.295722713864307,0.5,0.295722713864307,4326));SELECT ST.AsBinary(the_geom) FROM ST.FilterQuery#dbo#Adminbndy2#the_geom(ST.MakeEnvelope(-0.5,-0.295722713864307,0.5,0.295722713864307,4326);2007-05-30 08:43:50.323;2007-05-30 08:43:50.433;1;4547;32784
SELECT ST.AsBinary(the_geom) FROM ST.FilterQuery#dbo#Adminbndy1#the_geom(ST.MakeEnvelope(-0.5,-0.295722713864307,0.5,0.295722713864307,-1));SELECT ST.AsBinary(the_geom) FROM ST.FilterQuery#dbo#Adminbndy1#the_geom(ST.MakeEnvelope(-0.5,-0.295722713864307,0.5,0.295722713864307,-1);2007-05-30 08:43:41.430;2007-05-30 08:43:42.310;1;4508829;5140524
(@1 varchar(8000))SELECT [SRID] FROM [ST].[GEOMETRY_COLUMNS] WHERE [F_TABLE_NAME]=@1;)SELECT [SRID] FROM [ST].[GEOMETRY_COLUMNS] WHERE [F_TABLE_NAME]=@;2007-05-30 08:43:22.483;2007-05-30 08:43:23.023;1;122042;128066