Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Performance Expand / Collapse
Author
Message
Posted Friday, December 7, 2012 4:26 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 15, 2014 12:18 AM
Points: 67, Visits: 263
Hi,

I am developing one tool. I need to write to stored procedure for each and every functionality.
Should i write single and call all the functionality in that? OR I can write different Stored procedure for different functionality?

Which will give us good performance? please give idea on this

Regards,
Tony
Post #1393989
Posted Friday, December 7, 2012 5:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, December 18, 2014 12:52 PM
Points: 13,636, Visits: 11,509
Your questions is too vague to give a decent answer on.
You can create different stored procedures for different functionality. I wouldn't want to create one single sp for everything, just out of usability, manageability and maintainability perspective.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1394019
Posted Friday, December 7, 2012 7:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:30 AM
Points: 1,950, Visits: 2,122
What you will need to balance are code reusability vs. performance. If you put too much functionality inside one stored proc, thent he query optimizer will have a more difficult time determining the best execution plan and you may have problems such as parameter sniffing creep up. I'd create different stored procedures for different functionality, as long as the functionality was on different criteria. I suppose to make more sense of this discussion you'd have to tell us what the different functionalities were, how many tables involved, etc.
Post #1394072
Posted Sunday, December 9, 2012 11:16 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, December 15, 2014 12:18 AM
Points: 67, Visits: 263
This tool for search the results in DB and give the output to front end.

There are lot of functionality!
1.Search(10 seperate search functionality) - can be done through 10 to 15 tables
2.Add - Can be done through near 20 tables
...

Def, I will go with different Stored procedure for each functionality
Please let me know if you can give more input.

REgards,
Tony


Post #1394464
Posted Monday, December 10, 2012 3:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 2:16 AM
Points: 2,840, Visits: 3,983
tonyarp05 61903 (12/9/2012)
Def, I will go with different Stored procedure for each functionality
Please let me know if you can give more input.
i will suppport you for this as it will give you more readability and easiness to troubloeshoot any issue.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1394513
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse