DSXchange

Hi,

I ready many thread on the pivot stage in parallel and the performance issue.

I created one temp job and tested it in parallel mode and seq mode. As expected the PX performance was better.

In PX mode, I kept the partitioning to auto.

Many of the threads mentioned that the pivot stage needs hash on pivot keys. The pivot documentation that I have does not talk abt partitioning for the stage or PX or Seq mode.

Though the pivot stage functionality is simple i.e. convert one row into multiple based on the input cols, just wondering why does it need hash partitioning?

E.g.
If input is: It has to generate the following output I also tried running the same job with RR partion and it produced the same no. of rows. The output was also the same.

Any thoughts?

In the example you have given, without hash partitioning would it be more difficult to remove duplicates? (without repartitioning further down the stream)

It needs key-based partitioning (whether Hash or Modulus) so that every key value has all its rows on the same partition, so that (necessarily) the pivot will generate the correct number of rows per key.