SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 2 of 2
  1. #1
    New Member
    Join Date
    Oct 2012
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Question ALM11 Issues with generated SQL from Requirements Module

    We are experiencing a major issue within the Requirements Module of ALM 11. This is already with HP and is being looked at by an Escalation manager and R&D. However, I was wondering if anyone else has seen the issue or can confirm if you are seeing the same SQL being generated.

    This appears to be ONLY relevant to Requirements. We have about 20 users on 1 project that are working exclusively with Requirements and have so far generated about 9000 items.

    What we are seeing, from debug log files, generated (by ALM) SQL like the following.....which is hugely inefficient!

    declare @p1 int
    set @p1=809
    exec sp_prepexec @p1 output,N @p1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int @p10 int @p11 int @p12 int @p13 int @p14 int @p15 int @p16 int @p17 int @p18 int @p19 int,@P20 int,@P21 int,@P22 int,@P23 int,@P24 int,@P25 int,@P26 int,@P27 int,@P28 int,@P29 int,@P30 int,@P31 int,@P32 int,@P33 int,@P34 int,@P35 int,@P36 int,@P37 int,@P38 int,@P39 int,@P40 int',N'SELECT RQ_REQ_ID,isnull((CAST(RQ_FATHER_ID AS VARCHAR(4000))), (''''))+''0'' GROUP_NAME,RQ_FATHER_ID FROM REQ WHERE RQ_REQ_ID IN ( SELECT PARENTS.RQ_REQ_ID FROM (SELECT RQ_REQ_ID,RQ_REQ_PATH FROM REQ WHERE (RQ_FATHER_ID IN @p1 ,@P2 ,@P3 ,@P4 ,@P5 ,@P6 ,@P7 ,@P8 ,@P9 @p10 @p11 @p12 @p13 @p14 @p15 @p16 @p17 @p18 @p19 ,@P20 ,@P21 ,@P22 ,@P23 ,@P24 ,@P25 ,@P26 ,@P27 ,@P28 ,@P29 ,@P30 )))PARENTS WHERE PARENTS.RQ_REQ_PATH IN (SELECT SUBSTRING(DESCENDANTS.RQ_REQ_PATH,1,( SELECT TOP 1 LEN(RQ_REQ_PATH) FROM REQ WHERE RQ_FATHER_ID =@P31 )) FROM (SELECT RQ_REQ_ID,RQ_REQ_PATH FROM REQ GET_VALUE_TABLE2 WHERE (RQ_REQ_PATH LIKE ((SELECT RQ_REQ_PATH FROM REQ WHERE RQ_REQ_ID=@P32 )+''%''))) DESCENDANTS UNION SELECT SUBSTRING(DESCENDANTS.RQ_REQ_PATH,1,( SELECT TOP 1 LEN(RQ_REQ_PATH) FROM REQ WHERE RQ_FATHER_ID =@P33 )) FROM (SELECT RQ_REQ_ID,RQ_REQ_PATH FROM REQ GET_VALUE_TABLE2 WHERE (RQ_REQ_PATH LIKE ((SELECT RQ_REQ_PATH FROM REQ WHERE RQ_REQ_ID=@P34 )+''%''))) DESCENDANTS UNION SELECT SUBSTRING(DESCENDANTS.RQ_REQ_PATH,1,( SELECT TOP 1 LEN(RQ_REQ_PATH) FROM REQ WHERE RQ_FATHER_ID =@P35 )) FROM (SELECT RQ_REQ_ID,RQ_REQ_PATH FROM REQ GET_VALUE_TABLE2 WHERE (RQ_REQ_PATH LIKE ((SELECT RQ_REQ_PATH FROM REQ WHERE RQ_REQ_ID=@P36 )+''%''))) DESCENDANTS UNION SELECT SUBSTRING(DESCENDANTS.RQ_REQ_PATH,1,( SELECT TOP 1 LEN(RQ_REQ_PATH) FROM REQ WHERE RQ_FATHER_ID =@P37 )) FROM (SELECT RQ_REQ_ID,RQ_REQ_PATH FROM REQ GET_VALUE_TABLE2 WHERE (RQ_REQ_PATH LIKE ((SELECT RQ_REQ_PATH FROM REQ WHERE RQ_REQ_ID=@P38 )+''%''))) DESCENDANTS UNION SELECT SUBSTRING(DESCENDANTS.RQ_REQ_PATH,1,( SELECT TOP 1 LEN(RQ_REQ_PATH) FROM REQ WHERE RQ_FATHER_ID =@P39 )) FROM (SELECT RQ_REQ_ID,RQ_REQ_PATH FROM REQ GET_VALUE_TABLE2 WHERE (RQ_REQ_PATH LIKE ((SELECT RQ_REQ_PATH FROM REQ WHERE RQ_REQ_ID=@P40 )+''%''))) DESCENDANTS ) ) ORDER BY RQ_FATHER_ID ASC,RQ_ORDER_ID ASC',0,1,1197,1213,4480,4480,4480,4480,4480,4480,4 480,4480,4480,4480,4480,4480,4480,4480,4480,4480,4 480,4480,4480,4480,4480,4480,4480,4480,4480,4480,1 197,1197,0,0,1213,1213,1,1,4480,4480
    select @p1


    This is a simple request that, even when run directly on the SQL server with R&D watching, took 5 minutes to complete and did around 24 million reads to produce 60 rows. This type of generated SQL is not limited to 1 function either. With multiple users, we are seeing huge delays as the SQL server gets overloaded resulting in people not being able to work.

    Is there anyone else experiencing problems in Requirements and/or seeing generated SQL like this from that module? Any confirmation that this is more widespread than just us would be appreciated.

    BTW, this project was not migrated from an earlier version but created in ALM 11. However, it appears we are also experiencing the same issue in migrated (from 9.2) projects as well.

  2. #2
    New Member
    Join Date
    Oct 2012
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0
    The problem has been identified, and fixed. In fact, it was fixed in Patch 14 but the symptoms for that bug/fix did not exactly match ours.

    So, if you don't have at least Patch 14 installed and you are planning to use the Requirements module a lot, get uptodate with the patches.

 

 

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 13.04%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 01:55 AM.

Copyright BetaSoft Inc.