poor performance

ravij · Post by **ravij** » Mon Jun 05, 2006 6:45 am

Hi ,

My job is like db--->AggStage---->db. I am aggregating the sal group by geography. i am processing 1 million records. my job is running very slow. I used Link Partitioner though its very slow. How can I improve my job performance?

any help would be appreciated

DSguru2B · Post by **DSguru2B** » Mon Jun 05, 2006 6:47 am

Sort the data before passing it to the aggregator stage. You can sort the incoming data by a mere order by clause in your sql select, and let the aggregator know that the sort order. That will help speed up things.
Also, for that amount of data, do the aggregation at the database level. I mean, in you sql itself.
Regards,

ravij · Post by **ravij** » Mon Jun 05, 2006 6:53 am

Hi thanks for quick response.

Do u mean that should i write the user defined sql at source(database) level to sort the records?

thanks in advance

DSguru2B · Post by **DSguru2B** » Mon Jun 05, 2006 7:04 am

You dont have to go for user defined. Even in generated query, you have a section where you can provide order by clauses. What database stage are you using?

dprasanth · Post by **dprasanth** » Mon Jun 05, 2006 7:06 am

ravij wrote:Hi thanks for quick response.

Do u mean that should i write the user defined sql at source(database) level to sort the records?

thanks in advance

You can use a sort stage to sort the records

ravij · Post by **ravij** » Mon Jun 05, 2006 7:11 am

Hi DSguru,

I am using Oracle OCI stage for source db.

thanks in advance.

ArndW · Post by **ArndW** » Mon Jun 05, 2006 7:12 am

Then put your ORDER BY into your Oracle SELECT statement.

DSguru2B · Post by **DSguru2B** » Mon Jun 05, 2006 7:17 am

A better option would be a db order by rather than a sort stage for that amount of records. Do as ArndW suggested. Put the orderby clause in the sql select. You might have to go for user defined sql. Look into it.

dprasanth · Post by **dprasanth** » Mon Jun 05, 2006 7:20 am

DSguru2B wrote:A better option would be a db order by rather than a sort stage for that amount of records. Do as ArndW suggested. Put the orderby clause in the sql select. You might have to go for user defined sql. Look into it.

In the OCI stage, in the sql tab, click on column generated sql query and click the build button.. There you can see the
FROM , WHERE and OTHER clause
You can specify your ORDER BY in the OTHER Clause. This way you don't need to use a user defined sql.

DSguru2B · Post by **DSguru2B** » Mon Jun 05, 2006 7:23 am

There you go. dprasanth outlined it for you. I wasnt able to do it as i have not used the OCI stage. Just never got the chance to work with Oracle.

DSXchange