Re: Moving a VBA function to SQL server

[lyle fairfield]

Tom van Stiphout <no*************@cox.netwrote in
news:kl********************************@4ax.com:

On Sun, 20 Apr 2008 12:26:14 +0200, "Erik Rudbeck" <no******@sorry.dk>
wrote:

If optimized is important, do not put procedural code in your T-SQL
procedures. SQL is a set-based language.

I can think of a third option: have the query return the raw data
columns, and format the jobheader in your VBA code.

-Tom.

<midnight precursor to dementia ramble>

I think there is nothing inherently more efficient or sophisticated in
using SQL udfs or cursors than using VBA, regardless of the snob appeal
for doing so.

It’s likely to be faster for many users to be running VBA procedures and
scanning record sets, each on his/her own workstation with its own CPU
(maybe two or four) than for many users to be running SQL functions and
scanning through cursors all on the same server, unless the record sets
are large enough that time for bringing them over the wire is a factor,
and this often depends on the wire.

Sometimes there are constructions that just seem simpler in SQL, and I
find this particularly so when doing aggregates of aggregates ... of
aggregates, or writing script that alters or creates procedures, views or
functions.

Of course, one may want to keep all the data-centric things in SQL. And
the more that is kept there, the smaller our front end can be and that’s
likely to make it easy to distribute (although clients might say “$16000
for 800 kilobytes? Are you nuts?”)

One must be very careful about writing procedures in SQL that deal with
numbers other than integers. We may think that 1000/ 3 = 333.333.... but
SQL will return 333 unless we explicitly require 1000 to be typed as
float or small money or whatever. Dates can have similar problems. And
NULLs can bite much harder than in VBA where error messages seem to pop
up much more readily than from a server 3000 miles away. If you're going
to write T-SQL with numerical calculations I recommend brushing up on
Convert or Cast beforehand.

</midnight precursor to dementia ramble>

[Tom van Stiphout]

On Tue, 22 Apr 2008 03:52:13 GMT, lyle fairfield <ly******@yah00.ca>
wrote:

Maybe the OP can implement it both ways and report back to us.
It occurred to me that his jobheader is really more a matter of
presentation, so my thinking moves away from the data tier.
It's a good thing cursors are not needed for his solution because they
are exceptionally slow.

-Tom.

>Tom van Stiphout <no*************@cox.netwrote in
news:kl********************************@4ax.com :

>On Sun, 20 Apr 2008 12:26:14 +0200, "Erik Rudbeck" <no******@sorry.dk>
wrote:

If optimized is important, do not put procedural code in your T-SQL
procedures. SQL is a set-based language.

I can think of a third option: have the query return the raw data
columns, and format the jobheader in your VBA code.

-Tom.

<midnight precursor to dementia ramble>

I think there is nothing inherently more efficient or sophisticated in
using SQL udfs or cursors than using VBA, regardless of the snob appeal
for doing so.

It’s likely to be faster for many users to be running VBA procedures and
scanning record sets, each on his/her own workstation with its own CPU
(maybe two or four) than for many users to be running SQL functions and
scanning through cursors all on the same server, unless the record sets
are large enough that time for bringing them over the wire is a factor,
and this often depends on the wire.

Sometimes there are constructions that just seem simpler in SQL, and I
find this particularly so when doing aggregates of aggregates ... of
aggregates, or writing script that alters or creates procedures, views or
functions.

Of course, one may want to keep all the data-centric things in SQL. And
the more that is kept there, the smaller our front end can be and that’s
likely to make it easy to distribute (although clients might say “$16000
for 800 kilobytes? Are you nuts?”)

One must be very careful about writing procedures in SQL that deal with
numbers other than integers. We may think that 1000/ 3 = 333.333.... but
SQL will return 333 unless we explicitly require 1000 to be typed as
float or small money or whatever. Dates can have similar problems. And
NULLs can bite much harder than in VBA where error messages seem to pop
up much more readily than from a server 3000 miles away. If you're going
to write T-SQL with numerical calculations I recommend brushing up on
Convert or Cast beforehand.

</midnight precursor to dementia ramble>