先有3张表 产品表,生产厂商表,产品类型表如下:CREATE TABLE [dbo].[TSB_Product] (
[Pro_id] [int] IDENTITY (1, 1) NOT NULL ,
[Pro_name] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Par_ID] [int] NULL ,
[Pro_model] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Pro_decription] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[Pro_image] [image] NULL ,
[Pro_Key] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NULL ,
[Pro_intime] [datetime] NULL ,
[Com_id] [numeric](18, 0) NULL ,
[Pro_counter] [numeric](18, 0) NULL ,
[Pro_deleteTag] [bit] NULL ,
[Pro_ShowImage] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[TSB_Commpany] (
[Com_id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Com_name] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[Com_tel] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Com_connect] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[Com_infomation] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
[Com_Email] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Com_address] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[Com_web] [nvarchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
[Com_productweb] [nvarchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
[Com_parterner] [bit] NULL ,
[User_id] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[TSB_Parmater] (
[Par_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Par_TypeID] [int] NOT NULL ,
[Par_data] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[Par_description] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
现在的所有搜索是对PRO_KEY字段模糊搜索,具体搜索存储过程如下,
create proc DefaultPageSearch2
@StrKey nvarchar(4000)
as
beginSELECT top 9 tsb_product.pro_id,tsb_commpany.com_web,tsb_commpany.com_productweb,TSB_Product.Pro_name,TSB_Parmater.Par_description,TSB_Product.Pro_decription,TSB_Product.Pro_model,
tsb_partype.par_typename,tsb_commpany.com_name ,'ShowImage.aspx?pID='+cast(tsb_product.pro_id as char) as ShowImage FROM TSB_Parmater,TSB_Product,tsb_commpany,tsb_partype
WHERE TSB_Parmater.Par_ID = TSB_Product.Par_id AND tsb_Product.com_id=tsb_commpany.com_id and pro_key like '%'+ltrim(rtrim(@StrKey))+'%' AND TSB_Product.Pro_deleteTag = 0 and tsb_parmater.par_typeid=tsb_partype.par_typeid order by pro_intime
end
目前产品表中大约有10条数据,每次搜索至少需要4分钟,严重超时,我的程序中已经使用了分页,每次只返回9条符合条件的数据,曾经尝试给PRO_KEY加上CLUSTERED INDEX,但是以为其数据类型为TEXT,所以加索引失败了,如果强行改为NVARCHAR,需要等待半天的时间,有没有什么好的方法能加快搜索效率!
分数不够我可以加分!谢谢拉
[Pro_id] [int] IDENTITY (1, 1) NOT NULL ,
[Pro_name] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Par_ID] [int] NULL ,
[Pro_model] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Pro_decription] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
[Pro_image] [image] NULL ,
[Pro_Key] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NULL ,
[Pro_intime] [datetime] NULL ,
[Com_id] [numeric](18, 0) NULL ,
[Pro_counter] [numeric](18, 0) NULL ,
[Pro_deleteTag] [bit] NULL ,
[Pro_ShowImage] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[TSB_Commpany] (
[Com_id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Com_name] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[Com_tel] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[Com_connect] [nvarchar] (40) COLLATE Chinese_PRC_CI_AS NULL ,
[Com_infomation] [nvarchar] (1000) COLLATE Chinese_PRC_CI_AS NULL ,
[Com_Email] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Com_address] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[Com_web] [nvarchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
[Com_productweb] [nvarchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,
[Com_parterner] [bit] NULL ,
[User_id] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[TSB_Parmater] (
[Par_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Par_TypeID] [int] NOT NULL ,
[Par_data] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[Par_description] [nvarchar] (150) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
现在的所有搜索是对PRO_KEY字段模糊搜索,具体搜索存储过程如下,
create proc DefaultPageSearch2
@StrKey nvarchar(4000)
as
beginSELECT top 9 tsb_product.pro_id,tsb_commpany.com_web,tsb_commpany.com_productweb,TSB_Product.Pro_name,TSB_Parmater.Par_description,TSB_Product.Pro_decription,TSB_Product.Pro_model,
tsb_partype.par_typename,tsb_commpany.com_name ,'ShowImage.aspx?pID='+cast(tsb_product.pro_id as char) as ShowImage FROM TSB_Parmater,TSB_Product,tsb_commpany,tsb_partype
WHERE TSB_Parmater.Par_ID = TSB_Product.Par_id AND tsb_Product.com_id=tsb_commpany.com_id and pro_key like '%'+ltrim(rtrim(@StrKey))+'%' AND TSB_Product.Pro_deleteTag = 0 and tsb_parmater.par_typeid=tsb_partype.par_typeid order by pro_intime
end
目前产品表中大约有10条数据,每次搜索至少需要4分钟,严重超时,我的程序中已经使用了分页,每次只返回9条符合条件的数据,曾经尝试给PRO_KEY加上CLUSTERED INDEX,但是以为其数据类型为TEXT,所以加索引失败了,如果强行改为NVARCHAR,需要等待半天的时间,有没有什么好的方法能加快搜索效率!
分数不够我可以加分!谢谢拉
解决方案 »
- 如何解决这个问题:textarea 输入多行,显示的时候还是显示多行
- 如何通过调用RadioButtonList的value值写if语句
- 一个关于三层架构中,分页+排序+Top Num的实现——在下想一个问题想到有点死循环,所以得麻烦各位了……
- 2 00分,求聊天室中插入表情图片,显示表情图片,信息向上滚动,的代码,在线,谢谢了。
- 谁用过CuteEditor5.0??
- 有没有办法给datalist绑定的记录前自动加上编号?
- 问了半个月了,如何插入日期类型到sql server 2000??来者有分
- datagrid模板列中放了TextBox和lable,请问怎么往这两个控件中绑定数据?
- 问个简单问题
- 网上怎么使用sql数据库?
- 小弟刚写了个小东东请高手指点:kingzhong.oicp.net
- 加载控件为什么有时候不成功 ,控件没有移动到tableCell中去
分两次:
1.select ... from ... insert into aaa
2.SELECT top 9 ... from aaa order by pro_intime