Optimizing data access using parameterized queries

Question

I am looking into using parameterized queries in my VB 2010/ADO.net app and found this resource:

The ins and outs of parameterized queries

Something that the author mentioned got my attention and I want to confirm that I understood the issue he discusses. Halfway down the page where the author starts the paragraph with "We arent there 100% yet." he mentions that the command object should not be created everytime the query is executed. At this time when I execute a query I do something like the following: (air code)

Start process

dim cmd as SQLCommand

cmd.CommandText = "select * from MyTable where XField=@Value"

cmd.Connection = MyDBConnectionObj

cmd.paramters.add(...)

dim MyData as SQLDataReader = cmd.ExecuteReader()

'''Do work

End process

In my case, I am rebuilding the command object every time I execute a query. If I understood the article referenced above, I should initilize a command object for this query, leave it alone and when I wish to run the query I just update the value of the parameter without having to go through the above code.

If this is so then I could initialize different command objects (when the app is run), each for a specific query. When I need to run the query I just update the parameter(s) and execute the query using its own command object. Did I understand that correctly? Thanks for reading this and any feedback given. Regards, Saga

---

Insanity is the prelude to discovery

Answer 1

If this is so then I could initialize different command objects (when the app is run), each for a specific query. When I need to run the query I just update the parameter(s) and execute the query using its own command object. Did I understand that correctly? Thanks for reading this and any feedback given. Regards, Saga

Hello Saga,

You can reuse the command object, if you like; but you can also always create a new command.

One advantage of parameterized queries is, that's SQL Server don't need to compile them every time, it can reuse the cached query plan, because only the parameter value changes.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Thanks for your help. So if I understand this correctly, I can destroy and rebuild my command object as many times as necessary and this will not cause the server (SQL Server) to recompile the query plan, as long as I don't modify the query itself. Is that correct? This is very interesting because I see a different way of doing the query execution. At this time I am changing all the queries so that they are not modified. While this tedious activity is driving me up the wall, I think the results will be worth it. Thanks again! Saga

---

Insanity is the prelude to discovery

Answer 3

Yes, that's right. It doesn't matter which command object sends the SQL statement, you could also end and restart you application with loosing the connection to SQL Server; as long as the SQL statement is the same, the cached plan will be used.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 4

That's fantastic! Thanks for your time and assistance Olaf. Saga

---

Insanity is the prelude to discovery