Export/Publication Speed Troubleshooting - Precisely_EnterWorks - EnterWorks

Export/Publication Speed Troubleshooting - Precisely_EnterWorks - EnterWorks - 11.0

EnterWorks Guide

Product type

Software

Portfolio

Verify

Product family

EnterWorks

Product

Precisely EnterWorks

Precisely EnterWorks > EnterWorks™ software

Version

11.0

ft:locale

en-US

Product name

Precisely EnterWorks

ft:title

EnterWorks Guide

2024

First publish date

2007

ft:lastEdition

2025-01-21

ft:lastPublication

2025-01-21T05:56:06.852000

Steps to Analyze Export/Publication Speed

To analyze the speed of an export/publication, follow these steps:

Set the logging level for the Web Application Layer -> Facade component to FINEST.
Run the export/publication job. You can use just a few records instead of everything that was originally exported. The goal is to see and analyze the query generated by the template definition.
The webcm*.log will contain the SQL statements used to get the results. Locate one of the queries for the root repository involved in the template (you can use the Model -> Repositories page to get the REPOSITORY_ID). The query should begin with "WITH ..."

Copy and paste this query into SQLServer Management Studio and run it. Replace the ITEM_ID parameter with one of the ITEM_IDs involved in the export/publication. Here is an example of a very large query (notice it is easier to read if you add line breaks as shown here):


    WITH link10179 (link10179_ITEM_ID,F_1006507) AS ( SELECT s10213.ITEM_ID as s10213_ITEM_ID,s10213.F_1006507 FROM B_SNAPSHOT_10213 s10213 WITH (NOLOCK) ), 
    link10157 (link10157_ITEM_ID,F_1006362) AS ( SELECT s10202.ITEM_ID as s10202_ITEM_ID,s10202.F_1006362 FROM B_SNAPSHOT_10202 s10202 WITH (NOLOCK) ), 
    link10165 (link10165_ITEM_ID,F_1006411) AS ( SELECT s10205.ITEM_ID as s10205_ITEM_ID,s10205.F_1006411 FROM B_SNAPSHOT_10205 s10205 WITH (NOLOCK) ), 
    link10000 (link10000_ITEM_ID,F_1000045) AS ( SELECT s10001.ITEM_ID as s10001_ITEM_ID,s10001.F_1000045 FROM B_SNAPSHOT_10001 s10001 WITH (NOLOCK) ), 
    link10167 (link10167_ITEM_ID,F_1006418) AS ( SELECT s10207.ITEM_ID as s10207_ITEM_ID,s10207.F_1006418 FROM B_SNAPSHOT_10207 s10207 WITH (NOLOCK) ), 
    link10166 (link10166_ITEM_ID,F_1006383,F_1006382) AS ( SELECT s10204.ITEM_ID as s10204_ITEM_ID,s10204.F_1006383,s10204.F_1006382 
    FROM B_SNAPSHOT_10204 s10204 WITH (NOLOCK) ), 
    link10144 (link10144_ITEM_ID,F_1000015,F_1000010,F_1000005) AS ( SELECT s10003.ITEM_ID as s10003_ITEM_ID,s10003.F_1000015,s10003.F_1000010,s10003.F_1000005 
    FROM B_SNAPSHOT_10003 s10003 WITH (NOLOCK) ), 
    link10140 (link10140_ITEM_ID,F_1006217,F_1006215) AS ( SELECT s10197.ITEM_ID as s10197_ITEM_ID,s10197.F_1006217,s10197.F_1006215 
    FROM B_SNAPSHOT_10197 s10197 WITH (NOLOCK) ), 
    link10154 (link10154_ITEM_ID,F_1006207) AS ( SELECT s10190.ITEM_ID as s10190.ITEM_ID,s10190.F_1006207 FROM B_SNAPSHOT_10190 s10190 WITH (NOLOCK) ), 
    link10158 (link10158_ITEM_ID,F_1006117) AS ( SELECT s10191.ITEM_ID as s10191.ITEM_ID,s10191.F_1006117 FROM B_SNAPSHOT_10191 s10191 WITH (NOLOCK) ), 
    link10000A (link10000A_ITEM_ID,F_1000045) AS ( SELECT s10001.ITEM_ID as s10001.ITEM_ID,s10001.F_1000045 FROM B_SNAPSHOT_10001 s10001 WITH (NOLOCK) ), 
    link10153 (link10153_ITEM_ID,F_1000015,F_1000010,F_1000005) AS ( SELECT s10003.ITEM_ID as s10003.ITEM_ID,s10003.F_1000015,s10003.F_1000010,s10003.F_1000005 
    FROM B_SNAPSHOT_10003 s10003 WITH (NOLOCK) ), 
    link10141 (link10141_ITEM_ID,F_1006038,F_1006036) AS ( SELECT s10186.ITEM_ID as s10186_ITEM_ID,s10186.F_1006038,s10186.F_1006036 
    FROM B_SNAPSHOT_10186 s10186 WITH (NOLOCK) ), 
    link10168 (link10168_ITEM_ID,F_1006431) AS ( SELECT s10208.ITEM_ID as s10208_ITEM_ID,s10208.F_1006431 FROM B_SNAPSHOT_10208 s10208 WITH (NOLOCK) ), 
    link10155 (link10155_ITEM_ID,F_1006301) AS ( SELECT s10199.ITEM_ID as s10199_ITEM_ID,s10199.F_1006301 FROM B_SNAPSHOT_10199 s10199 WITH (NOLOCK) ), 
    link10182 (link10182_ITEM_ID,F_1006521) AS ( SELECT s10214.ITEM_ID as s10214_ITEM_ID,s10214.F_1006521 FROM B_SNAPSHOT_10214 s10214 WITH (NOLOCK) )
    
    SELECT bmi.ITEM_ID, bmi.REPOSITORY_ID, bmi.PK_COL_1,bmi.PK_COL_2,bmi.PK_COL_3,bmi.PK_COL_4,bmi.PK_COL_5,link10179.link10179_item_ID,link10157.link10157_item_ID,
    link10165.link10165_item_ID,link10000.link10000_item_ID,link10167.link10167_item_ID,link10166.link10166_item_ID,link10144.link10144_item_ID,
    link10140.link10140_item_ID,link10154.link10154_item_ID,link10158.link10158_item_ID,link10000A.link10000A_item_ID,link10153.link10153_item_ID,
    link10141.link10141_item_ID,link10168.link10168_item_ID,link10155.link10155_item_ID,link10182.link10182_item_ID 
    FROM B_MASTER_REPOSITORY_ITEM bmi WITH (NOLOCK)  
    JOIN B_SNAPSHOT_10188 snap10188 WITH (NOLOCK)  ON bmi.ITEM_ID= snap10188.ITEM_ID 
    LEFT JOIN link10140 ON (snap10188.F_1006202 = link10140.F_1006215) 
    LEFT JOIN link10179 ON (link10140.F_1006217 = link10179.F_1006507) 
    LEFT JOIN link10157 ON (link10140.F_1006217 = link10157.F_1006362) 
    LEFT JOIN link10165 ON (link10140.F_1006217 = link10165.F_1006411) 
    LEFT JOIN link10166 ON (link10140.F_1006217 = link10166.F_1006383) 
    LEFT JOIN link10144 ON ((link10140.F_1006217 = link10144.F_1000010) AND link10144.F_1000015=10197)  
    LEFT JOIN link10141 ON (link10140.F_1006217 = link10141.F_1006038) 
    LEFT JOIN link10168 ON (link10140.F_1006217 = link10168.F_1006431) 
    LEFT JOIN link10167 ON (link10166.F_1006382 = link10167.F_1006418) 
    LEFT JOIN link10000 ON (link10000.F_1000045 = link10144.F_1000005) 
    LEFT JOIN link10154 ON (link10141.F_1006036 = link10154.F_1006207) 
    LEFT JOIN link10158 ON (link10141.F_1006036 = link10158.F_1006117) 
    LEFT JOIN link10153 ON ((link10141.F_1006036 = link10153.F_1000010) AND link10153.F_1000015=10186)  
    LEFT JOIN link10155 ON (link10141.F_1006036 = link10155.F_1006301) 
    LEFT JOIN link10182 ON (link10141.F_1006036 = link10182.F_1006521) 
    LEFT JOIN link10000A ON (link10000A.F_1000045 = link10153.F_1000005) 
    
    WHERE bmi.REPOSITORY_ID= 10188  AND bmi.ITEM_ID IN ( 6750048 )

SQLServer will sometimes suggest creating an index, especially if you select the Include Action Execution Plan option. Otherwise, check the indexes on the columns used in the WHERE clauses, JOIN clauses, and the ORDER BY. There might be some improvements, like composite indexes, that can help (you may need a DBA for this).

Sometimes, one or more root records in an export template can have a very large number of children, grandchildren, and so on. This can make the result set for just one record extremely huge because all the JOINs create a cross product. Here is an example of a SQL query to see how many total "linked" records would be returned for each root record. Basically, you use the query you found in the webcm*.log, add "mainTable as (" before the main SELECT part, and change the ending of it. These sections are in bold below:


    WITH link10179 (link10179_ITEM_ID,F_1006507) AS ( SELECT s10213.ITEM_ID as s10213_ITEM_ID,s10213.F_1006507 FROM B_SNAPSHOT_10213 s10213 WITH (NOLOCK) ), 
    link10157 (link10157_ITEM_ID,F_1006362) AS ( SELECT s10202.ITEM_ID as s10202_ITEM_ID,s10202.F_1006362 FROM B_SNAPSHOT_10202 s10202 WITH (NOLOCK) ), 
    link10165 (link10165_ITEM_ID,F_1006411) AS ( SELECT s10205.ITEM_ID as s10205_ITEM_ID,s10205.F_1006411 FROM B_SNAPSHOT_10205 s10205 WITH (NOLOCK) ), 
    link10000 (link10000_ITEM_ID,F_1000045) AS ( SELECT s10001.ITEM_ID as s10001.ITEM_ID,s10001.F_1000045 FROM B_SNAPSHOT_10001 s10001 WITH (NOLOCK) ), 
    link10167 (link10167_ITEM_ID,F_1006418) AS ( SELECT s10207.ITEM_ID as s10207_ITEM_ID,s10207.F_1006418 FROM B_SNAPSHOT_10207 s10207 WITH (NOLOCK) ), 
    link10166 (link10166_ITEM_ID,F_1006383,F_1006382) AS ( SELECT s10204.ITEM_ID as s10204_ITEM_ID,s10204.F_1006383,s10204.F_1006382 
    FROM B_SNAPSHOT_10204 s10204 WITH (NOLOCK) ), 
    link10144 (link10144_ITEM_ID,F_1000015,F_1000010,F_1000005) AS ( SELECT s10003.ITEM_ID as s10003.ITEM_ID,s10003.F_1000015,s10003.F_1000010,s10003.F_1000005 
    FROM B_SNAPSHOT_10003 s10003 WITH (NOLOCK) ), 
    link10140 (link10140_ITEM_ID,F_1006217,F_1006215) AS ( SELECT s10197.ITEM_ID as s10197_ITEM_ID,s10197.F_1006217,s10197.F_1006215 
    FROM B_SNAPSHOT_10197 s10197 WITH (NOLOCK) ), 
    link10154 (link10154_ITEM_ID,F_1006207) AS ( SELECT s10190.ITEM_ID as s10190.ITEM_ID,s10190.F_1006207 FROM B_SNAPSHOT_10190 s10190 WITH (NOLOCK) ), 
    link10158 (link10158_ITEM_ID,F_1006117) AS ( SELECT s10191.ITEM_ID as s10191.ITEM_ID,s10191.F_1006117 FROM B_SNAPSHOT_10191 s10191 WITH (NOLOCK) ), 
    link10000A (link10000A_ITEM_ID,F_1000045) AS ( SELECT s10001.ITEM_ID as s10001.ITEM_ID,s10001.F_1000045 FROM B_SNAPSHOT_10001 s10001 WITH (NOLOCK) ), 
    link10153 (link10153_ITEM_ID,F_1000015,F_1000010,F_1000005) AS ( SELECT s10003.ITEM_ID as s10003.ITEM_ID,s10003.F_1000015,s10003.F_1000010,s10003.F_1000005 
    FROM B_SNAPSHOT_10003 s10003 WITH (NOLOCK) ), 
    link10141 (link10141_ITEM_ID,F_1006038,F_1006036) AS ( SELECT s10186.ITEM_ID as s10186_ITEM_ID,s10186.F_1006038,s10186.F_1006036 
    FROM B_SNAPSHOT_10186 s10186 WITH (NOLOCK) ), 
    link10168 (link10168_ITEM_ID,F_1006431) AS ( SELECT s10208.ITEM_ID as s10208_ITEM_ID,s10208.F_1006431 FROM B_SNAPSHOT_10208 s10208 WITH (NOLOCK) ), 
    link10155 (link10155_ITEM_ID,F_1006301) AS ( SELECT s10199.ITEM_ID as s10199.ITEM_ID,s10199.F_1006301 FROM B_SNAPSHOT_10199 s10199 WITH (NOLOCK) ), 
    link10182 (link10182_ITEM_ID,F_1006521) AS ( SELECT s10214.ITEM_ID as s10214_ITEM_ID,s10214.F_1006521 FROM B_SNAPSHOT_10214 s10214 WITH (NOLOCK) ),
    mainTable as (
    SELECT bmi.ITEM_ID, bmi.REPOSITORY_ID, bmi.PK_COL_1,bmi.PK_COL_2,bmi.PK_COL_3,bmi.PK_COL_4,bmi.PK_COL_5,link10179.link10179_item_ID,link10157.link10157_item_ID,
    link10165.link10165_item_ID,link10000.link10000_item_ID,link10167.link10167_item_ID,link10166.link10166_item_ID,link10144.link10144_item_ID,
    link10140.link10140_item_ID,link10154.link10154_item_ID,link10158.link10158_item_ID,link10000A.link10000A_item_ID,link10153.link10153_item_ID,
    link10141.link10141_item_ID,link10168.link10168_item_ID,link10155.link10155_item_ID,link10182.link10182_item_ID 
    FROM B_MASTER_REPOSITORY_ITEM bmi WITH (NOLOCK)  
    JOIN B_SNAPSHOT_10188 snap10188 WITH (NOLOCK)  ON bmi.ITEM_ID= snap10188.ITEM_ID 
    LEFT JOIN link10140 ON (snap10188.F_1006202 = link10140.F_1006215) 
    LEFT JOIN link10179 ON (link10140.F_1006217 = link10179.F_1006507) 
    LEFT JOIN link10157 ON (link10140.F_1006217 = link10157.F_1006362) 
    LEFT JOIN link10165 ON (link10140.F_1006217 = link10165.F_1006411) 
    LEFT JOIN link10166 ON (link10140.F_1006217 = link10166.F_1006383) 
    LEFT JOIN link10144 ON ((link10140.F_1006217 = link10144.F_1000010) AND link10144.F_1000015=10197)  
    LEFT JOIN link10141 ON (link10140.F_1006217 = link10141.F_1006038) 
    LEFT JOIN link10168 ON (link10140.F_1006217 = link10168.F_1006431) 
    LEFT JOIN link10167 ON (link10166.F_1006382 = link10167.F_1006418) 
    LEFT JOIN link10000 ON (link10000.F_1000045 = link10144.F_1000005) 
    LEFT JOIN link10154 ON (link10141.F_1006036 = link10154.F_1006207) 
    LEFT JOIN link10158 ON (link10141.F_1006036 = link10158.F_1006117) 
    LEFT JOIN link10153 ON ((link10141.F_1006036 = link10153.F_1000010) AND link10153.F_1000015=10186)  
    LEFT JOIN link10155 ON (link10141.F_1006036 = link10155.F_1006301) 
    LEFT JOIN link10182 ON (link10141.F_1006036 = link10182.F_1006521) 
    LEFT JOIN link10000A ON (link10000A.F_1000045 = link10153.F_1000005)
    
    --WHERE bmi.REPOSITORY_ID= 10188  AND bmi.ITEM_ID IN ( 6750048, 8173745 )
    WHERE bmi.REPOSITORY_ID= 10188  AND bmi.ITEM_ID IN (select item_Id from B_MASTER_REPOSITORY_ITEM  WHERE REPOSITORY_ID= 10188)
    )
    select item_id, count(*) cnt from mainTable group by item_id order by cnt desc

Also, run the Index Fragmentation Report (found in the Repository Issues Troubleshooting section) to check if the repositories used in the template need defragmentation.