Improving SQLite index

Question

I have a SQLite database C# application which I'm running in memory (:memory:). My table has about 650k rows, which isn't much. I want to get fast response-times on the following query (syntax fetched from LINQ to SQL dynamic LINQ query)

SELECT 
  SUM([t0].[Value1]) AS [Sum],
  [t0].[Dim1] AS [Primary], 
  [t0].[Dim2] AS [Secondary]
FROM [BudgetLine] AS [t0]
WHERE [t0].[BudgetID] = 4
GROUP BY [t0].[Dim1], [t0].[Dim2]

Which would be the optimal index for this query? My only index other than the primary key looks like the following...

create index IX_0 on budgetLine (BudgetID, Dim1, Dim2)
create index IX_1 on budgetLine (BudgetId)
create index IX_2 on budgetLine (Dim1, Dim2)
create index IX_3 on budgetLine (BudgetID, Dim1, Dim2,Value1)

Currently the execution times varies, but around 1s is the current average. I want this query to take less than 0.5s at least.

The table has about 50 columns.

Please assist

Update: See my 4 indexes above, with these I'm getting 0.8s response...

Answer 1

I would try these indexes.

create index IX_2 on budgetLine(BudgetId)
create index IX_3 on budgetLine(Dim1, Dim2)

Answer 2

Remove indexes

create index IX_3 on budgetLine (BudgetID, Dim1, Dim2,Value1)
create index IX_0 on budgetLine (BudgetID, Dim1, Dim2)

and just use the budgetid index provided by bartosz (but NOT the dim1, dim2)

an in memory database is going to be limited by your systems memory - are you running on an underpowered or overtaxed machine? Is there a reason why you're using :memory: instead of an actual disk file? (see Stack overflow question here)

Also consider reading the SQLite documentation about performance tuning, SQLite performance for windows

Also try changing your SQL query as follows:

    select
    SUM([Value1]) AS [Sum]
    ,[Dim1] AS [Primary], 
    [Dim2] AS [Secondary]
from
(
    SELECT 
      value1, dim1, dim2
    FROM [BudgetLine]
    WHERE [BudgetID] = 4
) as a
GROUP BY [Dim1], [Dim2]