none
Poor performance or not using best way to use Entity Framework 4 ? RRS feed

  • Question

  • Hi,
    I m using MySql 5.1.45 and the .Net Connector 6.3.6

    I have two tables
    One table "mailing" and one "adresses" (addresses).
    For one query I have to search for names in the addresses for a specified mailing.
    I have this query :

    Dim m as mailing = initialization with a proper mailing object

    Dim q = From a In m.adresses Where a.nom Like filtre

    The problems is that MySQL Server gets this query :
    SELECT
    `Extent1`.`id`,
    ...(other fields)
    FROM `adresses` AS `Extent1`
     WHERE `Extent1`.`mailing_id` = 7


    So it seems that all records are loaded (usually more than 100'000) and then the request on field nom is done in memory...
    My index on the 'nom' field is event not used...

    What am I doing wrong. Or should I leave EF and go back to old good SQL querying ?

    Thanks for your posts in advance, there should be tricks that I don't know yet in EF.

    Steve
    Sunday, July 3, 2011 10:39 AM

Answers

  • Hi Steve;

    I was just going crazy here trying to figure out how it was translating StartsWith string function to using LOCATE on the DB and then I came across this web page which states that the MySQL provider is translating it incorrect. Please see this link, Entity Framework 4 - Support for "LIKE" function, and at the bottom of the page they have the following as the suggested fix: Add the following to the .NET 4 version of the MySqlProviderManifest class:. I converted the function to VB from C#.

    Public Overrides Function SupportsEscapingLikeArgument(ByRef escapeCharacter As Char) As Boolean
    	escapeCharacter = "~"C
    	Return True
    End Function
    
    


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Saturday, July 9, 2011 2:17 AM

All replies

  • Hi Steve;

    Can you please post the actual query because Linq to Entity Framework does not have a actual Like operator. It is hard to answer the question without it. In Linq to Entity Framework to have a SQL statement sent to the server with the Like operator you need to use the C# Contains method.

     


    Fernando

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Sunday, July 3, 2011 3:42 PM
  • The actual query is :

    Dim q = From a In m.adresses Where a.nom Like "Paul%"

     

    The server get this query :
    `Extent1`.`id`,
    ...(other fields)
    FROM `adresses` AS `Extent1`
    WHERE `Extent1`.`mailing_id` = 7

    but it's working, so i suppose the like is done in memory.

    but i have an index on this field.

    i suppose the MySQL entity library is not so well written.

     


    Steve
    Friday, July 8, 2011 8:23 AM
  • Hi stivo,

    Welcome!

    I'm not familiar with MySQL, I didn't see the "Like" in your T-sql. We also use "Contains, EndsWith, StartsWith" to do the similar thing with " Like".

    You can refer this link to watch the last content of this page(http://www.asp.net/entity-framework/tutorials/the-entity-framework-and-aspnet-%E2%80%93-getting-started-part-3)

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, July 8, 2011 9:50 AM
    Moderator
  • Hi Steve;

    Because you want to find all names in the column nom you will need to use the StartsWith method and remove the % in the string. If you were to be looking to a string ending with some character string then you would use EndsWith or if it were any where in the string you would use Contains. The following query should give you want you want.

    Dim q = From a In m.adresses _
        Where a.nom.StartsWith("Paul")
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Friday, July 8, 2011 2:55 PM
  • Yep,

    i already did try this :

    Dim q = From a In myProjectEntities.adresses Where a.mailing_id = m.id And (a.nom.StartsWith('Hein'))

    But the performance stay poor because the query generated still doesn't use a LIKE and mysql server doesn't use therefore the index on the field 'nom'. The query generated is the following :



    SELECT
    `Extent1`.`id`,
    ... (more fields)
    FROM `adresses` AS `Extent1`
    WHERE (`Extent1`.`mailing_id` = 7) AND ((LOCATE('Hein', `Extent1`.`nom`)) = 1)

     

    thanks for the post anyway...


    Steve
    Friday, July 8, 2011 5:50 PM
  • Hi Steve;

    I was just going crazy here trying to figure out how it was translating StartsWith string function to using LOCATE on the DB and then I came across this web page which states that the MySQL provider is translating it incorrect. Please see this link, Entity Framework 4 - Support for "LIKE" function, and at the bottom of the page they have the following as the suggested fix: Add the following to the .NET 4 version of the MySqlProviderManifest class:. I converted the function to VB from C#.

    Public Overrides Function SupportsEscapingLikeArgument(ByRef escapeCharacter As Char) As Boolean
    	escapeCharacter = "~"C
    	Return True
    End Function
    
    


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Saturday, July 9, 2011 2:17 AM
  • Seems that entity framework is just very slow... and very buggy (at least the mysql library)...

    after some tests i can tell that entity framework (connected to mysql) is approx. 3-4 slower to save records in a table...


    Steve
    Thursday, July 28, 2011 1:56 PM