Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> LLBLGen v2.6 Self-Servicing SQL Server 2008 R2 - Slow GetMulti
 

Pages: 1
Bugs & Issues
LLBLGen v2.6 Self-Servicing SQL Server 2008 R2 - Slow GetMulti
Page:1/1 

  Print all messages in this thread  
Poster Message
mshe
User



Location:
Markham, Canada
Joined on:
02-Feb-2006 23:31:34
Posted:
167 posts
# Posted on: 06-Jan-2011 05:28:51.  
Hi all,

I have a query which should take approximately 200ms to execute. I copied and pasted the query from LLBLGen's trace and ran it in Management Studio and it consistently returns in 200 - 300ms (or less!).

However, LLBLGen seems to take 5 - 7 SECONDS to run the query.

From the trace:
Quote:

00000017    11:24:05.280 PM    [4456]     
00000018    11:24:05.280 PM    [4456] Method Exit: CreateSelectDQ     
00000019    11:24:05.280 PM    [4456] Method Exit: CreatePagingSelectDQ: no paging.     
00000020    11:24:05.280 PM    [4456] Method Enter: DaoBase.ExecuteMultiRowRetrievalQuery     
00000022    11:24:09.811 PM    [4456] Executed Sql Query:     


Notice host ExecuteMultiRowRetrievalQuery takes a very long time?

What could cause such a discrepancy?

I do have one varchar(max) in the table, could this reduce the speed significantly? (Management Studio seems ok with the varchar(max)). Data inside the varchar(max) column is quite small - 200 - 300bytes max for now.

  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8088 posts
# Posted on: 06-Jan-2011 06:32:24.  
To get the full picture:

- What is the code snippet you are running?
- What is the generated sql for that code?
- What is your runtime library version? (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7717)


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
mshe
User



Location:
Markham, Canada
Joined on:
02-Feb-2006 23:31:34
Posted:
167 posts
# Posted on: 06-Jan-2011 06:35:41.  
Code:


Dim Records As New RecordCollection

                Transaction.Add(Records)

                'Create a SQL WHERE clause
                Dim PE As New PredicateExpression

                With PE
                    'Fetch all unlocked records or records that are older than 30 minutes (locked records should never take 30 minutes to process...)
                    .AddWithAnd(RecordFields.LockTime = System.DBNull.Value Or RecordFields.LockTime <= Now.Subtract(New TimeSpan(0, 30, 0)))

                    'Fetch all records that have not been attempted or fetch records that were tried at least 5 minutes ago
                    .AddWithAnd(RecordFields.LastAttemptTime = System.DBNull.Value Or RecordFields.LastAttemptTime <= Now.Subtract(New TimeSpan(0, 5, 0)))

                    'Don't select records more than 5 attempts
                    .AddWithAnd(RecordFields.Attempt <= 5)
                End With

                Dim SE As New SortExpression

                With SE
                    .Add(RecordFields.EntryDate Or SortOperator.Ascending)

                    'Select oldest attempted record first
                    .Add(RecordFields.Attempt Or SortOperator.Descending)
                End With

                'Run the query
                Records.GetMulti(PE, maxNumberOfItemsToReturn, SE, Nothing, Nothing)



Code:

Query: SELECT TOP(@top0) [LLBLGen].[dbo].[Records].[ID] AS [Id], [LLBLGen].[dbo].[Records].[EntryDate], [LLBLGen].[dbo].[Records].[Type], [LLBLGen].[dbo].[Records].[LockTime], [LLBLGen].[dbo].[Records].[Attempt], [LLBLGen].[dbo].[Records].[LastAttemptTime], [LLBLGen].[dbo].[Records].[XML] AS [Xml] FROM [LLBLGen].[dbo].[Records] WHERE ( ( ( [LLBLGen].[dbo].[Records].[LockTime] IS NULL OR [LLBLGen].[dbo].[Records].[LockTime] <= @LockTime2) AND ( [LLBLGen].[dbo].[Records].[LastAttemptTime] IS NULL OR [LLBLGen].[dbo].[Records].[LastAttemptTime] <= @LastAttemptTime3) AND [LLBLGen].[dbo].[Records].[Attempt] <= @Attempt4)) ORDER BY [LLBLGen].[dbo].[Records].[EntryDate] ASC,[LLBLGen].[dbo].[Records].[Attempt] DESC


Parameter: @top0 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 50.
Parameter: @LockTime2 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1/5/2011 11:56:09 PM.
Parameter: @LastAttemptTime3 : DateTime. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1/6/2011 12:21:09 AM.
Parameter: @Attempt4 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 5.


Running Self-Servicing, SQL Server 2008 R2, with the latest (October 9, 2010) runtimes (2.6.10.0930)
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14531 posts
# Posted on: 06-Jan-2011 08:28:23.  
5-7 seconds are too much.
Quote:
Running Self-Servicing, SQL Server 2008 R2, with the latest (October 9, 2010) runtimes.

The latest build is from December 2010. Could you please pick this up and try it.
Also would you try the same query using ordinary ADO.NET code, to see how much time it takes.

Thanks.


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37644 posts
# Posted on: 06-Jan-2011 11:01:29.  
Your profile isn't precise enough. You should look into profiling every method so also the methods called by ExecutingMultiRowRetrievalQuery. It might very well be that the majority of the time is consumed by ExecuteReader. This would suggest most time is consumed by the data transfer from db to client, e.g. because you have image/ntext fields in the resultset.

Don't consider query execution time in management studio equal to total time llblgen pro has to work with. management studio only measures the SQL executiontime. The data retrieval and network time is not taken into account.

We did optimize code in v3 and lately also in 3.1 which is going beta soon. But these optimizations aren't in the 'whoa, the query first took 5 seconds and now 200ms' area, as that's not how things work in real life: the majority of time is always spend inside ExecuteReader.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
mshe
User



Location:
Markham, Canada
Joined on:
02-Feb-2006 23:31:34
Posted:
167 posts
# Posted on: 06-Jan-2011 15:19:36.  
Walaa wrote:
5-7 seconds are too much.
Quote:
Running Self-Servicing, SQL Server 2008 R2, with the latest (October 9, 2010) runtimes.

The latest build is from December 2010. Could you please pick this up and try it.
Also would you try the same query using ordinary ADO.NET code, to see how much time it takes.

Thanks.



Hello,

Where do I pick up the December 2010 build? In the customer site, I only see:

Runtime libraries package 04-Oct-2010     09302010

Which seems to be the same version I have?


Otis wrote:
Your profile isn't precise enough. You should look into profiling every method so also the methods called by ExecutingMultiRowRetrievalQuery. It might very well be that the majority of the time is consumed by ExecuteReader.


What would be the best way to profile the code in the runtime libraries? Any tools you guys can suggest? Thanks!


  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37644 posts
# Posted on: 06-Jan-2011 16:46:46.  
yeah that's the runtime you should get.

Look at DotTrace (jetbrains) or Ants (RedGate) to find bottlenecks in your application. Both have trials you can get started with.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.