none
Generated SQL different between C# and VB.NET RRS feed

  • Question

  • Hi, i have two applications (a test one in C# and one in VB.NET). In both i have (almost identical) Linq2SQL queries. They look something like this:

    var devices = from x in dbContext.Devices

    var groupings = from x in devices
                            select new {
                                                    Key = x.ID,
                                                    SubList = from y in db.SubDevices
                                                                            where y.DeviceID = x.ID
                                                                            select y
                                                    }

    In C# this generates a sequence of SQL select statements for each device, which runs in about 5 seconds, in VB.NET it generates one SQL statement, which takes about 30 seconds to run.

    Any idea as to why C# and VB.NET give such different outputs?

    Cheers, Ben

     

    Wednesday, November 23, 2011 9:30 PM

Answers

  • Without seeing both in the context they live, it would be hard too say. I recommend that you use Linqpad and run the query in C#/VB and then in the output section see what lamda is generated and the final SQL then IL. From that you might divine the difference between the two and which is causing the slower sql.

     


    William Wegerson (www.OmegaCoder.Com)
    Wednesday, November 23, 2011 10:54 PM
    Moderator
  • Hi Ben,

    >> CallSegment.MutateAndReturn(s, x.ID, "Extn")

    I think you can retrieve the Anonymous Type, then call MutateAndReturn method in memory. You can use SQL Profiler to watch the performance hit in database.

    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.

    Tuesday, November 29, 2011 7:54 AM
    Moderator

All replies

  • Without seeing both in the context they live, it would be hard too say. I recommend that you use Linqpad and run the query in C#/VB and then in the output section see what lamda is generated and the final SQL then IL. From that you might divine the difference between the two and which is causing the slower sql.

     


    William Wegerson (www.OmegaCoder.Com)
    Wednesday, November 23, 2011 10:54 PM
    Moderator
  • Thanks, LinqPAD helped! I've now got the C# and VB.NET code generating the same SQL. Now problem two :)
    I have a method I call during my select, e.g.
    From s In callSegs
    Where (s.StartTime.Date >= new DateTime(2011, 10, 1) And s.StartTime.Date <= new DateTime(2011, 10, 31))
    Select CallSegment.MutateAndReturn(s, x.ID, "Extn")

    The mutate and return method in C# has little or no performance inpact. If i have it there, same SQL as VB.NET is generated, and a complete listen is returned in about 6 seconds. However, in VB.NET that same MutateAndReturn adds an impact on the return time of about 25 seconds.
    This is slowly melting my brain, so any pointers would agian be of great assistance!
    Thanks,
    Ben
    Thursday, November 24, 2011 11:00 AM
  • Hi Ben,

    >> CallSegment.MutateAndReturn(s, x.ID, "Extn")

    I think you can retrieve the Anonymous Type, then call MutateAndReturn method in memory. You can use SQL Profiler to watch the performance hit in database.

    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.

    Tuesday, November 29, 2011 7:54 AM
    Moderator
  • Hi,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

    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.

    Tuesday, December 6, 2011 2:53 AM
    Moderator