I have two tables: CDDemographics and CBTechProviderData.
Both have 200,000+ rows.
A common column between these tables is CensusBlockID, of which the first 5 integers are the fips code - in this case I am defining that code as 55001.
I am trying to sum the population of CensusBlockIDs where a certain technology type is present.

The query below seems to be okay syntax-wise (maybe not?), but my server times out:

SELECT SUM(Population) FROM CBDemographics WHERE CensusBlockID IN (SELECT CensusBlockID FROM CBTechProviderData WHERE LEFT(CensusBlockID, 5) = 55001 AND TransTech = 80)

The subquery identifies all the CensusBlockIDs that match the fips (55001) and tech type (80); then the outer query should sum the population of all identified CensusBlockIDs.

Any ideas to make this work?

Hello There,

Try to use join instead of subquery. Use below given modified query.

SELECT SUM(a.Population) FROM CBDemographics a JOIN CBTechProviderData b ON a.CensusBlockID=b.CensusBlockID AND LEFT(b.CensusBlockID, 5) = 55001 AND b.TransTech = 80

Create index on b.CensusBlockID and b.TransTech and try to avoid function with where clause.

Alok Pathak

commented: using JOIN in place of sub query is good solution. +8

The root cause is the string function LEFT in your code.

and if CensusBlockID is a character field it will be even more time consuming.

Otherwise using JOIN in place of sub query is good option.