Fastest way to check for existence in a table

In order to improve performance on one of the pages in our Java code, I was making a SQL query which, along with the typical section grade information, also pulls in a field to tell whether that particular grade type is in use.  Between my own brains and some quick Google searching, this was the best query I could come up with:

1 2 3

select sg.*, (select top 1 1 from section_roster sr where sr.section_grade_id = sg.section_grade_id) as isUsed from section_grade sg

I assumed that in the absence of any ordering, "select top 1 1" would be converted by SQL Server into a sort of "exists" statement, and would therefore be the fastest query for the job.  But just out of curiosity, I ran a similar LINQ query in LINQPad to see what SQL would be generated.  Based on those results, I created the following query:

1 2 3 4 5

select sg.*, (case when exists(select null from section_roster sr where sr.section_grade_id = sg.section_grade_id) then 1 else null end) as isUsed from section_grade sg

Although it's not as simple a query, I was able to drop the execution time from about 27 milliseconds to about 9 milliseconds.

Enumerate across a date range in Linq using "yield"

I'm making a generic data table displayer which should be able to display any set of data that is formatted properly.  It can either deduce the table headers based on the data it receives, or it can use ones that I specify via a list of key/value pairs.  For example, by setting the value of this property:

1	IEnumerable<DataPair<object, String>> RowKeysWithHeaders

... I can make the row headers display all of the Strings on the right side of the given DataPairs in the IEnumerable's order, and the data for each row of the table will be aligned to match the keys on the left side of the DataPairs.

Now, let's say I'm using the data returned by the query I mentioned yesterday, which will only include dates for which there are TrackedTime entries, but I want to display all dates within a given date range, and simply leave cells empty if there are no entries for those dates.  Furthermore, I want to specify how the dates are formatted.  I could create a list of date/String pairs to use as row keys with headers, like this:

1 2 3 4 5 6 7 8 9 10 11

List<DataPair<object, String>> dateHeaders = new List<DataPair<object, String>>(); for(DateTime date = startDate; date < endDate; date.AddDays(1)) { DataPair<object, String> header = new DataPair<object, String> { LeftObject = date, RightObject = date.ToShortDateString() }; dateHeaders.Add(header); } this.ReportTableDisplay1.RowKeysWithHeaders = dateHeaders;

But that would be kind of wasteful, wouldn't it?  It would mean creating an entire List of dates, when all I need is to print a bunch of consecutive dates--something I should be able to do mathematically on the fly.

A more efficient way would be to create an IEnumerable class (and an accompanying IEnumerator class) that know how to iterate across dates.  But that's a lot more work and a lot more code for something that should be relatively simple.

Thanks to the yield operator, there is a better way.  This simple method:

1 2 3 4 5 6 7 8 9

public static IEnumerable<DateTime> DaysInRange(DateTime startDate, DateTime endDate) { DateTime current = startDate; while (current <= endDate) { yield return current; current = current.AddDays(1); } }

... will create an enumerable object that does exactly what I need it to, without the need for any extra classes or anything.  Here's how I use it:

1 2 3 4 5 6

this.ReportTableDisplay1.RowKeysWithHeaders = from d in DaysInRange(startDate, endDate) select new DataPair<object, String> { LeftObject = d, RightObject = d.ToShortDateString() };

This simple LINQ statement then gives me an IEnumerable that iteratively creates new DatePairs as the program traverses it.  You have to admit, that's pretty smooth.  That means that if I decide to paginate the table results, I can use the Take() method, and the system won't even produce DataPairs for dates that I don't iterate over.  I can also move my DaysInRange method into a common utility class where it can be accessed any time I need to traverse a range of dates.  It's a great example of how we can use LINQ in conjunction with the yield operator to create simple, efficient code.

It can also be used to highlight one of the dangers of accepting IEnumerable arguments.  Since I literally have no idea how expensive it might be to iterate over a given IEnumerable, I need to make sure my ReportTableDisplay class only iterates over the RowKeysWithHeaders once.  Otherwise I could end up creating who-knows-how-many copies of exactly the same DataPair without even realizing it.  Just think how that would turn out if I was using a truly expensive IEnumerable--one whose IEnumerator begins by accessing data from over the Internet, for example!

