Re: Set based solution

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 04/09/04 

Date: Fri, 9 Apr 2004 09:16:51 -0500

Yes, these arguments are why I am starting to agree with it. If I could
work with UI programmers that had even half the "purist" beliefs of some of
the relational programmers here in this group, it would be a lot easier to
do it right.

Thanks, 

-- 
----------------------------------------------------------------------------
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:OpTKtRdHEHA.3696@TK2MSFTNGP10.phx.gbl...
> 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
    ... And SQL IS a data manipulation language, hence my problem with not using it ... that I had always used SQL Server to do data formatting for the past 12 ... instead of coding hooks in a dll to do this formatting, ... SQL, and T-SQL, is not a general purpose programming language. ...
    (microsoft.public.sqlserver.programming)
  • Re: How do i move an SQLDatabase to another location?
    ... I went to my clients and installed SQL Server ... Express and copied my database to the same location it was in while i was ... knowing that i can bring a database with me and install ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: New to Windows CE Development - Have some questions
    ... validating against the main database, or querying the database during data ... iterate through the SQL CE table and create new entries in the ... SQL Server database that correspond. ... >>> not connected to the Enterprise. ...
    (microsoft.public.windowsce.app.development)
  • RE: Logging in in background
    ... wold take all kinds of modifictions as I'd need to be checking that each SQL ... my database and all have connection strings associated with them. ... I suspect that there is also an issue on the SQL Server side as I keep ...
    (microsoft.public.access.modulesdaovba)
  • RE: SBS 2003 Unable to connect to database STS_Config
    ... MSDE to store SharePoint uses the MSSQL$SharePoint database, ... SharePoint named instance to full SQL. ... You cannot upgrade the Monitoring instance to SQL ... What I see is the SQL server group and under ...
    (microsoft.public.windows.server.sbs)