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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Multiple Servers
 New Topic  Topic Locked
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 8

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/15/2009 :  07:50:48  Show Profile
How is this trolling? I'm giving the topic poster enterprise advice.

Also, what is this about deploying to 1000 users? It's a VB.NET application. Because it uses the .NET framework, and app location configuration files, it is possible to let all the users just use ONE application file on a network share. It's the MODERN IT standards, not ancient IT. There is no massive redeploying required (working smarter vs working harder).

With ASP.NET, it's even easier since it's web-eccentric.

At the end of the day....keep designing your way, there are not enough "server overload" and "database timeouts" in this world. You keep hardware vendors in business with the need for another load-balancing server.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 04/15/2009 :  08:08:47  Show Profile
I have enjoyed reading this thread and watching the passion on both sides. Even though I consider myself more of a C# developer and a relative novice at SQL, I am more on the "go with the stored procedure" side.

Two features/products that may be underutilized that I want to point out:

1. When you have thousands of stored procedures, using the filter option in the object explorer is a great feature. Right click on stored procedures in object explorer and select Filter.

2. With the introduction of Visual Studio For Database Developers (aka DBDude) in VS 2005, a lot of the IDE and source control features that C# developers have enjoyed are available to database developers. For some reason, DBDude does not seem to be very popular, and I don't even see many blogs or discussion groups on it - but I used to use it very successfully.
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/15/2009 :  08:18:12  Show Profile
While VS does provide functionality for interacting with the Database, it's not as detailed as the SQL Studio Manager (for an example, writing a complex query where you tend to utility the GUI).

Another point is, you can create an entity model OR use an OR/M. The benefit of this is, whatever you do to the entity model is automatically persisted to the DB model. So if client requests modification to a column (which would be a class-specific property), the entity model would make that change to DB automatically. It's just way faster. This is the future. I've spoken to several IT directors, this is the approach they recommend and they advise against using SPs except for massive data processing operations.

Another point is that it opens a lot of caching possibilities. You can dedicate an entire server for "caching" which will improve performance enormously. It is possible with SPs, but it is MORE complex.



Edited by - whitefang on 04/15/2009 08:19:38
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/15/2009 :  08:40:07  Show Profile
As one .NET developer states: (http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx)
quote:
Stored procedures also will open up a maintenance problem. The reason for this is that they form an API by themselves. Changing an API is not that good, it will break a lot of code in some situations. Adding new functionality or new procedures is the "best" way to extend an existing API. A set of stored procedures is no different. This means that when a table changes, or behaviour of a stored procedure changes and it requires a new parameter, a new stored procedure has to be added. This might sound like a minor problem but it isn't, especially when your system is already large and runs for some time (when it becomes 'legacy' but the amount of time and money invested in the system is that huge that replacing it will cost more than altering the current system).


The difference between application code and stored procedure is that application code has the power of reflection. You can quickly see how the change might affect functionality using reflection. With SPs, you can't.

And this http://www.codinghorror.com/blog/archives/000117.html

One of the BIGGEST points he stated
quote:
Stored Procedures hide business logic. I have no idea what a proc is doing, or what kind of cursor (DataSet) or values it will return to me. I can't view the source code to the proc (at least, without resorting to #2 if I have appropriate access) to verify that it is actually doing what I think it is-- or what the designer intended it to do. Inline SQL may not be pretty, but at least I can see it in context, alongside the other business logic.


There is a reason companies have more programmers than DBAs.


Edited by - whitefang on 04/15/2009 08:47:01
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/15/2009 :  10:03:07  Show Profile
quote:
Your inexperience is clearly showing.

That's a funny! Thanks for giving me my laugh for the day!

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/15/2009 :  10:06:21  Show Profile
Data logic in the database. Interaction logic in the application.
If you can't understand the sense of that, then you need to do some more refactoring. You are wearing the typical developer's blinders that don't allow you to see beyond the piece of code you are writing at any particular moment. The database and the data will likely be around long after the .Net code you write is deprecated.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/15/2009 :  11:56:04  Show Profile
Actually, "Interaction logic" is the front-end where events are triggered. Business Logic is the "data logic/business rules" that are prefably located in the buisness logic layer or the data logic layer.

Other than constraints (in a properly design application, you should not need constraints....that is why you have separate test and production environments), logic (especially business logic) should not be in the database. The core function of a database is to store data and retrieve it fast. When you start adding your application logic and other crap, it's not a database anymore. Overtime, it will be a mess.

Like I said previously, in a properly architectured n-tier enterprise application, there is no need for SPs or data logic or constraints in a database (for critical apps, you may need constraints to prevent loss of data integrity "just in case"). To a n-tier application, a database is nothing more than a way to persist and retrieve the application business objects.
In fact, to a enterprise developer, a row in a table is a entity and a table is a collection of an entity (almost all DBA go against this), it's logical sense.

If you are using SPs and intervening application logic between the application layer and the backend, you are setting yourself up for guaranteed failure in the long-term. It is cheaper to add another application server.....but it is very expensive to add another database server.

Edited by - whitefang on 04/15/2009 12:04:14
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/15/2009 :  13:51:48  Show Profile
quote:
Originally posted by whitefang
If you are using SPs and intervening application logic between the application layer and the backend, you are setting yourself up for guaranteed failure in the long-term.

Uhm....how "long-term" are we talking about? Because I've been doing this 15 years and have yet to see any of my stored procedure-laden databases suffer the fate you have described.
What I HAVE seen, over and over and over and client after client, is applications loaded with business logic written into an application layer that is no longer supported and has to be reverse-engineered and rewritten from the ground up. And what I HAVE seen, over and over and over and client after client, is multiple application interfaces interacting with the same database but bypassing the opportunity of sharing business logic in a sproc layer, resulting in multiple copies of code with often conflicting and inconsistent rules. Resulting, of course, in inconsistent output depending on whether the numbers were pulled from a web app, a Crystal Report, a .Net application, or a linked spreadsheet.
THOSE types of problems, created by short-sighted thinking such of yours, have helped me make many a mortgage payment. Thanks, I guess.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/15/2009 :  16:00:45  Show Profile
The project I'm working on, the IT director team decided to postpone it and I'm sure they are going to just cancel the project. The project has about 2000 SPs, about a 1000 queries. It's just so badly done.

I have no idea about the scenario you're talking about. In .NET, you can interface with a lot of other layers without changing the API. Maybe your scenario applies to 1980s languages like Pascal, but it's invalid for a modern language like .NET. Crystal Reports, other reporting frameworks, and .NET reporting CAN ALL USE object entities so your object model BY DEFAULT should be compatible without changing a line of code.
For an example:
CrystalReport report = AccountingFactory.GetFinancialQuarterReport(); //returns Accounting entity

Where is the modification and interface you're blabbering about???

DBA's shouldn't try to become programmers, they should just stick to administrating the databases. Leave the big stuff to the programmers.

Also, if you're going to argue, at least know the facts instead of regurgitating stuff from 1980s.

Edited by - whitefang on 04/15/2009 16:02:30
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30099 Posts

Posted - 04/15/2009 :  16:26:56  Show Profile  Visit SwePeso's Homepage
Whitefang, you do have one point and I'll give you credit for that.
If you are the sole developer it makes some sense what you are defending.
But most of us here are used to 20+ people working on same application using some kind of SourceSafe team code store.

And for example, what will you do when your application needs to be modified to change the reporting year from 1/1-12/31 to 1/4-3/31 for some departments and 7/1-6/30 for other departments?
You will need to search and replace your complete application's classes to change this.
With an SP, just change some values in a table.

This is just one simple example.
And what happens when you leave the company and next developer are inheriting your code?

And also what happens when you have to make a minor alteration, such as changing an ORDER BY.
With the logic in the database, just change the SP and you're set. No change needed in application layer.

But with your approach, you need to recompile your application and then replace the exe on the unc share and pray that all 1000+ users DO NOT have the application started or otherwise you cannot replace the exe file.

We used this approach for some months in the 90's but quickly changed to stored procedures for many reasons.



E 12°55'05.63"
N 56°04'39.26"

Edited by - SwePeso on 04/15/2009 16:50:32
Go to Top of Page

blindman
Flowing Fount of Yak Knowledge

USA
2365 Posts

Posted - 04/15/2009 :  17:01:10  Show Profile
No, he doesn't know at all what he is blabbering about.
I have NEVER seen a database application of any size that has existed for any length of time at any client that has not had more than one path of interaction. OLTP applications, ad-hoc spreadsheet queries, ETL to and from other data repositories, canned and custom reports, etc.
I've yet to see this application layer you speak of being robust enough to handle all this traffic.
He lives in a fantasy land that exists only until the day he finishes his project. Then it vanishes and the admins and future developers are left to trudge through the swamp that remains.
And he has yet to put forth a decent argument against stored procedures.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/15/2009 :  17:01:50  Show Profile
quote:
Originally posted by Peso

Whitefang, you do have one point and I'll give you credit for that.
If you are the sole developer it makes some sense what you are defending.
But most of us here are used to 20+ people working on same application using some kind of SourceSafe team code store.

And for example, what will you do when your application needs to be modified to change the reporting year from 1/1-12/31 to 1/4-3/31 for some departments and 7/1-6/30 for other departments?
You will need to search and replace your complete application's classes to change this.
With an SP, just change some values in a table.

This is just one simple example.
And what happens when you leave the company and next developer are inheriting your code?

And also what happens when you have to make a minor alteration, such as changing an ORDER BY.
With the logic in the database, just change the SP and you're set. No change needed in application layer.

But with your approach, you need to recompile your application and then replace the exe on the unc share and pray that all 1000+ users DO NOT have the application started or otherwise you cannot replace the exe file.

We used this approach for some months in the 90's but quickly changed to stored procedures for many reasons.



E 12°55'05.63"
N 56°04'39.26"




Again, you're thinking in 1980s design. By the way, our team consist of multi-systems with over a 100 developers just in our branch. I think I know what I'm talking about.

Regarding your examples. First of all, you wouldn't just get a report. There would be a dedicated method for that report OR a reporting engine.
For an example: Accounting.GetFiscalReportByYearRange(range1, range2). Then, you would have a reporting class that has
AccountingReportFactory.GetLastYearFiscalReport()
{
return Accounting.GetFiscalReportByYearRange(2008,2009);
}

So if your pages ever need to change the date range, you can just modify it in ONE PLACE. This is logical architecture design, not 1980 crap.

Secondly, with .NET, you can have a self-patching/heal application meaning that you can deploy the library file into a patch folder that the application is self-aware of and then automatically recompiles itself etc. This is intelligent design.

Maybe you should read up on .NET before you try to argue.



Edited by - whitefang on 04/15/2009 17:02:34
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/15/2009 :  17:04:19  Show Profile
quote:
Originally posted by blindman

No, he doesn't know at all what he is blabbering about.
I have NEVER seen a database application of any size that has existed for any length of time at any client that has not had more than one path of interaction. OLTP applications, ad-hoc spreadsheet queries, ETL to and from other data repositories, canned and custom reports, etc.
I've yet to see this application layer you speak of being robust enough to handle all this traffic.
He lives in a fantasy land that exists only until the day he finishes his project. Then it vanishes and the admins and future developers are left to trudge through the swamp that remains.
And he has yet to put forth a decent argument against stored procedures.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________



Now we're resorting to insults are we? Anyways, I don't really care what a entry-level DBA has to say about application design.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30099 Posts

Posted - 04/15/2009 :  17:07:48  Show Profile  Visit SwePeso's Homepage
A fiscal year do not have to be coherent with calendar year. You do know that?
And you will to have 4000+ classes in memory if there are 4000+ departments in the company?
It means same table has to be read 4000+ times, with 4000+ roundtrips over the network?

