Skip to main content

( I've add new tip , see the latest reply)

Dear all, I would like to share experience about tuning openworklist function.

I success tune up from 3minute to less than 40 seconds for open worklist page.

And 3minute bindflow to less than 1 seconds.

 

My company use k2 . we currently have 400000 row in _worklist.

the user which having 500 task in worklist have problem when access worklist page, it take about 3 minutes or usually show timeout.

I investigate that k2 will use function openworklist when user click worklist button:

public static Worklist OpenWorklist(string user, string platform)
{
    int num2 = 3;
    string location = "K2Sql.OpenWorklist";
    int num = 0;
Label_000D:
    num++;
    SqlConnection cn = null;
    try
    {
        cn = new SqlConnection(m_Con);
        cn.Open();
        SqlCommand command = CreateCommand("kUserWorklist", cn);
        command.Parameters.Add("@User", user);
        command.Parameters.Add("@Platform", platform);
        SqlDataReader dr = command.ExecuteReader();
        Worklist worklist = LoadWorklistFromReader(dr);
        worklist.m_TotalCount = worklist.Count;
        dr.Close();
        return worklist;
    }
    catch (Exception exception)
    {
        K2ErrorFile.Add(location, exception);
        if (K2Util.IsThrowK2(exception))
        {
            throw exception;
        }
        if (num == num2)
        {
            throw new Exception("A database error occured");
        }
        TestConnection();
        goto Label_000D;
    }
    finally
    {
        CloseConnection(cn);
    }
    return null;
}

 

 

 

As you see they will call stored procedure named: kUserWorklist

I check the stored and found this command

CREATE PROCEDURE kUserWorklist @User NVARCHAR(128), @Platform NVARCHAR(128)
AS
SET NOCOUNT ON
SELECT ProcID, ActID, EventID, w.ID, w.Status, w.Data, pi.ID, pi.Status, pi.StartDate, pi.Priority, pi.ExpectedDuration, pi.Folio, pi.Guid, EventInstID, EIPriority, EIExpectedDuration, EIStartDate, ActInstDestID, wh.ActInstID, AIPriority, AIExpectedDuration, AIStartDate, pf.Data, pf.Xml, af.Data, af.Xml
FROM _Worklist w (NOLOCK)
 JOIN _WorklistHeader wh (NOLOCK) ON w.ProcInstID = wh.ProcInstID AND w.HeaderID = wh.ID
 JOIN _ProcInst pi (NOLOCK) ON w.ProcInstID = pi.ID
 JOIN _Field pf (NOLOCK) ON pi.ID = pf.ProcInstID AND wh.ProcInstFieldID = pf.ID
 JOIN _Field af (NOLOCK) ON pi.ID = af.ProcInstID AND w.ActInstDestFieldID = af.ID
WHERE w.iUser] = @User AND (Platform = @Platform OR @Platform IS NULL) AND w.Status <> 4
 
GO

 

I use Microsoft Index Tuning Wizard and they advice to add 2 index:

 CREATE CLUSTERED INDEX I_Field2] ON wdbo].u_Field] (;ProcInstID] ASC, IF( @@error <> 0 ) SET @bErrors = 1

IF( @bErrors = 0 )
  COMMIT TRANSACTION
ELSE
  ROLLBACK TRANSACTION

BEGIN TRANSACTION
SET @bErrors = 0

CREATE CLUSTERED INDEX _Worklist1] ON ;dbo].S_Worklist] (=User] ASC, ProcInstID] ASC )
IF( @@error <> 0 ) SET @bErrors = 1

IF( @bErrors = 0 )
  COMMIT TRANSACTION
ELSE
  ROLLBACK TRANSACTION


/* Statistics to support recommendations */

CREATE STATISTICS ehind_69575286_2A_1A] ON Edbo]. _field] (CREATE STATISTICS Nhind_770101784_1A_2A_3A_4A_5A_6A_7A_8A_9A] ON CREATE STATISTICS Ihind_770101784_1A_5A_2A_3A_4A_6A_7A_8A_9A] ON dbo].b_worklistheader] (rprocinstid], dprocinstfieldid], [id], iactid], ceventid], dactinstid], daipriority], yaiexpectedduration], naistartdate])
CREATE STATISTICS Ihind_1899153811_3A_11A] ON _dbo].__worklist] ( procinstid], ruser])

