[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