Jazz Forum Welcome to the Jazz Community Forum Connect and collaborate with IBM Engineering experts and users

Optimizing query with an index

When there are a large number of VVCMODEL.VERSION records queries may be slow. There are a lot of calls on the VERSION tables that take several seconds to complete. The query calls upon CONCEPT and STORAGE columns which don't have any indexes on them. As a result may be slow and cause high CPU usage.

0 votes



2 answers

Permanent link
 Updated and current instructions can be found in the following tech note:
http://www-01.ibm.com/support/docview.wss?uid=swg21975746


0 votes


Permanent link
When there are a large number of VVCMODEL.VERSION records queries may be slow.  To improve the performance of the queries,

Reduce the size of the CONCEPT and STORAGE columns to 450 via ALTER TABLE.

Add the following indices
CREATE  INDEX VERSION_CONCEPT_DX   ON VVCMODEL.VERSION (CONCEPT)  
CREATE  INDEX VERSION_STORAGE_DX ON VVCMODEL.VERSION (STORAGE)

Under some circumstances the query
select t1.ITEM_ID, t1.STATE_ID, t1.JZ_DISCRIMINATOR
from RESOURCE.RESOURCE t1
where ((t1.MODIFIED >= <time>) and (t1.MODIFIED <= >time>)
and (t1.URI like REPLACE(N'storage/%', N'[', N'[[') ESCAPE N'['
or t1.URI like REPLACE(N'baselines/%', N'[', N'[[') ESCAPE N'['
or t1.URI like REPLACE(N'reindex/%', N'[', N'[[') ESCAPE N'['))
order by t1.MODIFIED asc

Recreate the index RESOURCE_RESOURCE_MODIFD_DX using
CREATE NONCLUSTERED INDEX [RESOURCE_RESOURCE_MODIFD_DX] ON [RESOURCE].
[RESOURCE]
([MODIFIED] ASC)
INCLUDE ([STATE_ID],
[ITEM_ID],
[URI],
[JZ_DISCRIMINATOR])"

This will improve the way that the query optimizer plans the query.

There has been a Jazz.net Work Item opened up for this item which can be found here, https://jazz.net/jazz/web/projects/Jazz%20Foundation#action=com.ibm.team.workitem.viewWorkItem&id=371429 .

1 vote

Your answer

Register or log in to post your answer.

Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.

Search context
Follow this question

By Email: 

Once you sign in you will be able to subscribe for any updates here.

By RSS:

Answers
Answers and Comments
Question details

Question asked: Oct 05 '15, 2:01 p.m.

Question was seen: 4,299 times

Last updated: Oct 17 '16, 10:40 a.m.

Confirmation Cancel Confirm