virtual techdays india │ 18-20 august 2010 developing with sql server spatial & deep dive into...
TRANSCRIPT
virtual techdaysINDIA │ 18-20 august 2010
Developing with SQL Server Spatial & Deep Dive into Spatial IndexingPinal Dave│ Mentor, Solid Quality Mentors │SQLAuthority.com
Yes No May Be I Don’t know!
virtual techdaysINDIA │ 18-20 august 2010
Question to You!
Is the Earth Flat?
virtual techdaysINDIA │ 18-20 august 2010
The Earth is Flat…
virtual techdaysINDIA │ 18-20 august 2010
The Earth is Flat…
virtual techdaysINDIA │ 18-20 august 2010
The Earth is Flat…
virtual techdaysINDIA │ 18-20 august 2010
…but the Earth is Sphere (almost)!
Introduction to Spatial Database One line definition Planer vs Geographic
Understanding Spatial Indexing Planer vs Geographic Index Internals Query Hinting
Index Maintenance Performance Troubleshooting
virtual techdaysINDIA │ 18-20 august 2010
Agenda
Session Objectives And Key Takeaways
SQL Server MVP Mentor – Solid Quality Mentors India Founder – SQLAuthority.com Regional Mentor – Professional Association for SQL Server (PASS) Asia,
Pacific & Middle East User Group Lead – Ahmedabad SQL Server UG, Gandhinagar SQL Server UG Author, Trainer, Speaker, Consultant MCT, MCTS, MCP, MCDBA, MCAD [email protected], [email protected] http://twitter.com/pinaldave
virtual techdaysINDIA │ 18-20 august 2010
Agenda
Session Objectives And Key Takeaways
spa·tial [spey-shuhl] –adjective
1.of or pertaining to space. 2.existing or occurring in space.3.having extension in space.
Database that models space, objects in space, or a combination of both and provide capabilities to store and manipulate spatial data
virtual techdaysINDIA │ 18-20 august 2010
What is Spatial?
One Line Definition
GEOMETRY data type Infinite X and Y
virtual techdaysINDIA │ 18-20 august 2010
SQL Server 2008 Systems Story
Planar Systems GEOGRAPHY data type Latitude -90 to +90 Longitude 0 to 360
Geographic Systems
virtual techdaysINDIA │ 18-20 august 2010
DEMO: Various DatatypesDEMO: World Map
Requires bounding box Only one grid
virtual techdaysINDIA │ 18-20 august 2010
SQL Server 2008 Indexing Story
Planar Index No bounding box Two top-level projection grids
Geographic Index
4 levels Customizable grid granularity Three Grid Densities Per Level - Low, Medium, High Customizable max number of cells per object
virtual techdaysINDIA │ 18-20 august 2010
SQL Server 2008 Indexing Story
Multi-Level Grid
virtual techdaysINDIA │ 18-20 august 2010
MAIN SLIDE TITLE
Sub Slide Title
/ (“cell 0”)
/4/2/3/1
virtual techdaysINDIA │ 18-20 august 2010
Tessellation Process
• Create index example GEOMETRY:CREATE SPATIAL INDEX sixdON spatial_table(geom_column)WITH (
BOUNDING_BOX = (0, 0, 500, 500),GRIDS = (LOW, LOW, MEDIUM, HIGH),CELLS_PER_OBJECT = 20)
• Create index example GEOGRAPHY:CREATE SPATIAL INDEX sixdON spatial_table(geogr_column)WITH (
GRIDS = (LOW, LOW, MEDIUM, HIGH),CELLS_PER_OBJECT = 20)
• Use ALTER and DROP INDEX for maintenance.
virtual techdaysINDIA │ 18-20 august 2010
Index Creation and Maintenance
virtual techdaysINDIA │ 18-20 august 2010
DEMO: Geometry IndexDEMO: Index Analysis
FROM T WITH (INDEX (<Spatial_idxname>)) Spatial index is treated the same way a non-clustered index is
the order of the hint is reflected in the order of the indexes in the plan multiple index hints are concatenated no duplicates are allowed
The following restrictions exist: The spatial index must be either first in the first index hint or last in the last index hint for
a given table. Only one spatial index can be specified in any index hint for a given table.
virtual techdaysINDIA │ 18-20 august 2010
Index Hints
Make sure you are running SQL Server 2008 SP1 Check query plan for use of index Make sure it is a supported operation Hint the index (and/or a different join type) Do not use a spatial index when there is a highly selective non-spatial
predicate
virtual techdaysINDIA │ 18-20 august 2010
Checklist for Performance
virtual techdaysINDIA │ 18-20 august 2010
RESOURCES
Michael Rys http://sqlblog.com/blogs/michael_rys/
Pinal Dave http://blog.sqlauthority.com
