Re: Set based solution
From: Tenaya (ct_at_ct.ct)
Date: 04/08/04
- Next message: anonymous_at_discussions.microsoft.com: "Re: Tables size"
- Previous message: Joe Celko: "Re: update with join"
- In reply to: Louis Davidson: "Re: Set based solution"
- Next in thread: Louis Davidson: "Re: Set based solution"
- Reply: Louis Davidson: "Re: Set based solution"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 8 Apr 2004 14:16:56 -0400
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: anonymous_at_discussions.microsoft.com: "Re: Tables size"
- Previous message: Joe Celko: "Re: update with join"
- In reply to: Louis Davidson: "Re: Set based solution"
- Next in thread: Louis Davidson: "Re: Set based solution"
- Reply: Louis Davidson: "Re: Set based solution"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