after 20 minutes of index creation. All system much improved.

Next phase I will investigate other bottleneck. 

If you have some advice about tuning , your welcome.

 

Hi there,


Thanks for sharing your tips on improving worklist performance - the worklist seems to be often the bottleneck for performance problems and I am always curious to hear of tips on how people have improved it, so others who have had success please post your experiences too.


I am curious to know which version (SP) of K2.net 2003 are you using?


I will mention a few tips that I have picked up over the years, some of which comes down to proper process design.



  • Be carefull to use activity data fields or activity xml fields, especially if you have many destination users for your activity. In Black Pearl you now have much better control over ActivityInstances but in 2003 (pre-SP4) you had very little control and activity instances would be created for every destination user.
  • Return the worklist with nodata parameter.
  • Use Data on Demand on your data fields where ever possible.
  • Always try to limit the number of worklist items assigned to a single user - it does not make much sense for one user to have 100 000 work list items assigned to him because he will never be able to work through all of those items.

I'd love to hear other suggestions out there


 

Are you using SQL 2000?

Currently I have timeout issues with Search.   I'll give Tuning Wizard a try.

I am using SQL 2005 SP2.

I'm using k2 2003 sp1.

DB is sql 2000

My system can search in folio field only. Other field caused timeout issue.

I decide to abadon that feature.

Other Interesting TIP:

 add w.status!=2 condition to kUserWorklist will boot up your performance.

 The problem is  current kUserWorklist procedure query unnecessary rows ,  the "status 2" will not show in worklist

and 70% of total rows is in that status. If you change this stored procedure . Performance is greatly improved.

I 've test on development and everything seem perfect, No prolem. Please feel free to comment me. I need your result & comment

 

 

CREATE PROCEDURE kUserWorklist @User NVARCHAR(128), @Platform NVARCHAR(128)
AS
SET NOCOUNT ON
SELECT ProcID, ActID, EventID, w.ID, w.Status, w.Data, pi.ID, pi.Status, pi.StartDate, pi.Priority, pi.ExpectedDuration, pi.Folio, pi.Guid, EventInstID, EIPriority, EIExpectedDuration, EIStartDate, ActInstDestID, wh.ActInstID, AIPriority, AIExpectedDuration, AIStartDate, pf.Data, pf.Xml, af.Data, af.Xml
FROM _Worklist w (NOLOCK)
 JOIN _WorklistHeader wh (NOLOCK) ON w.ProcInstID = wh.ProcInstID AND w.HeaderID = wh.ID
 JOIN _ProcInst pi (NOLOCK) ON w.ProcInstID = pi.ID
 JOIN _Field pf (NOLOCK) ON pi.ID = pf.ProcInstID AND wh.ProcInstFieldID = pf.ID
 JOIN _Field af (NOLOCK) ON pi.ID = af.ProcInstID AND w.ActInstDestFieldID = af.ID
WHERE w./User] = @User AND (Platform = @Platform OR @Platform IS NULL) AND w.Status <> 4 and  w.Status <> 2
 

Have you tested the scenerio where user is in a destination queue?  dqu..User] = @User

Not sure what Status <> 4 means since it's not specified.



Normal
0


false
false
false







MicrosoftInternetExplorer4








/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Times New Roman";
mso-ansi-language:#0400;
mso-fareast-language:#0400;
mso-bidi-language:#0400;}
table.MsoTableGrid
{mso-style-name:"Table Grid";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
border:solid windowtext 1.0pt;
mso-border-alt:solid windowtext .5pt;
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-border-insideh:.5pt solid windowtext;
mso-border-insidev:.5pt solid windowtext;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:"Times New Roman";
mso-ansi-language:#0400;
mso-fareast-language:#0400;
mso-bidi-language:#0400;}



Worklist
Status




Status



Meaning



0



Available



1



Allocated



2



Open



3



Sleep




 
status=4 is "stopped"

Thanks for Sharing your experience.


I am trying ot follow what you are describing in your post but I am a little bit lost.


where can I find the OpenWorklist function and the kUserWorklist store procedure ? What database ?


I was trying to run the Microsoft Index Tuning Wizard with your script and it's giving me some errors. Maybe I am not running it the right way.


Would you please take a few minutes to walk me through.


Thank you

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings