output to temp table vs dynamic

Last Post 22 Apr 2008 10:58 AM by nathankatcgfdotorg. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
nathankatcgfdotorg
New Member
New Member

--
21 Apr 2008 03:15 PM
Hi. I've got a huge table of 20 m rows.

Scenario One

I'd I've got two views each one based on an fk that are about 1000 rows each.
I join the views on a column that is varchar 250.
The task is show me where vw_tblOne.Col001 AS ColA = vw_tblTwo.Col001 AS ColB.
This takes about 2 minutes 30 seconds.

Scenario Two

If instead I use the selects in the views to populate two temp tables.
And then join the temp tables just like I joined the views before,
Select my result
Then drop the tables.
The whole thing is done in 30 seconds.

Why is it that there is a time difference? Isn't the creation of the views basically building temp tables under the hood when I ask to see the views? Isn't under the hood the whole operation the same?

Is this an indication of a hardware performance bottleneck somewhere?
SwePeso
New Member
New Member

--
22 Apr 2008 12:54 AM
Is this related to this question?
http://sqlforums.windowsitpro.com/w...erthread=y
nathankatcgfdotorg
New Member
New Member

--
22 Apr 2008 10:58 AM
No. But thanks for your input on that other post. For that I ended up going with a permanent table in that other format (pivoted so to speak). It's clunky in that records are deleted before new ones are inserted.

This time the table is huge, the record sets are not unlimited and the columns are different and far fewer. This time theres a join on the two record sets as mentioned and I was just wondering and wishing that not going to temp tables was just as fast.

Both questions could use temp tables so in that regard there is a similarity.

There is another annoying factor. In building the temp tables I would like to name the tables specific to the data going in for example pid=111 and so #tblTablePID111. Unfortunately, to do this I have to use dynamic SQL and build a string in the sp. That means using exec (@sqlstr) which when used seems to close the connection. That dynamic sql builds temp tables, fills them, runs a select and deletes them. Works perfect in Query Analyzer. When you use asp though you get an error, object is closed. I'd have to break out the sp into separate sps, which means I can't really use the temp tables (because they won't last from one sp to the next).

I'll bet if I use real tables temporarily, that there's more overhead to using them and they won't be as fast as real temp tables. I'd use or try to use a table variable but they apaprently are limited in scope to the life of the sp session.
nathankatcgfdotorg
New Member
New Member

--
22 Apr 2008 11:25 AM
Good point, thanks.
nathankatcgfdotorg
New Member
New Member

--
22 Apr 2008 11:49 AM
I found the answer to the object being closed when trying to grab the recordset.

SET NOCOUNT ON

was needed before the inserts and

SET NOCOUNT OFF

after so the message returned of how many rows were updated didn't throw off the recordset.

Now I'll have to go back and see if I could build the sp with temp tables the way originally intended.


Acceptable Use Policy
---