Query Peformance

Hi all,

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!!!!

Hi Tadeu,

are you using datetime or smalldatetime?

If you are using datetime:

With(Index=IndexNameHere) WHERE ....

date >= '20060101' AND date < '20070228'

BTW, for more information read Using Indexed Computed Columns to Improve Performance.

tosc

|||

How many rows are in the SE1010 table, and how many rows are eventually returned by the query that you provided?

Chris

|||Hi all,

tosc,

Well, the problem is: I cannot change the query ou the tables, the columns, nothing :-(.

The table not contains datetime or smalldatetime columns. Only varchar and float.

Chris Howarth,

The table SE1010 contains 92387 rows and the query returns 9537 rows.

thanks all!!!!|||

SQL Server will almost always perform a table or clustered index scan in this scenario.

As you can't amend the query you'll probably find that there is no little you can do to improve the performance other than moving your database files to faster disks or upgrading your server. You need to monitor the CPU, disks and RAM to see where the bottleneck lies to see what which aspect you need to improve.

Chris

|||Hi,

The query is using clustered index scan for this case.
I monitored the CPU, memory, I/O, Network and the SQL Server for 2 days and the
only problem is the disk, where only exists one disk on the server.

Now I will try to use the Index Tuning Wizard and search for index fagmentation.

thank you very much.

[]′s|||only more one question:

Can I to perform DBCC INDEXDEFRAG or DBCC DBREINDEX to internal
tables of SQL Server?

The índices of SQL Server are fragmented.

thanks!!!!|||

It's worth a try before you resign yourself to an upgrade.

Check out this link for more info:

http://www.sql-server-performance.com/rebuilding_indexes.asp

Chris

|||thank you very much!!!!