Improved Dynamic Query

Yesterday I figured out how we could use C# Expressions to filter query results by any number of criteria. Today I refined my approach somewhat. Instead of the long, complex LINQ query I used there, I now have:

1 2 3 4 5 6 7 8 9

var userTimes = (from t in times group t by new {t.User.UserName, t.TargetDate} into ut select new { UserName = ut.Key.UserName, TargetDate = ut.Key.TargetDate, Minutes = ut.Sum(t => t.Minutes) } );

This produces a faster, more reasonable set of SQL code which returns almost identical data:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

-- Region Parameters DECLARE @p__linq__2 DateTime SET @p__linq__2 = '2009-02-14 16:01:50.069' -- EndRegion SELECT 1 AS [C1], [GroupBy1].[K2] AS [UserName], [GroupBy1].[K1] AS [TargetDate], [GroupBy1].[A1] AS [C2] FROM ( SELECT [Extent1].[TargetDate] AS [K1], [Extent2].[UserName] AS [K2], SUM( CAST( [Extent1].[Minutes] AS int)) AS [A1] FROM [dbo].[TrackedTime] AS [Extent1] LEFT OUTER JOIN [dbo].[aspnet_Users] AS [Extent2] ON [Extent1].[UserId] = [Extent2].[UserId] WHERE ([Extent1].[TargetDate] > @p__linq__2) AND ([Extent1].[TargetDate] < (GetDate())) GROUP BY [Extent1].[TargetDate], [Extent2].[UserName] ) AS [GroupBy1]

Note that I avoided the problem I mentioned here by returning a simple set of native types rather than Entity Framework objects. This approach wouldn't be best under normal circumstances. If I'm querying the database for usernames, for example, I will often want to have more user data as well (like their real names) available to my front-end code. In that case, I could benefit from using the Entity Framework to get all the object data in a single roundtrip. However, if I'm pulling data for reporting purposes, I generally know exactly what data I want to be displaying. In that case it's more important to keep the data set small and fast. I've seen poorly-designed reporting engines pull so much data from the database that the VM runs out of memory, which causes all sorts of problems.

Finally, rather than relying on the LINQ framework to package my objects into custom classes for me, I created a generic TableData class, along with an extender that allows me to do this:

1 2 3 4 5

public TableData<String, DateTime, int> generateData(List<Filter<TrackedTime>> dateFilters) { ... return userTimes.ToTable(d => d.UserName, d => d.TargetDate, d => d.Minutes); }

The next step would be to create a generic web control that creates a front-end table when given the returned report data. That way we can use a single control to display all kinds of report data, rather than creating a new method to display each new set of report data.

Dynamic Queries in Entity Framework

I've been working on a proof-of-concept for building dynamic Linq queries in the Entity Framework. The idea is to have any number of criteria that a user can set to limit or expand the scope of (for example) a report.

I still have a long way to go before it would be ready to use for real, but here's what I've been able to do:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

public class ReportPerUserPerDay2 { public List<UserNamesWithDatesWithMinuteTotals> generateData(List<Filter<TrackedTime>> dateFilters) { TimesheetEntities context = new TimesheetEntities(); IQueryable<TrackedTime> times = context.TrackedTime; foreach (Filter<TrackedTime> dateFilter in dateFilters) { times = times.Where(dateFilter.getPredicate()); } var userTimes = (from t in times group t by t.User.UserName into ut from ut2 in (from t in ut group t by t.TargetDate into ut3 select new DatesWithMinuteTotals() { TargetDate = ut3.Key, Minutes = ut3.Sum(t => t.Minutes) }) group ut2 by ut.Key into ut4 select new UserNamesWithDatesWithMinuteTotals() { UserName = ut4.Key, Dates = from t in ut4 select t } ); return userTimes.ToList(); } } public class DatesWithMinuteTotals { public DateTime TargetDate; public long Minutes; } public class UserNamesWithDatesWithMinuteTotals { public String UserName; public IEnumerable<DatesWithMinuteTotals> Dates; }

