Subquery is timing out

[mmlmitchell]

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?

[alokp16]

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.

Regards,
Alok Pathak

[debasisdas]

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.