CODEWITHSUNDEEP
sqlperformancelinqindexing
Lord Vermillion
Lord Vermillion

Reputation: 5424

SQL performance linq indexes

I have a SQL database where i store routes. I store routeinformation in one table and the coordinates in another table.

Right now i have around 40 routes with 50k coordinates if you sum all routes.

I use the following LINQ code to get the data

var query = (from b in db.routes select new { 
                    name = b.name, 
                    id = b.route_id,
                    coor = b.coordinates.Select(c => new 
                    { 
                           seq = c.sequence,
                           lat = c.position.Latitude, 
                           lon = c.position.Longitude 
                    }) });

This query takes 4.5sec to execute, i find that to be kind.

I'm new to indexes, right now both primary-keys are clustered indexes, and primary keys are normal(??)-indexes that i created with the following SQL-command:

CREATE INDEX IX_route on [db].[coordinates] (route_id)

Is my database slow or is this normal for this amount of data?

Upvotes: 1

Views: 648

Answers (1)

Jesse C. Slicer
Jesse C. Slicer

Reputation: 20157

If you can deal with your results flattened, this query might give you better performance:

            var query = from b in db.routes
                        from c in b.coordinates
                        select new
                        {
                            name = b.name,
                            id = b.route_id,
                            coor = new
                            {
                                seq = c.sequence,
                                lat = c.position.Latitude,
                                lon = c.position.Longitude
                            }
                        };

Upvotes: 1

Related Questions