Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««678910

Is a Temporary Table Really Necessary? Expand / Collapse
Author
Message
Posted Thursday, March 12, 2009 5:09 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, January 15, 2014 5:07 PM
Points: 227, Visits: 192
Hi Randy,

Thanks for the article. We also had similar issues joining two three tables with over 200 Million rows with WHERE condition, the O/P was dying if we used derived tables or JOINS. But once we started using #temp tables pre-populated with where conditions the queries executed in seconds.

I think execution manager in SQL Server 2005 in not efficiently managing the worker tables or derived tables when the dataset is huge.

Regards,


Razi, M.
http://questivity.com/it-training.html
Post #674840
Posted Thursday, March 12, 2009 5:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 3:05 PM
Points: 31,284, Visits: 15,750
SQL Server 2005 handles most code better, but not all.

If you are having performance issues, I suggest you post a more details explanation in the SQL Server 2005 Performance Tuning Forum.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #674856
Posted Friday, May 15, 2009 4:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 24, 2010 2:28 AM
Points: 18, Visits: 102
> I am not fond of cursors and advocate doing anything possible to replace the cursor

What do you gain by handling the looping yourself rather than just using a cursor? I try and get rid of cursors by replacing with a single sql statement. Obviously this won't work if you have to call a stored procedure for every record in a table (for example), but if you have to do this then why is using your own looping mechanism preferable to using a cursor?
Post #717741
Posted Friday, May 15, 2009 8:12 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 35,609, Visits: 32,200
alex (5/15/2009)
> I am not fond of cursors and advocate doing anything possible to replace the cursor

What do you gain by handling the looping yourself rather than just using a cursor? I try and get rid of cursors by replacing with a single sql statement. Obviously this won't work if you have to call a stored procedure for every record in a table (for example), but if you have to do this then why is using your own looping mechanism preferable to using a cursor?


I agree... a good "firehose" cursor makes sense here.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #717941
« Prev Topic | Next Topic »

Add to briefcase «««678910

Permissions Expand / Collapse