我以前写的一个存储过程,根据多个条件查询!这段时间客户反应查询超慢!我打开数据库发现表中的数据有三百多万条,而且我存储过程还是多表查询,其它几个联查的表也有几十万条数据!我想到优化,但没做过优化,请高手全盘帮我分析下!我知道建索引是一种方式,但是这索引还真不知道从哪建起!我提供一个虚拟表,求高手帮我分析分析CREATE TABLE [dbo].[AS1634_old](
[ID] [int] NULL,
[HOSE_COVER_OUTSIDE_DIAMETER] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[DASH_NO] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[LENGTH] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[STRAIGHT_END_FITTING_DIA] [float] NULL,
[ELBOW_END_FITTING_DIA] [float] NULL,
[LENGTH_CORRECTION_STRAIGHT_END] [float] NULL,
[LENGTH_CORRECTION_ELBOW_END] [float] NULL,
[HOSE_SIZE] [float] NULL,
[A1] [float] NULL,
[A2] [float] NULL,
[B] [float] NULL,
[C] [float] NULL,
[D_MIN] [float] NULL,
[D_MAX] [float] NULL,
[E] [float] NULL,
[F] [float] NULL,
[NAME] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[COVER_CODE] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[HOSE_OR_TYPE_COVER] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[TEMP_LIMIT] [float] NULL,
[WT] [float] NULL,
[THREAD] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[OPERATING_PRESSURE] [float] NULL,
[PROOF_PRESSURE] [float] NULL,
[BURST_PRESSURE_ROOM_TEMP] [float] NULL,
[BURST_PRESSURE_HIGH_TEMP] [float] NULL,
[BEND_RADIUS] [float] NULL,
[CODE] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[MATERIAL] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[FIGURE] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[Engineerinfor] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL,
[PREFIX] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[ID] [int] IDENTITY(1,1) NOT NULL,
[EXPRESSION] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL)
--[DASH_NO]、[LENGTH]、[MATERIAL]、[EXPRESSION] 、[THREAD]这几个为搜索字段
-- 多个表联查,另一个表还有几个字段供于搜索
[EXPRESSION] [nvarchar](255) COLLATE Chinese_PRC_CI_AS NULL
create index ix_name on tableA(c1,c2)