We have developed one job in SAS which loads data from table T1 to T2. There are around 40 Lacks rows in table. Job is desgined such that data is executed row by row (bulkload = no).
We are getting some perofrmance issue while executing this job. This job is taking almost 10 hrs to complete. Whenever user is giving restriction to number of rows (e.g. 5 Lacks), job is completing in 30 min. But if we remove row restriction, job is taking much time to complete.
There are no error messages recorded into server error log. If we checked server error log, actual time to complete job is 20 - 30 seconds.
I tunned IQ server (changed iqgovern, iqmc , iqtc and some database parameters like % wash area) so that now job is completing in 6-7 hrs.
I have following question about this job.
1. How the data is processing while execution? Whenever any job is executed, is there any workspace is created in IQ?
2. Why the job is completing in 30 min if we put row restriction in it. Is there any database option exists which controls number of rows.
3. Is there any other parameter that is affecting performance.
Job is simple which loads data from source table to target table. Both tables are in same schema, same database, same user and same table structure. Target table is initially empty.
Versions used are:
Sybase IQ - 15.3
SAS - 9.3
Any more suggestions to improve the performance of job or any more database options are required to changed?
Thank you for your help in advance.