Question Index on datetime fields ?

[bigwill]

Hi all

I have a rather large table that and i want to use as many indexes i can in order to get performance.
I have the following indexes:
-iClosecode
-iGroupName
-iFromDateTime
-iToDateTime

if i use:
def var dtStart as datetime no-undo.
def var dtEnd as datetime no-undo.
assign dtStart = datetime(6,1,2015,8,0,0)
dtEnd = datetime(6,8,2015,16,0,0).

for each xx no-lock
where xx.CloseCode = 1
and xx.GroupName = "Oslo"
and xx.StartTime >= dtStart
and xx.EndTime <= dtEnd:

end.
if i compile this with xref i can see from the output that it only uses the inexes "iCloseCode" and "iGroupName".

If i change ">=" and "<=" to just "=" and compiles again with xref, i can see that progress now uses all indexes.

Is there a way to get progress to use all 4 indexes if i use >= and <= ? Can i set progress to use all indexes with "use-index" of any kind ?

Would this worked better if i created one index with all four fields ?

 

[andre42]

I understand that each of these indexes only contains one column. A single index with all four fields will certainly work better, though in your example it will only use the first three fields since StartTime is compared with ">=" (see bracketing). This is definitely the way to do this properly, Progress combining multiple indexes does not always work, at least not with find statements, and not necessarily as you expect.

Compare http://knowledgebase.progress.com/articles/Article/21099 for your example:
"... WHERE using AND

When the selection criteria includes the use of AND, more than one index will be used when all the components of each index are involved in equality matches, and the indexes are not unique. ..."

 

[bigwill]

I understand that each of these indexes only contains one column. A single index with all four fields will certainly work better, though in your example it will only use the first three fields since StartTime is compared with ">=" (see bracketing). This is definitely the way to do this properly, Progress combining multiple indexes does not always work, at least not with find statements, and not necessarily as you expect.

Compare http://knowledgebase.progress.com/articles/Article/21099 for your example:
"... WHERE using AND

When the selection criteria includes the use of AND, more than one index will be used when all the components of each index are involved in equality matches, and the indexes are not unique. ..."

so you suggest to only create a new index containing "CloseCode, GroupName and StartTime" ? Using this new index, will it give better performace ?

 

[andre42]

Sounds good, and should definitely give better performance.
Usually I would also include EndTime, but that will only help you if search for StartTime with equality ("=") which is not very likely. Now, if you did separate date and time into two fields an index on StartDate and EndDate would be somewhat more useful, but I can't decide if that would make sense for you.
Oh, and assuming your data is sane (ie. an entry always ends after it starts) you could enhance performance further like this:

for each xx no-lock
where xx.CloseCode = 1
and xx.GroupName = "Oslo"
and xx.StartTime >= dtStart
and xx.StartTime <= dtEnd: