Combining two simple SQL Server queries takes long running time

Question

I have two simple queries that execute within expected time when they run alone. The first query:

SELECT OBJECTID AS OID FROM PST_35053_SNAPPED;

Takes less than a second and returns about 3000 rows. The second query:

SELECT DISTINCT PST.OBJECTID as OID FROM PST_35053_SNAPPED 
PST INNER JOIN  POWNERS_35053 POW
ON geometry::STGeomFromText('POINT(' + convert(varchar(16),POW.x) + ' ' +
convert(varchar(17),POW.y) + ')', 2100).STWithin(PST.Shape)=1;

Takes a second returning about 2500 rows.

When combining them with EXCEPT to retrieve polygon IDs without points falling in them (about 500 rows), the resulting query takes more than two minutes to execute (about 122 seconds):

SELECT OBJECTID AS OID FROM PST_35053_SNAPPED
EXCEPT
SELECT DISTINCT PST.OBJECTID as OID FROM PST_35053_SNAPPED 
PST INNER JOIN  POWNERS_35053 POW
ON geometry::STGeomFromText('POINT(' + convert(varchar(16),POW.x) + ' ' +
convert(varchar(17),POW.y) + ')', 2100).STWithin(PST.Shape)=1

Is there something I am missing or doing wrong? I am using SQL Server 2012 SP3

Answer 1

It's hard to say without query execution plan. However, if I understand correctly, the following query should get the same result, and would be faster.

SELECT PST.OBJECTID as OID 
FROM PST_35053_SNAPPED PST 
LEFT JOIN  POWNERS_35053 POW
ON geometry::STGeomFromText('POINT(' + convert(varchar(16),POW.x) + ' ' +
convert(varchar(17),POW.y) + ')', 2100).STWithin(PST.Shape)=1
WHERE POW.OBJECTID IS NULL

Answer 2

Try to insert your script to temp table than combine it. We don't have execution plan we can only show you to execute your query with different angles. :

SELECT DISTINCT PST.OBJECTID as OID INTO #Temp FROM PST_35053_SNAPPED 
PST INNER JOIN  POWNERS_35053 POW
ON geometry::STGeomFromText('POINT(' + convert(varchar(16),POW.x) + ' ' +
convert(varchar(17),POW.y) + ')', 2100).STWithin(PST.Shape)=1;

SELECT OBJECTID AS OID FROM PST_35053_SNAPPED
EXCEPT
SELECT OID FROM #Temp;

or try not exists :

SELECT OBJECTID AS OID 
FROM PST_35053_SNAPPED PST1
WHERE NOT EXISTS 
    (SELECT 1 FROM 
        (
            SELECT DISTINCT PST.OBJECTID as OID FROM PST_35053_SNAPPED 
            PST INNER JOIN  POWNERS_35053 POW
            ON geometry::STGeomFromText('POINT(' + convert(varchar(16),POW.x) + ' ' +
            convert(varchar(17),POW.y) + ')', 2100).STWithin(PST.Shape)=1
        ) PST2
        WHERE PST1.OBJECTID=PST2.OBJECTID
    )

or try WITH :

WITH Cte AS 
(
    SELECT DISTINCT PST.OBJECTID as OID FROM PST_35053_SNAPPED 
    PST INNER JOIN  POWNERS_35053 POW
    ON geometry::STGeomFromText('POINT(' + convert(varchar(16),POW.x) + ' ' +
    convert(varchar(17),POW.y) + ')', 2100).STWithin(PST.Shape)=1
)
SELECT OBJECTID AS OID FROM PST_35053_SNAPPED
EXCEPT
SELECT OID FROM Cte;