Home > IT Answers > SQL Server > Spatial query optimizing
Overtech
65 pts.
 Spatial query optimizing
SQL Server, SQL Server Express, SQL Server Spatial Data, SQL Server query optimization
I have a development machine and a production machine with identical databases, but I'm getting huge differences in execution plans when running a spatial query.

First, my development machine:
It's SQL Server Express x64 with Reporting Services running on Win7 with 8Gb RAM.

Then my production machine:
SQL Server with Reporting Services running on Windows Server 2008 with 8Gb RAM (x86 though, so only 4Gb usable).

I have a table that contains about 3000+ records containing polygon coordinates.  The database that holds the table is identical on both machines.  A sample query I'm running on the dev machine executes in a fraction of a second but takes 11 seconds on the production machine.  When I looked at the execution plans, I was surprised to see that they are completely different on the two machines.

The query is:

 SELECT *
 FROM USCounties
 WHERE geography::STGeomFromText('POINT(-86.7535 33.6499)', 4326).STIntersects(Polygon) = 1


On the production machine the execution plan shows 3 steps:
SELECT Cost: 0%
Filter Cost: 97%
Clustered Index Scan Cost: 3%

But on the dev machine the execution plan is much more complex and includes things like "Nested Loops", "Hash Match" and "Compute Scalar".  The indexes on the two machines look identical from what I can see and the data is identical.  I would expect a performance difference just based on x86 vs x64, but I don't understand the difference in execution plans...

I really don't know much of anything about db optimization, any tips from the experts here?

 - Dave
ASKED: May 29, 2010  11:32 PM GMT
UPDATED: June 2, 2010  5:24:49 PM GMT
RELATED QUESTIONS
Overtech
65 pts.
Answer Wiki:

I found the solution, but I'm not sure I understand why. Apparently the SQL server on my development machine uses the spacial index by default, but the production machine does not. I added "WITH (INDEX (geog_sidx))" to the query and now it runs just as fast on the production machine and the execution plan looks identical to the one on my development computer.

I just somehow stumbled onto this solution by accident while trying to research spatial query optimization techniques. I didn't even know that manually specifying an index was possible...

Why would an index not be used if it were available?

Dave
Last Wiki Answer Submitted:  Jun 1, 2010  1:10 PM (GMT)  by  Overtech   65 pts.
To see other answers submitted to the Answer Wiki View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



 

Please post the execution plans somewhere (you can right click on the plan and save it) and post a link so they can be downloaded and looked at.

Odds are the statistics in the production database are out of date.

Mrdenny
 63,630 pts.
 

I assume that you are referring to the faster of the two execution plans. I’ve posted it on Google Documents here.

I’m not sure how the statistics get created, but the table itself had just been created prior to running the query. The table contains polygons of each county in the US and was created by downloading the shapefile from cencus.gov then using “Shape2SQL” with the “Create Spatial Index” option checked. I used the same method on both servers, for what it’s worth.

By the way, I attended some of your sessions at a SoCal Code Camp last summer. Hopefully I’ll make it there again for the one next month. It’s a long haul from Atlanta, but it’s one of my favorite Code Camps to attend…

Dave

Overtech
 65 pts.
 

Actually now that I think about it, it’s probably more useful to you to see the query that isn’t working very efficiently. That execution plan is posted here.

Dave

Overtech
 65 pts.
 

If you had just created the table in your production system, it is quite likely that the table statistics had not been updated, as Mrdenny suggested. In the absence of any good statistics, the query optimizer probably fell back on a full table scan.

Following is a link to is a good article on statistics and how to update them. Normally, if your system is set with automatic statistics on, the system will self-tune, but if you suddenly do something (like create a new table, or load/change/delete a lot of records at once), the statistics may be wrong for a while after that.

http://msdn.microsoft.com/en-us/library/dd535534(SQL.100).asp

Kccrosser
 3,830 pts.