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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Long query's issue

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.
Go to Top of Page

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.

Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -