Sqlserver Query type match

Original Author: blog garden Yihui original text: Link

In a routineSQL ServerIn performance tracking, a passBlognameGetBlogidThe stored procedure is frequently called, but the performance is not satisfactory, although the stored procedure is very simple.

Create procedureDBO. ups_getblogidbyname

@ BlognameNvarchar(50)

As

Set nocount on

SelectIDFromBlogsWith (nolock)WhereName = @ blogname

Go

In factBlogsThe structure of is defined in this way

Field name	Name	Type	Remarks
ID	BlogID	Int	Primary Key
Name	Blog name	Varchar (50)	Create an index for this field
Blogheading	Blog title	Nvarchar (250)
...	...	...	Other fields are omitted

The table is in the order of millions.

This stored procedure does not seem to have any problems, if performance tracing is not performed with poor performance, it is not easy to find it. the reason is that the stored parameter blogname nvarchar (50 ), in this way, when the database queries a table, the blog name ( name ) is first converted to nvarchar , cause name the index is invalid.

I don't know if the author was a clerical error or didn't even notice it.VarcharAndNvarcharThe difference is that this small amount of attention causes the database performance to decline a lot.

The following is my specific test data.

Declare@ BVarchar (50)

Declare@ BCNvarchar (50)

Set @ B ='Blogtest'

Set @ BC = N'Blogtest'

SelectIDFrom blogsWhereName = @ B

SelectIDFrom blogsWhereName = @ BC

We can see from the figure that the performance difference between the two is really unexpected. Therefore, when writing a stored procedure in the future, we must pay special attention to the type of the query field, which must be consistent with the corresponding field in the table to avoid major mistakes that are not easily discovered.

For more information, see liunx.cnblogs.com)