Icon View Thread

The following is the text of the current message along with any replies.
Messages 11 to 14 of 14 total
Thread Optimize query/understanding impact where claus has when building result set.
Wed, Oct 25 2017 1:06 PMPermanent Link

David

Hi Tim.

Thanks, I will use the utility and see what it shows.

What I meant by using a compound index is  currently I filter on DocType and IssueDate.  If I want all documents for the given year returned, I would use DocType and IssueDate between StartOfYear and EndOfYear.  This works, but DBISam seems to build a bitmap from DocType first that may have 2000 records in any given year, then it filters on the issue date portion.

If I create a new field that is a combination of DocType and the year of issue then I can accomplish the same thing as above by only using one index, and returning a much smaller bitmap.

Does that make more sense?
Fri, Oct 27 2017 1:58 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

David,

<< What I meant by using a compound index is  currently I filter on DocType and IssueDate.  If I want all documents for the given year returned, I would use DocType and IssueDate between StartOfYear and EndOfYear.  This works, but DBISam seems to build a bitmap from DocType first that may have 2000 records in any given year, then it filters on the issue date portion. >>

It will typically filter first on the condition with the least amount of I/O (giving preference to indexed access).

<< If I create a new field that is a combination of DocType and the year of issue then I can accomplish the same thing as above by only using one index, and returning a much smaller bitmap. >>

That's just it: DBISAM *cannot* use compound indexes like that.  It can only use the first column in any given index for optimization purposes.

ElevateDB can use compound indexes because it supports using row value constructor expressions where you can do things like:

WHERE (DocType, IssueDate) BETWEEN (('A', 1999), ('B', 2010))

In such a case, an index on DocType, IssueDate *would* be used, and would be used in a very optimal manner.

Tim Young
Elevate Software
www.elevatesoft.com
Sun, Oct 29 2017 9:39 AMPermanent Link

David

Understood Tim, ElevateDB would be the way forward here and I do have it, I just have never go round to converting the database to EDB et.

I do not think I was being clear enough.  I intend to use just one index, for which I have created a new field called TypeYear which is populated by the DocType column and the year of issue combined.   

So before hand I would have 2 fields DocType = R and IssueDate = '2017-01-01' for example, but now I have created an additional field that hold R2017 in the TypeYear column which I can use as a combined index.  

I realise that DBISam can't use compound indexes, but by me creating this new field, I am doing the same thing technically and I can then do a filter for all documents of a particular type within a particular year using only one index.

This I think improves on the original way I was doing this as it would filter on DocType first, giving all documents of that type regardless of the year, then a filter was done on the IssueDate column for the required year, so the index was not optimal because of my poor design, but the addition of the TypeYear column, I think it makes it significantly faster,  For any type of document in any given year there could be up to 3000 records+ so the original would return maybe 30000 records as the system has been used for 10 year, but the new method would only return 3000.
Mon, Oct 30 2017 3:07 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

David,

<< I do not think I was being clear enough.  I intend to use just one index, for which I have created a new field called TypeYear which is populated by the DocType column and the year of issue combined. >>

Ahhh, yes, sorry about the misunderstanding.  Yes, that would work in your case and should improve the performance quite nicely.

Tim Young
Elevate Software
www.elevatesoft.com
« Previous PagePage 2 of 2
Jump to Page:  1 2
Image