Optimizing query with an index

 Updated and current instructions can be found in the following tech note:

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 .