Spatial query optimizing

65 pts.
Tags:
SQL Server
SQL Server Express
SQL Server Query
SQL Server Spatial Data
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    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.
    66,290 pointsBadges:
    report
  • Overtech
    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
    65 pointsBadges:
    report
  • Overtech
    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
    65 pointsBadges:
    report
  • Kccrosser
    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.
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following