locked
Very Slow LINQ query. RRS feed

  • Question

  • I have a LINQ query that is pulling about 200 records from a stored procedure.  The stored proc runs in about 1.5 seconds in the SQL Management Studio.  But it takes about 2 minutes to run in LINQ and about 3 more to cycle through the records and populate objects out of it.  Can anyone help?

     

    using (AgentDataContext db = new AgentDataContext())
    {
    	db.CommandTimeout = 600;
    	db.ObjectTrackingEnabled = false;
    
    	var Agts = from prods in db.spGetActiveProducers(null,null,state,null)
    				orderby prods.ProducerName
    				select prods;						  
    
    
    	foreach (var p in Agts)
    	{
    		agents.Add(new Agent
    		{
    			Address = new Address
    			{
    				Address1 = p.Address1,
    				Address2 = p.Address2,
    				City = p.City,
    				State = p.State,
    				Zip = p.Zip
    			},
    			LastBusinessDate = p.LastBusinessDate.GetValueOrDefault(new DateTime(1753, 1, 1)),
    			Name = p.ProducerName,
    			Phone = p.Phone,
    			ProducerID = p.ProducerID
    		});
    	}
    } // end using
    

    Monday, December 13, 2010 10:11 PM

Answers

  • On 12/13/2010 4:11 PM, Sithlrd wrote:
    > I have a LINQ query that is pulling about 200 records from a stored
    > procedure. The stored proc runs in about 1.5 seconds in the SQL
    > Management Studio. But it takes about 2 minutes to run in LINQ and about
    > 3 more to cycle through the records and populate objects out of it. Can
    > anyone help?
    >
    > using (AgentDataContext db =new AgentDataContext())
    > {
    > db.CommandTimeout = 600;
    > db.ObjectTrackingEnabled =false;
    >
    > var Agts =from prodsin db.spGetActiveProducers(null,null,state,null)
    > orderby prods.ProducerName
    > select prods;
    >
    >
    > foreach (var pin Agts)
    > {
    > agents.Add(new Agent
    > {
    > Address =new Address
    > {
    > Address1 = p.Address1,
    > Address2 = p.Address2,
    > City = p.City,
    > State = p.State,
    > Zip = p.Zip
    > },
    > LastBusinessDate = p.LastBusinessDate.GetValueOrDefault(new DateTime(1753, 1, 1)),
    > Name = p.ProducerName,
    > Phone = p.Phone,
    > ProducerID = p.ProducerID
    > });
    > }
    > }// end using
    >
     
    Maybe you should look up 'Eager' loading as opposed to 'Lazy' loading,
    when using a Linq query.
     
    You may also want to see if the foreach loop is going back to the
    database to select each record from the database table with 'select'
    statements for each iteration through the loop, which you can do with a
    tool like SQL Profiler. If it's going back 200 more times, then you have
    a problem, and you have to change something with the way you are doing
    the query.
     
    Also if you are using inner joins, that can slow things down. If you are
    not using clustered indexing can slow things down.
     
    Maybe, you need to be using SQL Command object with the Sproc and a
    datareader instead of a Linq query. You can still populate a new
    Address object in the datareader loop, load the Address object into a
    List
    , a List<T>, and return the List<T>.
     
     
    There is nothing to say that your application cannot use Linq-2-SQL or
    ADO.NET Entity Framework and SQL Command Objects in the same solution.
    I know ADO.NET EF has a backdoor in it to allow one to use SQL Command
    objects against a database when needed.
     
     
     
     
    • Marked as answer by liurong luo Wednesday, December 15, 2010 10:16 AM
    Monday, December 13, 2010 11:04 PM
  • db.DeferredLoadingEnabled = false;

    That fixed it up. 

    thanks!


    • Marked as answer by liurong luo Wednesday, December 15, 2010 10:17 AM
    Tuesday, December 14, 2010 2:01 PM

All replies

  • On 12/13/2010 4:11 PM, Sithlrd wrote:
    > I have a LINQ query that is pulling about 200 records from a stored
    > procedure. The stored proc runs in about 1.5 seconds in the SQL
    > Management Studio. But it takes about 2 minutes to run in LINQ and about
    > 3 more to cycle through the records and populate objects out of it. Can
    > anyone help?
    >
    > using (AgentDataContext db =new AgentDataContext())
    > {
    > db.CommandTimeout = 600;
    > db.ObjectTrackingEnabled =false;
    >
    > var Agts =from prodsin db.spGetActiveProducers(null,null,state,null)
    > orderby prods.ProducerName
    > select prods;
    >
    >
    > foreach (var pin Agts)
    > {
    > agents.Add(new Agent
    > {
    > Address =new Address
    > {
    > Address1 = p.Address1,
    > Address2 = p.Address2,
    > City = p.City,
    > State = p.State,
    > Zip = p.Zip
    > },
    > LastBusinessDate = p.LastBusinessDate.GetValueOrDefault(new DateTime(1753, 1, 1)),
    > Name = p.ProducerName,
    > Phone = p.Phone,
    > ProducerID = p.ProducerID
    > });
    > }
    > }// end using
    >
     
    Maybe you should look up 'Eager' loading as opposed to 'Lazy' loading,
    when using a Linq query.
     
    You may also want to see if the foreach loop is going back to the
    database to select each record from the database table with 'select'
    statements for each iteration through the loop, which you can do with a
    tool like SQL Profiler. If it's going back 200 more times, then you have
    a problem, and you have to change something with the way you are doing
    the query.
     
    Also if you are using inner joins, that can slow things down. If you are
    not using clustered indexing can slow things down.
     
    Maybe, you need to be using SQL Command object with the Sproc and a
    datareader instead of a Linq query. You can still populate a new
    Address object in the datareader loop, load the Address object into a
    List
    , a List<T>, and return the List<T>.
     
     
    There is nothing to say that your application cannot use Linq-2-SQL or
    ADO.NET Entity Framework and SQL Command Objects in the same solution.
    I know ADO.NET EF has a backdoor in it to allow one to use SQL Command
    objects against a database when needed.
     
     
     
     
    • Marked as answer by liurong luo Wednesday, December 15, 2010 10:16 AM
    Monday, December 13, 2010 11:04 PM
  • I have a LINQ query that is pulling about 200 records from a stored procedure.  The stored proc runs in about 1.5 seconds in the SQL Management Studio.  But it takes about 2 minutes to run in LINQ and about 3 more to cycle through the records and populate objects out of it.  Can anyone help?

     

    Does it run in 1.5 seconds in SSMS with the same parameter values passed in as when you run it from your app?

    More information about what the stored proc does, and the schema for the underlying tables might give a hint to what is going on. 

     


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers
     huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4
    Tuesday, December 14, 2010 3:07 AM
  • Yes, the sproc runs in 1.5 secs with the same parms. 

     

    The method that this code runs in used to be done with a SQLCommand object and a reader and was taking about the same time, if not longer. 

     

    I may have to see about this SQL Profiler thing and try to figure out what the frak's going on.

    Tuesday, December 14, 2010 1:24 PM
  • db.DeferredLoadingEnabled = false;

    That fixed it up. 

    thanks!


    • Marked as answer by liurong luo Wednesday, December 15, 2010 10:17 AM
    Tuesday, December 14, 2010 2:01 PM