日期:2014-05-18  浏览次数:20879 次

查询优化问题

小弟的数据库中有三个表
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]
AS
SELECT    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.StateText
FROM    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') and
clientNumber = 'KD0211400001' 
and (cInputTime between '2012-06-01' and '2012-06-19')
order by cNumber desc


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

我把语句改为
SQL code

select StateText,cNumbe