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 |
AdamWest
Constraint Violating Yak Guru
360 Posts |
Posted - 2013-02-12 : 08:42:50
|
HI I am looking at some query's at a company that is looking for report writing help.They have some querys that are 1000 lines of code. To me, this does not seem most effcient. But do processes necessarily take longer if they are written all in SQL rather than in a programming language.Some examples of what they do in SQL which I would do in C #: my question is, should the effort be made to convert these large sql's to a language, for what benefit? not so concerned about neatness or ease of use, the business runs as it is and my role is to create and enhance some critical reports.there are many joins, and joins where something = ' 'or there are these calcs as well:case when av.qty >= 0 then n_b.qty when (av.qty+n_b.qty) <= 0 then 0 when av.qty < 0 then (av.qty+n_b.qty)end as qty, |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-12 : 08:53:07
|
There are some things that a C# code can do better, some others that SQL code is much better at. For set-based queries, (where you might process the data in a loop in a C# program), there is really no comparison - SQL is far and away the best choice.The number of lines in the stored procedure itself is not an indicator of bad programming. The case expression you posted does not seem unreasonable either. So without looking at the code and understanding the logic, it would be hard to say whether it is poorly written, and whether it would perform better if done in C# or another language.There are couple of things you can look for though: If you see any CURSORs, more likely than not, that is an indication of bad programming. Not always, but 99 percent of the time. Similarly, if they have WHILE loops, that is probably and indication that there is room for improvement. Not always, but 98 percent of the time.There are free tools available to beautify SQL code; there are commercial ones available as well. Usually it is much easier to read and understand the code if you do beautify. |
|
|
AdamWest
Constraint Violating Yak Guru
360 Posts |
Posted - 2013-02-12 : 08:58:11
|
I have not seen CURSOR but there are quite alot of WHILE.In what way could you improve on the WHILE?quote: Originally posted by James K There are some things that a C# code can do better, some others that SQL code is much better at. For set-based queries, (where you might process the data in a loop in a C# program), there is really no comparison - SQL is far and away the best choice.The number of lines in the stored procedure itself is not an indicator of bad programming. The case expression you posted does not seem unreasonable either. So without looking at the code and understanding the logic, it would be hard to say whether it is poorly written, and whether it would perform better if done in C# or another language.There are couple of things you can look for though: If you see any CURSORs, more likely than not, that is an indication of bad programming. Not always, but 99 percent of the time. Similarly, if they have WHILE loops, that is probably and indication that there is room for improvement. Not always, but 98 percent of the time.There are free tools available to beautify SQL code; there are commercial ones available as well. Usually it is much easier to read and understand the code if you do beautify.
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-12 : 09:28:28
|
Can you post a sample? Pick the shortest while loop and post it. It may be that it is one of those cases where one has to have a while loop, but in most cases that is not the case - one can rewrite it using a set-based query.Even if you post the while loop, I may not be able to propose an alternative, because one would need the table schema and an understanding of the business logic to be able to rewrite it. But, post it if you can. |
|
|
|
|
|
|
|