July 24, 2016 at 6:17 am
hi all,
i have to following query :
DECLARE @latitude float=31.24326
DECLARE @longitude float=34.79418
DECLARE @g geometry= geometry::Point(@longitude, @latitude, 4326);
DECLARE @h geometry= geometry::Point(@longitude, @latitude, 0);
select top 8 id
from [dbo].[TBL_STREET_ROAD] with (nolock,FORCESEEK) --with (nolock,index([TBL_STREET_ROAD#xxx_geom]))
where geom.STDistance(@g)<0.004
order by geom.STDistance(@g) asc
which take long time from time to time ( it can become above 6 seconds.... ) nor always.
this is exec plan : is attached in files.
table data : ( small read only 🙂
name rows reserved data index_size unused
TBL_STREET_ROAD535771 415280 KB 148032 KB 266912 KB 336 KB
1 ) this table is on ssd drive.
2) no locking issues present on this table (read only).
3) its seems index is best for this query. (SPATIAL INDEX)
4) there is no pressure on this drive. ( mean no io bottleneck)
5) CPU is low.
5) table is vary small.
6) execute plan is attached in 2 files.
the big problem i cant analyze STDistance .
the problem : i cant explain my manager why from time to time its take long time
which cause other process in system to be in delay.
i ready to try test and analyze every solution in QA , any idea will be grateful :))
thank you vary much
Sharon.
July 24, 2016 at 11:05 am
Post the actual execution plans please, images of execution plans do not help in any way
😎
Further, something is not quite right if you are using lat/long with geometry, can you post the DDL for the tables in question including all indices?
July 24, 2016 at 11:56 am
hi ,
ddl:
GO
/****** Object: Table [dbo].[TBL_STREET_ROAD] Script Date: 7/24/2016 5:23:24 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TBL_STREET_ROAD](
[ID] [int] NOT NULL,
[source_column_ID] [bigint] NOT NULL,
[CITY2] [nvarchar](255) NULL,
[geom] [geometry] NULL,
[ROAD2] [nvarchar](255) NULL,
[From_R] [int] NULL,
[To_R] [int] NULL,
[From_L] [int] NULL,
[To_L] [int] NULL,
[fr_spd_lim] [numeric](5, 0) NULL,
[to_spd_lim] [numeric](5, 0) NULL,
[MAX_SPEED] [numeric](5, 0) NULL,
[l_postcode] [varchar](11) NULL,
[r_postcode] [varchar](11) NULL,
[min_longitude0004] [float] NULL,
[max_longitude0004] [float] NULL,
[min_latitude0004] [float] NULL,
[max_latitude0004] [float] NULL,
[isupdated] [smallint] NULL,
[REGION2] [nvarchar](255) NULL,
[STREET2] [nvarchar](255) NULL,
[SOURCE_ID] [smallint] NULL,
[CITY1] [nvarchar](255) NULL,
[REGION1] [nvarchar](255) NULL,
[STREET1] [nvarchar](255) NULL,
[ROAD1] [nvarchar](255) NULL,
CONSTRAINT [PK_STREET_ROAD1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG_STREET_ROAD]
) ON [FG_STREET_ROAD] TEXTIMAGE_ON [FG_STREET_ROAD]
GO
SET ANSI_PADDING OFF
GO
XML PLAN as attachment
July 24, 2016 at 12:36 pm
Will have a look at this when I can, cannot see the any DDL for any indices though.
😎
Still puzzled why on earth (pun intended) you would use two dimensional geometry instead of geography, simply does not make sense.
July 24, 2016 at 12:39 pm
thank you vary much 🙂
July 24, 2016 at 12:44 pm
hi ,
just to be clear 🙂
i didn't write this code ,
i need to improve this code, i put all my
knowledge as dba to solve it but still i cant 🙂
( i didnt work with clr before and not with geographic data type .:)
sharon
July 24, 2016 at 1:02 pm
At the first glance the statistics look way off, have those been updated recently? Further the index seek on [TRAFFILOG_GEO].[sys].[extended_index_2066106401_384000].[TBL_STREET_ROAD_NEW#ISRAEL_geom].(Spatial) is quite hefty, are you using FORCESEEK? A range scan would probably be more efficient here.
😎
Last but not least, as I mentioned before the geometry data type is not the correct spatial data type for this, geography is.
July 25, 2016 at 8:25 am
Here is the format for nearest neighbor code in MSDN.
https://msdn.microsoft.com/en-us/library/ff929109.aspx
I agree with Eirikur, get rid of your hints.
On a small scale, say within a city, you can substitute geometry for geography and pretend the earth is flat. Full blown geographic distance calculations are VERY cpu-intensive compared to Pythagoras' formula. BUT, to get your X-axis value correct, you need to add a conversion table that tells you how far the distance is between longitude points at various latitudes. Remember that latitudes are parallel, but longitude lines converge as you approach the poles. ( "Spatial Ed" Katibah blessed this approach. )
Geometric distance calculations will yield slightly different results from the geographic calculations, but unless you are targeting missiles or space lasers the precision is usually good enough. The closer together the points are, the less difference there will be in the results. At 1000 meters you may show one point as being a half-step closer, but at 1000 meters such a trivial difference may be insignificant.
Philosophically, whenever you choose a set of objects by distance, there will always be properties omitted because they are a hair outside your range. Increase your range to bring them into your circle and there may be an even larger set just outside the new distance. Any distance specified is an arbitrary number, whether it is 1000 meters, 1000 yards, or one mile and one inch.
Unless you are targeting things with space lasers.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply