DSXchange

Hi

I am tuning a select query used in datastage job.It is having 35 NVL functions. If i handle this in transformer, will it increase the performance of the job as a whole.This single query is taking 1 hr in the production environment.

Thanks and Regards
Shabnam

did you try modify stage??

Tunning querries depends on n number of things i.e. whether db is paritioned ? whether you are using indexing efficiently ? Whether you are extracting only required collumns? ....

As far as handling derivation in transformer stage is concenred it again depends but db2 normally turns to be efficient in comparison to DS server. But worth trying it on both and do a comparison of stats!

I am using server stage. It doesn't have modify stage. Need to handle in transformer only.
Thanks and Regards
Shabnam

Hey,

The database is Oracle 9i

Shabnam

You said the query is taking about an hour to finish in production env. Was that the same case in dev. and uat etc.?
How many cpus do you have in your prod. box. You can probably create a multi-instance job which does selects and handles nvl in the transformer.
Extract your data in chunks depending upon the invocation id you send in.
Or you can do a simple select on the complete data set, partition it with a link partitioner and send it to different transformers (the number will depend upon how many cpus you have) and then do collect it at the end.

How long does the Oracle query take in another utility, such as sqlplus or TOAD? I don't believe doing the NVL operations in DataStage would be any faster.

HI,
as said by the others indexing also affects.
better to use DB than DS.

My suggestin is while populating data into Prd Db use Std Prc stage and write a proc apply ur NVL functions which wld be better i think

chk it in Toad sql editor

You can do a explain plan on the query and check the cost of both the SQL's; the one with NVL and second one without NVL. Check the difference, if it very high or its minimal.

Another checks could to see if your query is picking up the right indices.
Sometimes the Oracle optomizer does not pick up the indices you would like it to pick up.
In such cases adding the correct HINTS can increase the performance dramatically.
Also ask your DBA's to run stats on the involved tables, if it not done for a while.

Hi all,

In other utilities (Toad) also it is taking that much time. In other environment, the data is very less, so simulation is a long process( Need to do a data refresh from production server). I am not sure about the number of CPU's in the server as i am new to this project, but raised a ticket to OS team to know it.

If it is taking almost the same time in TOAD, then you don't have option other than to tune it, before putting using it in datastage. The performance of the query cannot be enhanced inside datastage.

Tip:
Instead of doing a data refresh from production server, can you point your query to run against the production(If performance to production environment is not an issue)?