none
LINQ to Entities with variable substitution slow RRS feed

  • Question

  • hi

    we do have "identified" a problem with LINQ to Entities and variables.

    this happens in this specific case there are other similar queries even more complex ones where that does not seem to be a problem...

    following Query Expression Syntax of LINQ using a variable nopicking of type string takes about 10s or more to execute (in LinqPad and it is also slow in VS2010)

        var nopicking = "30518268";
        var q = from t1 in IOZ_NOUHIN_MEISAI
                 join t2 in IOZ_TANZAKU on new { t1.NO_SIJI, t1.NO_SIJI_SUB } equals new { t2.NO_SIJI, t2.NO_SIJI_SUB }
                 where t1.NO_PICKING == nopicking
                 select t1.NO_PICKING;
        var count = q.Count();
    

    the same query but instead of the variable nopicking we insert the value runs instantly

        var q = from t1 in IOZ_NOUHIN_MEISAI
                 join t2 in IOZ_TANZAKU on new { t1.NO_SIJI, t1.NO_SIJI_SUB } equals new { t2.NO_SIJI, t2.NO_SIJI_SUB }
                 where t1.NO_PICKING == "30518268"
                 select t1.NO_PICKING;
        var count = q.Count();

    if anyone has any explanation of why or if this is a bug in any of the following APIs we use

    API list:

    > .NET Framework 4.0

    > EntityFramework 4.1 with DbContext as base (4.1 because we use RIA Services 1.0 SP2 with DbContext base)

       (we use DbContext with database first not code first!)

    > Oracle ODAC 11.2 Release 4 (ODTwithODAC112030) on Oracle 11g database

    any advice or explanation is very much apreciated


    Wednesday, November 7, 2012 10:32 AM

Answers

  • hi

    we found a solution to this problem.

    It has to do with Unicode and fulltable scan.

    we had to write t1.NO_PICKING == nopicking like this

    t1.NO_PICKING == EntityFunctions.AsNonUnicode(nopicking)

    to tell the ODP.NET provider to treat a string as Varchar2 instead of NVarchar2 which it seems to do per default.

    I found the answer here

    Hope this helps someone

    Thank you for the advices

    Stefan

    • Marked as answer by Stefan U7 Monday, November 12, 2012 2:15 AM
    Monday, November 12, 2012 2:15 AM

All replies

  • Hi,

    Are you able to show me the SQL that is being generated by the two queries? I'm not familiar enough with Oracle to be able to tell you how to do that, but it should be possible to find out somehow.

    If the query is really slower in the first example and not in the second then my guess is that for some reason the provider is generating different SQL for the two of them. I don't know enough about this part of the framework to be able to tell you more than that, but if I get the SQL outputted and my guess is right then I should be able to get someone that knows more about it to give you more information about why it happens.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.

    Wednesday, November 7, 2012 7:52 PM
    Moderator
  • hi

    yes I extract the following SQL querys using q.ToString() before invoking Count() (i would like to see the count query tough unless count is executed on Object instead in the database, is there an easy way to confirm such behaviour?)

    case bad: with variable nopicking

    SELECT 
    "Extent1"."NO_PICKING" AS "NO_PICKING"
    FROM  "IOZ_OBJOWN"."IOZ_NOUHIN_MEISAI" "Extent1"
    INNER JOIN "IOZ_OBJOWN"."IOZ_TANZAKU" "Extent2" ON ("Extent1"."NO_SIJI" = "Extent2"."NO_SIJI") AND ("Extent1"."NO_SIJI_SUB" = "Extent2"."NO_SIJI_SUB")
    WHERE ("Extent1"."NO_PICKING" = :p__linq__0)
    

    case good: setting the value direct

    SELECT 
    "Extent1"."NO_PICKING" AS "NO_PICKING"
    FROM  "IOZ_OBJOWN"."IOZ_NOUHIN_MEISAI" "Extent1"
    INNER JOIN "IOZ_OBJOWN"."IOZ_TANZAKU" "Extent2" ON ("Extent1"."NO_SIJI" = "Extent2"."NO_SIJI") AND ("Extent1"."NO_SIJI_SUB" = "Extent2"."NO_SIJI_SUB")
    WHERE ('30518268' = "Extent1"."NO_PICKING")

    I will also look into a way to get the query including Count() even tough it does not make the difference

    there are only two reccords to be returned and ToList() resolves into the same behaviour

    thank you


    Thursday, November 8, 2012 3:33 AM
  • Hi Stefan,

    When you execute the generated PL/SQL statements that you pasted above against the server, do both of them execute quickly? In other words, is the time difference located in the database or is it located in the client?

    David.

    Friday, November 9, 2012 12:33 AM
  • hi David,

    that was a good advice thank you I executed the following two statements against our database and the result was astonishing the bad case took 14s where as the good case returned instantly

    case bad (14s):   I suppose the Oracle EF provider does execute the following but I can not confirm this yet, investigating...

    def p__linq__0 = '30518268'
    SELECT 
    "Extent1"."NO_PICKING" AS "NO_PICKING"
    FROM  "IOZ_OBJOWN"."IOZ_NOUHIN_MEISAI" "Extent1"
    INNER JOIN "IOZ_OBJOWN"."IOZ_TANZAKU" "Extent2" ON ("Extent1"."NO_SIJI" = "Extent2"."NO_SIJI") AND ("Extent1"."NO_SIJI_SUB" = "Extent2"."NO_SIJI_SUB")
    WHERE ("Extent1"."NO_PICKING" = &p__linq__0)

    case good (instantly):

    SELECT 
    "Extent1"."NO_PICKING" AS "NO_PICKING"
    FROM  "IOZ_OBJOWN"."IOZ_NOUHIN_MEISAI" "Extent1"
    INNER JOIN "IOZ_OBJOWN"."IOZ_TANZAKU" "Extent2" ON ("Extent1"."NO_SIJI" = "Extent2"."NO_SIJI") AND ("Extent1"."NO_SIJI_SUB" = "Extent2"."NO_SIJI_SUB")
    WHERE ("Extent1"."NO_PICKING" = '30518268')

    this might be entirely an Oracle problem

    Stefan



    Friday, November 9, 2012 10:31 AM
  • hi

    follow up information

    I identified that Oracle does subsitute

    def p__linq__0 = '30518268'

    with

    30518268

    which executes string vs number comparison which is slow

    I will investigate if this substitution is correct or not (Oracle settings?) or if it is an ODP.NET bug

    we also tested

    def p__linq__0 = ''30518268''

    which is substituted by

    '30518268'

    which results in string vs string comparision which is "fast"

    Stefan

    Friday, November 9, 2012 11:22 AM
  • hi

    we found a solution to this problem.

    It has to do with Unicode and fulltable scan.

    we had to write t1.NO_PICKING == nopicking like this

    t1.NO_PICKING == EntityFunctions.AsNonUnicode(nopicking)

    to tell the ODP.NET provider to treat a string as Varchar2 instead of NVarchar2 which it seems to do per default.

    I found the answer here

    Hope this helps someone

    Thank you for the advices

    Stefan

    • Marked as answer by Stefan U7 Monday, November 12, 2012 2:15 AM
    Monday, November 12, 2012 2:15 AM