MSSQL 300,000 data search text fields in various ways contrast _mssql

Source: Internet
Author: User
Tags getdate mssql
Database:
300,000, with an ID column but no primary key, a nonclustered index is built on the category field to search
procedure T-sql:
Copy Code code as follows:

/*
User-defined functions: Execution time is about 1150-1200 milliseconds
CREATE FUNCTION [dbo]. [GETHL] (@types nvarchar (4))
RETURNS table AS
Return select title from book 300,000 Where to categorize like '% ' + @types + '% '
Stored procedures:
CREATE PROCEDURE [dbo]. [GETFL] (@typen nvarchar (4))
As
Select title from book 300,000 Where to categorize like '% ' + @typen + '% '
*/


Copy Code code as follows:

DECLARE @a datetime,@b nvarchar (4)
Set @a=getdate ()
Select title from book 300,000 Where category like '% medicine% '--"category" column has nonclustered indexes, faster than clustered index 1150, almost execution time around 1100
--Select title from GETHL (' medicine ')--using user-defined functions, efficiency and building a clustered index, just a little slower at 1150-1200
--Execute GETFL ' medicine '-calling stored procedures cannot contain parameters in parentheses execute GETFL (' medical ')
--Select title from VIEW1--view
print ' Run time:
Print DateDiff (Ms,@a,getdate ())


Conclusion:
1, all the above use direct query, function, view, stored procedure performance is similar;
2. In this text field, nonclustered is better than clustered index.
A better way to do this is to create a corresponding retrieval ID on another table, faster!

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.