John..

In our discussions, I have NOT hyped the performance aspect to you. Rather, I have emphasized two other issues:

Security
Control

For me, performance is not the central issue. Why? Because SP's at worst, are AS FAST as RV's. And assuming for the moment that performance is exactly the same, look at what I get in return: better security, more granular control.

SO, before you start casting a big blanket over all the arguments in that they are only concerned with performance, you should re-read the posts. I have addressed this issue from 3 areas:

Security
Control
and
Performance

As somebody who is interested in the "facts", why don't you be factual and properly address ALL the arguments that have been put to you. FWIW, whether you get it or not is not a concern to me. For sure, there are a lot of people lurking here for some good information. From my perspective, I cannot let one side drive the discussion - epecially when that view point is extremely narrow in that it is only concerned with the peformance aspect of things.

When you get down to it, the performance aspect is really a red herring. From your perspective, you may be thrilled with the performance of your app. I have little doubt that you are pleased with the performance. And for you, that may be all of the story. For the subject matter we are discussing however, it is not the whole story. And that is why I am bringing up the other side of the discussion here..

A few things from your quote interested me:

"Direct execution is commonly used by applications that build and execute statements at run time and is the most efficient method for statements that will be executed a single time."

I would submit that insert, update, delete, and query procs are executed more than once.

Your thoughts??? And please, would you mind addressing the security and granularity of control issues I have raised???

Thanks,

< JVP >




>Charlie
>
>IMHO this is a severely circular argument. People apparently don't like RVs because on the "read" side, a RV "might" send an entire SQL command that may be pretty big, across the wire. That's assuming you don't use select *.
>
>Then on update the same people are willing to send each and every field back- hardly an advertisement for efficient use of bandwith and unnecessary extra work for the server that has to update every single field even when only one has changed.
>
>What about field-level contentions? Modern (for example) operating theatre apps rely on multiple users updating same-row fields every few seconds and not overwriting each others' work with stale material. Updating every field every time creates a contention nightmare for such an environment, slowing notoriously critical clinicians down with stupid contention decisions they do not have time for and creating still more cynicism about IT. RV or SPT solves this completely.
>
>As for the "performance" of optimised SP vs SQL: I refer to the existence in odbc of the "create temporary stored procedures for prepared SQL statements..." option that, apparently, speeds up passed SQL if you use stereotyped parameterised queries as RV does.
>
>At least, this was true prior to SQL 7 where such tactics apparently did make a difference. But this may not be the case with SQL7 whose "direct" as opposed to "prepared" SQL execution may be very similar.
>
>No I did not just make that up. Quoting from the SQL7 docs:
>
>< start quote >
>Direct execution is commonly used by applications that build and execute statements at run time and is the most efficient method for statements that will be executed a single time. Its drawback with many databases is that the SQL statement must be parsed and compiled every time it is executed, which adds overhead if the statement is executed a number of times.
>
>When connected to versions of SQL Server earlier than 7.0, direct execution should be used:
>
>Whenever a statement is likely to be executed fewer than four times.
>To call stored procedures.
>
>SQL Server 7.0 significantly improves the performance of direct execution of commonly executed statements in multiuser environments. For SQL Server 7.0 applications, using SQLExecDirect with parameter markers for commonly executed SQL Statements can approach the efficiency of prepared execution.
>< end quote >
>
>This seems fair- after all, VFP's own execution of macro-substituted SQL is fast+++ once the parsing engine has loaded. SQL just isn't that complicated, if that's all you're doing.
>
>So: at least some effort to determine the "facts" rather than "slogans" is urgently needed IMHO. The "obvious" principle that I was always prepared to accept, that SP is faster, may no longer be so "obvious" in every case- an argument for the "why?" that is supposed to characterise a practical profession. We need to ask it.
>
>As for the "Emperor's clothes"... if the Emperor is naked, or turns out to be wearing only a loin-cloth rather than the sumptuous robe others describe, I claim credit for noticing!
>
>Regards
>
>JR