SQL UDF or C# function
-
I have a database that houses (amongst other things) data on defects and repairs, car repairs as an example. The way this db breaks down is this: Table Defect holds the information about the defect (Damaged bumper, broken windshield, w/e) Table Material holds information about materials used to repair (ex: new bumper) Table Equipment holds information about equipment used to repair (ex: buffer) Table Trade holds information about who is responsible for the repair and their wage (ex: mechanic) There are three many to many tables that join defect with trade/eq/mat and have a quantity. Long story short, I have a Scalar value UDF that returns the value of all of these costs together divided by a value in the defect table. I decided to try to simplify it into a query (The simplification discussion is a TSQL issue outside the scope of this post) and in doing so had to ask myself, does this belong as a SQL query or should I write it as an additional property of the model in c#? I could just as easily write a partial class that handles all of this craziness on the web server side. Is this all pedantic? Is it really a question of db server performance vs web server performance? I have read that scalar value functions in TSQL can be low performing due to threading issues, which is what sparked this thing in the first place. Thoughts? Opinions? Cheers, --EA
-
I have a database that houses (amongst other things) data on defects and repairs, car repairs as an example. The way this db breaks down is this: Table Defect holds the information about the defect (Damaged bumper, broken windshield, w/e) Table Material holds information about materials used to repair (ex: new bumper) Table Equipment holds information about equipment used to repair (ex: buffer) Table Trade holds information about who is responsible for the repair and their wage (ex: mechanic) There are three many to many tables that join defect with trade/eq/mat and have a quantity. Long story short, I have a Scalar value UDF that returns the value of all of these costs together divided by a value in the defect table. I decided to try to simplify it into a query (The simplification discussion is a TSQL issue outside the scope of this post) and in doing so had to ask myself, does this belong as a SQL query or should I write it as an additional property of the model in c#? I could just as easily write a partial class that handles all of this craziness on the web server side. Is this all pedantic? Is it really a question of db server performance vs web server performance? I have read that scalar value functions in TSQL can be low performing due to threading issues, which is what sparked this thing in the first place. Thoughts? Opinions? Cheers, --EA
eddieangel wrote:
Is it really a question of db server performance vs web server performance?
Probably. If it is in fact a 'better' application if this specific feature is 'faster' and the speed up is achieved by one or the other (probably via TSQL) then that would be the deciding factor. Other than that it is conceptually business logic but given the complexity of the data entities then I would suspect that it is less complex (lower maintenance) to leave it in the database.
eddieangel wrote:
I have read that scalar value functions in TSQL can be low performing
Which sounds irrelevant without information about the requirements, architecture, design and measured performance of the application (not benchmarks) related to this.
-
eddieangel wrote:
Is it really a question of db server performance vs web server performance?
Probably. If it is in fact a 'better' application if this specific feature is 'faster' and the speed up is achieved by one or the other (probably via TSQL) then that would be the deciding factor. Other than that it is conceptually business logic but given the complexity of the data entities then I would suspect that it is less complex (lower maintenance) to leave it in the database.
eddieangel wrote:
I have read that scalar value functions in TSQL can be low performing
Which sounds irrelevant without information about the requirements, architecture, design and measured performance of the application (not benchmarks) related to this.
Thank you. You are right, "low performing" is immeasurable in the scope of my application. Because of the complexity of using a UDF with Entity Framework I am going to move the calculation to the web server side for the time being. Cheers, --EA
-
I have a database that houses (amongst other things) data on defects and repairs, car repairs as an example. The way this db breaks down is this: Table Defect holds the information about the defect (Damaged bumper, broken windshield, w/e) Table Material holds information about materials used to repair (ex: new bumper) Table Equipment holds information about equipment used to repair (ex: buffer) Table Trade holds information about who is responsible for the repair and their wage (ex: mechanic) There are three many to many tables that join defect with trade/eq/mat and have a quantity. Long story short, I have a Scalar value UDF that returns the value of all of these costs together divided by a value in the defect table. I decided to try to simplify it into a query (The simplification discussion is a TSQL issue outside the scope of this post) and in doing so had to ask myself, does this belong as a SQL query or should I write it as an additional property of the model in c#? I could just as easily write a partial class that handles all of this craziness on the web server side. Is this all pedantic? Is it really a question of db server performance vs web server performance? I have read that scalar value functions in TSQL can be low performing due to threading issues, which is what sparked this thing in the first place. Thoughts? Opinions? Cheers, --EA
I would be asking myself where you are consuming the result, if it is ONLY in your UI then doing the calc in the model is valid. If however you intend to use it in reports then the database should be used. You may also consider storing the result somewhere if it is used in reports. Recalcing it every time you run a report may not be the best option.
Never underestimate the power of human stupidity RAH
-
I have a database that houses (amongst other things) data on defects and repairs, car repairs as an example. The way this db breaks down is this: Table Defect holds the information about the defect (Damaged bumper, broken windshield, w/e) Table Material holds information about materials used to repair (ex: new bumper) Table Equipment holds information about equipment used to repair (ex: buffer) Table Trade holds information about who is responsible for the repair and their wage (ex: mechanic) There are three many to many tables that join defect with trade/eq/mat and have a quantity. Long story short, I have a Scalar value UDF that returns the value of all of these costs together divided by a value in the defect table. I decided to try to simplify it into a query (The simplification discussion is a TSQL issue outside the scope of this post) and in doing so had to ask myself, does this belong as a SQL query or should I write it as an additional property of the model in c#? I could just as easily write a partial class that handles all of this craziness on the web server side. Is this all pedantic? Is it really a question of db server performance vs web server performance? I have read that scalar value functions in TSQL can be low performing due to threading issues, which is what sparked this thing in the first place. Thoughts? Opinions? Cheers, --EA
Hi, From my experience SQL is extremely powerful on data manipulation, and if I understood it right that what you doing here... I see no reason (maybe conformance :)) to do it in C#, not even in a hosted CLR...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is (V).
-
I would be asking myself where you are consuming the result, if it is ONLY in your UI then doing the calc in the model is valid. If however you intend to use it in reports then the database should be used. You may also consider storing the result somewhere if it is used in reports. Recalcing it every time you run a report may not be the best option.
Never underestimate the power of human stupidity RAH
The question of storing the calculated field has been on my mind actually. In theory the calculation could happen one time (Only when the record is changed) and then stored. Otherwise it happens 1500 times (The number of records roughly) every time someone needs to access the data. I think you are right that recalcing is not the best option, in spite of the fact that it has been done that way for a long time. Thanks for the input, going to move it DB side, the amount of extra storage (1 field, 1500 to 3000 records) is negligible compared to the processing benefits. Cheers, --EA
