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!