CODEWITHSUNDEEP
c#sql-serverlinq
Ralt
Ralt

Reputation: 2166

Linq Where much quicker than SQL Where

I'm new to SQL, and I'm having some trouble understanding why this Where statement is so inefficient.

A bit of background on the database. It's an SQL Compact Edition database which is used to store icons. A project can have multiple icons, each icon can have multiple paths, and each path is made out of a Geometry, a Colour, and an Opacity. There's roughly 2000 icons, which results in roughly 12000 paths.

enter image description here

I'm trying to create a query which returns only the icons which belong to a given project

SELECT Icons.Id, Icons.Name... etc FROM Icons
INNER JOIN Projects ON Icons.FK_ProjectId = Projects.Id 
INNER JOIN IconDetails ON Icons.Id = IconDetails.FK_IconId
INNER JOIN Paths ON IconDetails.FK_PathId = Paths.Id
INNER JOIN Colours ON IconDetails.FK_ColourId = Colours.Id
WHERE (Icons.FK_ProjectId = 5)

This takes ~2.8 seconds to complete. However, if I remove the bottom Where statement it only takes ~0.3 seconds. I can then use C# Linq to select all of the icons where they belong to the project that I want.

var iconTable = GetIconDataFromDatabase().Where(e => e.Project == projectName);


private List<IconData> GetIconDataFromDatabase()
{
    var getAllIconsCommand = new SqlCeCommand( // SQL Above);

    return ReturnIconData(LOCAL_CONNECTION_STRING, getAllIconsCommand);
}

private List<IconData> ReturnIconData(string connectionString, SqlCeCommand command)
{
    var IconDataToReturn = new List<IconData>();

    using (var connection = new SqlCeConnection(connectionString))
    {
        command.Connection = connection;

        using (command)
        {
            try
            {
                connection.Open();

                 using (SqlCeDataReader dataReader = command.ExecuteReader())
                 {
                     while (dataReader.Read())
                     {
                         IconDataToReturn.Add(new IconData
                         {
                             Id = int.Parse(dataReader["Id"].ToString().Trim()),
                             Project = dataReader["ProjectName"].ToString().Trim(),
                             Name = dataReader["Name"].ToString().Trim(),
                             Geometry = Geometry.Parse(dataReader["Geometry"].ToString().Trim()),
                             Colour = dataReader["Colour"].ToString().Trim(),
                             Opacity = double.Parse(dataReader["Opacity"].ToString().Trim()),
                             IsPathCompact = bool.Parse(dataReader["Compact"].ToString().Trim()),
                             ZOrder = int.Parse(dataReader["ZOrder"].ToString().Trim())
                       });

                    }
                }  
            }
        }
    return IconDataToReturn;
}

I don't understand how it can be so much quicker to return every single icon, and then filter out the results myself.

Upvotes: 4

Views: 140

Answers (2)

Ralt
Ralt

Reputation: 2166

Not an answer per se, but possibly a useful solution to anyone looking in the future. I implemented the exact same thing in SQLite and was able to the data back in 0.03 seconds, and without having to do any linq myself.

Upvotes: 0

Sefe
Sefe

Reputation: 14007

There is no general answer for that. Your performance values will depend greatly on factors like database size and used indexes. If you have a a lot of rows in your table and you have an index on Icons.FK_ProjectId which is very selective (for example you select only 10 of a million rows), I doubt it will be faster to load all rows and select with LINQ, since the DB can use the index for a seek operation (fast) and return only a small subset of the rows (also fast).

On the other hand if you have no index and you select a large subset of your rows (for example 2000 out of 2500 rows), SQL Server would first have to perform a clustered index scan, before it ireturns almost all rows of the data set. This additional operation would take the best part of the execution time and would not considerably reduce the size of the result set.

What you should do is to compare your execution plans with and without where and see if you can optimize your query. Tuning on the DB level is usually preferrable to tuning on the client side.

Upvotes: 3

Related Questions