>Hi all
>
>I was told a stored procedure is ALWAYS faster than dynamic SQL. However I've read that this is a myth.
>
>I know there has to be reasons where the SP will beat the dynamic SQL - as in cases where the SP has to do major processing where it's proximity to the data will help.
>
>However this seems to indicate a report writer that generates a dynamic SQL should beat a simple SP.
>
>I've got an ODBC connection to the pubs database called PubsODBC. SQL Server and FoxPro are on the same machine. Since both of these tests return the same data across the network, the test is valid, no?
>
>

STORE SQLCONNECT('PubsODBC') TO m.gnConnHandle
>IF m.gnConnHandle <= 0
>   = MESSAGEBOX('Cannot make connection', 16, 'SQL Connect Error')
>   RETURN
>ENDIF
>
>LOCAL m.lnPercentage, m.lnElapsed
>m.lnPercentage = 30
>
>sqlprepare(m.gnConnHandle,"select au_id from titleauthor where titleauthor.royaltyper = ?lnpercentage")
>a=seconds()
>FOR m.X = 1 TO 10000
>  sqlexec(m.gnConnHandle,"select au_id from titleauthor where titleauthor.royaltyper = ?lnpercentage","c_ByRoyalty")
>ENDFOR m.X
>m.lnElapsed = seconds() - m.a
>?"Dynamic SQL",m.lnElapsed
>
>a=seconds()
>FOR m.X = 1 TO 10000
>  sqlexec(m.gnConnHandle,"sp_byroyalty ?lnpercentage","c_ByRoyalty")
>ENDFOR m.X
>m.lnElapsed = seconds() - m.a
>?"SP Call",m.lnElapsed
>
>= SQLDISCONNECT(m.gnConnHandle)

>
>The dynamic SQL is 2 times faster than the SP. Can you guys confirm this?
>
>Thanks!

Mike,
I didn't run the test and I expect to get similar results. But that only confirms "always" is myth not the rest. This SQL is too simple to benchmark against SP.
This is from "Inside MS SQL server 2000 -MS press, Delaney":

"When to use stored procedures and other caching mechanisms
...
Stored procedures Use when multiple applications are executing batches in which parameters are known.
...
The prepepare and execute method Use when multiple users are executing batches in which parameters are known, or when a single user will definitely use the same batch multiple times".

Those are few lines from the book, story of benefits details etc are following. From SQL2000 to SQL2005 there are changes and as new versions come up it looks like SPs would have more pluses. Also a table valued function is sort of an SP IMHO and it accepts parameters.

"I was told a stored procedure is ALWAYS faster than dynamic SQL. However I've read that this is a myth."

I agree "always" is wrong (I had more complex scenarios where SP was slower).
Cetin