Why does my dictionary lookup give a SQL timeout error?

Question

Part of my application uses road names which are stored in a SQL database. The application will access every name record at least once and most will be accessed multiple times. So I decided to load the names data into a dictionary and then look-up against that to reduce the database reads.

The road names table has ~3 million records and I use the following Linq-to-SQL to load it into memory;

Dictionary<String, DbRoadName> roadNames;

using (RoutingDataContext dc = new RoutingDataContext(connectionString))
{
    roadNames = dc.DbRoadNames.ToDictionary(x => x.RoadId);
}

This executes as expected. Stopping the code at this point and putting the mouse over the roadNames variable in Visual Studio shows that the dictionary appears to contain the expected key-value pairs.

However, when I get to the following line later in the program

DbRoadName roadName = roadNames[lookupId];

the program stops with the following exception

.Net SqlClient Data Provider: Timeout expired.

As I understand it, the ToDictionary() method should cause the database query to execute at that point, so why am I getting a SQL timeout error at the dictionary lookup?

Update: I 'fixed' the problem by replacing

DbRoadName roadName = roadNames[lookupId];

with a TryGetValue statement. However, I'm still interested in why the in-memory dictionary was producing a SQL exception.

Full exception details (or something more than the Message at least) could help — MatteoSp
– MatteoSp, Commented Apr 24, 2015 at 14:24
Wow put a roadNames.Count right after that using. My guess is deferred execution. — paparazzo
– paparazzo, Commented Apr 24, 2015 at 14:35
@Blam I did that. The count shows the correct value but the exception still occurs on the lookup. — Steve Bird
– Steve Bird, Commented Apr 24, 2015 at 14:36
Check your DbRoadName properties and constructor. If there's a value missing somewhere maybe you coded a get to obtain the data individually if it wasnt returned by the database, at which point this could all be a specific data error — Spacemonkey
– Spacemonkey, Commented Apr 24, 2015 at 14:38
It's almost certainly deferred execution for something in DbRoadNames. Yes, calling ToDictionary will force execution to get them, but won't force getting all the navigation properties. As Blam asked, What is DbRoadName? It's understood that it's an object, and a record from the table, but does it contain references to other objects? — DrewJordan
– DrewJordan, Commented Apr 24, 2015 at 14:56

gReX · Accepted Answer · 2015-05-08 08:23:35Z

1

to avoid the SQL timeout i would not load the Storage-Model in my dictionary. Instead load the data As Model with the necessary properties like this:

Dictionary<String, DbRoadNameModel> roadNameModelDictionary;
using (RoutingDataContext dc = new RoutingDataContext(connectionString))
{
    roadNames = dc.DbRoadNames
        .Select(roadName => new DbRoadNameModel(roadName.RoadId, roadName.Prop1, roadName.Prop2))
        .ToDictionary(x => x.RoadId);
}

Does this help?

answered May 8, 2015 at 8:23

gReX

1,1101 gold badge21 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Steve Bird Over a year ago

I did something very similar, with a very simple new poco object holding the data in the dictionary. To my surprise, I still got the SQL timeout at the dictionary access even though the dictionary didn't contain any database objects.

gReX Over a year ago

Have you tried the suggestion from Steffenn Winkler or Flaudre? Access a element from the dictionary inside the using-Statement. Maybe usr is right, and the Exception is missleading.

Collectives™ on Stack Overflow

Why does my dictionary lookup give a SQL timeout error?

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related