SQL query tuning

Question

Hello All,

Following is the query I found on SP_WHO2 -----

" (@ClaimNo nvarchar(40))SELECT * FROM [sv_XXX] WHERE [ClaimNo] = @ClaimNo "

sv_XXX is a SQL 'view'.

this query is coming from application side.

So my QUS is --

CliamNo has varchar datatype in table but here it is showing that ClaimNo we r getting is nvarchar

Application team doesnt knw anythng bt I want to tune this query by converting that nvarchar to varchar.so my question is where should be my lookout for this....

Aplication coding is in .NET and in dot net there is only string datatype...I thnk so..

Answer 1

Hi Q SQL,

@ClaimNo is declared by people who develop this code I think. But according to your question, it seems this variable was converted to nvarchar automatically?

Could you describe the command((@ClaimNo nvarchar(40))SELECT * FROM [sv_XXX] WHERE [ClaimNo] = @ClaimNo ) more detailed? It should be coded in your application coding, the way to change the nvarchar to varchar is finding the source code and make some change there.

If my understanding is incorrect, please correct me.

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

HI Iric,

initially I was thnking in same manner as you but coding is in Dot Net and Dot Net doesnt hv nvarchar datatype,it just hv 'String' datatype i thnk the only datatype.they hv passed string in their application coding...i dnt knw where this nvarchar is coming from.....bt SQL is showing this cmd on backhand.devolopers are using views and using * so CPU goes mad when de fired these type of commands.they are nt using any SP or nt passing any value manualy but whenever they open their applications first page i.e. Credential Page i got these commands comming on SQL.

Answer 3

Hi Q_SQL

in the .Net application they can specify the variable type for the command this way :

cmd.Parameters.Add("@ClaimNo", SqlDbType.VarChar, 50, "MyClaimNo");

This way you can control the type of the parameter.

Cheers

Régis

---

Blog