SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Long query's issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AdamWest
Constraint Violating Yak Guru

USA
353 Posts

Posted - 02/12/2013 :  08:42:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3659 Posts

Posted - 02/12/2013 :  08:53:07  Show Profile  Reply with Quote
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

USA
353 Posts

Posted - 02/12/2013 :  08:58:11  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

3659 Posts

Posted - 02/12/2013 :  09:28:28  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000