Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dbwilson4
Yak Posting Veteran
50 Posts |
 Posted - 2007-07-07 : 01:06:52
|
| I'm doing a very large complex query with lots of command parameters and I just found out that command parameters are BAD for complex queries. In fact, my query takes 30 seconds to finish with command parameters and 0 seconds to finish without command parameters (all caches cleared etc) |
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2007-07-07 : 04:41:30
|
| The reason for this is.Let say you are sending 50 input parameters.Now the OLEDB provide or anyother provide will have to break all parameters including the stored procedure name into small packets and send it to the database. Now in the database end, database engine will retrive all the command and parameter parse them and execute. The result of command is send back to the front end in the same way (broken down in small packets and sent to the front end.)In your case when you are not sending parameters you are saving time and effort put by provider and Database engine. Moreover network communication costs is also decreased as they dont have to travel though the wires to the database engine.ThanksSachinDon't sit back because of failure. It will come back to check if you still available. -- Binu |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
dbwilson4
Yak Posting Veteran
50 Posts |
Posted - 2007-07-07 : 12:37:10
|
| I know about parameter sniffing but this doesn't help with my issue because I'm building a very long dynamic sql paging query with complex selective filters and correlated queries. Do you think using command parameters will increase overhead? (theres about 20-30 command parameters and all of the parameters have "part" of a guid) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-07-07 : 12:58:54
|
| Then show us some code so we can help you out; otherwise, all we can do is guess. My guess is that your paging sql statement isn't as efficient as it good be. Command parameters will never increase overhead, and will make your sql code actually shorter, strongly typed, safer, and more efficient.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
|
|
|
Just a tip regarding command parameters