Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-03-25 : 08:06:49
|
| Bob writes "Hello,I didn't find this answered in the manual or in the FAQ. Hopefully it's something new (or I'm a bonehead)!I'm a C# developer, but lately I've had to rewrite a lot of T-SQL sprocs. The original sprocs included a good bit of complex logic, which was originally migrated back to the DB from the middle tier for performance reasons. Over time, the sprocs got out of synch with each other and therefore yielded inconsistent results. I went in and modularized the sprocs into functions and smaller, single-purpose sprocs so as to reduce the number of maintenance points (shooting for better encapsulation, modularization, reuse, etc.).However, our DBA saw my work and said it "looked like it was written by a C# developer", implying (correctly) that I knew nothing about writing T-SQL that would achieve good DB performance. He said we'd have big performance problems because I had sprocs calling other functions and sprocs.So we've reached a point where, at one extreme, we can copy-and-paste logic so everything is inline and therefore "fast" (which is the way our DBA is leading), or at the other extreme, we have single points of maintenance and good reuse at the cost of theoretically bad DB performance. Is what he's saying correct? If not, we're going with modularization because we, not he, are the ones fixing all of the bugs (not fun). If he's right, there a way to resolve this without reverting to copy-and-paste?Thanks for your help!" |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-03-25 : 08:23:45
|
| Modularization of procedures is not necessarily bad but typically the only way to process a "set" of data with modular procedures is iteratively (ie. looping throught the recordset row by row and calling the proc(s) ) and this is very inefficient for SQL Server as it performs most efficiently using set-based processing. Some performance gains may be realized by coverting procs to udf's but it is difficult to make specific recommendations without specific information. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-03-25 : 08:32:05
|
I agree with ehorn, I was writing this while he posted so if it repeats what he says, well, it's because he's right.  The truth is, both of you have valid points. Modularization is a good thing, but it takes a different form in SQL. You want to avoid taking an approach that works on individual rows of a table. No cursors. Not one. Ever. Don't use UDF's to do things that regular SQL can do. For example, let's say you have a customer table and an orders table, and you write a function to return the number of orders a customer has by passing the cust_id:SELECT CustomerName, fn_orderCount(cust_id) AS OrdersFROM CustomerTableThe UDF would open a new query as it processes each row of the CustomerTable. It would be akin to:SELECT CustomerName, (SELECT Count(*) FROM Orders WHERE Orders.cust_id=CustomerTable.cust_id) AS OrdersFROM CustomerTableThese are far less efficient than:SELECT CustomerName, Count(*) AS OrdersFROM CustomerTable C INNER JOIN Orders O ON C.Cust_id=O.Cust_idGROUP BY CustomerNameUsing a JOIN with Count() and GROUP BY works on the entire set as one operation. It will perform less I/O and can be optimized by SQL Server's query optimizer.You don't have to copy and paste logic into everything, but you do have to look at performing operations on SETS of data, not ROWS of data. This isn't a trivial issue for you as a developer, but don't worry, you're not the first nor alone in this challenge.  I'd say the best approach is to learn SQL, just learn it, find out what it can do first. THEN take that knowledge and apply it to solving a problem. Don't look at using SQL the same way you use C#; they're different, and you'll end up with lousy SQL if you try.Your DBA is pretty sharp BTW, you may not agree with everything he says but he sounds like he's had enough experience to know what works and what doesn't. I'd suggest getting his advice on what would be the best way to do something before getting too deeply into it. Procedures and functions should be written so that they would be useful OUTSIDE of your application (someone could open Query Analyzer and run it) I can definitely vouch for the fact that too many developers write too many database apps from the point of view of developers, and it's really painful looking at their code (I'm talking about major commercial software, people actually charge MONEY for it, A LOT, even you would cry if you saw this code) |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
413 Posts |
Posted - 2004-03-25 : 08:52:08
|
| I assume your DBA has something like this in mind http://www.sommarskog.se/dynamic_sql.html#Dyn_tableWhile modularization is a good approach in programming, even in database programming to a certain degree, writing generic code like mentioned in the article is, hm, let's say bad.--Frankhttp://www.insidesql.de |
|
|
|
|
|
|
|
|
T-SQL sproc modularization: a performance killer?