Comparison of text fields in mssql 0.3 million data searches

Source: Internet
Author: User

Database:
0.3 million, with an ID column but no primary key, a non-clustered index is created on the "category" field to be searched
Process T-SQL:
Copy codeThe Code is as follows:
/*
User-Defined Function: the execution time is around-milliseconds.
Create function [dbo]. [gethl] (@ types nvarchar (4 ))
RETURNS table
Return select title from 0.3 million articles of the book Where classification Like '%' + @ types + '%'
Stored Procedure:
Create procedure [dbo]. [getfl] (@ typen nvarchar (4 ))
AS
Select the title from the book's 0.3 million Where categories Like '%' + @ typen + '%'
*/


Copy codeThe Code is as follows:
Declare @ a datetime, @ B nvarchar (4)
Set @ a = getDate ()
Select the title from the book's 0.3 million Where categories Like '% medical %' -- the "category" column has non-clustered indexes, which is a little faster than clustered index 1150 and takes about 1100 of the execution time.
-- Select from gethl ('medical ') -- using a user-defined function, the efficiency is the same as building a clustered index. It is a little slower than 1150-1200.
-- Execute getfl 'medical '-- the Stored Procedure Call cannot contain the parameter Execute getfl ('medical') in parentheses ')
-- Select title from VIEW1 -- View
Print 'runtime:
Print datediff (MS, @ a, getDate ())


Conclusion:
1. The above features include direct query, functions, views, and stored procedures;
2. In this text field, non-clustering is better than clustered index.
A better way is to create a search ID on another table, which is faster!

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.