I′m using SQL Server 2000 in the Windows 2003 Server with 2GB RAM
and 2 dual core processors.
There is an application that access the SQL Server.
Several queries are with problems of performance.
I cannot change these queries ′cause the application
was made by other IT company.
[code]
SELECT * FROM SE1010 SE1
WHERE E1_FILIAL = ' '
AND D_E_L_E_T_ <> '*'
AND E1_CLIENTE BETWEEN ' ' AND 'ZZZZZZ'
AND E1_PREFIXO BETWEEN ' ' AND 'ZZZ'
AND E1_NUM BETWEEN ' ' AND 'ZZZZZZ'
AND E1_PORTADO BETWEEN ' ' AND 'ZZZ'
AND E1_VENCREA BETWEEN '20010101' AND '20070228'
AND (E1_MULTNAT = '1' OR (E1_NATUREZ BETWEEN ' ' AND 'ZZZZZZZZZZ'))
AND E1_EMISSAO BETWEEN '20010101' AND '20070228'
AND E1_LOJA BETWEEN ' ' AND 'ZZ'
AND E1_EMISSAO <= '20070301'
AND ((E1_EMIS1 BETWEEN '20060101' AND '20070228')
OR E1_EMISSAO BETWEEN '20060101' AND '20070228')
ORDER BY E1_FILIAL,E1_PREFIXO,E1_NUM,E1_PARCELA,E1_TIPO
The execution plan is:
|--Sort(ORDER BY:([SE1].[E1_PREFIXO] ASC, [SE1].[E1_NUM] ASC,
[SE1].[E1_PARCELA] ASC, [SE1].[E1_TIPO] ASC))
|--Filter(WHERE:((((([SE1].[E1_EMISSAO]>='20010101'
AND [SE1].[E1_EMISSAO]<='20070228') AND [SE1].[E1_LOJA]>=' ')
AND [SE1].[E1_LOJA]<='ZZ') AND [SE1].[E1_EMISSAO]<='20070301')
AND (([SE1].[E1_EMIS1]>='20060101' AND
[SE1].[E1_EMIS1]<='20070228') OR
|--Clustered Index Scan(OBJECT:([DADOSADV].[dbo].[SE1010].[SE1010_PK]
AS [SE1]), WHERE:(((((((((((([SE1].[E1_FILIAL]=' '
AND [SE1].[D_E_L_E_T_]<>'*') AND [SE1].[E1_CLIENTE]>=' ')
AND [SE1].[E1_CLIENTE]<='ZZZZZZ') AND [SE1].[E1_PREFIXO]>=''
[/code]
I′ve created index for the columns that are used by where clause
and I′ve performed the DBCC INDEXDEFRAG and DBREINDEX, but after I′ve performed
theses functions, the performance fell.
What can I do to improve query performance?
thanks!!!!Moving to engine forum.