So I can pass the generateData method filters like this one:

1 2 3 4 5 6 7 8 9 10 11 12 13

public partial class Controls_ReportFilters_FilterDateRange : System.Web.UI.UserControl, TimesheetReporting.Filters.Filter<TrackedTime> { ... public Expression<Func<TrackedTime, bool>> getPredicate() { if (StartDate.Ticks == 0 && EndDate.Ticks == 0) return t => true; if (StartDate.Ticks == 0) return t => t.TargetDate <= EndDate; if (EndDate.Ticks == 0) return t => t.TargetDate >= StartDate; return t => t.TargetDate <= EndDate && t.TargetDate >= StartDate; } }

and the Linq statement will auto-generate this crazy SQL code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55

-- Region Parameters DECLARE p__linq__2 DateTime SET p__linq__2 = '2009-02-13 18:13:53.470' -- EndRegion SELECT [Project7].[UserName] AS [UserName], [Project7].[C1] AS [C1], [Project7].[C3] AS [C2], [Project7].[TargetDate] AS [TargetDate], [Project7].[C2] AS [C3] FROM ( SELECT [Distinct2].[UserName] AS [UserName], 1 AS [C1], [Project6].[TargetDate] AS [TargetDate], CASE WHEN ([Project6].[C2] IS NULL) THEN CAST(NULL AS bigint) ELSE CAST( [Project6].[C1] AS bigint) END AS [C2], [Project6].[C2] AS [C3] FROM (SELECT DISTINCT [Project2].[UserName] AS [UserName] FROM (SELECT @p__linq__2 AS [p__linq__2], [Distinct1].[UserName] AS [UserName] FROM ( SELECT DISTINCT [Extent2].[UserName] AS [UserName] FROM [dbo].[TrackedTime] AS [Extent1] LEFT OUTER JOIN [dbo].[aspnet_Users] AS [Extent2] ON [Extent1].[UserId] = [Extent2].[UserId] WHERE [Extent1].[TargetDate] > @p__linq__2 ) AS [Distinct1] ) AS [Project2] CROSS APPLY (SELECT [Extent3].[TargetDate] AS [K1] FROM [dbo].[TrackedTime] AS [Extent3] LEFT OUTER JOIN [dbo].[aspnet_Users] AS [Extent4] ON [Extent3].[UserId] = [Extent4].[UserId] WHERE ([Extent3].[TargetDate] > @p__linq__2) AND (([Extent4].[UserName] = [Project2].[UserName]) OR (([Extent4].[UserName] IS NULL) AND ([Project2].[UserName] IS NULL))) GROUP BY [Extent3].[TargetDate] ) AS [GroupBy1] ) AS [Distinct2] LEFT OUTER JOIN (SELECT [Project5].[UserName] AS [UserName], [GroupBy2].[K1] AS [TargetDate], [GroupBy2].[A1] AS [C1], 1 AS [C2] FROM (SELECT @p__linq__2 AS [p__linq__2], [Distinct3].[UserName] AS [UserName] FROM ( SELECT DISTINCT [Extent6].[UserName] AS [UserName] FROM [dbo].[TrackedTime] AS [Extent5] LEFT OUTER JOIN [dbo].[aspnet_Users] AS [Extent6] ON [Extent5].[UserId] = [Extent6].[UserId] WHERE [Extent5].[TargetDate] > @p__linq__2 ) AS [Distinct3] ) AS [Project5] CROSS APPLY (SELECT [Extent7].[TargetDate] AS [K1], SUM( CAST( [Extent7].[Minutes] AS int)) AS [A1] FROM [dbo].[TrackedTime] AS [Extent7] LEFT OUTER JOIN [dbo].[aspnet_Users] AS [Extent8] ON [Extent7].[UserId] = [Extent8].[UserId] WHERE ([Extent7].[TargetDate] > @p__linq__2) AND (([Extent8].[UserName] = [Project5].[UserName]) OR (([Extent8].[UserName] IS NULL) AND ([Project5].[UserName] IS NULL))) GROUP BY [Extent7].[TargetDate] ) AS [GroupBy2] ) AS [Project6] ON ([Project6].[UserName] = [Distinct2].[UserName]) OR (([Project6].[UserName] IS NULL) AND ([Distinct2].[UserName] IS NULL)) ) AS [Project7] ORDER BY [Project7].[UserName] ASC, [Project7].[C3] ASC

