This post might be better suited for another section, but I am trying to track down a curious problem that I have been having executing one of our predicate sets.

When I run the query in QA, the results are returned in under 1 second, but when I run it through LLBL using the SQL Provider, it takes 3-5 minutes and I occassionaly get timeouts even with a 300 second CommandTimeout setting.

It is a reasonably complex query and it does contain a computed field for comparison, however I am baffled as to why QA can run so quickly. I thought initially that the problem was my error and that I was copying the sql text incorrectly. However, I fired up the profiler and copy the code out directly and get the same results - almost an immidiate return in QA and timeouts through the SQL Provider.

Any help would be greatly appreciated as I am baffled at this point.

Best regards,

Hal

The timeout occurs on the server? As in: the query is fired, then the server is very busy and nothing comes back, or is LLBLGen Pro code slow in consuming the results?

I think it would be best if you could provide some code so I have a better picture of what kind of query we're talking about

One thing, but I doubt that's it, is that in QA you probably didn't use parameters, though in SqlClient you do.

Otis wrote:

I think it would be best if you could provide some code so I have a better picture of what kind of query we're talking about

Here is the code from the profiler:

exec sp_executesql N'SELECT DISTINCT TOP 1000 [dbo].[tblLetter].[LetterID] AS [LetterID],[dbo].[tblLead].[LeadID] AS [LeadID] FROM ((((( [dbo].[tblLead]  INNER JOIN [dbo].[tblLeadCategory]  ON  [dbo].[tblLead].[LeadID]=[dbo].[tblLeadCategory].[LeadID]) INNER JOIN [dbo].[tblCategory]  ON  [dbo].[tblCategory].[CategoryID]=[dbo].[tblLeadCategory].[CategoryID]) INNER JOIN [dbo].[tblCategoryLetter]  ON  [dbo].[tblCategory].[CategoryID]=[dbo].[tblCategoryLetter].[CategoryID]) INNER JOIN [dbo].[tblLetter]  ON  [dbo].[tblLetter].[LetterID]=[dbo].[tblCategoryLetter].[LetterID]) LEFT JOIN [dbo].[tblLeadLetter]  ON  [dbo].[tblLetter].[LetterID]=[dbo].[tblLeadLetter].[LetterID] AND ( [dbo].[tblLeadLetter].[LeadID] = [dbo].[tblLead].[LeadID])) WHERE ( [dbo].[tblLead].[LeadActive] = @LeadActive1 And [dbo].[tblLead].[UserID] = @UserID2 And [dbo].[tblLeadLetter].[LeadLetterID] IS NULL And [dbo].[tblLeadCategory].[AddDate] + [dbo].[tblCategoryLetter].[LetterWait] <= @AddDate3)', N'@LeadActive1 smallint,@UserID2 int,@AddDate3 datetime', @LeadActive1 = -1, @UserID2 = 3, @AddDate3 = 'Jul 28 2005  6:00:44:377AM'

Understandably, it's a pretty complex query... however, in Query Analyzer it runs in less than 1 second. It takes as long as 15 minutes to run in my application. Any ideas?

Thanks for the help,

Hal

hlesesne wrote:

Understandably, it's a pretty complex query... however, in Query Analyzer it runs in less than 1 second. It takes as long as 15 minutes to run in my application. Any ideas?

That query is actually quite basic and does not contain anything that I can see that would make it run slower in the app than in QA.

The only thing I can think of is a deadlock or locks from other users... But I'm also curious why you're not getting a timeout rather than having to wait for 15 minutes... How big are these tables and are other user updating them while you ran the application tests?

Because of this part of the query (below), the optimizer will more than likely cause table scans which will block on any locked rows (depending on the transaction isolation level in your application...)

[dbo].[tblLeadCategory].[AddDate] + [dbo].[tblCategoryLetter].[LetterWait] <= @AddDate3

You will need to post the application code in order to help us understand the full context of the problem. Running this query in QA runs the query in isolation which is why you might not be getting deadlocks or locks from the QA window...

Marcus

I agree with Marcus, there's nothing unusal with this code that should slow it down tremendously, and it's likely the statements executed before this query will somehow lock it. I also wonder why you don't get any timeout... (as it defaults to 30 seconds)

I have increased my timeout setting in this application (which is why it takes so much longer than 30 seconds for the code to timeout) to combat this specific problem. In addition, I have tried to run the application without any additional users executing code on both our production db and my development db - with similar results.

I will try to clean up some application code and post it for you to look at for review. Thanks for the help.

Otis wrote:

I agree with Marcus, there's nothing unusal with this code that should slow it down tremendously, and it's likely the statements executed before this query will somehow lock it. I also wonder why you don't get any timeout... (as it defaults to 30 seconds)

hlesesne wrote:

I have increased my timeout setting in this application (which is why it takes so much longer than 30 seconds for the code to timeout) to combat this specific problem. In addition, I have tried to run the application without any additional users executing code on both our production db and my development db - with similar results.

I will try to clean up some application code and post it for you to look at for review. Thanks for the help.

Ok. Pay special attention to transactional work you're executing around the code, which can make this code run extremely slow/slower or deadlock.

Is is my method that runs the query:

Public Overrides Sub Start()
    IsRunning = True
    LastStart = Now
    Dim Pred As New PredicateExpression
    Dim Rel As New RelationCollection
    With Rel
        .Add(TblLeadEntity.Relations.TblLeadCategoryEntityUsingLeadID, JoinHint.Inner)
        .Add(TblLeadCategoryEntity.Relations.TblCategoryEntityUsingCategoryID, JoinHint.Inner)
        .Add(TblCategoryEntity.Relations.TblCategoryLetterEntityUsingCategoryID, JoinHint.Inner)
        .Add(TblCategoryLetterEntity.Relations.TblLetterEntityUsingLetterID, JoinHint.Inner)
    End With


    '       Rel.Add(TblLetterEntity.Relations.TblLetterQueueEntityUsingLetterID, JoinHint.Left)
    '       Rel.Add(TblLetterEntity.Relations.TblLeadLetterEntityUsingLetterID, "tblLeadLetter", JoinHint.Left)

    Dim CustomFilter As IPredicateExpression = New PredicateExpression
    Dim Expr As IExpression = New Expression(EntityFieldFactory.Create(TblLeadFieldIndex.LeadID))
    CustomFilter.Add(PredicateFactory.CompareExpression(TblLeadLetterFieldIndex.LeadID, ComparisonOperator.Equal, Expr))
    Rel.Add(TblLetterEntity.Relations.TblLeadLetterEntityUsingLetterID, "tblLeadLetter", JoinHint.Left).CustomFilter = CustomFilter

    Dim CustomFilter2 As IPredicateExpression = New PredicateExpression
    Dim Expr2 As IExpression = New Expression(EntityFieldFactory.Create(TblLeadFieldIndex.LeadID))
    CustomFilter2.Add(PredicateFactory.CompareExpression(TblLetterQueueFieldIndex.LeadID, ComparisonOperator.Equal, Expr2))
    Rel.Add(TblLetterEntity.Relations.TblLetterQueueEntityUsingLetterID, "tblLetterQueue", JoinHint.Left).CustomFilter = CustomFilter2


    Pred.Add(PredicateFactory.CompareValue(TblLeadFieldIndex.LeadActive, ComparisonOperator.Equal, -1))
    Pred.AddWithAnd(PredicateFactory.CompareNull(TblLetterQueueFieldIndex.LetterQueueID))
    Pred.AddWithAnd(PredicateFactory.CompareNull(TblLeadLetterFieldIndex.LeadLetterID))

    Dim Fields As New ResultsetFields(4)
    Fields.DefineField(TblCategoryLetterFieldIndex.LetterID, 0, "LetterID")
    Fields.DefineField(TblLeadFieldIndex.LeadID, 1, "LeadID")
    Fields.DefineField(TblLeadCategoryFieldIndex.AddDate, 2, "AddDate")
    Fields.DefineField(TblCategoryLetterFieldIndex.LetterWait, 3, "LetterWait")

    Dim Dao As New DaoClasses.TypedListDAO

    Dim dt As New DataTable

    Dao.GetMultiAsDataTable(Fields, dt, 0, Nothing, Pred, Rel, False, Nothing, Nothing, 0, 0)

    IsRunning = False
    LastEnd = Now
End Sub

It builds the relations and predicates for the TypeListDAO and then executes the query. There is no transactional context or sort order.

I couldn't find anything that might cause problems in the code above, but would be grateful for a review just in case. The Dao.GetMultiAsDataTable line of code is where it hangs, more specifically, it is the Line "Return MyBase.ExecuteMultiRowDataTableRetrievalQuery..." from the TypedListDAO generic class. I am using self-servicing. Thanks again.

Thanks.

The code clearly hangs in the execution of the query on the server. This is what's done:

public virtual bool ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill)
{
    TraceHelper.WriteLineIf(TraceHelper.PersistenceExecutionSwitch.TraceInfo, "DaoBase.ExecuteMultiRowDataTableRetrievalQuery(3)", "Method Enter");

    // wire up connection, command with adapter
    ((IDbDataAdapter)dataAdapterToUse).SelectCommand = queryToExecute.Command;
    if(queryToExecute.RequiresClientSideLimitation)
    {
        DataSet dummyDS = new DataSet();
        dummyDS.Tables.Add(tableToFill);
        dataAdapterToUse.Fill(dummyDS, 0, (int)queryToExecute.MaxNumberOfItemsToReturnClientSide, tableToFill.TableName);
        dummyDS.Tables.Remove(tableToFill);
    }
    else
    {
        dataAdapterToUse.Fill(tableToFill);
    }

    TraceHelper.WriteLineIf(TraceHelper.PersistenceExecutionSwitch.TraceInfo, "DaoBase.ExecuteMultiRowDataTableRetrievalQuery(3)", "Method Exit");
    return true;
}

RequiresClientSideLimitation is only set if you specify a 'max' and distinct can't be set. You don't do that, so that's no problem. In your query you posted earlier on you have TOP 1000, if you want all rows, simply specify 0, not a big number.

'Adapter' in the snippet above is an SqlDataAdapter.