如何快速获得结果

本文介绍了如何快速获得结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我们有2个crore数据，我们需要根据条件使用选择3列 需要很多时间。 我使用全文搜索但是它的限制我们只能在一个columb上申请 一栏*一个索引 我能做些什么来让它快速 任何想法？ b $ b 我尝试了什么： 我用过全文搜索但是它的局限性我们只能应用于一个列We have 2 crore data and we need 3 columns in select based on where condition using likeIt takes lot of time.I have used full text search but its limitation we can apply on only one columbOne column * one indexWhat i can do to make it fastAny idea?What I have tried:I have used full text search but its limitation we can apply on only one colum推荐答案花了相当长的时间来制作一个包含大量数据的相关示例，但在这里...... /> 首先，你可以使用全文索引在一个表中索引多个列。 另一个事情是，当使用全文索引时，使用CONTAINS函数。 第三件事是满足条件的行数应该与总量相比相对较小行。 考虑以下示例： 创建测试表It took quite some time to make a relevant example with larger amounts of data but here it goes...The first thing is that you can index multiple columns in a single table using full text index.Another thing is that when using full text indices, use CONTAINS function.And third thing is that the amount of rows satisfying the condition should be relevantly small compared to the overall amount of rows.Consider the following example:Create the test tableCREATE TABLE FullTextTest ( id int identity(1,1) NOT NULL, textcol1 varchar(max) NOT NULL, textcol2 varchar(max) NOT NULL);ALTER TABLE FullTextTestADD CONSTRAINT pk_FullTextTestPRIMARY KEY (id); 添加大量行Add a large set of rowsINSERT INTO FullTextTest (textcol1, textcol2)SELECT 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam at elit enim. Mauris et sapien vitae nulla convallis maximus non sit amet nunc. Sed quis consequat eros, nec suscipit nulla. Suspendisse potenti. Duis a posuere dui. Donec sollicitudin eget enim sit amet hendrerit. Nullam neque nisl, sodales ac magna suscipit, iaculis interdum lectus. Integer vehicula egestas augue, finibus commodo urna feugiat et. Integer cursus, lorem ut convallis fringilla, dui elit posuere turpis, nec porta erat ligula quis tortor. Mauris imperdiet ipsum diam, sed eleifend elit blandit sit amet. Fusce eu porttitor elit. Cras tempor orci quis vestibulum tincidunt. Maecenas auctor metus eget diam pulvinar, eget commodo augue pellentesque. Integer congue vitae tellus ut sollicitudin. Nulla non augue ligula.' as data1, 'Nulla ullamcorper, justo quis malesuada imperdiet, arcu metus consectetur diam, quis consectetur arcu tellus nec magna. Ut faucibus risus ut ex placerat, et scelerisque dui vehicula. Nullam in aliquet lorem. Quisque lobortis quam at dui bibendum gravida. Ut in sodales sapien, quis pretium neque. Mauris feugiat urna dolor, sit amet fringilla mi accumsan sit amet. Vivamus sit amet dictum quam, vel condimentum lorem. Cras elementum eu massa lobortis pharetra. Quisque fringilla volutpat mauris, sed feugiat velit accumsan sed. Nam venenatis nec justo eget consequat. Donec vitae lorem hendrerit nulla pulvinar eleifend. Curabitur ut odio posuere ipsum faucibus commodo sed a est. Quisque tincidunt augue nec mollis volutpat. Maecenas elementum tempus velit vitae condimentum. Integer fermentum mi sapien.' as data2FROM sysobjects s1, sysobjects s2, sysobjects s3;INSERT INTO FullTextTest (textcol1, textcol2)SELECT 'Nulla ullamcorper, justo quis malesuada imperdiet, arcu metus consectetur diam, quis consectetur arcu tellus nec magna. Ut faucibus risus ut ex placerat, et scelerisque dui vehicula. Nullam in aliquet lorem. Quisque lobortis quam at dui bibendum gravida. Ut in sodales sapien, quis pretium neque. Mauris feugiat urna dolor, sit amet fringilla mi accumsan sit amet. Vivamus sit amet dictum quam, vel condimentum lorem. Cras elementum eu massa lobortis pharetra. Quisque fringilla volutpat mauris, sed feugiat velit accumsan sed. Nam venenatis nec justo eget consequat. Donec vitae lorem hendrerit nulla pulvinar eleifend. Curabitur ut odio posuere ipsum faucibus commodo sed a est. Quisque tincidunt augue nec mollis volutpat. Maecenas elementum tempus velit vitae condimentum. Integer fermentum mi sapien.' as data1, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam at elit enim. Mauris et sapien vitae nulla convallis maximus non sit amet nunc. Sed quis consequat eros, nec suscipit nulla. Suspendisse potenti. Duis a posuere dui. Donec sollicitudin eget enim sit amet hendrerit. Nullam neque nisl, sodales ac magna suscipit, iaculis interdum lectus. Integer vehicula egestas augue, finibus commodo urna feugiat et. Integer cursus, lorem ut convallis fringilla, dui elit posuere turpis, nec porta erat ligula quis tortor. Mauris imperdiet ipsum diam, sed eleifend elit blandit sit amet. Fusce eu porttitor elit. Cras tempor orci quis vestibulum tincidunt. Maecenas auctor metus eget diam pulvinar, eget commodo augue pellentesque. Integer congue vitae tellus ut sollicitudin. Nulla non augue ligula.' as data2FROM sysobjects s1, sysobjects s2, sysobjects s3; 添加一小组要搜索的行Add a small set of rows to be searched forINSERT INTO FullTextTest (textcol1, textcol2)SELECT 'Fusce et ligula vel dolor maximus maximus non eu urna. Aenean condimentum sem diam. Morbi cursus metus sed nulla ornare blandit. Fusce ut faucibus quam. Etiam fringilla convallis mi, hendrerit hendrerit sapien volutpat quis. Suspendisse tempor viverra congue. Sed eu velit maximus, maximus sapien id, fermentum felis. Vestibulum rutrum leo felis, ut sagittis risus efficitur sit amet. Phasellus tempor tellus vel placerat scelerisque. In hac habitasse platea dictumst. Nunc tincidunt elementum justo, vel dapibus felis pellentesque non. Nulla mattis lorem eget metus dictum, sed condimentum ipsum sodales. Nunc pretium efficitur suscipit. Proin facilisis molestie erat nec gravida. Suspendisse potenti. Ut nunc nisi, vulputate venenatis enim sit amet, consequat elementum tortor.' as data1, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam at elit enim. Mauris et sapien vitae nulla convallis maximus non sit amet nunc. Sed quis consequat eros, nec suscipit nulla. Suspendisse potenti. Duis a posuere dui. Donec sollicitudin eget enim sit amet hendrerit. Nullam neque nisl, sodales ac magna suscipit, iaculis interdum lectus. Integer vehicula egestas augue, finibus commodo urna feugiat et. Integer cursus, lorem ut convallis fringilla, dui elit posuere turpis, nec porta erat ligula quis tortor. Mauris imperdiet ipsum diam, sed eleifend elit blandit sit amet. Fusce eu porttitor elit. Cras tempor orci quis vestibulum tincidunt. Maecenas auctor metus eget diam pulvinar, eget commodo augue pellentesque. Integer congue vitae tellus ut sollicitudin. Nulla non augue ligula.' as data2FROM sysobjects s1;-- 119 additional rows added 更新统计数据并查看总计数Update statistics and see the overall countUPDATE STATISTICS FullTextTest;SELECT COUNT(*) FROM FullTextTest-- total 3'370'437 rows 创建全文索引Create the full text indicesCREATE FULLTEXT CATALOG FullTextTestCatalog AS DEFAULT;CREATE FULLTEXT INDEXON FullTextTest (textcol1, textcol2)KEY INDEX pk_FullTextTest;SELECT c.Name, CASE FULLTEXTCATALOGPROPERTY(c.name,'PopulateStatus') WHEN 0 THEN 'Idle' WHEN 1 THEN 'Full population in progress' WHEN 2 THEN 'Paused' WHEN 3 THEN 'Throttled' WHEN 4 THEN 'Recovering' WHEN 5 THEN 'Shutdown' WHEN 6 THEN 'Incremental population in progress' WHEN 7 THEN 'Building index' WHEN 8 THEN 'Disk is full. Paused.' WHEN 9 THEN 'Change tracking'END AS StatusFROM sys.fulltext_catalogs AS c-- WAIT UNTIL POPULATION FINISHED-- since this is initial population-- this takes awhile, like hours 测试LIKE查询Test LIKE query-- LIKEBEGIN DECLARE @word varchar(100); SET @word = '%rutrum%'; SELECT ftt.* FROM FullTextTest ftt WHERE ftt.textcol1 LIKE @word OR ftt.textcol2 LIKE @word;END-- Result is 119 rows---- time 1 hour 8 minutes--Table 'FullTextTest'. Scan count 1, logical reads 676596, physical reads 3, read-ahead reads 676591, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.-- SQL Server Execution Times:-- CPU time = 3162688 ms, elapsed time = 4079922 ms. 查询完成时超过1小时结果集的大小为119行。 现在测试CONTAINS查询So little bit over 1 hour for the query to finish and the size of the result set is 119 rows.Now test CONTAINS query-- CONTAINSBEGIN DECLARE @word varchar(100); SET @word = 'rutrum'; SELECT ftt.* FROM FullTextTest ftt WHERE CONTAINS(ftt.textcol1, @word) OR CONTAINS(ftt.textcol2, @word);END-- Result is 119 rows-- time 2 seconds--Table 'FullTextTest'. Scan count 0, logical reads 737, physical reads 1, read-ahead reads 27, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.-- SQL Server Execution Times:-- CPU time = 0 ms, elapsed time = 1739 ms. 好​​多了，2秒，结果相同。 希望这可以澄清情况。Much better, 2 seconds, the same result.Hopefully this clarifies the situation. 这篇关于如何快速获得结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！