Bind Vars in Sql Server Exec plan

[FRBhote]

Is there a way to check the execution plan in Sql Server management Studio?

It does not seem to accept bind vars like in CTD.

[RainerE]

Hi Feroz,

yes, there is a way to check an execution plan. And the execution plan provides much more and better information. And you can in addition check the physical and logical reads/writes (to a) see the costs and b) the cache usage).
You can either show the estimated execution plan. Or you can include the real execution plan in the select.

To include IO statistics:
set statistics io on
No you will get an additional window 'Messages' as tab beside the 'Result'.
NOte: Important are the logical reads, not the physical ones. The less logical reads, the better the query. The less physical reads, the better is the cache used (but thats not so important).

To include the execution plan:
a) select a query and click on the toolbar button 'Estimated execution plan'. or
b) Click on the toolbar button 'Include real execution plan' and execute a query.
If you use b) and set statistics on, you will get 3 tabs in the result window (results, messages and execution plan).

Note that for SqlServer a) the execution plan and b) the query capabilities are totally better than with SQLBase. I really really would recommend to visit a training!

For example you can use an 'or' at the highest level and Sql Server will split the query into 2 separate queries both using a separate index. The results will be merged afterwards. The execution plan shows all this and tells you which step has which costs. Note that you can write totally different queries for the same purpose and Sql Server usually builds the same execution plan for them.


And yes, you can AND SHOULD use bind variables in Sql Server (in the where part of queries). Sql Server caches prepared statemens to reuse them. But this only works, if the statements are identically (either because the statement without bind variables is the same or because the statement uses bind variables and only differs in the values of this variable).
In Sql Server Mgmt Studio enter:
declare @1 as Char(10)
set @1 = 'LOCAL'
select * from YourTable where YourCol = @1
go
Select all the 4 lines and press F5.

In C# you address a bind variable as follows:
"... where YourCol =@1"
YourClass.Parameters.Add(new SqlParameter("@1", SqlDbType.VarChar));
// e.g. in a loop
YourClass.Parameters["@1"].Value = "LOCAL";
or
YourClass.Parameters.AddWithValue("@1", "LOCAL");

Regards,
Rainer

[FRBhote]

Hi Rainer, Let me try it - you say for each bind I have to declare it? And I have to change to @1, :y to @2 and so on? Big job.

[RainerE]

Hi Feroz,

the name of a Sql Server bind variable in C# can be (nearly) anything after the @. E.Gl @x, @y, @tradeNumber, @counter1, ...
I don't know if it can be : instead of @ -> try it out.

Do I understand correctly, that you migrate a Gupta TD code to C# and that you migrate SQLBase to Sql Server?

Or do you want to change SQLBase by Sql Server but still code in Gupta TD?
In this case, I would recommend to use OLEDB (and not ODBC) and you can ignore my previous post. But then there are some other issues...

Regards,
Rainer