Attention: We are retiring the ASP.NET Community Blogs. Learn more >

Contents tagged with SQL

  • Update to my LINQ to SQL performance in VB.NET saga

    2 Comments

    Hello,

    If you have  read my posts before where I was complaining about VB generated sub optimal SQL when using nullable columns in your where clauses, I came to the conclusion I was being silly and not using .Value on those nullable fields in my queries.

    I stumbled across this post today:

    http://blogs.msdn.com/vbteam/archive/2008/03/28/linq-to-sql-and-linq-to-entities-performance-improvements-tim-ng.aspx


    Looks like it was an issue in the end and not me being totally stupid :).

    Read more...

  • Different SQL between VB.NET and C# LINQ to SQL SOLVED!!

    5 Comments

    Hi All,

    Following my previous post I have solved this issue. Somehow accidently I discovered that if the field has allow nulls set on it VB.NET will add the Where Coalesce in your query hence in my case slowing it down dramatically. It seems that C# does not do this. So finally I have my VB.NET sql comming out exactly the same as in C# simply by turning off allow nulls on my database column... Still seems like odd behaivour to me but for now it all works fine.


    Thanks
    Stefan

    Read more...

  • Different SQL between C# and vb.net using LINQ to SQL causes performance issues

    15 Comments

    Hi All,

    I am currently working on a project and we are using VB.NET, I am using LINQ to SQL for my data access. I have just implemented my search query and thought I would check the generated SQL's execution plan and found that the subtree cost was about 7.3. I know that I get different SQL between C# and VB.NET when using LINQ to SQL, as VB.NET seems to add a WHERE COALESCE instead of the straight WHERE that C# will do. In the past I did a quick check on a small query to see if it would make a difference and found that there was not a noticable one.

    Now having a larger more complex query I thought I would check the execution plan for the same query but written in C#, I now get a much smaller SQL query and the subtree cost drops from 7.3 to 0.1. A big difference IMO. Has anyone come across this before and what are your throughts on this. It baffles me that you would get such a difference in queries between the two languages.

    Read more...

  • Gotcha with linq and paging

    2 Comments

    Hey All,

    Had a query which I was paging on the front end. I knew that a certain product was meant to be in my display but could not see it. But on page 2 a product would repeat itself. Odd, got into profiler and looked at the queries. The first page would get a select top 9 which would not do any orderby, the next page would have a query like so: SELECT ROW_NUMBER() OVER (ORDER BY [t15].[test], [t15].[ID], [t15].[CreatedDate] which was ordering by all my columns.

    So I added an orderby to my LINQ query which ordered my results by ProductName, then the paging was working as expected. Had another look in profiler and now I had what I expected.


    So a warning to all, make SQL Profiler your best friend because if you are not careful you could get spanked by LINQ.


    Thanks

    Stefan

    Read more...

  • Tagging Implementation with LINQ

    3 Comments


    I decided to implement tagging on my video library. My first solution was to use one table for the tags. Containing the videoid and tagname. When I loaded in 1,000,000 test tags this became slow to generate my tag cloud data. My new solution was to split up the tags into two tables. One containing distinct tags and the other joining videos to them. I will also show some things I came by when extracting my tag data.

    Tables

    My tables for tagging are as follows:

    Tags = {
        ID,   
        TagName
     }

    VideoTags = {
        ID,
        TagID,
        VideoID
    }

    Read more...

  • Unexpected results with Compiled Queries and LINQ to SQL

    3 Comments

    SOLUTION:

    Ok fired up SQL profiler and see my problem, the non compiled query seems to not evaluate the query until you actualy use the object. But the Compiled version is hitting the database straight away. And as I was not using this data the non compiled version seemed fast as it never hit the database to get the data.. So added a .ToList() on my non compiled query and am now getting the results I expected:

    Non Compiled: 143ms -Compiled: 78ms


    Sorry to all, any input is always appreciated...

    Read more...