Greg Worthey
greg at worthey.com
Wed Jun 25 21:41:55 CDT 2008
Arthur,
I know what you mean. A whole lot of features in sql server (and other
technologies) sound great and advanced in theory, but in the widely-unknown
innards of the beast, and actual practice, many great
features/proper-methods are reduced to big steaming piles. The general idea
of sql, as I understood it, is to have a subsystem that knows all about
optimizing database access/management, and does all sorts of egghead db
optimizations to make the queries/etc super fast. In my experience, on
average, the reality of sql seems to be that it creates huge slogging
bottlenecks in a whole slew of different unexpected ways. (I could swear JET
or Rushmore were far superior!) You can get to become an expert and predict
and prevent an ever-growing list of those ways (e.g. don't use UDF's), but
when you're all done, you have a system that is forced to be rather ugly and
verbose and repetitive where it ought to be (needs to be) elegant and
concise. Creates bloat and bugs and unforeseeable problems of all kinds.
MySQL doesn't have UDF's, stored procedures, or views. But I don't miss them
because those features seem to me so primitive in the full sql
implementations that it sort of makes a farce of the idea of a specialized
db subsystem. In theory, it's great form to separate data layer and app
layers, and so on. But in practice it often just creates whole new fields
and pits of gotchas and half-implemented under-baked theoretically complete
functionality.
It seems to me that more and more we're working against armies of
under-designed features-by-committee.
Greg
----------------------------------------------------------------------
Message: 1
Date: Tue, 24 Jun 2008 12:00:32 -0700
From: "David McAfee" <davidmcafee at gmail.com>
Subject: Re: [dba-SQLServer] Scalar UDFs -- NOT!
To: "Discussion concerning MS SQL Server"
<dba-sqlserver at databaseadvisors.com>
Message-ID:
<8786a4c00806241200x4f10d76axa321088845ce24c2 at mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Arthur, would it be any faster to insert the result (sans udf) into a
temp table (or table variable) then run the udf against the data in
temp table instead of every possible record?
David
On Tue, Jun 24, 2008 at 3:12 AM, Arthur Fuller <fuller.artful at gmail.com>
wrote:
> The problem, insofar as my investigations have identified it (serious
> qualifer there), is that calling a UDF in a WHERE clause causes it to
> execute for every potential row, emphasis on Potential, i.e. every actual
> row investigated prior to the WHERE clause's cutting down the qualifying
> rows. And I think (just guessing) this is where the performance penalty
> kicks in. So let me revise my previous diatribe and say instead "don't use
> scalar UDFs in a WHERE clause".
>
> The UDFs in question did things such as return the PK corresponding to a
> description, i.e. BankAccountType('Savings') might return 3, say. The
> original consultant thought that these UDFs would be good because if
> anything changed, it would only have to change in one place, and to that
> extent he is certainly correct. But in practice it turns out that calling
a
> bunch of these (similar) functions costs us dearly. 23 seconds to zero
> seconds is meaningful, especially given that the sproc in question is
called
> frequently.
>
> New topic:
> Suppose that a web app calls sproc A, passing parm 123, and its execution
> takes 3 seconds.
> Suppose that another user logs on and at approximately the same time
> executes sproc A, passing parm 234.
> Suppose that another user logs on and at approximately the same time
> executes sproc A, passing parm 345.
>
> What happens to the execution time? Does each additional user executing
the
> same sproc with different parms cause the time to multiply by the number
of
> users? Or perhaps it's better engineered than that and some optimization
> occurs under the covers. I don't know enough about the belly of the beast
to
> even guess about this. Does somebody on this list?
>
> TIA.
> Arthur
>
>
> On Mon, Jun 23, 2008 at 6:01 PM, Jim Lawrence <accessd at shaw.ca> wrote:
>
>> Hi Arthur:
>>
>> Does the poor performance persist even when the UDF is called a numbers
>> time? It should load into memory and optimize when called a number of
>> times?
>>
>> Jim
>>
>> -----Original Message-----
>> From: dba-sqlserver-bounces at databaseadvisors.com
>> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
>> Fuller
>> Sent: Monday, June 23, 2008 12:36 PM
>> To: Discussion concerning MS SQL Server
>> Subject: [dba-SQLServer] Scalar UDFs -- NOT!
>>
>> When scalar UDFs first appeared, I was a big fan. No longer. In my
current
>> project, we had a bunch of them and in some sprocs they were called
>> frequently. In one sproc various UDFs were called 57 times (they were
>> emebedded in CASE WHEN blocks and such, mostly, but some were called from
>> WHERE clauses. The first sproc I investigate was taking 28 seconds to
>> execute. I copied it and replaced all the UDFs with hard-coded calls. The
>> execution time went down to 3 seconds. I then tackled another
>> time-consuming
>> sproc (23 seconds) and did the same thing. The results were even more
>> spectacular. Execution time as reported in Query Analyzer was zero
seconds.
>>
>> I'm big enough to eat my own words. I've written various articles about
how
>> cool scalar UDFs are. I take it all back.
>>
>> Arthur
>> _______________________________________________
>> 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
>>
>>
> _______________________________________________
> 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
End of dba-SQLServer Digest, Vol 64, Issue 20
*********************************************