Re: Dynamically selected columns with column switch option
From: DC (dc_at_upsize.de)
Date: 02/28/05
- Next message: Klaus Aschenbrenner: "Managed Stored Procedure Performance"
- Previous message: Neil: "Re: Machine Name In Trigger"
- Messages sorted by: [ date ] [ thread ]
Date: 28 Feb 2005 00:32:34 -0800
"--CELKO--" <jcelko212@earthlink.net> wrote in message news:<1109427772.910829.104260@g14g2000cwa.googlegroups.com>...
> They would avoid dynamic SQL and do the order of presentation of the
> columns in the front end. I have become a fan of the idea that one
> team in the shop handles the database and writes all the SQL for
> everyone, and the application developers make requests to that team.
Two good advises which collide with out original requirements (which
we are about to change though). This is a typical scenario that I am
facing more often so it might be worth to bring it up again:
- Our application provides XML data directly to consumers using FOR
XML clauses.
- There are just about ten stored procs to retrieve this data (like a
search proc, a proc that returns top x results from a predefined
list...) but each proc can return data from about 80 columns (some of
them derived).
- In some cases the comsumer might just require one output column, at
other times 40 different output columns. To keep this as flexible as
possible and to minimize the size of the returned xml structures, we
have decided to provide a web user interface where the comsumer can
enter the required columns and give this set of columns a name which
the middle tier passes to the stored procs.
- This has worked fine so far but as you can see from my previous
posts we are now in the middle of "dynamic sql hell" since our
requirements expanded.
So we are facing a design problem here. The conclusion from your post
- if I get the idea right - is that we "normalize" our requirements
and make a limited amount of "column sets" available directly through
the stored proc, which will be designed by the sql team and on request
they might have to add new requests with new column sets.
The tradeoff is, that the consumers won't be able to perfectly suite
their "column sets" to their requirements, they will have to look for
a predefined set and receive unused columns (and consume additional
bandwith which they pay for). Or they will ask out sql team to
specifically suite a stored proc for their requirement and pay for the
development time.
Another option is to place the "column select" logic into the middle
tier and have sql server typically select a broad range of columns and
pick the ones required in the middle tier. However, these relatively
broad SELECT statements will force the sql server to produce huge
resultsets and one of our initial design requirements was to limit the
sql server output bandwidth where possible.
So in essence we are trading good design vs. sql server output
bandwidth. I am posting this lengthy statement hoping for some rule of
thumb on how to weight these requirements.
- Next message: Klaus Aschenbrenner: "Managed Stored Procedure Performance"
- Previous message: Neil: "Re: Machine Name In Trigger"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
