Order of dimensions in accumulation register



For students who study 1C:Professional course.

#1
People who like this:0Yes/0No
Just came
1C:Professional
Rating: 0
Joined: May 15, 2015
Company: CloudOffice AB

What order of dimensions in accumulation register is better? For example, for register Materials? 1) Material, 2) warehouse or 1) warehouse, 2) material. Why?

 
#2
People who like this:0Yes/0No
Moderator
1C:Professional
Rating: 4
Joined: May 8, 2013
Company: 1C Company

The most important requirement here is the following (from the Lesson 2): "You need to have a dimension for every condition in your “WHERE” clause. Or (the same from another perspective) you need to use all dimensions in the query’s “WHERE” clause."

If this requirement is met, the order of dimensions plays no practical role. The only (100% pure theoretical) recommendation could be: place more populated dimensions closer to the top of the list. In our example it would mean placing the Material dimension first because it has more different values than Warehouse dimension (we have very few warehouses and hundreds of materials). That said, we've never seen any practical effect of following this recommendation in any real-world system, so you can just ignore this factor.

Using only some of the dimensions in the WHERE clause is quite a different story.

Let's say you have an accumulation register with five dimensions (Dim1, Dim2, ...). You created these particular dimensions set for a reason - the main (most frequently executed) query uses all of them. But what if you have some peripheral functionality that needs a query with only some of the dimensions (say, 3 and 4)? In order for this query to execute efficiently, you need to place these two dimensions first. I.e, your dimensions set, should be Dim3, Dim4, Dim1, Dim2, Dim5. Not following this rule can cause significant slowing down of this particular query.

OK, but what if you have another peripheral functionality that uses say, Dim5 and Dim2 in its query? Well this problem has no solution with the only register (we cannot place two different pairs of dimensions to the top of the list at the same time), so you are going to need another accumulation register for this.

 
Subscribe