query optimization

Uma Kameswari

I have to use aggregate function sum() on a table having more than 4 millions of records. It is a history table. Are there any tips to be followed. the queries are a follows

SELECT @TransferIn = isnull(sum(abs(trxqty)),0)
FROM iv30300 WHERE doctype = 3 and itemnmbr = @itemnmbr
and trnstloc = @route and docdate between @FromDate and @ToDate

SELECT @TransferOut = isnull(sum(abs(trxqty)),0)
FROM iv30300 WHERE doctype = 3 and itemnmbr = @itemnmbr
and trxloctn = @route and docdate between @FromDate and @ToDate

SELECT @Sales = isnull(sum(abs(trxqty)),0)
FROM iv30300 WHERE doctype=6 and itemnmbr = @itemnmbr and
trxloctn = @route

In the above query @route,@FromDate and @ToDate are user inputs

Senthil_M

Dear Uma, Please give me the table structure with the available indexes. Senthil

Uma Kameswari

Dear Senthil, The table structure is as follows [TRXSORCE] [char](13)NOT NULL, [DOCTYPE] [smallint] NOT NULL, [DOCNUMBR] [char](21) NOT NULL, [DOCDATE] [datetime] NOT NULL, [HSTMODUL] [char](3) NOT NULL, [CUSTNMBR] [char](15) NOT NULL, [ITEMNMBR] [char](31) NOT NULL, [LNSEQNBR] [numeric](19, 5) NOT NULL, [UOFM] [char](9) NOT NULL, [TRXQTY] [numeric](19, 5) NOT NULL, [UNITCOST] [numeric](19, 5) NOT NULL, [EXTDCOST] [numeric](19, 5) NOT NULL, [TRXLOCTN] [char](11) NOT NULL, [TRNSTLOC] [char](11) NOT NULL, [TRFQTYTY] [smallint] NOT NULL, [TRTQTYTY] [smallint] NOT NULL, [IVIVINDX] [int] NOT NULL, [IVIVOFIX] [int] NOT NULL, [DECPLCUR] [smallint] NOT NULL, [DECPLQTY] [smallint] NOT NULL, [QTYBSUOM] [numeric](19, 5) NOT NULL, [DEX_ROW_ID] [int] NOT NULL There are no indexes on this table. I cannot create any indexes as it a table used by ERP named Microsoft Dynamics GP

J4amieC

Uma Kameswari wrote:

I cannot create any indexes as it a table

Then you cant really optimize the table, can you?

Wendelius

Since you don't have any indexes, you cannot create them and you don't have joins, there's not much you can do. The only thing that comes in mind is that yu copy the data to another table (perhapes in another database), index it and execute the query there. However this isn't a good solution if you need the data to be up-to-date.

The need to optimize rises from a bad design.My articles[^]