The 'benchmark' code

I've decided to post the code I used to test what's faster: dynamic queries or stored procedures with optional parameters. The code can be found here. Let me add a disclaimer here, that I'm not pretending to have done scientific research or other scientific benchmarking. All I've done is wrote a couple of routines which represent for me a real life situation using either one of the techniques. Of course the routines can be sped up and recoded in other forms, and perhaps I've made a mistake in the code which results in the slow speed of either one of the used techniques. Feel free to comment :)

1 Comment

  • Frans,


    Here is my test on a Celeron 1.8 running XP Pro. I can run more tests against separate servers later.





    The dynamic query wins by 17 seconds.


    Dynamic - Total time: 00:01:33.5845680.


    StoredProc - Total time: 00:01:50.7292208.





    This is good news, since I am building a routine to copy data between tables on different servers using DB2/400. The class uses a select statement to retrieve the source data, then creates an insert statement from the source dataset for the target.





    I wanted to handle this for many tables and potential libraries and did not want to create and manage all the stored procedures. I don't care if the table structures change and have a generic copy routine.





    I will wrap this in a web service and use it to copy policies between our production and test libraries that reside on separate servers.





    I enjoy reading your common sense and practical articles and comments.





    Ron





    Dynamic query benchmark


    Benchmark started on: 6/13/2003 12:14:45 AM.





    Benchmark ended on: 6/13/2003 12:16:19 AM.


    Total time: 00:01:33.5845680.


    Amount of runs: 10000. Max. amount of rows retrieved: 830


    Stored procedure benchmark


    Benchmark started on: 6/13/2003 12:16:19 AM.





    Benchmark ended on: 6/13/2003 12:18:09 AM.


    Total time: 00:01:50.7292208.


    Amount of runs: 10000. Max. amount of rows retrieved: 830


Comments have been disabled for this content.