查询优化有关问题

查询优化问题

小弟的数据库中有三个表

SQL code

Create Table Clearing(  cNumber              char(14)             NOT NULL,--主键  eNumber              char(14)             NOT NULL,  cPriceTot            money                NOT NULL,  cTPrice              money                NOT NULL,  cRPrice              money                NOT NULL,  cAPrice              money                NOT NULL,  cUPrice              money                NOT NULL,  InvoiceId            bigint               NULL,  PieceworkId          bigint               NULL,  cInput               varchar(60)          NOT NULL,  cInputTime           datetime             NOT NULL,  cOPrice              money                NOT NULL,  cVPrice              money                NOT NULL)Create Table Entrust(  eId                  bigint               IDENTITY,--主键  clientNumber         varchar(18)          NOT NULL,  clientName           nvarchar(80)         NOT NULL,  clientSalesman       varchar(60)          NULL,  eNumber              char(14)             NULL,  ePrincipal           varchar(100)         NULL,  ePhone               varchar(100)         NULL,  eSend                bit                  NOT NULL,  eUrgent              bit                  NOT NULL,  eSendAddress         varchar(80)          NULL,  eReceive             varchar(60)          NULL,  eClaimFinishTime     varchar(30)          NULL,  eClaim               varchar(-1)          NULL,  eItemName            nvarchar(80)         NULL,  eState               char(1)              NOT NULL DEFAULT ('N'),  eCurrentFlow         nvarchar(200)        NULL,  eInput               varchar(60)          NULL,  eInputTime           datetime             NOT NULL DEFAULT (getdate()),  eLog                 varchar(-1)          NULL)Create Table Entrust_State(  State                char(1)              NULL,  StateText            varchar(20)          NULL)

一个视图

SQL code

CREATE VIEW [dbo].[v_Clearing]ASSELECT    dbo.Entrust.clientNumber, dbo.Entrust.clientName, dbo.Entrust.clientSalesman, dbo.Clearing.eNumber, dbo.Entrust.ePrincipal, dbo.Entrust.eSend,         dbo.Entrust.eUrgent, dbo.Entrust.eSendAddress, dbo.Entrust.ePhone, dbo.Entrust.eReceive, dbo.Entrust.eClaimFinishTime, dbo.Entrust.eClaim,         dbo.Entrust.eItemName, dbo.Entrust.eState, dbo.Entrust.eCurrentFlow, dbo.Entrust.eInput, dbo.Entrust.eInputTime, dbo.Entrust.eLog, dbo.Clearing.cNumber,         dbo.Clearing.cPriceTot, dbo.Clearing.cOPrice, dbo.Clearing.cRPrice, dbo.Clearing.cTPrice, dbo.Clearing.cVPrice, dbo.Clearing.cAPrice, dbo.Clearing.cUPrice,         dbo.Clearing.cInput, dbo.Clearing.cInputTime, dbo.Clearing.InvoiceId, dbo.Clearing.PieceworkId, dbo.Entrust_State.StateTextFROM    dbo.Clearing INNER JOIN        dbo.Entrust ON dbo.Clearing.eNumber = dbo.Entrust.eNumber INNER JOIN        dbo.Entrust_State ON dbo.Entrust.eState = dbo.Entrust_State.State

查询数据时一般是

SQL code

select StateText,cNumber,clientName,eItemName,cPriceTot,cOPrice,cRPrice,cTPrice,(cRprice+cTprice) as validPrice,cAPrice,cUPrice,InvoiceId,PieceworkId,cInput,cInputTime from [v_Clearing] where eState in ('S','P','F') andclientNumber = 'KD0211400001' and (cInputTime between '2012-06-01' and '2012-06-19')order by cNumber desc

之前数据库在1万以内时查询速度很快,一般1秒以内
但近几天数据到了1.3W以上
查询时间涨到了20秒的样子

我把语句改为

SQL code

select StateText,cNumber,clientName,eItemName,cPriceTot,cOPrice,cRPrice,cTPrice,(cRprice+cTprice) as validPrice,cAPrice,cUPrice,InvoiceId,PieceworkId,cInput,cInputTime from(select * from dbo.Entrust_State) as ts,(select * from dbo.Entrust where eState in ('S','P','F') and clientNumber = 'KD0211400001' ) as te,(select * from dbo.Clearing where cInputTime >= '2012-06-01') as tcwhere ts.state=te.eState and te.eNumber=tc.eNumber 
 

查询时间又到了1秒以内,但如果我加上
order by cNumber desc 排序
查询时间又要7秒的样子,还是有些慢

请大神们给个优化方案,万分谢谢

[解决办法]
cNumber 字段加索引。
[解决办法]
TRY

SQL code

select StateText,cNumber,clientName,eItemName,cPriceTot,cOPrice,cRPrice,cTPrice,(cRprice+cTprice) as validPrice,cAPrice,cUPrice,InvoiceId,PieceworkId,cInput,cInputTime from(select * from dbo.Entrust_State) as ts,(select * from dbo.Entrust where eState in ('S','P','F') and clientNumber = 'KD0211400001' ) as te,(select * from dbo.Clearing where cInputTime >= '2012-06-01') as tcwhere ts.state=te.eState and te.eNumber=tc.eNumber---将 所有的 * 号换成你要取的字段（不要偷懒）有助提高速度。---select * from dbo.Entrust where eState in ('S','P','F') and clientNumber = 'KD0211400001'  用UNION ALL 来实现，在clientNumber、eNumber、eState 上建索引（或复合索引）--select * from dbo.Clearing where cInputTime >= '2012-06-01'  在cInputTime 上建索引---同样可以在 cNumber 上面建索引
[解决办法]
楼上说得对，建议不要用*号，会引起表遍历
[解决办法]
SQL code
select StateText,cNumber,clientName,eItemName,cPriceTot,cOPrice,cRPrice,cTPrice,(cRprice+cTprice) as validPrice,cAPrice,cUPrice,InvoiceId,PieceworkId,cInput,cInputTime from(select * from dbo.Entrust_State) as ts,(select * from dbo.Entrust where eState in ('S','P','F') and clientNumber = 'KD0211400001' ) as te,(select * from dbo.Clearing where cInputTime >= '2012-06-01') as tcwhere ts.state=te.eState and te.eNumber=tc.eNumber/****************以上子查询可以直接改为表连接查询吧！，然后在where条件里限制，另外有需要的字段加索引。
[解决办法]
增加两个索引：

Clearing（cInputTime）
Entrust（clientNumber ,eState ）

去掉子查询直接连接

[解决办法]
try this,
SQL code
create index idx_Entrust_clientNumber on Entrust(clientNumber)select StateText,cNumber,clientName,eItemName,cPriceTot,cOPrice,cRPrice,cTPrice,(cRprice+cTprice) as validPrice,cAPrice,cUPrice,InvoiceId,PieceworkId,cInput,cInputTime from dbo.Entrust_State(nolock) as tsinner join dbo.Entrust(nolock) as te on ts.state=te.eStateinner join dbo.Clearing(nolock) as tc on te.eNumber=tc.eNumberwhere te.eState in ('S','P','F') and te.clientNumber = 'KD0211400001' and tc.cInputTime >= '2012-06-01'order by tc.cNumber desc 

 

查看更多 下一篇