which, amazingly enough, rather efficiently produces exactly the information I wanted, and no more. Pretty slick, eh?

LinqPad, my new favorite tool

As I was looking into LINQ, I found a tool called LINQPad, which allows you to run LINQ queries on a typical SQL database. "Cool," I thought, "That might help me become familiar with LINQ syntax." I had no idea of the power of this tool.

When it first connects to your chosen database, LINQPad automatically generates a data-object mapping based on the foreign and primary keys in your database. So rather than having to worry about doing an INNER JOIN ON table1.some_column = table2.some_column, you can just use standard LINQ syntax to join the objects based on their relationships. Another advantage is that it provides links from one table to another, so you can easily jump around the table structure, seeing how things are interconnected.

Visualization of the returned data is much more intuitive and powerful than the simple grid that is returned by normal applications. For example, using the "select new" statement I discussed in my last posting, you can see lists of objects grouped within other objects, fully collapsible and everything! For this reason, I prefer it to SQL Server Management Studio for viewing information in the database.

LINQPad also lets you see what code is generated based on the LINQ queries you put in. You can see the Lambda code, the SQL code, and even the Instruction Language code that comes from the statements you write. That's very handy for understanding the performance of your code.

LINQPad's uses aren't limited to Linq to SQL, however! You can reference the .dll file from your Entity Framework data layer and run queries as if you were writing code right within Visual Studio. It holds true to its claim to be a "code snippet IDE." In fact, you don't even have to use it for LINQ! Say you just want to run a method and see what kind of data comes out: rather than creating a temporary unit-test in your Visual Studio directory, you can simply write the C# code snippet in LINQPad and run it!

If you're new to LINQ, LINQPad actually comes with a hearty set of code samples to help you get started.

Oh, yeah, and you can also use it to run SQL queries and commands if you want.

Overall, I've been extremely impressed by all that I can do with LINQPad. Now I'm trying to get my bosses to invest in an Autocomplete license for it, and looking forward to the increased productivity I can expect from that feature.

Joining entities using the "new" operator

I took some time in my last post to explain that two database queries are better than many database queries. Namely, it's better to do one database query and create a mapping with the returned data, than it is to perform a new database query for every chunk of information that we want. (I should note that this is obviously only true if the results of your two database queries can be limited to a size that roughly fits the amount of data you'd otherwise be requesting. If you're clever, this can usually be the case.)

As I played around a bit more with Linq, I found another syntax that can be used to join objects together. For example, the following Linq (to SQL) code:

1 2	from ec in EntityCategory select new {ec, te = from te in ec.TrackedEntity select te}

... will produce objects of a new Anonymous type with the following elements:

• EntityCategory ec
• IEnumerable te

This approach can use a single SQL round-trip, which you'd think would be ideal. After all, if two is better than many, shouldn't one be better than two? But there are a few things to keep in mind:

1. Because it creates an Anonymous data type, the returned results don't really lend themselves to being passed around your program as arguments.
   
2. There is actually quite a bit of overhead associated with this method. The generated SQL query is a lot more expensive for the database, and the returned data set has a lot of unnecessarily-duplicated data. Once the data returns from the database, it has to be parsed and organized by the framework (Entity or Linq to SQL) in much the same way as the JoinedEntityMap class that I mentioned in my last post. So this syntax can run much more slowly, despite reducing the number of database round-trips.

That said, this syntax can be useful in certain circumstances, so it's good to keep this alternative in the back of your mind.

Joining entities to avoid database roundtrips

In my last post, I hinted that I had an alternative in mind for the Eager Loading problem that has vexed me over the past few days.

