Bind Vars in Sql Server Exec plan

General discussion forum about all databases other than SqlBase.
FRBhote
India
Posts: 2182
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Bind Vars in Sql Server Exec plan

Post by FRBhote » 21 Jun 2013, 14:42

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
Germany
Posts: 2069
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: Bind Vars in Sql Server Exec plan

Post by RainerE » 24 Jun 2013, 09:06

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
India
Posts: 2182
Joined: 09 Mar 2017, 05:32
Location: Hyderabad, India

Re: Bind Vars in Sql Server Exec plan

Post by FRBhote » 27 Jun 2013, 09:54

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

RainerE
Germany
Posts: 2069
Joined: 27 Apr 2021, 12:40
Location: Germany

Re: Bind Vars in Sql Server Exec plan

Post by RainerE » 28 Jun 2013, 09:15

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

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot], [Google] and 0 guests