Faster SQL Provider performance

Topics: General
Oct 10, 2012 at 8:49 PM

For those wanting a faster response out of thier secondary (or primary) MS SQL Server cache provider and have an a ppropriate version of SQL Server then the following script will allow for a faster tile lookup :

CREATE VIEW [dbo].[GMAPNETcache_key]
WITH SCHEMABINDING 
AS
SELECT Type, Zoom, X, Y
FROM  dbo.GMapNETcache
GO
CREATE UNIQUE CLUSTERED INDEX [GMAPNETcache_key_UC] ON [dbo].[GMAPNETcache_key] ([Type] ASC, [Zoom] ASC, [X] ASC, [Y] ASC) WITH (DATA_COMPRESSION = PAGE) GO
If the script runs on your SQL instance (if you get errors it is more likely that your version does not support indexed views) then you will get a virtual lookup table that is efectively the same as an actual table from the perspective of the database engine and optimisation.

This saves having to create a custom provider, stored procedures and tables to get better performance.

If you want to be able to add in the timestamp data for the tiles then at the moment you have to write your own provider.....

Coordinator
Oct 10, 2012 at 9:07 PM

current provider adds tiles to database, it won't work with view, so just add index to the table

Oct 10, 2012 at 10:58 PM

Within SQL Server a clustered indexed view is visible to the base table from the optimisers perspective, when querying the underlying table.That is to say if you have (Table A) with a single key on Column 1 and no other indexes on the table and then create a schema bound indexed view (View 1) on the table using Column 2 the index on Column 2 would be available to the optimiser when you query (Table A). This works well for the SQL Server cache provider instance because the varbinary(max) column is dropped out of the secondary indexed view and the clustered index key lookup to see if a tile exist does not have to look in a 1GB+ table with 600,000+ tiles, rather a 14MB smaller keyset (reduced even more due to the page compression).

Coordinator
Oct 11, 2012 at 12:24 PM
Edited Oct 11, 2012 at 12:28 PM

current definition is:

CREATE TABLE [GMapNETcache] ( 
                     [Type] [int]   NOT NULL, 
                     [Zoom] [int]   NOT NULL, 
                     [X]    [int]   NOT NULL, 
                     [Y]    [int]   NOT NULL, 
                     [Tile] [image] NOT NULL, 
                     CONSTRAINT [PK_GMapNETcache] PRIMARY KEY CLUSTERED (Type, Zoom, X, Y))

do i need to add 'WITH (DATA_COMPRESSION = PAGE)' or it wont help?

Coordinator
Oct 11, 2012 at 12:41 PM

..i can't test it myself in the moment, would be interesting to see the actual execution estimations from sql management studio for tile query, as i remember tile select triggers primary index and executes really fast

Oct 22, 2012 at 4:15 PM

Use the default schema that is generated with the SQL provider and then run the first script to create the view. The create statement looks correct for the base table, although use varbinary(max) instead of image as MS are trying to drop support for the image type.

Coordinator
Oct 22, 2012 at 4:47 PM

so is it faster or not ;} where is the evidence?