Using #temp tables in subqueries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using #temp tables in subqueries

Author

Topic

devonkyle
Starting Member

19 Posts

Posted - 2003-10-27 : 14:09:04

I have an web based Zipcode Count system that offers a feature that allows a user to find all the zipcodes within X amount of miles, based on a specific zipcode ( i.e. Show me all the zipcodes within a 3 mile radius of 90210) - storing that result set in a #radiussearch temp table . The UDF I have that calculates the result set, based on a lookup table of latitudes and logitudes of each zipcode in the nation, is workng properly and excutes very fast.

I am then running a query that does a count of each occurance of each zipcode in the #radiussearch table against an indexed MAIN table that contain about 30 million records - that reads:

select zipcode, count(zipcode) from MAIN where zipcode IN (select zipcode from #radiussearch)
group by zipcode
order by zipcode

The problem I have is that - based on even a very small radius value (2-3 miles) - the query is DREADFULLY slow - 30 minutes plus - but if I manually test the speed of the query by typing in the actually SELECT IN subquery zipcodes, rather than using the #radiusearchtable temp table, i.e.

select zipcode, count(zipcode) from MAIN where zipcode IN ('90004', '90005', '90006', '90007', '90008', '90009')
group by zipcode
order by zipcode

It returns the correct result set, searching approx 30 million records in about 5-6 SECONDS.... VERY FAST!!

Is there something I'm missing when using a #temp table as a SELECT IN subquery???

Thank you
Devon Kyle
Los Angeles

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-10-27 : 14:14:15

Do you have any indexes on the zipcode field?

devonkyle
Starting Member

19 Posts

Posted - 2003-10-27 : 14:17:11

I have indexes on the non temp MAIN table - I've tried creating an index on the zipcode in the #radiussearch temp table, but that didn't seemd to help the performance at all......

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-10-27 : 14:32:57

How many records typically in the subquery?

devonkyle
Starting Member

19 Posts

Posted - 2003-10-27 : 15:19:55

approx 0 - 30 - not a lot....

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-27 : 15:35:34

select zipcode, count(zipcode) from MAIN where zipcode IN
('90004', '90005', '90006', '90007', '90008', '90009')
group by zipcode
order by zipcode

Put this string together dynamically. Then, exec(@string)...
Of course, I mean must be no any subqueries, only explicit values...

devonkyle
Starting Member

19 Posts

Posted - 2003-10-27 : 19:16:07

I assume you are suggesting something like this?
- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)

-- Enter the dynamic SQL statement into the
-- variable @SQLStatement
SELECT @SQLStatement = "SELECT * FROM TableName WHERE "
+ @WHEREClause

-- Execute the SQL statement
EXEC(@SQLStatement)

That should work - but how would I get the temp table of zipcodes assigned to a concatenated string of zipcodes to assign to the @whereclause ? Do I have to use cursors for this?

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-10-27 : 19:37:14

Have your tried using one of the other relational operators besides IN, like JOIN or EXISTS


select M.zipcode, count(M.zipcode) 
from MAIN M
INNER JOIN #radiussearch rs on rs.zipcode = M.zipcode
group by M.zipcode
order by M.zipcode

DavidM

"SQL-3 is an abomination.."

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-10-27 : 19:39:05

To build your list, look at the New and Improved Approach in this article: [url]http://www.sqlteam.com/item.asp?ItemID=2368[/url]

--------------------------------------------------------
Visit the SQLTeam Weblogs at [url]http://weblogs.sqlteam.com[/url]

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-28 : 04:19:18

byrmol--

I can bet one million mongolian tugriks that your
suggestion will not help. Moreover, would be in the
table #radiussearch too many rows it would be worth
opening a cursor for it. And then, in loop, 'insert'
the fetched values into the WHERE clause.
Of course, it's just my opinion.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-10-28 : 09:49:18

You shouldn't be surprised that the hard-coded query works far faster...because you've left OUT the table that causes the query to slow down.......e "#radiussearch"...this table by the sounds of it....should have MORE than 30million records....(with no index??? by the sounds of it)

You've no qualifier (where condition/join) on "#radiussearch"...so it's a fair guess that it's scanning a dumpertruckload of records!

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-28 : 14:07:07

Andrew,

the case is that #radiussearch table has a very few rows (0 - 40) in it,
in fact this is a kind of LookUp table... Those millions of rows are contained
in a MAIN table with needful indexes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources