0

I have a performance problem
I need to run a Stored Procedure from .Net 1.1. This stored procedure calls 8 Stored Procedures. Each one of them process information to throw a comparative between old an new informacion and anter afects the physical table in DataBase.

The problem comes since I try to run it directly from SSMS. Servers starts crashing, getting so slow and almost impossible to work. I think infrastructure people has to restar service directly on the server.

I'm working in development enviroment so there is no much problem, but I can't upload this into production enviroment.

I've been thinking in use procedures only for comparison purposes and never affect physical data. Retrive information from them in Temporary tables in principal procedure and then open my try-catch and begin-end transactions blocks and affect database in my principal stored with the informacion in Temp tables.

My principal stored look as follows: Is this the best way I can do this??

create proc spTest
as
/*Some processes here, temporary tables, etc...*/
begin try
begin distributed transaction
sp_nested1
sp_nested2
sp_nested3
sp_nested4
sp_nested5
sp_nested6
sp_nested7
sp_nested8
/*more processes here, updates, deletes, extra inserts, etc...*/
commit transaction
end try
begin catch
rollback transaction
DECLARE @ERROR VARCHAR(3000)
SELECT @ERROR = CONVERT(VARCHAR(3000),ERROR_MESSAGE())
RAISERROR(@ERROR,16,32)
RETURN
end catch

The basic structure of each nested stored proc is similar but doesn't call any other proc, only each one has their own try and catch blocks.

Any help will be really appreciated... The version Im using is SQL Server 2005

Thank you all in advance....

KM.
  • 101,727
  • 34
  • 178
  • 212
Alejandro
  • 3
  • 3
  • 7
    calling 8 procedures shouldn't be a problem, but what you do in those is most likely the problem. you have provided only 1% of the info that is necessary to solve this for you. For example, if you told me that you have a red car with four doors, but is doesn't start and run well. How can anyone diagnose a car problem with that given information? – KM. Dec 01 '10 at 21:36
  • 1
    +1 @KM - What OP thinks is relevant to the issue probably is not, otherwise he wouldn't be asking for help on SO! :) – JNK Dec 01 '10 at 21:48
  • Well In the first paragraph describes what each nested procedure does. "Each one of them processes information to throw a comparative between old an new information and after afects the physical table in DataBase." Sorry if is not clear... but I can't provide more information about.. Maybe if I tell you that I get infomration from a table or various tables in database into a temporary table and then i get into other temporary table more information from other tables. Continues in next comment..... – Alejandro Dec 01 '10 at 22:11
  • Information is merged in a third table that match identical records and records where primary key is identical but othe fields change and records that didn't exist but now does and viceversa. At the end I've a table with a comparative between old and new info, then I update, insert and delete info. I do this in each one of 8 nested proc. If is relevant, I ran the procedure in live and ran for 5 minutes, then I ran it from asp .net and ran for 10 mins. At least I know code runs but now I dont know if I't doing best practice... As I said.. All this is about Performance and best practices... – Alejandro Dec 01 '10 at 22:13
  • Alejandro - My concern would be you are approaching a setbased language in a linear fashion (one SP_Nested after another). If these processes are looping through processing one row at a time, you'll start to see horrible performance when you try to process 1000+ lines at a time. You'll have to be more specific with your 'sp_nested_one' and what each of those are doing if you want a more specific answer...The first thing I would investigate is if the linear logic in those nested SP's can be combined into one statement. – Twelfth Dec 01 '10 at 22:25
  • 1
    code is very particular.the subtle way that you actually code something can have a **profound** impact on how fast it runs. You have given no indication of the size of your data, your table and index structure, the number of rows you are attempting to affect, or even a clue as to what your actually trying to do with the data. There is no way possible to diagnose this issue with the given info. your problem lies in the code. show the tables, indexes, & all the code if you want any real help on this one. If you use `WHILE` or `DECLARE CURSOR` you most likely need to restructure your code. – KM. Dec 01 '10 at 22:25

1 Answers1

2

First when things are slow, there is likely a problem in what you wrote. The first place to look is the execution plan of each stored proc. Do you have table scans?

Have you run each one individually and seen how fast each one is? This would help you define whether the problem is the 8 procs or something else. You appear to have a lot of steps involved in this, the procs may or may not even be the problem.

Are you processing data row-by-row by using a cursor or while loop or scalar User-defined function or correlated subquery? This can affect speed greatly. Do you have the correct indexing? Are your query statements sargable? I see you have a distributed transaction, are you sure the user running the proc has the correct rights on other servers? And that the servers exist and are running? Are you running out of room in the temp db? Do you need to run this in batches rather than try to update millions of records across multiple servers?

Without seeing this mess, it is hard to determine what might be causing it to slow.

But I will share how I work with long complex procs. First they all have a test variable that I use to rollback the transactions at the end until I'm sure I'm getting the right actions happening. I also return the results of what I have inserted before doing the rollback. Now this initially isn't going to help the speed problem. But set it up anyway because if you can't figure out what the problem would be from the execution plan, then probably what you want to do is comment out everything but the first step and run the proc in test mode (and rollback) then keep adding steps until you see the one that it is getting stuck on. Of course it may be more than one.

HLGEM
  • 94,695
  • 15
  • 113
  • 186