[dba-SQLServer]Stored Procedures and Arrays?

Covered all the bases there Arthur.

By the way a contact at MS passed me to pass this on to anyone interested in
SQL Server web Bloggs

http://www.sqljunkies.com/WebLog/


Martin


----- Original Message ----- 
From: "Arthur Fuller" <artful at rogers.com>
To: "dba-sqlserver" <dba-sqlserver at databaseadvisors.com>
Sent: Wednesday, August 13, 2003 10:42 AM
Subject: RE: [dba-SQLServer]Stored Procedures and Arrays?


> I'm prepared to be persuaded otherwise, but at first blush it seems you're
> sacrificing huge performance for convenient code. We could try a couple of
> tests with 50,000 records to verify this, but I'm pretty sure I'm right by
a
> wide margin. Your approach will never get the built-in sql optimizations,
> and worse, suffer run-time compilation for every execution, since the
sproc
> can't possibly know what to expect.
>
> Not to mention that the code will growtesque :-)
>
> If @OrderByParm = 'x'
> Else
>
> IMO, yuck!
>
> IMO this is the wrong approach. Better to define a sproc that retrieves
> precisely what you want for every combo, no matter how many there are.
With
> a naming scheme you'll know exactly what to use when (for new forms) and
> what each in the list is for. FE:
>
> ap_CustomerID_ListByName =
>
> ap = app sproc (as opposed to sys proc)
> for combo CustomerID
> sort order obvious
>
> A sproc such as this is reusable, but more important with large dbs, it's
> optimized. With large tables, I would guess at least twice the performance
> and wouldn't be surprised at thrice.
>
> >From the FE pov, imagine that there is a sort-order (or filter) control.
The
> user selects a new value and all you do is swap sproc-names into the
> record/row source. Access automatically refreshes and you have your new
data
> and that's that. A simple Select Case block, and as the app grows you add
> two lines per growth, and another sproc to the BE. I'd rather do that and
> gain the speed than write one generic sproc that can handle anything.
>
> A.
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Martin
> Reid
> Sent: Wednesday, August 13, 2003 3:56 AM
> To: dba-sqlserver
> Subject: [dba-SQLServer]Stored Procedures and Arrays?
>
>
> Hi All
>
> Any comments on the following in terms of performance or anything else
that
> springs to mind. I have a couple of concerns over this approach re
> recompiling of the SP every time it is called.
>
> Say I want to build a drop list on the form for the user to select from
and
> the drop list is from a table.  Now, the application has many forms with
> drop lists. Instead of having a separate SP for each drop list, we create
a
> generic SP that accepts the field names as a string separated by commas.
In
> my code, for each form, I would define what these fields would be in an
> array.  Then, with the VB join function, the array is made into a comma
> separated string of strings that is passed, along with the table name, to
> the generic stored procedure.  The stored procedure will take these two
> parameters and convert it into a select statement, process it and return
the
> data for my code to process into the drop list.  Another parameter can be
> passed for setting the field or fields that are used for ordering the data
> (for example order by name).
>
>
> Martin
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>