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.


    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.