300,000 data, search for text fields in various ways compared

Source: Internet
Author: User
Keywords Network programming Database synthesis
Tags compared create data function index key network network programming

300,000, with an ID column but no primary key, a nonclustered index is built on the category field to search

Procedure T-sql:

/*


user-defined function: Execution time is around 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 + '% '





*/

declare @a datetime,@b nvarchar (4)


set @a=getdate ()


Select title from book 300,000 Where class like '% medicine% '--"category" column has nonclustered index, 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 is a little slower than 1150-1200





--Execute GETFL ' medicine '--calling a stored procedure 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!

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.