I would like to reduce the time taken by these query,It is taking 29 seconds in production enviroment

SELECT TOP 20 II.NAME_FOR_SALES COMPANYNAME, -SUM(TOTAL_PRICE) AMOUNT, 6 GROUPORDER, II.ITEM_ID,II.ITEM_NUM ITEMNUM FROM ITEM_LEDGER_ENTRY ENTRY, INV_ITEM II WHERE ENTRY.ITEM_ID = II.ITEM_ID AND ENTRY.DOCUMENT_TYPE_ID IN (3, 4, 5) AND ENTRY.DIVISION_ID IN (-999, -1, 7, 5, 6, 2, 1, 3, 4) AND YEAR(GETDATE()) = YEAR(ENTRY.POSTING_DATE) AND MONTH(GETDATE()) = MONTH(ENTRY.POSTING_DATE) AND II.ITEM_TYPE_ID = 2 GROUP BY II.ITEM_ID, II.NAME_FOR_SALES, II.ITEM_NUM ORDER BY AMOUNT DESCafter creating below indexs it is taking 15 seconds for first time and 7 seconds for second.I would like to reduce to 0 seconds.CREATE INDEX ILE_IDX4 ON ITEM_LEDGER_ENTRY (ITEM_ID, DOCUMENT_TYPE_ID, DIVISION_ID, POSTING_DATE)please help me

Welcome to the forum!

• First of all, you some formatting when sending the query. It will help others to get into the problem faster. You also will allow many to attend into your problem.
• Can you provide the execution plan it takes? I suspect that instead of using one index, multiple indexes couldhelp you more.
• Instead of using

YEAR(GETDATE()) = YEAR(ENTRY.POSTING_DATE) AND MONTH(GETDATE()) = MONTH(ENTRY.POSTING_DATE)

it is better to use explicit values for the first and the last day of the month.DECLARE
@FromDate datetime, @ToDate datetime SET
@FromDate = CONVERT(datetime, CONVERT(char,YEAR(GETDATE()))+'/'+CONVERT(char,MONTH(GETDATE()))+'/01') SET
@ToDate = DATEADD(month, 1, @FromDate)
in the query use this: ENTRY
.POSTING_DATE >= @FromDate ANDENTRY
.POSTING_DATE < @ToDate

Thank you preethi,
The Table Item_ledger_entry table as 3 indexes one on Item_ID,Comibination of Item_Id,Document_id and etc.
I would like to create on more index on coulum Total_Price (amount Field) on table Item_ledger_entry.
when i commented order by amount desc query execution improved.now i would like to remove group by but i need to sum total_price.what shall i do

Agreed with Madhivanan. It is a better method.
The best way is to pass @FromDate and @ToDate from the application as parameters. It will allow Query optimimzer to get the advantage of parameter. When local variables are used, the query can't get the value of the variables for the optimizer.
I have seen execution plans using Index scan instead of Index Seek when local variables ar used.

have you tried with derived tables.. it may reduce your execution time... you can fetch very little data and you can make some conditions like,select your data with the below condition II
.ITEM_TYPE_ID = 2
and after wards do your processing..
a small tip in your query...
on
ENTRY.ITEM_ID = II.ITEM_ID where
(ENTRY.DOCUMENT_TYPE_ID = 3 or ENTRY.DOCUMENT_TYPE_ID= 4 or ENTRY.DOCUMENT_TYPE_ID= 5) and
Regards,
Venkatesan Prabu .J

[quote user="venkatesanj@hcl.in"]where (ENTRY.DOCUMENT_TYPE_ID = 3 or ENTRY.DOCUMENT_TYPE_ID= 4 or ENTRY.DOCUMENT_TYPE_ID= 5) and[/quote]
IN and OR will work the same way in this case.