CDR Tickets

BZDATETIME::2010-01-28 11:26:19
BZCOMMENTOR::Alan Meyer
BZCOMMENT::1

I've got the development system running on my workstation and
will try to figure out the problem.

Can you send me the steps needed to duplicate the problem,
including the names of the problem topics?

Thanks.

BZDATETIME::2010-01-28 12:17:07
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::2

(In reply to comment #1)
> I've got the development system running on my workstation and
> will try to figure out the problem.
>
> Can you send me the steps needed to duplicate the problem,
> including the names of the problem topics?
>
> Thanks.

Is it possible for Cynthia to log into your system to attempt re-creating the issue?

BZDATETIME::2010-01-28 14:54:41
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::3

Here are the instructions from Cynthia. I will email you her phone number shortly.

Login to the import utility and go to Find Record screen. Select the following parameters:
editorial board = “add” and select “pediatric treatment”
review cycle = “add” and select “December 2009”
Double click on any citation. Click the “Add” button and select one of the following topics
Childhood Craniopharyngioma
Childhood Intracranial Germ Cell Tumors
Late Effects of Treatment for Childhood Cancer
Unusual Cancer of Childhood
Then select “December 2009” as the review cycle and then click “ok”. Then click “Save & Close”.
Repeat with several citations so that you have 3 or 4 citations for each topic to test.
Log out of the import utility and login to the CMS web interface using my login. Click on the “Adim Tool” tab. At the top in the “Import and Divide” section, select “pediatric treatment” and “December 2009” then click “import & divide”. Select the citations and click “Submit”.
Log out and log back in using Sharon Q Kasner’s login. From her welcome page in the review pull down menu, select one of the above for topics and click “submit”. If the problem is recurring then you should get a message on the following screen that there are no citation ready for review.

BZDATETIME::2010-02-04 13:04:56
BZCOMMENTOR::Bob Kline
BZCOMMENT::4

Not a critical issue (users can still get their work done).

BZDATETIME::2010-04-14 14:34:09
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::5

Marked this as a P5 (from P6).

BZDATETIME::2010-04-30 12:58:28
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::6

Per discussions on Thursday, I have raised the priority of this issue to a P4.

BZDATETIME::2010-05-27 22:16:03
BZCOMMENTOR::Alan Meyer
BZCOMMENT::7

I'm working on this again.

I sent out an email tonight to Sharon Quint-Kasner, asking if she
can work with me in my office next Tuesday to demonstrate the
problem. That will be a lot faster, I think, than having me
flail around and not know what I'm looking at.

I'm entering this note in Bugzilla just to record what I'm doing.

BZDATETIME::2010-06-23 00:21:45
BZCOMMENTOR::Alan Meyer
BZCOMMENT::8

I haven't solved the problem but I have made some progress.

First off, for the Bugzilla record, I'll record here that we're
now using a new test environment. It's running on the same
server as the production environment, using the same exact copies
of SQL Server and IIS. To access the test environment a user can
use the URL:

http://citems-dev.nci.nih.gov

Login credentials are the same as in the production database
except that a user must put "test" in front of his password,
e.g., "ABC" becomes "testABC". If a user changes his password in
the production system it will NOT change in the test system, but
when I refresh the test database from production, I will, again,
prepend "test" to whatever was in production.

The new test environment is working much better than the old one.
I can reliably reproduce the problem in the new environment. I
couldn't do that in the old one, not because it worked there but
because a number of other things were broken that prevented me
from even getting that far.

Now, here's what I've discovered so far:

The problem is happening in the program "cipsviewlist.asp". The
program is executing a fairly complicated stored procedure called
"cmssp_CIPSviewList", passing in a reviewer ID, an editorial
board ID, and a summary topic ID. It appears to me that all of
the passed parameters are correct. When I tested using the
scenario that Cynthia demonstrated to me, the reviewer ID is
Sharon Kasner's, the editorial board is the one for Pediatric
Treatment, and the Summary Topic ID is the one for Childhood
Craniopharyngioma - all of which match what I was trying to test.

The stored procedure is timing out using the default timeout of
30 seconds. I set it to 60 seconds and it still timed out. I
was about to give up that line of inquiry when I thought, what
the heck, and tried 120 seconds. To my surprise, it completed
and returned the expected records.

It is still a puzzle to me why this procedure should take so long
to execute. It seems that something has built up either under
Sharon's ID, the Pediatric Treatment board ID, or a few specific
Summary topics, that is causing the query to take forever (well,
more than one minute.)

There is an intriguing comment by "pc" (Chauvette I think) in the
stored procedure that says:

3/11/05 got rid of "WHERE decision_id = 1" which was part of
the NOT IN subquery this matches what we're doing on
cipsmenu, also seems to speed up this query (why?)

I need all the clues I can get, so I tried to figure out what was
going on with the decision_history and decision_id.

I looked at the subquery as it now stands and executed it by
itself. It completes in under one second. I added the WHERE
clause that pc mentioned, and it still completes in under one
second. "decision_id = 1" restricts the query to looking at
records for which the decision is "Full Text Request", but this
may all be a red herring and the real answer may have nothing to
do with decision_history.

Whatever the cause of the problem, I've got enough information,
and a good enough test environment, that I'm much more optimistic
than I was yesterday about solving this.

I'll work on it some more when I come in on Thursday.

BZDATETIME::2010-06-24 15:59:28
BZCOMMENTOR::Alan Meyer
BZCOMMENT::9

I believe the problem is solved.

I created a new review_id index on the mt_decision_history table in the development database. I logged on to the development system as Sharon and tried searching for the Childhood Craniopharyngioma articles needing review.

Without the new index this took 62 seconds, which exceeded the default 30 second timeout and so produced no hits.

With the new index it took less than one second.

I tried deleting the new index and it went back to 62 seconds. Then I re-instated it and it was less than one second again. So I'm pretty sure this solves the problem. I think it will speed up everyone, not just Sharon.

I'd like someone to test this on the dev system and approve it before I put it into production. It's a very low risk change since I haven't changed any code or data, just added an index. It will just take me a few minutes to put it in production once I am instructed to proceed.

When testing it in the dev system you'll see some debugging messages at the top of the screen. I've left them in for while we are testing but will remove them from the dev system if we decide to put this into production. In that jumbled stream of messages you'll see a "Before time=..." and an "After time=..." which show the timed before and after executing the search.

BZDATETIME::2010-06-24 16:36:33
BZCOMMENTOR::Alan Meyer
BZCOMMENT::10

Adding Cynthia to the CC list for this issue.

BZDATETIME::2010-06-24 16:47:26
BZCOMMENTOR::Cynthia Boggess
BZCOMMENT::11

I have tested this in the dev system and I agree the problem looks solved. I checked all 5 summary topic that were having the display problem as well as other topics that were not. Everything worked correctly.

(In reply to comment #9)
> I believe the problem is solved.
> I created a new review_id index on the mt_decision_history table in the
> development database. I logged on to the development system as Sharon and
> tried searching for the Childhood Craniopharyngioma articles needing review.
> Without the new index this took 62 seconds, which exceeded the default 30
> second timeout and so produced no hits.
> With the new index it took less than one second.
> I tried deleting the new index and it went back to 62 seconds. Then I
> re-instated it and it was less than one second again. So I'm pretty sure this
> solves the problem. I think it will speed up everyone, not just Sharon.
> I'd like someone to test this on the dev system and approve it before I put it
> into production. It's a very low risk change since I haven't changed any code
> or data, just added an index. It will just take me a few minutes to put it in
> production once I am instructed to proceed.
> When testing it in the dev system you'll see some debugging messages at the top
> of the screen. I've left them in for while we are testing but will remove them
> from the dev system if we decide to put this into production. In that jumbled
> stream of messages you'll see a "Before time=..." and an "After time=..." which
> show the timed before and after executing the search.

BZDATETIME::2010-06-24 16:54:55
BZCOMMENTOR::Alan Meyer
BZCOMMENT::12

I've created the new index in the production database.

I think we can wait a bit to be sure everything is working correctly in production and, if so, close the issue.

BZDATETIME::2010-06-24 17:03:31
BZCOMMENTOR::Cynthia Boggess
BZCOMMENT::13

I have tested this in production and it is working correctly. I'll let Sharon know it is ok for her to start reviewing citations. If she does not run into any problems during her review then I think we can close this issue.

BZDATETIME::2010-06-26 07:24:16
BZCOMMENTOR::Alan Meyer
BZCOMMENT::14

Closing the issue after confirmation from Sharon and Cynthia that everything is working.