July 7, 2010 at 9:55 am
Hi,
I have a scalar function that works ok, it's just that its slow. I have indexed the geom column but it can take upto 40 mins to run on around 10000 rows. Does anybody know how to speed it up.
CREATE FUNCTION [dbo].[GET_USGS_PROVINCE]
(@latitude float, @longitude float)
RETURNS nvarchar(255)
AS
BEGIN
-- Declare the return variable here
DECLARE @intersect nvarchar(255)
declare @fet geometry
-- Add the T-SQL statements to compute the return value here
set @fet =([geometry]::STPointFromText(((('POINT('+CONVERT([varchar](20),@Longitude,(0)))+' ')+CONVERT([varchar](20),@Latitude,(0)))+')',(4326)))
select top 1 @intersect = CODE
from dbo.SPATIAL_USGS_PROVINCE where GEOM.STIntersects(@fet)=1
-- Return the result of the function
RETURN @intersect
END
GO
Many Thanks,
Oliver
July 7, 2010 at 12:05 pm
oliver.morris (7/7/2010)
Hi,I have a scalar function that works ok, it's just that its slow. I have indexed the geom column but it can take upto 40 mins to run on around 10000 rows. Does anybody know how to speed it up.
CREATE FUNCTION [dbo].[GET_USGS_PROVINCE](@latitude float, @longitude float)
RETURNS nvarchar(255)
AS
BEGIN
-- Declare the return variable here
DECLARE @intersect nvarchar(255)
declare @fet geometry
-- Add the T-SQL statements to compute the return value here
set @fet =([geometry]::STPointFromText(((('POINT('+CONVERT([varchar](20),@Longitude,(0)))+' ')+CONVERT([varchar](20),@Latitude,(0)))+')',(4326)))
select top 1 @intersect = CODE
from dbo.SPATIAL_USGS_PROVINCE where GEOM.STIntersects(@fet)=1
-- Return the result of the function
RETURN @intersect
END
GO
Many Thanks,
Oliver
You have a TOP without an ORDER BY. Ignoring spatial data, that's problematic.
First question is going to sound weird, but what service pack are you on? There was a pretty major update to spatial data behavior in SP1.
Can you post the execution plan? It honestly sounds like the spatial index is not getting used.
You might try making a derived table something like this.
select top 1 @intersect = CODE
from (SELECT * FROM dbo.SPATIAL_USGS_PROVINCE where GEOM.STIntersects(@fet)=1)
I've found this type of approach worked well sometimes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 8, 2010 at 3:09 am
Hi,
Many thanks for the response.
I have uploaded two sql plans one before rebuilding the spatial index on after.
For 500 rows it took 1min 40secs
Currently running SP1 SQL Server Standard.
Thanks for your help,
Oliver
July 8, 2010 at 6:10 am
Right, neither version of the query are using the spatial index. All you've got are table scans. First thing I'd try is using the derived table like I showed before. The second thing you can try is to use an index hint to force the query to use the spatial index. That's not my preferred approach, but it can help. After that... it's hard to say. Try those & let me know how it worked.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 8, 2010 at 6:15 am
Hi,
Thanks for the reply I have put the code into the modified trigger as below
ALTER FUNCTION [dbo].[GET_USGS_PROVINCE]
(@latitude float, @longitude float)
RETURNS nvarchar(255)
AS
BEGIN
-- Declare the return variable here
DECLARE @intersect nvarchar(255)
declare @fet geometry
-- Add the T-SQL statements to compute the return value here
set @fet =([geometry]::STPointFromText(((('POINT('+CONVERT([varchar](20),@Longitude,(0)))+' ')+CONVERT([varchar](20),@Latitude,(0)))+')',(4326)))
select top 1 @intersect = CODE
from (SELECT * FROM dbo.SPATIAL_USGS_PROVINCE where GEOM.STIntersects(@fet)=1)
-- Return the result of the function
RETURN @intersect
END
When I execute this I get the error
Msg 156, Level 15, State 1, Procedure GET_USGS_PROVINCE, Line 19
Incorrect syntax near the keyword 'RETURN'.
Sorry I am sure I am being stupid, but I dont know why this wouldnt work.
Thank you for your help,
Oliver
July 8, 2010 at 6:25 am
One thing at a time. The query plans you posted were for a query without using the STIntersect command. I'm comparing apples to hammers with that. I'd need to see how the query works within an execution plan as written.
Second, break down the problem. Performance stinks on the query within the function. Fine. Get the function out of the way and let's focus on the query first, then build the function second. Break the query out of the function and let's just focus on it, see how the performance improves, degrades, whatever, without having to sweat secondary matters.
Third, are you calling this from another procedure? Is this a column within an outer query? If so, that could be the biggest part of your peformance problem. Scalar functions are notorious for causing performance hits. You might want to reexamine the outer query if I'm right about that. Usually taking the scalar function out of the way and moving it into the outer query as a join against a derived table or maybe a CTE or something will work better.
But again, let's not worry about that until we see that we've got good performance on the root query.
Fourth, add an alias to the derived table, 'AS X' or whatever, and it should compile just fine.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 8, 2010 at 10:00 am
Sorry for the incorrect execution plans, I was running a query which included the function.
The core part of my scalar function is
DECLARE @intersect nvarchar(255)
declare @fet geometry
declare @longitude float
declare @latitude float
set @longitude =1
set @latitude = 10
-- Add the T-SQL statements to compute the return value here
set @fet =([geometry]::STPointFromText(((('POINT('+CONVERT([varchar](20),@Longitude,(0)))+' ')+CONVERT([varchar](20),@Latitude,(0)))+')',(4326)))
select top 1 @intersect = code
from dbo.SPATIAL_USGS_PROVINCE where GEOM.STIntersects(@fet)=1
print @intersect
I have also attached the execution plan for this. I am calling this function from a query e.g. dbo.GET_USGS_PROVINCE([latitude],[longitude]) and using it to return the region for which that point lat and long are located in. So I have tried select and update versions of this to populate fields.
I am sorry to be a pain, but I dont understand how I can make this derived table work or have it as a CTE. I put in a CTE as below and the execution plan seems the same
DECLARE @intersect nvarchar(255)
declare @fet geometry
declare @longitude float
declare @latitude float
set @longitude =1
set @latitude = 10
-- Add the T-SQL statements to compute the return value here
set @fet =([geometry]::STPointFromText(((('POINT('+CONVERT([varchar](20),@Longitude,(0)))+' ')+CONVERT([varchar](20),@Latitude,(0)))+')',(4326)));
with CTE1
as
(select * from dbo.SPATIAL_USGS_PROVINCE where GEOM.STIntersects(@fet)=1)
select top 1 @intersect = code
from CTE1;
print @intersect
Thanks for your help again, does you book cover improving spatial queries?
Cheers,
Oliver
July 8, 2010 at 11:08 am
I wish I could say yes so you'd buy a copy, but no, it doesn't cover spatial queries. If you don't have a copy of Alastair Aitchison's excellent book, I'd pick one up. It doesn't get into tuning, but it's a great way to learn about how spatial data within SQL Server works.
Anyway, back to the issue at hand...
I was unclear regarding the CTE, sorry. What I meant was to put this query into a CTE within the larger context of the query that was calling the scalar function. I'm sorry about that. Ignore the CTE for now.
We're still seeing nothing but clustered index scans. First off, you're sure you've got a spatial index created on the table, right?
Let's modify this query. First, let's completely boil it down:
select GEOM
from dbo.SPATIAL_USGS_PROVINCE
where GEOM.STIntersects(@fet)=1
Does that get an index seek? Assuming it does, let's then try this:
select top 1 @intersect = x.code
from (SELECT Code
FROM dbo.SPATIAL_USGS_PROVINCE
where GEOM.STIntersects(@fet)=1) AS x
If it doesn't get an index seek, let's try this:
select GEOM
from dbo.SPATIAL_USGS_PROVINCE WITH (INDEX(TheSpatialIndexName))
where GEOM.STIntersects(@fet)=1
Does that get an index seek?
If not, I have to think that your index might not be configured well. Then, what we need is to run a check on the index using sp_help_spatial_geometry_index. You have to pass it the table, the index, output and a query sample. Use @fet for the sample. You're looking primarily for the primary_filter_efficiency. The higher the better on this one.
Let's see how that goes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 9, 2010 at 2:47 am
Hi,
Thank you for your continued support. I already have Alastair's superb book.
I have attached the 3 execution plans to for the 3 queries in your post. They all use the clustered index seek, I do get a warning with a couple saying columns with no statistics - but I can't write statistics on a spatial column so I dont know what I can do.
Many Thanks,
Oliver
July 9, 2010 at 5:56 am
oliver.morris (7/9/2010)
Hi,Thank you for your continued support. I already have Alastair's superb book.
I have attached the 3 execution plans to for the 3 queries in your post. They all use the clustered index seek, I do get a warning with a couple saying columns with no statistics - but I can't write statistics on a spatial column so I dont know what I can do.
Many Thanks,
Oliver
Well, we're getting somewhere. The first query works. It reads from the spatial index. The second query does not. It's getting a table scan. So, the problem seems to be the TOP part of the query.
That gives us something to focus on. You have a TOP w/o an order by. That's generally not considered a good thing because you can't be assured of what order the values will return. First, I'd suggest putting an order by on the column. Let's see what that does. Next, we might try something like this (I just wrote something like this for a system in-house):
SELECT @value = y.code
FROM (SELECT x.*, ROW_NUMBER() OVER (partition by x.id ORDER BY x.version desc) as rnk
FROM (SELECT * FROM table
WHERE geog.STIntersects(@test) AS X) AS Y
WHERE y.rnk = 1
I'm not sure what else to try. You'll just have to experiment. You might also try using a GROUP BY (not my first choice, I like to avoid aggregates). The good news is, your spatial index works. The bad news is, the way you're accessing it doesn't. So it's just a question of adjusting the query to get what you need the right way.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 9, 2010 at 6:51 am
Hey guys, I know I'm coming late to the party, but I saw "spatial" pop up and I've been working on that quite a bit lately. Oliver, it seems that the purpose of your function is to determine what areas intersect a certain lat/long point. Is that correct? If so, I have a solution that doesn't use spatial indexes, but which performs well.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 9, 2010 at 7:33 am
Hi,
Yes I would be very interested to see what function you have developed. I am trying to find out what polygon the lat long point lies in for thousands of records.
Thank you,
Oliver
July 9, 2010 at 7:42 am
Oliver would you post up your schema for your table that contains the polygons? We can talk it through using that as a starting point.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 9, 2010 at 7:50 am
See below.
Thanks for your help
CREATE TABLE [dbo].[SPATIAL_USGS_PROVINCE](
[OBJECT_ID] [int] IDENTITY(1,1) NOT NULL,
[numeric](25, 5) NULL,
[PERIMETER] [numeric](25, 5) NULL,
[WEP_PRVG_] [numeric](11, 0) NULL,
[WEP_PRVG_I] [numeric](11, 0) NULL,
[int] NULL,
[NAME] [varchar](50) NULL,
[TYPE] [varchar](2) NULL,
[REG] [smallint] NULL,
[STUDIED] [varchar](1) NULL,
[GOR] [numeric](14, 0) NULL,
[O_G] [varchar](13) NULL,
[CUM_OIL] [numeric](14, 0) NULL,
[REM_OIL] [numeric](14, 0) NULL,
[KWN_OIL] [numeric](14, 0) NULL,
[CUM_GAS] [numeric](14, 0) NULL,
[REM_GAS] [numeric](14, 0) NULL,
[KWN_GAS] [numeric](14, 0) NULL,
[CUM_NGL] [numeric](14, 0) NULL,
[REM_NGL] [numeric](14, 0) NULL,
[KWN_NGL] [numeric](14, 0) NULL,
[CUM_PET] [numeric](14, 0) NULL,
[REM_PET] [numeric](14, 0) NULL,
[KWN_PET] [numeric](14, 0) NULL,
[UNDS_OIL] [numeric](14, 0) NULL,
[UNDS_GAS] [numeric](14, 0) NULL,
[UNDS_NGL] [numeric](14, 0) NULL,
[UNDS_PET] [numeric](14, 0) NULL,
[ENDO_OIL] [numeric](14, 0) NULL,
[ENDO_GAS] [numeric](14, 0) NULL,
[ENDO_NGL] [numeric](14, 0) NULL,
[ENDO_PET] [numeric](14, 0) NULL,
[MATR_OIL] [numeric](10, 0) NULL,
[MATR_GAS] [numeric](10, 0) NULL,
[MATR_NGL] [numeric](10, 0) NULL,
[MATR_PET] [numeric](10, 0) NULL,
[FUTR_OIL] [numeric](16, 0) NULL,
[FUTR_GAS] [numeric](16, 0) NULL,
[FUTR_NGL] [numeric](16, 0) NULL,
[FUTR_PET] [numeric](16, 0) NULL,
[GEOM] [geometry] NULL,
CONSTRAINT [PK_SPATIAL_USGS_PROVINCE] PRIMARY KEY CLUSTERED
July 9, 2010 at 8:15 am
Okay, thanks. I just sent you an email. Let me know if you got it.
__________________________________________________
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 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply