What are other Performance tunings you have done in your last project to increase the performance of slowly running jobs?

Question:What are other Performance tunings you have done in your last project to increase the performance of slowly running jobs??

Answer: <OL><LI>Staged the data coming from ODBC/OCI/DB2UDB stages or any database on the server using Hash/Sequential files for optimum performance also for data recovery in case job aborts.</LI><LI>Tuned the OCI stage for &#39;Array Size&#39; and &#39;Rows per Transaction&#39; numerical values for faster inserts, updates and selects.</LI><LI>Tuned the &#39;Project Tunables&#39; in Administrator for better performance.</LI><LI>Used sorted data for Aggregator.</LI><LI>Sorted the data as much as possible in DB and reduced the use of DS-Sort for better performance of jobs</LI><LI>Removed the data not used from the source as early as possible in the job.</LI><LI>Worked with DB-admin to create appropriate Indexes on tables for better performance of DS queries</LI><LI>Converted some of the complex joins/business in DS to Stored Procedures on DS for faster execution of the jobs.</LI><LI>If an input file has an excessive number of rows and can be split-up then use standard logic to run jobs in parallel.</LI><LI>Before writing a routine or a transform, make sure that there is not the functionality required in one of the standard routines supplied in the sdk or ds utilities categories.<BR>Constraints are generally CPU intensive and take a significant amount of time to process. This may be the case if the constraint calls routines or external macros but if it is inline code then the overhead will be minimal.</LI><LI>Try to have the constraints in the &#39;Selection&#39; criteria of the jobs itself. This will eliminate the unnecessary records even getting in before joins are made.</LI><LI>Tuning should occur on a job-by-job basis.</LI><LI>Use the power of DBMS.</LI><LI>Try not to use a sort stage when you can use an ORDER BY clause in the database.</LI><LI>Using a constraint to filter a record set is much slower than performing a SELECT … WHERE….</LI><LI>Make every attempt to use the bulk loader for your particular database. Bulk loaders are generally faster than using ODBC or OLE.</LI></OL>

Tags:Interview Questions, Data Warehouse, DataStage,

Please submit your Interview Answer for the above Question via replying to this post. Thank you for visiting to our blog, Have a Good Day!.

---

Sponsor Advertisement

Real Estate Management System Software

Real Estate Management Software is an advanced end-to-end Real Estate Office Management System that enables Real Estate Agencies to manage more effectively, search prorty details, keep property owner data etc. Web-based tracking and accounting solution ideal for commercial, residential housing....