Can we remove temp tables to improve performance
-
Hello guys,
Please give some inputs for an newbie.
The plan is to redevelop 10 PL/SQL package hundred page code, without changing the func. and the specifications, 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 links, if it can be achieved using cursors or smth else.
please advice,if can it be achieved and how difficult is the task?
Thanks & Regards,
Andy
-
andyron (11/18/2011)
Hello guys,Please give some inputs for an newbie.
The plan is to redevelop 10 PL/SQL package hundred page code, without changing the func. and the specifications, 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 links, if it can be achieved using cursors or smth else.
Cursors are not a replacement for temporary structures. They're a different beast entirely and they can only slow you down (you're thinking Oracle where they're more useful). Temp tables also can usually help optimize your code. However, in this case, I've never seen the code. I have no idea what their use is so I don't know if you can optimize them away.
Dynamic SQL if coded well isn't really that much different then standard SQL, but it's a lot less secure and more annoying to manage. Under most circumstances you can remove dynamic SQL. Again, I haven't seen the code though so I can't speak to THIS code.
please advice,if can it be achieved and how difficult is the task?
Sounds like it's going to be very difficult for you, you seem inexperienced in T-SQL and SQL Server. You might look into hiring a contractor for a week or two to do an initial review with you and make sure you start off on the right foot, and then work from the guidelines he recommends.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
PLSQL is very cursor oriented,where the same operation in can be redesigned to do the same work in a set based manner, which is typically several orders of magnitude faster than a cursor.
rewriting your code depends, of course on what it is doing. why are there 10 temp tables? why are there cursors and what are they doing?
we can probably offer hints and bits and pieces, but in a general sense, the answer to your question is yes...you cna get rid of it and do it faster, most likely.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank you very much for the quick replies.much appreciated.
would you please like to view the code once and comment.
I will PM my email address.
Regards
-
General forum 'rules' - post it publicly and we'll comment publicly so that others who read will also benefit.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
ok thanx.no idea,how i can post such long code.
-
Put it in a text file or .sql file and upload it (edit attachements)
-
I have attached the file.
The code in question is the attachment 1 called code.
Can it be done on the lines of code 2?
Thanks
-
Please post as text files. People are usually frisky about opening 3rd party office files from programmers ;-).
-
Good to know that buddy
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply