Why is this query taking so long to execute in SQL Server?

Question

I want to reduce the execution time. Here is my query:

SELECT 
    StoreID,
    ItemLookupCode,
    sum(TotalQuantity) TotalQuantity,
    sum(ExtendedPrice) ExtendedPrice,
    sum(ExtendedCost) ExtendedCost
FROM 
    [HQMatajer].[dbo].[JCF_ItemDailySalesParent]
WHERE
    time >= CONVERT(DATETIME, '2015-01-01 00:00:00', 102) AND
    time <= CONVERT(DATETIME, '2015-12-31 00:00:00', 102)
GROUP BY 
    ItemLookupCode, StoreID

It's taking 3 min 33 sec to execute this query. Total number of records in that table is 8 Million. The total number of rows records retrieved from the database is 87,000

I created index for all the columns.

Table Definition

[StoreID]             [nchar](150)      NOT NULL,
[ItemLookupCode]      [nvarchar](25)    NULL,
[ExtendedDescription] [varchar](3000)   NULL,
[DepartmentID]        [int]             NULL,
[DepartmentName]      [nvarchar](30)    NULL,
[CategoryID]          [int]             NULL,
[CategoryName]        [nvarchar](30)    NULL,
[SupplierID]          [int]             NULL,
[SupplierCode]        [nvarchar](17)    NULL,
[SupplierName]        [nvarchar](30)    NULL,
[Time]                [datetime]        NOT NULL,
[TotalQuantity]       [float]           NULL,
[ExtendedPrice]       [float]           NULL,
[ExtendedCost]        [float]           NULL`

Index Definition

CREATE NONCLUSTERED INDEX [JCF_AllColumns] ON [dbo].[JCF_ItemDailySalesParent]
(
[Time]                ASC,
[ItemLookupCode]      ASC,
[StoreID]             ASC,
[ExtendedDescription] ASC,
[DepartmentID]        ASC,
[DepartmentName]      ASC,
[CategoryID]          ASC,
[CategoryName]        ASC,
[SupplierID]          ASC,
[SupplierCode]        ASC,
[SupplierName]        ASC,
[TotalQuantity]       ASC,
[ExtendedPrice]       ASC,
[ExtendedCost]        ASC
)

I am using SQL Server 2012.

Answer 1

You have created the datatype, you can avoid nchar type. You are storing only numbers on StoreID. It's just only four digit.

[StoreID] [nchar](150) NOT NULL

Change the datatype into varchar(10). nchar is unicode format. It will make the size double. As well as you put the nchar size is 150. This will create a memory for all the charecters even though you don't use more than 4 char.

Due to the size of nchar(150) It will create multiple pages on Index(unwanted b-tree pages)

So change into following constraint

[StoreID] [varchar](10) NOT NULL

And create the following index

CREATE NONCLUSTERED INDEX [JCF_AllColumns] ON [dbo].[JCF_ItemDailySalesParent] ( [Time] ASC, [TotalQuantity] ASC, [ExtendedPrice] ASC, [ExtendedCost] ASC ) INCLUDE( [ItemLookupCode] , [StoreID]
)