If suppose my code is running fine since last 2 yrs , but reporting team says since last 2 days its taking too much of time to generate a report,
even every thing is same since last 2yrs , how can i justify this situatuion and how how can resolve this performance issue from etl side??
there might be so many causes can be for this,
please check the session log and see in the peformance statistics detail where it is going wrong.
or might be some other jobs running in the DB might have caused this peformance issue.
As our folks said, there could be many reasons for the degradation of queries performance.
1) over the past two years, data might have increased enormously in the tgt table. You may have to take a look at the indexes created, database spaces etc.
2) if tgt is Teradata, consider collecting statistics on key columns.
3) use query analyzer to see which join in the query is taking long time to perform its action.
I hope this may help u a bit and keep us informed.
Just to clarify...is the issue the report is taking long or the informatica session?
If it's the report...you need to tune the query..run the query straight against the database..generate an Explain plan and investigate rewriting, adding indexes to fields in WHERE clause...Database statistics. The query should run fast against the database. As tables getting bigger, the database tries to run the query a bit different, or statistics become outdated and old. If you don't have experience tuning SQL..I recommend having a DBA look at your query.
If it's the session taking long..you need to go through the process of:
1. Where's the bottleneck - source, transformations, target, session? Tons of ways to figure out...check the session log...look at Thread Statistics...look careful at the times and find the time where something is taking very long...
hope that helps