locked
Slow Performance on Geometry data type in arcgis (ESRI) database RRS feed

  • Question

  • Hi,

    I have a database with 1500000 rows in geometry table and my field type is geometry.

    I have this query :

    Declare	@P8 varbinary(max)=0xE6100000010405000000806B661B22013740A0CF7850C0782840B0FE4CE0BDB45340A0CF7850C0782840B0FE4CE0BDB45340F0304B7AD9E34940806B661B22013740F0304B7AD9E34940806B661B22013740A0CF7850C078284001000000020000000001000000FFFFFFFF0000000003;
    Declare @Str nvarchar(1000)=N'Select Shape from Network where Shape.Filter(@p1)=1';
    exec sp_executeSql @str,N'@p1 varbinary(max)',@P8;

    and return 980000 rows but take about 2 minutes. Also I create spatial index on this table.

    I know that, my index is not suitable for this query. 

    I use sp_help_spatial_geometry_index to analyze index for this polygon .

    Base_Table_Rows	981797
    Bounding_Box_xmin	53
    Bounding_Box_ymin	33
    Bounding_Box_xmax	56
    Bounding_Box_ymax	34
    Grid_Size_Level_1	16
    Grid_Size_Level_2	16
    Grid_Size_Level_3	16
    Grid_Size_Level_4	16
    Cells_Per_Object	10
    Total_Primary_Index_Rows	1034449
    Total_Primary_Index_Pages	3080
    Average_Number_Of_Index_Rows_Per_Base_Row	1
    Total_Number_Of_ObjectCells_In_Level0_For_QuerySample	1
    Total_Number_Of_ObjectCells_In_Level0_In_Index	973713
    Total_Number_Of_ObjectCells_In_Level1_For_QuerySample	16
    Total_Number_Of_ObjectCells_In_Level2_In_Index	1338
    Total_Number_Of_ObjectCells_In_Level3_In_Index	18132
    Total_Number_Of_ObjectCells_In_Level4_In_Index	41266
    Total_Number_Of_Interior_ObjectCells_In_Level1_For_QuerySample	16
    Total_Number_Of_Intersecting_ObjectCells_In_Level2_In_Index	1338
    Total_Number_Of_Intersecting_ObjectCells_In_Level3_In_Index	18132
    Total_Number_Of_Intersecting_ObjectCells_In_Level4_In_Index	41266
    Total_Number_Of_Border_ObjectCells_In_Level0_For_QuerySample	1
    Total_Number_Of_Border_ObjectCells_In_Level0_In_Index	973713
    Interior_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage	0.0
    Intersecting_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage	0.0
    Border_To_Total_Cells_Normalized_To_Leaf_Grid_Percentage	0.0
    Average_Cells_Per_Object_Normalized_To_Leaf_Grid	0.0
    Average_Objects_PerLeaf_GridCell	0.0
    Number_Of_SRIDs_Found	2
    Width_Of_Cell_In_Level1	0.75
    Width_Of_Cell_In_Level2	0.1875
    Width_Of_Cell_In_Level3	0.046875
    Width_Of_Cell_In_Level4	0.01171875
    Height_Of_Cell_In_Level1	0.25
    Height_Of_Cell_In_Level2	0.0625
    Height_Of_Cell_In_Level3	0.015625
    Height_Of_Cell_In_Level4	0.00390625
    Area_Of_Cell_In_Level1	0.1875
    Area_Of_Cell_In_Level2	0.01171875
    Area_Of_Cell_In_Level3	0.000732421875
    Area_Of_Cell_In_Level4	4.57763671875E-05
    CellArea_To_BoundingBoxArea_Percentage_In_Level1	6.25
    CellArea_To_BoundingBoxArea_Percentage_In_Level2	0.390625
    CellArea_To_BoundingBoxArea_Percentage_In_Level3	0.0244140625
    CellArea_To_BoundingBoxArea_Percentage_In_Level4	0.00152587890625
    Number_Of_Rows_Selected_By_Primary_Filter	981797
    Number_Of_Rows_Selected_By_Internal_Filter	8996
    Number_Of_Times_Secondary_Filter_Is_Called	972801
    Number_Of_Rows_Output	981780
    Percentage_Of_Rows_NotSelected_By_Primary_Filter	0
    Percentage_Of_Primary_Filter_Rows_Selected_By_Internal_Filter	0.916279027130863
    Internal_Filter_Efficiency	0.916294892949541
    Primary_Filter_Efficiency	99.9982684811626

    This is my index script :

    CREATE SPATIAL INDEX [S7_idx] ON [dbo].[NETWORK] ( [Shape] )USING GEOMETRY_GRID WITH (BOUNDING_BOX =(53,33,56,34), GRIDS =(LEVEL_1 = Low,LEVEL_2 = low,LEVEL_3 = low,LEVEL_4 = low), CELLS_PER_OBJECT = 10

    How can I find best value for bounding box and cells per object?

    thanks in advance



    Monday, February 19, 2018 10:00 AM

All replies

  • Hi Hamid.Sadeghian,

    Have you tried using the following code directly?

    Select Shape from Network where Shape.Filter(0xE6100000010405000000806B661B22013740A0CF7850C0782840B0FE4CE0BDB45340A0CF7850C0782840B0FE4CE0BDB45340F0304B7AD9E34940806B661B22013740F0304B7AD9E34940806B661B22013740A0CF7850C078284001000000020000000001000000FFFFFFFF0000000003)=1
    

    >>How can I find best value for bounding box and cells per object?

    For bounding box, finding the min and max X and Y of your dataset would work to create the index, we can refer to this method: http://aboutsqlserver.com/2013/09/03/optimizing-sql-server-spatial-queries-with-bounding-box/

    Many times, we will use MEDIUM on all levels.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 20, 2018 9:10 AM
  • Dear Teige,

    Thank you for your reply. I tried set bounding box with min and max x,y , but nothing changed.

    Also create this index :

    CREATE SPATIAL INDEX [S7_idx] ON [dbo].[NETWORK]
    (
    	[Shape]
    )USING  GEOMETRY_GRID 
    WITH (BOUNDING_BOX =(50,30,58,36), GRIDS =(LEVEL_1 = medium,LEVEL_2 = medium,LEVEL_3 = medium,LEVEL_4 = low), 
    CELLS_PER_OBJECT = 64, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = off, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO

    I need to know how to calculate best value for bounding box and cells-per-object and how to detect which grid size is suitable for my data.

    The main problem is , I cannot change the query, because queries send from arcgis application.

    Tuesday, February 20, 2018 1:44 PM