First of all, it's important to understand what we intend to gain by eager loading--fewer database round-trips. You see, every time your program asks the database for information, there is considerable overhead with establishing the connection, waiting for your request to go over the wire, and waiting for the response to come back. If your queries return large data sets, this overhead will have a minimal impact. But if you're doing a whole bunch of tiny queries, this overhead can make a huge difference. In my case, where I often work from home and connect to the database at work, the overhead from these so-called "round-trips" can actually take more time than everything else in a typical page load.

So rather than saying:

1 2 3 4 5 6 7 8 9 10 11

TrackedEntityBO tebo = new TrackedEntityBO(context); foreach (RepeaterItem item in Repeater1.Items) { CheckBoxList list = (CheckBoxList)item.FindControl("CheckBoxList1"); foreach (ListItem li in list.Items) { TrackedEntity te = tebo.getById(Decimal.Parse(li.Value)); // Run 1 query per TrackedEntity ... } }

... , I could save a lot of time by saying:

1 2 3 4 5 6 7 8 9 10 11 12 13

TrackedEntityBO tebo = new TrackedEntityBO(context); // Runs a single linq query for all TrackedEntities Dictionary<decimal, TrackedEntity> ueMap = tebo.getAll().ToDictionary(c => c.TrackedEntityId); foreach (RepeaterItem item in Repeater1.Items) { CheckBoxList list = (CheckBoxList)item.FindControl("CheckBoxList1"); foreach (ListItem li in list.Items) { TrackedEntity te = ueMap[Decimal.Parse(li.Value)]; ... } }

The folks who designed the Entity Framework were aware of this, and they took action to prevent round-trips as much as possible. For example:

1. Each context instance can cache a lot of the data that it gathers. If a later LINQ query on the same context can be determined to contain only data that has previously been cached, the Framework will use the cached data rather than executing another query.
2. Unlike Linq to SQL, the Entity Framework will not make a database query without being asked. For example, the following code will produce an exception because the EntityCategory object never got loaded into the context:
   
   
   
   

   1 2 3

   TrackedEntity e = (from te in context.TrackedEntity select te).First(); Console.WriteLine (e.EntityCategory.Name);

As I pointed out in my last post, you simply can't make assumptions regarding which items have been loaded into the context. That means if you want to get certain objects associated with other objects, you'll either have to do a whole bunch of queries, or put both groups of objects into a data structure that maps them to one another. Thanks to the power of the .NET framework, however, this isn't all that difficult. For example, with relatively few lines of actual code, I was able to make the following statements possible:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

IQueryable<TrackedEntity> teQuery = from te in context.TrackedEntity orderby te.Name select te; IQueryable<EntityCategory> ecQuery = from te in teQuery let ec = te.EntityCategory orderby ec.Name select ec; JoinedEntityMap<EntityCategory, TrackedEntity> j = ecQuery.ToMap(c => c.EntityCategoryId, teQuery, te => te.EntityCategory); foreach (EntityCategory c in j.UniqueKeys) { foreach (TrackedEntity e in j[c]) { Console.WriteLine(c.Name + ": " + e.Name); } }

With negligible memory overhead, the above code causes a grand total of two SQL queries and takes 110 milliseconds to run with 4 EntityCategories and 47 TrackedEntities, as opposed to the code below, which takes 251 milliseconds:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

IQueryable<EntityCategory> ecQuery = from ec in context.EntityCategory orderby ec.Name select ec; foreach (EntityCategory c in ecQuery) { IQueryable<TrackedEntity> teQuery = from te in context.TrackedEntity where te.EntityCategory.EntityCategoryId == c.EntityCategoryId orderby te.Name select te; foreach (TrackedEntity e in teQuery) { Console.WriteLine(c.Name + ": " + e.Name); } }

With that kind of difference on such a small scale, you can imagine how much of a difference it could make if you had hundreds, or even thousands of records in each table!

Another potential benefit to this approach is that the main page's code can create the data it knows will be needed, and then hand strongly-typed data to each user control in turn. It not only makes it possible for the user controls to be more specific in what sort of data they need as parameters, but it also increases the transparency of your data accesses. With most of your calls to the data layer occurring in one code file, it becomes obvious what's taking so much time to load. And if you're not using an HttpContext-based Entity context (which we are), it can also simplify the problems you'll run into with conflicting contexts.