如何在SQL Server中快速搜索8000个字符列 [英] How to make search fast for 8000 character column in SQL server

问题描述

嗨

i有一个产品表，其中有一些列，如id，name，price和description

description列是8000个字符列。

如果我在描述栏中使用搜索，则会变慢。例如。描述如％baby product％

i可以实现完整的测试搜索，但我想要一些其他的方法。

你能指出一些解决这个问题的方法（即使我们可以改变数据库和表格的结构也是如此。

i希望快速获取查询数据条件的描述列。

谢谢。



我尝试过：



完整测试搜索.............. ................................

hi
i have a product table which has some columns like id, name, price and description
description column is 8000 character column.
if i use search in description column, it make slow. eg. description like %baby product%
i can implement full test search but i want some other way for this.
would you please point out some solutions for this problem (even we can alter structure of database and table also).
i want to fetch query data fast with condition on description column.
Thanks.

What I have tried:

full test search..............................................

推荐答案

如果你有一些集合描述，而不是有一个大的列与该信息具有一对多的关系描述表，例如

If you have some set descriptions then instead of having one large column with that information have a one-to-many relationship to a table of descriptions e.g.

create table Product(id int, [name] varchar(255),[price] decimal(15,2))
create table Description (id int, description varchar(255))
create table ProductDescription (ProductId int, DescriptionId int)

insert into #Product (id, [name], price) values
(1, 'Product1',100.10), (2, 'Product2', 200.20), (3, 'Product3', 300.30)
insert into #Description (id, [description]) values
(1, 'Baby Product'),(2,'Adult Product')
insert into #ProductDescription(ProductId, DescriptionId) values
(1,1),(1,2),(2,2),(3,1)

你可以像这样链接它们：

You can link them up like this:

select P.id, P.[name], P.price, D.[Description]
	FROM #Product P
	INNER JOIN #ProductDescription PD ON PD.ProductID=P.id
	INNER JOIN #Description D on D.id=PD.DescriptionId
	WHERE D.[Description] = 'Baby Product'

结果：

1	Product1	100.10	Baby Product
3	Product3	300.30	Baby Product

您可以使用FOR XML PATH重建产品的完整描述，例如（可能不是最好的查询tbh）

You can reconstruct the "entire description" for the product using FOR XML PATH e.g. (possibly not the best query tbh)

;WITH CTE AS
(
	select P.id, P.[name], P.price, D.[Description]
	FROM #Product P
	INNER JOIN #ProductDescription PD ON PD.ProductID=P.id
	INNER JOIN #Description D on D.id=PD.DescriptionId
),
CTE1 AS
(
	SELECT id, [Description] = 
    STUFF((SELECT ', ' + CAST([Description] as nvarchar)
           FROM [CTE] p1
           WHERE p1.id = p2.id 
          FOR XML PATH('')), 1, 2, '')
	FROM CTE p2
	GROUP BY id
)
SELECT * from CTE1 A WHERE id IN 
(SELECT ProductId FROM #ProductDescription PD INNER JOIN #Description D ON PD.DescriptionId = D.id WHERE D.[Description] = 'Baby Product')

结果：

1	Baby Product, Adult Product
3	Baby Product

原始架构或我的建议的表现可能会有所改善使用适当的索引

Performance from your original schema or my suggestion could potentially be improved by the use of appropriate indexes

这篇关于如何在SQL Server中快速搜索8000个字符列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！