It's all about the answers!

Ask a question

Optimizing query with an index


Ben Tran (16018) | asked Oct 05 '15, 2:01 p.m.
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.

2 answers



permanent link
Richard Rakich (1113) | answered Oct 17 '16, 10:40 a.m.
JAZZ DEVELOPER
 Updated and current instructions can be found in the following tech note:
http://www-01.ibm.com/support/docview.wss?uid=swg21975746



permanent link
Ben Tran (16018) | answered Oct 05 '15, 2:02 p.m.
edited Dec 04 '15, 9:26 a.m.
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 .

Your answer


Register or to post your answer.