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

Answer 1

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

Answer 2

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).