1

I'm having a few performance bottlenecks in my queries. What happens is whenever i introduce a property as a byte in my entity, EF 4.1 casts it to an int prior to working with it. The given code will explain:

var segmentQuery = workUnit.SegmentRepository.GetQuery()
                                             .Where(x => x.FileId == file.Id)
                                             .Where(x => x.StateValue == (byte)SegmentState.Unhandeled)
                                             .OrderBy(x => x.Index);

Translates nicely to:

SELECT 
....
FROM ( SELECT 
    [Extent1].[Id] AS [Id], 
    ...
    [Extent1].[StateValue] AS [StateValue]
    FROM [Segments] AS [Extent1]
    WHERE ([Extent1].[FileId] = @p__linq__0) AND (0 = [Extent1].[StateValue])
)  AS [Project1]
ORDER BY [Project1].[Index] ASC

However, in the case above: StateValue actually is an integer which is way to much for my requirements (4 different states), but when changing it to a byte, i get:

SELECT ...
FROM ( SELECT 
    [Extent1].[Id] AS [Id], 
      ...
    [Extent1].[StateValue] AS [StateValue]
    FROM [Segments] AS [Extent1]
    WHERE ([Extent1].[FileId] = @p__linq__0) AND (0 = ( CAST( [Extent1].[StateValue] AS int)))
)  AS [Project1]
ORDER BY [Project1].[Index] ASC

Since this table might contain more then 100 000 of rows one day, its space efficient (although luckily not essential) for its State field to only occupy 1 byte, however, changing to bytes kill my queries.

Did i do something wrong? Is there anything i can do? is this 'problem' known?

thanks!

** UPDATE **

[Flags]
public enum SegmentState : byte
{
    Unhandeled,
    Downloaded,
    Invalid,
    Assembled
}

and in my entity:

/// <summary>
/// Dont use this, use SegmentState instead
/// </summary>
[Required]
public byte StateValue
{
    get { return _stateValue; }
    set { _stateValue = value; }
}

public SegmentState State
{
    get { return (SegmentState)StateValue; }
    set 
    {
        if (State != value)
        {
            StateValue = (byte)value;
            RaisePropertyChanged(StatePropertyName);
        }
    }
}
Polity
  • 14,734
  • 2
  • 40
  • 40
  • can you post the types of `StateValue and `SegmentState`? if you are handling large data volumes then i don't think SQL CE can handle them – Eranga Jun 10 '11 at 23:17

1 Answers1

0

Seriously don't use EF - it will be the biggest pain in your life. Take a look at MASSIVE and Dynamics in C# - it will open your eyes for life ;-)

mch_dk
  • 369
  • 1
  • 4
  • 15
  • Aha, answer i dont like to hear ;) i just swapped my plain old Sql DB layer for a neat EF 4.1 layer. Its wonderfull in many ways although there are some problems (like this one). Luckily, i can always fall back to plain old SQL queries in EF (but rather not). – Polity Jun 10 '11 at 21:37
  • Massive is also great but it got its down sides, I dont like to use dynamics when i really dont have to. I picked .NET and C# for a reason and i want to stick with that – Polity Jun 10 '11 at 21:39
  • As long as you don't aim for performance EF could be an option ;-) – mch_dk Jun 10 '11 at 21:39
  • We shouldnt start a discussion here but.... your partially wrong. EF isnt optimized that good that it will fit for high performance but it does pretty well (esspecially with the intelligent SQL CE query planner in place). I've seen massive linq statements turned into highly optimized SQL queries which i wouldnt have written any more optimized. EF is definatly an option. (i know about the clientside performance problems but even that can be tackled, although still slower then massive, i agree, but thats just a tiny part) – Polity Jun 10 '11 at 21:43
  • Check out dapper and see EF bleeding to death. http://code.google.com/p/dapper-dot-net/ – mch_dk Jun 10 '11 at 21:49
  • Well, i saw that one before. There are 2 arguments i have: First of all, this test isnt run against EF 4.1 code first. I dont know if it makes a difference but its surely something to take into account (especially since i dont use tracking). Secondly and more importantly. My performance problems are with SQL itself. I think the average client wont notice the performacne problems when for example materializing – Polity Jun 10 '11 at 21:52