SQL Server Performance Forum – Threads Archive

Stored Procedure performance

Hello, My project is running a group of storeed procedures on the database.
The Problem is there are about 150 stored procedures,
There is one CompanyMaster table which contains about 2500 records (the no may incerase). I have to run these 150 stored procedures for each of the record in CompanyMaster. I wanted to ask, which of the following will be faster.
1 running all 150 stored procedutres for each row in CompanyMaster, by passing
CompanyId to each stored procedure 2 Running these stored procedures for all the records at once
Not passing the CompanyId to them These stored procedures are for checking the consistancy of the data in the related tables. So they need to be applied on all the rescords in Master table. If the records is ok we will be transfering it to some other database, only the records which pass the consistancy check will be transfered to the other database and rest all will contain some flag to indicate ‘not passed Consistancy check’ pls help in this concern Thanks in advance Pallavi

if you are sure that all the records are going to update then I think it will be better to update all records rather than selecting them one by one. for more information on SP tunning
http://www.sql-server-performance.com/stored_procedures.asp
—————————————-
Cast your vote
http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka

As the rowcount is not big enough to worry, I would suggest first option will be better. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Generally speaking set operations are better than row by row processing, but again sometimes it may not be possible or may prove otherwise.
If I understand correctly, even though you have 2500 records in companymaster table
which may not be significant from perf standpoint, but here performance would also depend on related tables. ( Another question is Are your procedures written such that they can handle both the scenarios without needing a change ? IF you need to change for either of the case then the time it takes to change also should be considered, which may offset the perf gain )

Thanks for all your replies, Actually the process is going on , we have not yet developed lot many stored procedures, so making change is possible at this stage. so thought of taking openions. thanks.
the process is first we are transfering the data from paradox to SQL, and then we have to check that data for consistancy. then we have to report this in consistant data back, and only the consistant data will go to the central database i am doing the consistancy check part till now i have developed onlyu 10 procedures. so its not too late. lets see what pm says well thanks to all opf u again. I will be definately needing more help onwards
Pallavi

]]>