>When you create a stored procedure, you wouldn't necessarily do it the same way as you would if you were using fox. For example, the test you have setup returns two author ids times how ever many iterations you have. As I mentioned in another thread, the preferred method would be to let SQL server do all the work and the return the results. Here is an example stored procedure that returns the 10000 iterations in .5 seconds (or 100,000 iterations in 6 seconds).

You've completely missed the point. I'm simulating a thousand users hitting the server to run 1000 queries. Each of them calling a stored procedure is worse than if they ran the query directly.

Your test would be like all of the queries being cached and returned to one user.

There are times when a stored procedure can be beneficial, but this isn't one of them.

>
>

>-- example of how to call the procedure
>-- exec test_byroyalty @iterations = 10000, @percentage=30
>create procedure dbo.test_byroyalty
>( @iterations int = 100000,
>  @percentage int =30
>)
>as
>set nocount on
>
>declare @au_table table (au_id varchar(11))
>
>declare @counter int
>select @counter = 0
>while @counter < @iterations
>begin
>  insert into @au_table
>    select au_id
>    from titleauthor
>    where titleauthor.royaltyper = @percentage
>  select @counter = @counter + 1
>end
>
>select * from @au_table
>

>
>
>
>>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!