Linq Date Performance Death

Question

Hi,

I have a database that stores dates broken into int's. I.e. Day, Month and Year are stored separately in different columns.

In existing SQL queries, the method used is:

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's converting from an integer (0). Linq to SQL does not have a similar functionality i.e. Convert.ToDateTime(0). That results in an InvalidCastException.

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

What else can I do? I don't particularly want to start mixing in SQL queries into the project either.

Thanks.

Answer 1

You could have linq to sql map to a custom sql statement or stored procedure to get the same sql performance.

Answer 2

Can you not just write:

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

---

Alright, I tested this in Linqpad and it seems that Linq to SQL does decide to generate some weird character-conversion query. I'm not entirely convinced that this is the source of the perf issue, but you can force a projection like this:

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)
        });

This will also get you the last day of the next month.

If you're really seeing such a huge performance, difference, though, I would venture a guess that you're looking in the wrong place, and that there's something else that's different. 90% of all database-related performance problems are due to poor or nonexistent indexing or non-sargable queries.

Answer 3

You can map the function to make it usable in LINQ-TO-SQL: http://msdn.microsoft.com/en-us/library/bb546175.aspx

Answer 4

Can you add a view to the database? If you can, you can just define a view that looks just like your table, except with a real date, since it's backed by your turn-those-crazy-columns-into-a-date query. Then just query that with LINQ. I don't remember if SQL Server has smart view caching, but if so, it might actually perform better than your direct SQL.

Answer 5

On the performance side of things, I presume that this date is your primary filtering criteria (the one that you expect to be indexed).

If that is the case:

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

Answer 6

Note: Much faster than concatenating strings is creating a character array and setting the elements of the character array yourself. This is most practical if you can control at compile time the size of the strings.