To speed up the aggregator

ICE · Post by **ICE** » Wed Jun 06, 2007 7:51 pm

Dear All,

Is there any way to speed up the aggregator?
Any suggestion pls.

Thanks,
ICE

ArndW · Post by **ArndW** » Wed Jun 06, 2007 7:53 pm

pre-sort the incoming data and tell the aggravator about it.

ray.wurlod · Post by **ray.wurlod** » Wed Jun 06, 2007 9:03 pm

You might also investigate option #6 on the DS.TOOLS menu, where you can modify the reporting intervals and memory allocation model.

vmcburney · Post by **vmcburney** » Wed Jun 06, 2007 9:59 pm

Wow, version 4. You could use a Universe stage, write all your data into it and pull it out with a group by SQL command. You could upgrade a couple versions and try multiple instance jobs. Upgrade to DataStage EE parallel jobs with much faster sort and aggregation functions.

ICE · Post by **ICE** » Wed Jun 06, 2007 10:06 pm

Dear Ray,

Just now I am checking the option#6 from DS.tools.
I see there are some options such as ReportAfterRows, ReportAfterTime,TableSize, and so on ... to choose.

So may I know which option would be maximize the performance of the aggregator?
Is it the TableSize option???

Thanks in advance,
ICE

ray.wurlod wrote:You might also investigate option #6 on the DS.TOOLS menu, where you can modify the reporting intervals and memory allocation model. ...

ray.wurlod · Post by **ray.wurlod** » Wed Jun 06, 2007 11:01 pm

At the menu prompt enter each number followed by a question mark; for example

Code: Select all

2?

- this will give you more information. Which ones to change depends on where your performance problem is. Increasing the reporting interval (so that the stage updates its status less frequently) will always help.

Sorted input (sorted by grouping keys) will give the best gains.

ICE · Post by **ICE** » Sat Jun 09, 2007 8:23 pm

Thank you, Ray.

Thank you all for your advice.

ray.wurlod wrote:At the menu prompt enter each number followed by a question mark; for example
Code: Select all
2?
- this will give you more information. Which ones to change depends on where your performance problem is. ...

karrisuresh · Post by **karrisuresh** » Sun Jun 10, 2007 10:45 pm

Hi always
sort the data before it is sent to aggregator

so that sll the data belonging to one particular group
will go to one chunk and hence grouping at aggr stage will become faster

thanks
suresh

ArndW wrote:pre-sort the incoming data and tell the aggravator about it. ...

chulett · Post by **chulett** » Mon Jun 11, 2007 7:13 am

karrisuresh wrote:always sort the data before it is sent to aggregator

As noted, there is more to it than that. You also have to assert the sorted order in the stage so it knows you've done this. And hopefully you've sorted in such a manner that supports the grouping being done, otherwise it's all for naught.

ICE · Post by **ICE** » Tue Jun 12, 2007 8:15 pm

Dear Chulett and karrisuresh,

Could u pls let me know whether I need to use sort stage before I sent to aggregator?
Currently I am using the sort stage to sort the data before I sent to aggregator. But I got below error message.

ImptoTEST.sort14: Unable to retrieve value for property 'SortSpec

I have no experience for sort stage.

Thanks you all for your advice.
ICE

chulett wrote:
karrisuresh wrote:always sort the data before it is sent to aggregator
As noted, there is more to it than that. You also have to assert the sorted order in the stage so it knows you've done this. And hopefully you've sorted in such a manner that supports the grouping being done, otherwise it's all for naught.

ray.wurlod · Post by **ray.wurlod** » Tue Jun 12, 2007 10:01 pm

The correct spelling of the second person personal pronoun is "you" not "u".

You can use any method you like to sort the data. A Sort stage is one way. If the source is a database table, you could include an ORDER BY clause in the extraction SQL.

chulett · Post by **chulett** » Tue Jun 12, 2007 11:48 pm

As noted, many sort options exist, the specific stage is only one such option available. Check the Property Help for the Sort Specification in the stage, it has examples of the syntax needed. Yes, it's a little confusing if you've never used it before, but check the help and give that a shot.

Just as an FYI, that stage is pretty slow. If you can do this in the source, say an order by in a database, that would generally be more performant. Or perhaps leverage a high-speed sort package or just the plain old 'sort' command from the O/S. All of them typically beat the pants off the Sort stage.

ICE · Post by **ICE** » Wed Jun 13, 2007 1:52 am

Hummmm...........

I use "u" as short word of "you" due to boring to type a lot of words

Ok. Later will try to type full word.

Thank you for your advice

Thank you,
ICE

ray.wurlod wrote:The correct spelling of the second person personal pronoun is "you" not "u".

You can use any method you like to sort the data. A Sort stage is one way. If the source ...

ICE · Post by **ICE** » Wed Jun 13, 2007 2:27 am

Oop!!! Really???
Ok. I think I better use the order by clause in my sql. Thank you for your advice.

Thanks,
ICE

chulett wrote:As noted, many sort options exist, the specific stage is only one such option available. Check the Property Help for the Sort Specification in the stage, it has examples of the syntax needed. Yes, it's a little confusing if you've never used it before, but check the help and give that a shot.

Just as an FYI, that stage is pretty slow. If you can do this in the source, say an order by in a database, that would generally be more performant. Or perhaps leverage a high-speed sort package or just the plain old 'sort' command from the O/S. All of them typically beat the pants off the Sort stage.

chulett · Post by **chulett** » Wed Jun 13, 2007 6:20 am

Yes, best to do that if you can unless your database sort speed is horrible. Oh, and...

You are welcome.