LLBLGen v2.6 Self-Servicing SQL Server 2008 R2 - Slow GetMulti

Posts   
 
    
mshe
User
Posts: 167
Joined: 02-Feb-2006
# 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:

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.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 06-Jan-2011 06:32:24   

To get the full picture:

David Elizondo | LLBLGen Support Team
mshe
User
Posts: 167
Joined: 02-Feb-2006
# Posted on: 06-Jan-2011 06:35:41   


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)


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)

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 06-Jan-2011 08:28:23   

5-7 seconds are too much.

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.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# 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 | Lead developer LLBLGen Pro
mshe
User
Posts: 167
Joined: 02-Feb-2006
# Posted on: 06-Jan-2011 15:19:36   

Walaa wrote:

5-7 seconds are too much.

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!

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# 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 | Lead developer LLBLGen Pro