| Author |
Topic |
|
SQLLearner2
Starting Member
14 Posts |
 Posted - 2008-08-29 : 09:53:50
|
| In oracle, you are encouraged to use bind variables. Oracle considers the following statements to be two different statements, requiring each to be parsed and the optimal execution plan to be determined for each.select last_name from customers where customer_id = 333select last_name from customers where customer_id = 444Oracle encourages the use of bind variables, so that thestatement will only be parsed once and then will use the same execution plan for subsequent queries.cust_id := 333;select last_name from customers where customer_id = :cust_idcust_id := 444;select last_name from customers where customer_id = :cust_idMy question...are there similar advantages (or no advantages) in sql server to using bind variables? I've looked thru some books and searched the internet, but most references i've found are for oracle. Haven't really found any references to sql server other than one article said they are 'common to other RDBMS platforms such as MS sql server, so there's no excuse for not using them because they might be an oracle-only feature'.Thanks in advance any thoughts you might want to share. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-29 : 09:55:56
|
No, using variables can lead to potential problem parameter sniffing.You can work around that with the query hint OPTIMIZE FOR and still use variables.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
SQLLearner2
Starting Member
14 Posts |
Posted - 2008-09-09 : 07:42:57
|
| so you're saying to NOT use bind variables at all, or go ahead and use them with the 'OPTIMIZE FOR'? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-09 : 07:57:40
|
It depends on what you mean by "bind" variables.Are 333 and 444 the most common values when searching?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-09 : 07:58:20
|
If you ALWAYS search for 333 and 444, there is no need for using variables.It makes you prone the parameter sniffing.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
SQLLearner2
Starting Member
14 Posts |
Posted - 2008-09-09 : 08:17:56
|
| the 333 and 444 were just very basic examples. customer id could be any of several thousand different values. My main question, as a general rule, is it better to use bind variables instead of actual values (ie. better to do 'select last_name from customers where customer_id = :cust_id' than 'select last_name from customers where customer_id = 'somevalue').Bind variables are recommended with oracle, I haven't found much on the internet about it, one way or the other, with SQL Server. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-09 : 08:27:45
|
Use variables, which is what I think you mean by "bind".
E 12°55'05.63"N 56°04'39.26" |
|
|
|
SQLLearner2
Starting Member
14 Posts |
Posted - 2008-09-09 : 09:05:22
|
| I pulled this off the internet, it explains bind variables better than I can.Again, it's an oracle reference, but it sounds like you are recommending to do the same thing in SQL?----------------------------------------------------------The way to get Oracle to reuse the execution plans for these statements is to use bind variables. Bind variables are «substituion» variables that are used in place of literals (such as 674, 234, 332) and that have the effect of sending exactly the same SQL to Oracle every time the query is executed.----------------------------------------------------------link is http://www.akadia.com/services/ora_bind_variables.html |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-09-11 : 20:14:55
|
| Generally I would go with the 'bind variable'. It does cut down on the cost of parsing etc and reuses plans very effectiely. As Peso says though, you can get caught out when the column is massively skewed because it only gets evaluated once (Oracle used to suffer from this but later versions not produce conditional plans). In your case of ... where customer_id = :cust_id then always use the variable as the plan will be the same for any PK (assuming customer_id is a pk of course). The traps come when you use WHERE status=@theStatus when you have 100000000000 rows of status 'A' and 2 rows of status 'B'. As Peso says, you might be better off writing individual SPs/statements without binds or with hints in these extreme cases. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-09-11 : 20:16:49
|
| Oh, and as with all databases, stay well away from dynamic SQL or generating SQL in the front end by concatenating parameters into SQL strings. |
|
|
|
|
bind variables