I ran into something curious today. I was running a subselect in MS SQL 7.0. It took over 40 minutes. Being a Fox programmer, I tried the same syntax in Fox. Result: Fox returned the query in 9 seconds. Needing to use SQL, I tried to figure out a way to optimize it and ended up with the same syntax that Steve proposed to you. Result: MS SQL returned a result in 13 seconds (big change from 40+ minutes, although I did NOT change the environment so SQL probably had a good deal of optimizing info available to it the second time around). I went back to Fox and tried the new syntax. No significant change.

It appears that subselects are optimized in Fox but not in SQL Server.

SQL subquery: (40 minutes)

Select code from tableA where code not in (select code from tableB)

SQL left join with null test: (13 seconds)

Select distinct tableA.code from tableA left join tableA.id=tableB.id where tableA.code is not null and tableB.code is null


Anybody know why there is such a huge difference in performance for these two queries that result in the same thing? (besides the obvious self optimizing features of MS SQL Server 7.0)




>>Try using a left join.
>>
>>select * from table1 left join table2 on table1.keyfield = table2.keyfield
>> where isnull(table2.keyfield)
>>
>>This will return only the records from table1 where there is no match in table2.
>
>Thanks, Steve, but it's still extremely slow. I think internally that's exactly what my code does...so, same problem, I think - it cannot use the index in any helpful way.