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