Performance issue

Question

Performance issue

Ramakrishna-148487

Newbie

Points: 8
More actions
May 18, 2014 at 5:09 am

#307473

I am facing problem with stored procedure. Procedure contains three temporary tables. suddenly giving low performance when i am calling from the asp.net application.
it is working fine, when i rename that procedure.
Can any anyone help on this. it is degrading the performance of the website

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Definitely not enough information to help. First thing I would do is read the second article I have linked in my signature block regarding performance issues. It will walk you through what you need to post and how to post it to get the best help possible for a performance problem.

Ramakrishna-148487 Newbie Points: 8 More actions · Answer 2

my procedure contains three four temporary tables and using without creation of table structure

for ex:

Select * into #td from transsactions

Transactions tables contains huge data. Like that i am using three temporary tables without creating the table structure.

while calling with procedure sometimes iam getting performance issues. execution getting delayed. it is working when i rename the procedure for some days.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Ramakrishna-148487 (5/18/2014)
my procedure contains three four temporary tables and using without creation of table structure
for ex:
Select * into #td from transsactions
Transactions tables contains huge data. Like that i am using three temporary tables without creating the table structure.
while calling with procedure sometimes iam getting performance issues. execution getting delayed. it is working when i rename the procedure for some days.

As I said earlier:

Definitely not enough information to help. First thing I would do is read the second article I have linked in my signature block regarding performance issues. It will walk you through what you need to post and how to post it to get the best help possible for a performance problem.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

No where near enough information. We need at minimum the procedure definition and the table definitions, the execution plan as well would be extremely useful

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

free_mascot One Orange Chip Points: 27169 More actions · Answer 5

As you have mention that renaming the stored procedure works fine. Based on this info you can try executing stored procedure with recompile option every time and see if you gain any performance? However recompile every time will have slight load on the server and I assume that this procedure is not executing very frequently.

Though this might not have the ideal solution and you need to study execution plan as mention by other mate in earlier post.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

MMartin1 One Orange Chip Points: 27795 More actions · Answer 6

It would be good to investigate how the tables are joined, are the indexes appropriate, is it fast when you call it from ssms? As mentioned by others taking the time to provide good information helps others to help you 🙂

----------------------------------------------------