Optimize LINQ query that runs fast in Sql server?

Question

I want to calculate the rows of a related table:

MainTable tbl = tblInfo(id);
var count = tbl.Related_Huge_Table_Data.Count();

The problem is: this takes too long (about 20 seconds) to execute, although when I run this query in Sql Server it executes below one second. How can I optimize this query in linq? I also tried to use stored procedure but no luck.

This is the tblInfo method:

public MainTable tblInfo(int id)
{
    MyDataContext context = new MyDataContext();
    MainTable mt = (from c in context.MainTables
                    where c.Id == id
                    select c).SingleOrDefault();
    return mt;
}

I used LinqToSql and classes was generated by LinqToSql.

Answer 1

By running SingleOrDefault() you execute the query and have to deal with results in memory after that. You need to stay with IQueryable until your query is fully constructed.

The easiest way to answer "how many child records this parent record has" is to approach it from the child side:

using (var dx = new MyDataContext())
{
    // If you have an association between the tables defined in the context
    int count = dx.Related_Huge_Table_Datas.Where(t => t.MainTable.id == 42).Count();

    // If you don't
    int count = dx.Related_Huge_Table_Datas.Where(t => t.parent_id == 42).Count();
}

If you insist on the parent side approach, you can do that too:

using (var dx = new MyDataContext())
{
    int count = dx.MainTables.Where(t => t.id == 42).SelectMany(t => t.Related_Huge_Table_Datas).Count();
}

If you want to keep a part of this query in a function like tblInfo, you can, but you can't instantiate MyDataContext from inside such function, otherwise you will get an exception when trying to use the query with another instance of MyDataContext. So either pass MyDataContext to tblInfo or make tblInfo a member of partial class MyDataContext:

public static IQueryable<MainTable> tblInfo(MyDataContext dx, int id)
{
    return dx.MainTables.Where(t => t.id == id);
}

...

using (var dx = new MyDataContext())
{
    int count = tblInfo(dx, 42).SelectMany(t => t.Related_Huge_Table_Datas).Count();
}

Answer 2

Try this

MyDataContext context = new MyDataContext();
var count=context.Related_Huge_Table_Data.where(o=>o.Parentid==id).Count();

//or

int count=context.Database.SqlQuery<int>("select count(1) from Related_Huge_Table_Data where Parentid="+id).FirstOrDefault();

Answer 3

If you wish to take full advantage of your SQL Database's performance, it may make sense to query it directly rather than use Linq. Should be reasonably more performent :)

 var Related_Huge_Table_Data = "TABLENAME";//Input table name here
 var Id = "ID"; //Input Id name here     
 var connectionString = "user id=USERNAME; password=PASSWORD server=SERVERNAME; Trusted_Connection=YESORNO; database=DATABASE; connection timeout=30";

 SqlCommand sCommand = new SqlCommand();
 sCommand.Connection = new SqlConnection(connectionString);
 sCommand.CommandType = CommandType.Text;
 sCommand.CommandText = $"COUNT(*) FROM {Related_Huge_Table_Name} WHERE Id={ID}";
 sCommand.Connection.Open();

 SqlDataReader reader = sCommand.ExecuteReader();
 var count = 0;
 if (reader.HasRows)
 {
     reader.Read();
     count = reader.GetInt32(0);
 }
 else
 {
      Debug.WriteLine("Related_Huge_Table_Data: No Rows returned in Query.");
 }
 sCommand.Connection.Close();

Answer 4

Try this:

MyDataContext context = new MyDataContext();
var count = context.MainTables.GroupBy(x => x.ID).Distict().Count();

Answer 5

The answer of GSerg is the correct one in many case. But when your table starts to be really huge, even a Count(1) directly in SQL Server is slow.

The best way you can get round this is to query the database stats directly, which is impossible with Linq (or I don't know of).

The best thing you can do is to create a static sub (C#) on your tables definition witch will return the result of the following query:

SELECT 
    SUM(st.row_count)
FROM 
    sys.dm_db_partition_stats st
WHERE 
    object_name(object_id) = '{TableName}' 
    AND (index_id < 2)

where {TableName} is the database name of your table.

Beware it's an answer only for the case of counting all records in a table!

Answer 6

Is your linq2sql returning the recordset and then doing the .Count() locally, or is it sending SQL to the server to do the count on the server? There will be a big difference in performance there.

Also, have you inspected the SQL that's being generated when you execute the query? From memory, Linq2Sql allows you to inspect SQL (maybe by setting up a logger on your class?). In Entity Framework, you can see it when debugging and inspecting the IQueryable<> object, not sure if there's an equivalent in Linq2Sql.

Way to view SQL executed by LINQ in Visual Studio?

Alternatively, use the SQL Server Profiler (if available), or somehow see what's being executed.

Answer 7

You may try following:-

  var c = from rt in context.Related_Huge_Table_Data
    join t in context.MainTables
    on rt.MainTableId ==t.id where t.id=id
    select new {rt.id};

   var count=c.Distict().Count();