Linq Date Performance Death

Question

I have a database that stores dates broken into int. That is, the Day, month and year are stored separately in different columns.

Existing SQL queries use the following method:

DATEADD (dd, - 1, DATEADD (mm, Z.LastMonth, DATEADD (yy, Z.LastYear - 1900, 0))) AS last_date

I cannot change the database to store dates instead.

Looking further into the dateadd () function, it converts from an integer (0). Linq to SQL does not have similar functionality, i.e. Convert.ToDateTime (0). This throws an InvalidCastException.

I tried combining strings to create a date, but this is WAYYYYY tooooo sllloowww. the time difference was about 10 minutes.

What else can I do? I don’t even want to start mixing in SQL queries in a project.

Thank.

Answer 1

Can you just write:

table.Select(z => new DateTime(z.LastYear, z.LastMonth, z.LastDay));

---

Ok, I tested this on Linqpad, and it seems that Linq to SQL decides to create some kind of weird character conversion query. I'm not quite sure if this is the source of performance, but you can force such a projection:

var dates = 
    (from z in table
     select new { Year = z.LastYear, Month = z.LastMonth, Day = z.LastDay })
    .AsEnumerable()
    .Select(d => new 
        {
            Date = new DateTime(d.Year, d.Month, d.Day),
            NextDate = new DateTime(d.Year, d.Month, 1).AddMonths(2).AddDays(-1)
        });

It will also give you the last day of the next month.

If you really see such a huge performance, but, of course, I will risk assuming that you are looking in the wrong place, and that there is something else. 90% of all database performance problems are related to bad or non-existent indexes or intermittent queries.

Answer 2

linq sql sql , sql.

Answer 3

, LINQ-TO-SQL: http://msdn.microsoft.com/en-us/library/bb546175.aspx

Answer 4

? , , , , , turn-the-crazy-columns-in-date. LINQ. , SQL Server , , , SQL.

Answer 5

, , (, ).

:

• Make sure that the order of the columns in the index is Year, Month, Day.
• Do not send Date criteria to the database as DateTimes, send integers instead. If you convert the table columns to a date before filtering, the index is useless.

Answer 6

Note. Much faster than string concatenation, it creates an array of characters and independently sets the elements of a character array. This is most practical if you can control the size of the lines at compile time.