Adopted Bitmaps example now on Open Exchange

In my earlier article on Adopting Bitmaps I described the technique already.
Now you can find a code example also on Open Exchange.

Summary:

The base class Bmap.Person defines persons within an organization distributed
by various countries. All records are indexed by (Country, PersonalId).
The idea is to separate data by country and have an easy way to MERGE it into a common Repository

As you know this structure doesn't allow the use of bitmaps.

So a wrapper class Bmap.PersonQ around the data eliminates the top level of
the index (Country) and isolates the PersonId (%Integer, MINVAL=1) as its IdKey
Now you are ready to use a Bitmap index.

A few performance figures on 300010 generated records.
You see also that Relative Cost is sometimes quite misleading.
At the end of the day, the count of Global references and Lines Executed is the truth.

select count(*) from Bmap.Person
300010 global references 1600446 lines executed

 

Test #1: total count for a specific country

select count(*) from Bmap.Person where Ctry='RU'
Relative cost = 197762
10015 global references 70474 lines executed

 

select count(*) from Bmap.PersonQ where Bmap.Ctry('RU')=1
Relative cost = 401400
10 global references 424 lines executed

 

Test #2: Persons with a specifc Home_State

select count(*) from Bmap.Person where Ctry='RU' and home_state='MA'
Relative cost = 457.96
218 Global references 2335 lines executed 

 

select count(*) from Bmap.PersonQ where Bmap.Ctry('RU')=1 and home_state='MA'
Relative cost = 2012.8
16 global references 478 lines executed

 

Test #3: Persons by Home_State

select home_state,count(*) from Bmap.Person where Ctry='RU' group by home_state
Relative cost = 372162
Row count: 50 
10420 global references 153708 lines

 

select home_state,count(*) from Bmap.PersonQ where Bmap.Ctry('RU')=1 group by home_state
Relative cost = 453400
Row count: 50
817 global references 155475 lines executed


Here it is obvious that grouping takes quite a significant part of lines executed. 

 

  • + 7
  • 1
  • 99
  • 0