Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query that joins to a table containing 2 million rows and need to filter on its geometry column by its geometry type (using geom.STGeometryType()). The query takes less than a second to run without this WHERE clause but over 40 seconds to run with the WHERE clause.

The execution plan seems to indicate that the filter on STGeometryType() is being run over the geometry column of the whole locations table and there's an accompanying warning that there are no statistics for the geometry column.

If I first select into a temporary table without the filter and then filter on the temporary table, I get the results in under a second. This is because it is only testing for geometry type over a result set of 25000 records. So one solution would be to do this (though it would require re-factoring the application).

I've also tried using a CTE and derived table to do all of the joins 'first' and then filter but that doesn't improve performance.

Is there a way I can structure the query so that this relatively expensive filter is applied last without the use of intermediate tables?

   select o.code as Code, ro.ReportTaxonCode as TaxonCode, t1.TaxonName as TaxonName,
   case
       when o.NumberObserved IS NOT NULL THEN o.NumberObserved
       when o.NumberObserved IS NULL then su.Mean
   end as Scale,
   ro.OriginalTaxonCode as RecordedCode, t2.TaxonName as RecordedName, s.code as SetCode, CAST(s.StartDate AS nvarchar), CAST(s.EndDate AS nvarchar), l.code as Location, l.geom
   from obsveg.obs_observations o
   inner join obsveg.obs_sets s
   on o.obs_setsguid = s.guid
   inner join obsveg.obs_locations l
   on o.obs_locationsguid = l.guid
   inner join obsveg.obs_rpt_reportobservations ro
   on o.guid = ro.obs_observationsguid
   inner join obsveg.obs_taxon t1
   on ro.ReportTaxonCode = t1.code
   inner join obsveg.obs_taxon t2
   on ro.OriginalTaxonCode = t2.code
   left join obsveg.OBS_MeasurementMethodScaleUnits mmsu
   on o.OBS_MeasurementMethodScaleUnitsGUID = mmsu.GUID
   left join obsveg.obs_ScaleUnits su
   on mmsu.OBS_ScaleUnitsGUID = su.GUID
   where ro.reportguid = '64c0a4af-ee4d-4e83-a194-2a14e8a6ab0e'
   and l.geom.STGeometryType() = 'Point'

EDIT Warning in execution plan added execution plan warning

EDIT Execution plan posted to pastebin Execution plan

share|improve this question
1  
I think it would help if you added the actual execution plan. –  ypercube Sep 19 '13 at 10:39
    
Okay. How is it best to do that? As an image (it's quite large ...)? –  andyb Sep 19 '13 at 10:46
1  
Read the paragraph under Post real execution plans in the article: Help us help you : keys to getting good answers –  ypercube Sep 19 '13 at 10:57
    
Thanks. Wasn't aware of that article. –  andyb Sep 19 '13 at 11:04
    
The donwsize from 2M to 25K of rows comes from the ro.reportguid = '64c0a4af-ee4d-4e83-a194-2a14e8a6ab0e' condition? –  ypercube Sep 19 '13 at 11:18

1 Answer 1

The first thing to look at is your indexing strategy. Bad execution plans are often caused by insufficient indexes or stale statistics. Your statistics warning might hint at that.

If that does not resolve your problem there are a few hacks that you can try:

A top operation requires SQL Server to separate query sections in the execution plan.

If you know you are always dealing with less then 2 billion rows you could write your query like this:

SELECT * FROM(
  SELECT TOP(2000000000) *
    FROM <complex join>
    WHERE ro.reportguid = '64c0a4af-ee4d-4e83-a194-2a14e8a6ab0e'
  )X
WHERE l.geom.STGeometryType() = 'Point'

An alternative is to write a scalar valued function that takes in the l.geom column and a few other columns from the other tables and returns the STGeometryType() value while ignoring all the other values. Because SQL Server does not consider the function logic at optimization time, it is forced to execute the function after the join. That does not guarantee that the other filter is executed first but often it works out that way.

The third option is to play around with join hints and join order. They often lead to a change in where filters are applied.

All three options I would consider a last resort because they make the code ugly and you run the risk that someone removes that ugliness later trying to make the code better.

share|improve this answer
    
Thanks for the advice. The query runs pretty quickly without that final part of the WHERE clause. Unfortunately, the value - geom.STGeometryType() - is a call into the .NET CLR, which I've assumed has some overhead and is causing the poor performance when it is called over the whole locations table unnecessarily. However, I wasn't aware that TOP had this effect and that may just solve the problem. Thank you. –  andyb Sep 19 '13 at 12:27
1  
The TOP trick has this effect because the 2 queries (with and without TOP) are not equivalent. They actually are because you know that less than 2000000000 rows will be returned by the subquery. But the optimizer does not have that info (and even if it had the info somehow, I'm not sure that the specific transformation is among the ones implemented). –  ypercube Sep 19 '13 at 12:46
    
Unfortunately, the query was still very slow using TOP so I bit the bullet and re-factored. I selected all the data for the reportGUID into a table along with STGeometryType() stored as a string. Then I queried this table based on the string's value. I'm still interested to see if the performance can be improved directly in the query, though, so will keep the question open and investigate a bit further. –  andyb Sep 20 '13 at 15:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.