Sybase IQ Performance Issue

[Former Member]

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.

Please Note:
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.

[markmumy]

One other thing to consider....  If you were to migrate to IQ 16 and have a simplex, you could leverage the new IQ in-memory capability for high-speed loading.  We released a new in-memory cache called RLV that would allow you load data direct to an in-memory structure and not to disk so that OLTP style transactions are faster.  The data is safeguarded so that should IQ be shutdown the data would persist and be loaded back into memory when restarted.

Behind the scenes the data is flushed from memory to main store so that memory is not always in use and doesn't cache the entire table.

As pointed out, though, you will want to push all processing into IQ and not bring any back to the SAS application.  IQ 15 will move data fast from table to table.  Moving to IQ 16 can nearly guarantee that the table to table data move is fully parallel and significantly faster than IQ 15.

Mark

[Former Member]

Hi

If you load the data into an IQ table row-by-row (INSERT-VALUES statements) then the processing will be very slow, no matter how much your IQ server is tuned. IQ is just not designed for row-by-row activity. If you have no choice but to process the data in your source table on row-by-row basis, you'd better not insert rows directly to the destination IQ table. Instead, you may write the data to a staging table in a "regular" RDBMS, maybe even to a table in the IQ catalog, or even better - to a delimited file. Then, the data from the staging file/table can be loaded to IQ using LOAD TABLE statement very quickly, 4 million rows to load is not that big for IQ.

Hope it helps

Leonid Gvirtz

[Former Member]

If we load data using load table command, data is loading in 8 - 10 seconds (40 lacks rows).  But if we put option bulkload=no, data is processed in row by row manner which is taking much time.  As per discussion with users, it is requirement of the job.  They can not change the setting.

[c_baker]

By not using bulk load, you are forcing IQ to perform row-by-row inserts.  This causes heavy versioning build up.  Reducing the rows (Lacks? = 5) reduces the input to the row-by-row operations and the corresponding reduction in insert operations reduces the time.

Remember - IQ is not an OLTP engine. IQ performs best using set and bulk operations, why are you not using bulkload = 'yes'?

The other issue here is that you are also taking data out of IQ T1 to SAS and back into T2. The additional network traffic to move the data in and out of IQ is killing your performance.  You can alter the SAS process to keep the data in IQ by directly selecting from T1 into T2, taking advantage of no network traffic and the usage of IQ's bulk features by keeping the data in IQ.  You can also start IQ with a larger network packet size (-p 16000) for IQ 15.3, that might help.

There is also a SAS Accelerator for IQ that can utilize the features of IQ's native client-side load for bulk loading.  Are you using it?

What are the capabilties of the IQ server (CPU and memory configuration)?

Rewriting the SAS workflow to keep some operations (like select into from one table to another) internal to IQ can also help reduce the overall time of your model.

Chris

[Former Member]

As per out project design, SAS is to be used as ETL tool for processing data in IQ.  I have alreday suggested users to use bulkload=yes.  But according to their logic and requirement, they can not use bulkload option.

This job is designed only for testing which loads data from T1 table to T2.  If I use load table command, it is completing in 10 sec.  But as per their logic it is taking much time to complete.  There are some more complecated jobs designed which access data from 4-5 tables at a time.

Will you please send some links or give details about SAS Accelerator for IQ?

Also if we restrict no of rows to 5 lacks, job is getting completed in 30-40 min.  But if we remove no of rows restriction job is taking more than 8 hrs to complete.  Is this data / generated dml statement is gets stored anywhere in temp work space? or any other reason.

We have following configuration as of now.

No of CPU - 8 (7 used for IQ)

iqmc - 5G

iqtc - 6G

iqgovern - 20

RAM Size - 16G

Also i will check by increasing networking packet size.  Is this have any negative impact on the performance?

Thanks.