Removing temp tables to improve Performance

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

General SQL Server Forums

New to SQL Server Programming

Removing temp tables to improve Performance

Author

Topic

andyron
Starting Member

4 Posts

Posted - 2011-11-18 : 16:26:04

Hello,

Please give some inputs for an SQL newbie.
The plan is to redevelop SQL package of a long code, without changing the func. and

the specifications of the the changed, can we remove all use off temporary table and dynamic SQL? Main issue is to make the performance much better.
If anyone has come up with such scenario please give some inputs or hints if it can be achieved using cursors or smth else.

please advice,how difficult the task is and if can it be achieved.

Thanks & Regards,
Andy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-18 : 16:33:41

We can't help with what you have provided. You will have to post the code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

andyron
Starting Member

4 Posts

Posted - 2011-11-18 : 16:43:24

can you pls tell....how can i post many page code.
Thanks.

andyron
Starting Member

4 Posts

Posted - 2011-11-18 : 16:57:29

can someone please view the code which i want to email and share their thoughts.will be very helpful to know with what im dealing with.
Thanks
andyron@live.com

andyron
Starting Member

4 Posts

Posted - 2011-11-18 : 17:27:07

Is there an option to attach the file here?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-18 : 18:52:15

Many pages of code? Sounds like you need to hire a consultant to help you through this. We offer free help here. Spending hours on an item is not something that we can do here free.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Kristen
Test

22859 Posts

Posted - 2011-11-20 : 00:43:44

quote:
Originally posted by andyron

dynamic SQL?

How are you doing dynamic SQL? If you are not using parametrised queries, e.g. using sp_ExecuteSQL, then consider doing that. Performance will be much better than using simple dynamic SQL, and it may be easy to retro-fit that to your application.

johntech
Yak Posting Veteran

51 Posts

Posted - 2011-11-21 : 05:34:50

ok use the code below i copy for you to detect temp tables and drop them

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results`
GO
CREATE TABLE #Results ( Company CHAR(3), StepId TINYINT, FieldId TINYINT, )
GO
select company, stepid, fieldid from #Results
GO
ALTER TABLE #Results ADD foo VARCHAR(50) NULL
GO
select company, stepid, fieldid, foo from #Results
GO
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
GO

http://stackoverflow.com/questions/659051/check-if-temp-table-exist-and-delete-if-it-exists-before-creating-a-temp-table
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67818
http://weblogs.sqlteam.com/mladenp/archive/2008/08/21/SQL-Server-2005-temporary-tables-bug-feature-or-expected-behavior.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources