Re: Set based solution
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 04/08/04
- Next message: Vishal Parkar: "Re: how to do an append query from one table to another table"
- Previous message: Tony C.: "how to do an append query from one table to another table"
- In reply to: Tenaya: "Re: Set based solution"
- Next in thread: Tenaya: "Re: Set based solution"
- Reply: Tenaya: "Re: Set based solution"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 8 Apr 2004 16:28:19 -0500
I know, I know, I know.
I have heard people going on about time and resources thing, but because I
have never been involved with a case where my SQL Server was ever CPU or
memory bound, even when I had thousands of concurrent users, and I was doing
some pretty wild data manipulation to present web pages (I was just serving
up data, but it was pretty complex.) Usually it was the web servers that
were taxed. On "most" systems, nothing is too overly taxed, and the only
performance problems are caused by poor design and infrastructure.
And SQL IS a data manipulation language, hence my problem with not using it
to transform data from one shape to another. It is not like using SQL to
format data is a foreign thing, other than a few cases (like the one that
got this thread started.) Plus it is very doggone good at it. The reason
that I had always used SQL Server to do data formatting for the past 12
years has been because it was so much more elegant of a solution to format
it in a Stored Procedure using set based manipulations and give it to the
client in a use it as it is format. Sure three tier development means that
some of this can be moved out of SQL, but it is so easy to change:
SELECT dateValue
to
SELECT convert(100, varchar(20),dateValue)
instead of coding hooks in a dll to do this formatting, even though it is
usually a better use of resources.
I am not arguing with you at all, I am doing my weekly (or so) try to
justify in my brain that it is better to push off work that SQL Server does
so very well to a place where the coding takes significantly longer to get
done (unless you are using a reporting tool or canned software, then it is
probably already done.)
-- ---------------------------------------------------------------------------- Louis Davidson (drsql@hotmail.com) Compass Technology Management Pro SQL Server 2000 Database Design http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies will be ignored :) "Tenaya" <ct@ct.ct> wrote in message news:e8eouWZHEHA.3528@TK2MSFTNGP09.phx.gbl... > Louis, > > There are two major arguments, at least in my mind, against doing reporting > on the server: > > 1. Time and resources: The server has very finite limits on the resources > available to it ... mostly CPU and memory. It is therefore advantageous in > almost all environments to offload work from the database server so that the > database server can concentrate on doing what it does best ... namely, in a > reporting environment, the retrieval of data. Isn't is better to have a > hundred boxes doing formatting locally rather than a single box doing the > formatting for all those hundred boxes :-) As an aside, by doing the > formatting locally, one can also query the local Windows settings to > determine how best to format things like dates and time rather than having a > single pre-determined format for all users. > > 2. SQL, and T-SQL, is not a general purpose programming language. It is a > very specialized language. It is, as is often noted, a "data manipulation > language". While this does not mean that database vendors have not extended > the ANSI SQL standard, it does have two significant implications. First, by > doing the formatting in T-SQL you likely reduce portability. While you might > not care right now whether the code can run in another database, it is > possible that later the code might have to be ported. By sticking to ANSI > SQL coding conventions, you reduce potential problems. I realize that this > is a little bit like telling a 20-year old to begin saving for retirement, > but when that individual is 65, he's probably going to be a lot happier if > he did start thinking about retirement funds at the age of 20 rather than > waiting till he's 40 or so :-) Secondly, I'll assume you've heard the > phrase "when all you have is a hammer, everything looks like a nail". This > same concept applies here. While T-SQL can be used to do formatting, it's > just not the best tool for the job. Would you defend someone using assembler > language to code most commercial applications just because (a) they know > assembler, and (b) they claim that they can get "optimal" performance. The > latter might be true, but one has to consider the expense. Even if > performance could be doubled, would the expense and cost of coding in > assembler be worth it ... keeping in mind that coding in assembler would > almost certainly entail an order of magnitude (if not more) increase in cost > and delivery time. > > I'm not trying to "pick an argument" with you. I'm just trying to elaborate > on why most SQL professionals voice this opinion. > > Chief Tenaya > > > "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message > news:OtZBJkXHEHA.2260@TK2MSFTNGP09.phx.gbl... > > > T-SQL is not a reporting language. You can return the results in a > single > > > query if you perform the data formatting in your application. This will > > > improve performance and provide more flexibility. > > > > This is a statement that I still struggle with. T-SQL is a data > > manipulation language, and you need to manipulate data to do reporting. > It > > is also true that row by row actions are usually easier to do on the > > presentation layer, but will it always be faster? > > > > Consider this set for a report > > > > Col1, ..., Col100, RolledUpColumn1 > > ... > > Col1, ..., Col100, RolledUpColumn100 > > > > If Col1 through Col100 contain the exact same values for every row, and we > > want to display the RolledUpColumn as: > > > > Value1, Value2, ..., Value100 > > > > If we had a solid method of doing this at the data layer, it would > certainly > > be faster than the alternative. Now if you had additional groups applied > to > > the set, Col1!...Col100!, it would get really bad. > > > > Is is terribly wrong to use SQL to manipulate data in a set wise fashion, > > even if the final output is not a proper normalized table? I never hear > > anyone tell someone that is would be better to do other aggregate type > > functions on the front end: > > > > Select value1, value2, sum(value) as value3 > > ... > > group by value1, value > > is considered okay, so why not: > > > > Select value1, value2, concatenate(value) as value3 > > ... > > group by value1, value > > > > This is one of the first aggregate functions I will create when given the > > possibility, since this kind of thing annoyingly pops up all of the time, > > since users don't always think in normalized structures, they frequently > > think in lists. > > > > -- > > -------------------------------------------------------------------------- > -- > > Louis Davidson (drsql@hotmail.com) > > Compass Technology Management > > > > Pro SQL Server 2000 Database Design > > http://www.apress.com/book/bookDisplay.html?bID=266 > > > > Note: Please reply to the newsgroups only unless you are > > interested in consulting services. All other replies will be ignored :) > > > > > >
- Next message: Vishal Parkar: "Re: how to do an append query from one table to another table"
- Previous message: Tony C.: "how to do an append query from one table to another table"
- In reply to: Tenaya: "Re: Set based solution"
- Next in thread: Tenaya: "Re: Set based solution"
- Reply: Tenaya: "Re: Set based solution"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
