Entity framework learning curve

Tim Wappat

Nov 9, 2010 • 4 min read

I have just started using the Entity Framework (EF) to create some quick winforms for our Dynamics GP modifications. However I’ve been bogged down by performance issues. Writing the data layer in datasets brings my performance back to what I expect again.
It is possible, even normal to use stored procedures to gain control again, but for these forms it seemed over the top of the use they get, and I just wanted a quick and dirty implantation for a quick win. The performance arguments for stored procedures are getting thinner and in our environment they create a maintenance burden. Don’t get me wrong, the use of stored procedures and views has many times got me out a hole in avoiding recompiling applications, instead just a tweaking of the procedure driving the application.

**Update from investigations:**Entity Framework 4 should improve on generated SQL. The issue I experienced here is the unicode one listed on the ADO.NET team blog, Provide mechanism for efficient queries on non-Unicode columns, this is issue 5 in the list.

In .NET 3.5, whenever a constant or a parameter was used in LINQ to Entities query, we treated it as being Unicode. As a result, when comparing a constant to a property stored in a non-unicode column on SQL Server, if there was an index on that column, it was not being used.

To address the issue, we now generate non-unicode constants and parameters when these are used in LINQ to Entities queries in comparisons with non-unicode columns.

Original problem EF generated SQL has wrong types

Table Defined

The table I am working with is defined like so;

/****** Object:  Table [dbo].[CA_PICKLSTMAST]    Script Date: 11/09/2010 10:16:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CA_PICKLSTMAST](
    [SOPTYPE] [smallint] NOT NULL,
    [SOPNUMBE] [char](21) NOT NULL,
    [LASTAMENDED] [datetime] NOT NULL,
    [VERSION] [smallint] NOT NULL,
    [VOID] [bit] NOT NULL,
    [ACTIVE] [bit] NOT NULL,
    [PRINTED] [bit] NOT NULL,
    [TIMEPRINTED] [datetime] NULL,
    [ORDERTIMESTAMP] [datetime] NOT NULL,
    [CUSTNAME] [char](65) NULL,
    [PICKVALUE] [numeric](19, 5) NULL,
    [SHIPMTHD] [char](15) NULL,
    [PICKCODE] [char](3) NULL,
    [DOCID] [char](15) NULL,
    [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_CA_PICKLSTMAST] PRIMARY KEY NONCLUSTERED 
(
    [DEX_ROW_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [IX_CA_PICKLSTMAST] ON [dbo].[CA_PICKLSTMAST] 
(
    [SOPTYPE] ASC,
    [SOPNUMBE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_CA_PICKLSTMAST_1] ON [dbo].[CA_PICKLSTMAST] 
(
    [SOPTYPE] ASC,
    [SOPNUMBE] ASC,
    [VERSION] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Random Code to access this pick list' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CA_PICKLSTMAST', @level2type=N'COLUMN',@level2name=N'PICKCODE'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'DOCID of order' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CA_PICKLSTMAST', @level2type=N'COLUMN',@level2name=N'DOCID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Holds the master records for pick lists generated by vb.net canford application.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CA_PICKLSTMAST'
GO

Linq

I want to select all the records that match a certain SOPTYPE and SOPNUMBE.

Dim QueryCA_PICKLSTMAST As ObjectQuery(Of CA_PICKLSTMAST) _
    = CType((From results In oSalesOrderProcessingEnity.CA_PICKLSTMAST _
    Where results.SOPNUMBE = Me.SopNumber And results.SOPTYPE = Me.SOPTYPE), _
    ObjectQuery(Of CA_PICKLSTMAST))
CAPICKLSTMASTBindingSource.DataSource = QueryCA_PICKLSTMAST.Execute(MergeOption.NoTracking)

Where the params are defined as;

Private m_SopNumber As String
Public Property SopNumber() As String
    Get
        Return m_SopNumber
    End Get
    Set(ByVal value As String)
        If m_SopNumber <> value Then
            m_SopNumber = value
            RefreshForm()
        End If

    End Set
End Property

Private m_SOPTYPE As Short = 2
Public Property SOPTYPE() As Short
    Get
        Return m_SOPTYPE
    End Get
    Set(ByVal value As Short)
        m_SOPTYPE = value
    End Set
End Property

Resulting TSQL executed against SQL server

Now using datasets I get TSQL like this;

exec sp_executesql 
N'SELECT SOPTYPE,
 SOPNUMBE, LASTAMENDED, VERSION, VOID, ACTIVE, 
 PRINTED, TIMEPRINTED, ORDERTIMESTAMP, CUSTNAME, 
 PICKVALUE, SHIPMTHD, PICKCODE, 
 DOCID, DEX_ROW_ID
FROM CA_PICKLSTMAST
WHERE 
SOPTYPE=@SOPTYPE AND SOPNUMBE=@SOPNUMBE',
N'@SOPTYPE smallint,@SOPNUMBE char(21)',
@SOPTYPE=2,
@SOPNUMBE='W36077               '

Just as I would get if crafted by hand, however EF creates the following;

exec sp_executesql N'SELECT 
[Extent1].[SOPTYPE] AS [SOPTYPE], 
[Extent1].[SOPNUMBE] AS [SOPNUMBE], 
[Extent1].[LASTAMENDED] AS [LASTAMENDED], 
[Extent1].[VERSION] AS [VERSION], 
[Extent1].[VOID] AS [VOID], 
[Extent1].[ACTIVE] AS [ACTIVE], 
[Extent1].[PRINTED] AS [PRINTED], 
[Extent1].[TIMEPRINTED] AS [TIMEPRINTED], 
[Extent1].[ORDERTIMESTAMP] AS [ORDERTIMESTAMP], 
[Extent1].[CUSTNAME] AS [CUSTNAME], 
[Extent1].[PICKVALUE] AS [PICKVALUE], 
[Extent1].[SHIPMTHD] AS [SHIPMTHD], 
[Extent1].[PICKCODE] AS [PICKCODE], 
[Extent1].[DOCID] AS [DOCID], 
[Extent1].[DEX_ROW_ID] AS [DEX_ROW_ID]
FROM [dbo].[CA_PICKLSTMAST] AS [Extent1]
WHERE 
([Extent1].[SOPNUMBE] = @p__linq__101) 
AND 
([Extent1].[SOPTYPE] = @p__linq__102)',
N'@p__linq__101 nvarchar(6),
@p__linq__102 smallint',
@p__linq__101=N'W36077',
@p__linq__102=2

Notice all the type conversion going on, this is what I guess is causing my issue, nvarchar(6), for example...

This then kills my query performance (I have many more of these running on the Winform, so the form becomes very sluggish.

Execution Plans compared

Above is the entity framework query plan.

Above is the dataset query plan, look at the execution times and the way the load have been moved from an index to costly joins.

Solution

Entity Framework 4 should improve on generated SQL. The issue I experienced here is the unicode one listed on the ADO.NET team blog, Provide mechanism for efficient queries on non-Unicode columns, this is issue 5 in the list.

In .NET 3.5, whenever a constant or a parameter was used in LINQ to Entities query, we treated it as being Unicode. As a result, when comparing a constant to a property stored in a non-unicode column on SQL Server, if there was an index on that column, it was not being used.

To address the issue, we now generate non-unicode constants and parameters when these are used in LINQ to Entities queries in comparisons with non-unicode columns.

Off I go to try it out with EF4 CTP…