Re: Set based solution

From: Tenaya (ct_at_ct.ct)
Date: 04/09/04 

Date: Thu, 8 Apr 2004 21:46:06 -0400

Louis,

First, thanks for understanding that I wasn't "attacking" you or anything
like that :-)

The bottom line, of course, is if it works, then fine and good. I'm not such
a purist that I'm going to insist that code conform to some theoretical
ideal. There have been times when I was in situations where I coded some
"amazing" things in T-SQL, that should never have been coded in T-SQL in the
first place.

But I'd still argue that if one "knows" the logical arguments for moving
coding off the database server, then it is worthwhile doing it. It sort of
goes back to the concept that while it might work today, under the current
workload, the same coding might labor unnecessarily down the road ... and if
I can avoid a potential problem in the future, then I think it's worthwhile
to do so. Forewarned is forearmed :-)

At the very least, I'd argue that formatting things that differ from country
to country ... date and time formats, or even just the representation of
numerics ... is better done closer to the client than at the database
server. As I mentioned in my first post ... isn't it better to query the
local Windows settings to determine what the user wants to see re: a date or
a time format, or whether a comma or a decimal should be used to separate
the fractional part of a number.

I won't argue that it's easier to make a change in one place than in many
places, but I do contend that it's even better to accomodate a user's wishes
rather than have to explain why one's application is "locked" into a
particular display that ignores the local Windows settings.

But, again, bottom line is it may be better not to change something if it's
not broken :-)

Chief Tenaya

"Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
news:%23DdWqBbHEHA.3880@tk2msftngp13.phx.gbl...
> 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
:)
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Set based solution
    ... There are two major arguments, at least in my mind, against doing reporting ... The server has very finite limits on the resources ... hundred boxes doing formatting locally rather than a single box doing the ... SQL, and T-SQL, is not a general purpose programming language. ...
    (microsoft.public.sqlserver.programming)
  • Re: Stupid Question
    ... My case One server and Internet. ... > can get that from SQL Server, ... In the case you are describing it does seem doing the manipulation within ... Need to get all the orders for a given client for the current week - some ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL question
    ... In this specific case, let your database layer fetch the data, and then use ... your presentation layer for formatting that data and displaying it the way ... server to client - that's always a "good thing" to do. ... you need to use SQL Server's SUBSTRING method - look it up in SQL ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Where can I set the output format of a database date field ?
    ... This date that you are displaying, is this the System Date or the SQL date? ... i.e. are you using or Select GetDatewith formatting? ... The first one I believe uses the regional setting, ... Options->Customize of the Server and the Workstation and also ...
    (microsoft.public.frontpage.client)
  • Re: Missing Format() - Function
    ... >i have to translate SQL-Statements from Access 2000 to SQL ... >at SQL Server? ... Formatting is not a task that the server ... such as CONVERT, CAST and STR. ...
    (microsoft.public.sqlserver.mseq)