And with your healing mechanism, doesn't it mean that the application can exist in several versions at the same time?
If one user hasn't closed the exe file and you are recompiling the application exe, when will the eager user recieve the new binary?

Let me guess? You're born in late '80s, early '90s?


E 12°55'05.63"
N 56°04'39.26"

Edited by - SwePeso on 04/15/2009 17:12:23
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30099 Posts

Posted - 04/15/2009 :  17:25:45  Show Profile  Visit SwePeso's Homepage
quote:
Originally posted by whitefang

Leave the big stuff to the programmers.
I have to enlarge this and post on the wall behind me!
So that whenever I feel blue I just turn around and get well again.

Programmers (.Net or any other vendor) are the reason I not long ago performance tuned an adhoc-query from front end from going on 3 hours to 12 seconds. Or the 10 hour adhoc query I performance tuned to 28 seconds.
Programmers simply doesn't know their database tool well enough.


E 12°55'05.63"
N 56°04'39.26"

Edited by - SwePeso on 04/15/2009 17:28:26
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/15/2009 :  18:07:42  Show Profile
quote:
Originally posted by Peso

A fiscal year do not have to be coherent with calendar year. You do know that?
And you will to have 4000+ classes in memory if there are 4000+ departments in the company?
It means same table has to be read 4000+ times, with 4000+ roundtrips over the network?

And with your healing mechanism, doesn't it mean that the application can exist in several versions at the same time?
If one user hasn't closed the exe file and you are recompiling the application exe, when will the eager user recieve the new binary?

Let me guess? You're born in late '80s, early '90s?


E 12°55'05.63"
N 56°04'39.26"




Wow, do you even know how to design an application? Your first questions in this post seems like you don't.
I was giving an example, I know fiscal year doesn't have to be coherent with the current year. Also, if you have 4000 departments, not a problem. You would have a Department collection.

Let me show you how REAL enterprise programmers do it:
public DepartmentCollection GetFiscalReportsForDepartments(whatever parameters required)
{
DepartmentCollection collection = new DepartmentCollection();
while(sqlDataReader.read())
collection.Add(new Department());
return new DepartmentCollection();
}

That is just a very quick basic sample (not exact).

On the healing note, there are multiple ways to implement it. The application would create a healing thread that monitors if there are any current patches and if there are, it lets the user know that a patch is available and the information on the patch and whether to apply it or not.

All this maybe too advanced for you to understand, looking at your previous posts, I'd say that you are new to application design or programming at a advanced level.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30099 Posts

Posted - 04/15/2009 :  18:09:54  Show Profile  Visit SwePeso's Homepage
*chuckle*



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/15/2009 :  18:10:22  Show Profile
quote:
Originally posted by Peso

quote:
Originally posted by whitefang

Leave the big stuff to the programmers.
I have to enlarge this and post on the wall behind me!
So that whenever I feel blue I just turn around and get well again.

Programmers (.Net or any other vendor) are the reason I not long ago performance tuned an adhoc-query from front end from going on 3 hours to 12 seconds. Or the 10 hour adhoc query I performance tuned to 28 seconds.
Programmers simply doesn't know their database tool well enough.


E 12°55'05.63"
N 56°04'39.26"




I'm not going to debate anymore, it's pointless to debate with entry-level people because they are usually arrogant and don't have the advanced enterprise knowledge.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30099 Posts

Posted - 04/15/2009 :  18:19:14  Show Profile  Visit SwePeso's Homepage
Define Advanced.

http://forums.asp.net/p/1399724/3023525.aspx



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 04/15/2009 :  18:22:38  Show Profile
That's a simple data-entry application. Yeah, that's ONE of the projects I'm working on. What's your point?

The idiot who designed the application wrote massive amount of queries, kind of like the advice you're giving in this thread.
Go to Top of Page
Page: of 8 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Topic Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000