Sql Server Geometry Column causing query to take long time to run

Question

I have written a query to fetch the polygon data from Sql database.

I have following query to fetch the results.

SELECT ZIP,
       NAME,
       STABB,
       AREA,
       TYPE,
       orgZc.OrganizationId,
       orgZc.[ZipCode] AS ORGzip,
       REPLACE(REPLACE(REPLACE(REPLACE(GEOM.STAsText(),'POLYGON ((',' '),'MULTIPOLYGON (((',' '),'))',''),')))','')AS WKT
FROM USZIP
INNER JOIN ORGANIZTION_ZIP_CODES orgZc ON orgZc.[ZipCode]=USZIP.zip
WHERE orgZc.OrganizationId=@ORGANIZATION_ID

On this table i have already added a spatial index as below

CREATE SPATIAL INDEX SIndx_SpatialTable_geometry_col1 
ON USZIP(GEOM) WITH ( BOUNDING_BOX = ( -90, -180, 90, 180 ) );

But it took 38 sec to fetch the 2483 records. Can anyone help me to optimize this query

Indexes tends to assist when search predicates in the `WHERE` clause can benefit from using them. But your `WHERE` clause doesn't appear to reference the geometry column at all. Why did you think the index would help? — Damien_The_Unbeliever, Sep 03 '13 at 10:51
Your correct Damien_The_Unbeliever. But i need to fetch based on zip all the geometry values — Chandra Mohan, Sep 03 '13 at 10:54
Zip column is primary column so i have already index on that. I am fetching geometry values for the zip codes belongs to a organization — Chandra Mohan, Sep 03 '13 at 10:55

score 0 · Answer 1 · answered Sep 03 '13 at 11:03

My guess is that important part of your query is the from and where clauses. However, you can test this by removing the line:

   REPLACE(REPLACE(REPLACE(REPLACE(GEOM.STAsText(),'POLYGON ((',' '),'MULTIPOLYGON (((',' '),'))',''),')))','')AS WKT

To see if that processing is taking up a lot of time.

For this part of the query:

FROM USZIP INNER JOIN
     ORGANIZATION_ZIP_CODES orgZc
     ON orgZc.[ZipCode] = USZIP.zip
WHERE orgZc.OrganizationId = @ORGANIZATION_ID;

You say that the zip code is "a primary column". However, it has to be the first column in a composite index (or primary key) in order to be used for the join. So, you really want an index on USZIP(zip) for the join to work. (I'm guessing this is true based on the name of the table, but I want to be explicit.)

Second, your where clause is limited to one OriganizationId, presumably of many. If so, you want an index on ORGANIZATION_ZIP_CODES(OrganizationId). Or, better yet, on ORGANIZATION_ZIP_CODES(OrganizationId, ZipCode).

I removed the line as you said .It just took fraction of seconds. I work with single organization at a time — Chandra Mohan, Sep 03 '13 at 11:07
@ChandraMohan . . . Bummer. That means that the join optimizations probably won't help. — Gordon Linoff, Sep 03 '13 at 11:13

score 0 · Answer 2 · answered Sep 04 '13 at 06:09

I found the solution. I added a new column and updated REPLACE(REPLACE(REPLACE(REPLACE(GEOM.STAsText(),'POLYGON ((',' '),'MULTIPOLYGON (((',' '),'))',''),')))','')AS WKT Now i can fetch from the newly added column directly without doing any manipulations. Now it is taking 3 sec to fetch 2483 records

Sql Server Geometry Column causing query to take long time to run

2 Answers2