Member Avatar for 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?

  • 3 Contributors
  • 2 Replies
  • 207 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by debasisdas
Member Avatar for 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

commented: using JOIN in place of sub query is good solution. +8
Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.