slow insert into IQ 15.4 from ASE 15.5

[Former Member]

In IQ we have a table with 3 columns (searched_column char(7),id int, rr varchar(5)), let call the tabel IQ_tbl.

In our ASE database we have a proxy table to the IQ table with the same definition called prx_IQ_tbl.

The following insert statment takes 1 minute to acheive:

set rowcount 5000

insert into prx_IQ_tbl select adp_num, 111, 'dev' from adp_info

Indeed after 1 minute 5000 records are inserted into the IQ table

Is there a way to increase the speed?

CIS info of our ase server is as follows:

    Parameter Name                 Default Memory  Used Config Value Run Value  Unit     Type   

----------------------------------------     ------------ ------------ -------------------------- --------------   ----------- -------------

1 cis bulk insert array size          50           0          500                           500    rows dynamic   

2 cis bulk insert batch size           0           0            0                                0 rows dynamic   

3 cis connect timeout                   0           0            0                               0 seconds dynamic   

4 cis cursor rows                         50          0           50                             50 number dynamic   

5 cis idle connection timeout          0          0            0                                0 seconds dynamic   

6 cis packet size                       512          0         2048                         2048 bytes dynamic   

7 cis rpc handling                         0           0            0                                0 switch dynamic   

8 enable cis                                 1           0            1                                1 switch static   

9 max cis remote connections       0      5346            0                                0 number dynamic  

[markmumy · Accepted solution]

This method (ASE to CIS to IQ) forces single row updates in IQ which is the slowest way possible to move data into IQ.

The best method, knowing that data is in ASE and must be moved to IQ, is to pull the data rather than push the data as you are currently doing.  To do this, you would log into IQ and use the INSERT command but specify the LOCATION key words like this:

insert into IQ_tbl

location 'ASE_SERVER.ASE_database'

{ select * from ADP_INFO }

The ASE_SERVER would be the interfaces file on the IQ server that defines the IP/port for ASE and ASE_database would be the database that your adp_info table is in.

By default, the IQ login must also be a valid ASE login with both systems having the same password.  There are other methods for this, too, but this is the simplest.

Your push method takes 1 minute.  This method should take a few seconds.

Mark

[Former Member · Accepted solution]

Thanks a lot Mark.

That waht I thought too,i.e ASE insert row by row teh data into proxy and there teh IQ table.

Indeed, we do lots of insert location from many different ASE to IQ, we can not use teh insert location in this specific case, as the data set to be sent to IQ is created in a ASE stored procedure, the id column is infact teh spid of the user at the time when he/she is inserting data into the IQ table. The stored procedure them uses the inserted data to do some treatment on IQ and get results back from IQ.

I wonder if I can use the insert location in the stored procedure which runs on ASE.

[saroj_bagai]

on ASE,  you can increase cis_packet_size  and cis_bulk_insert_batch_size, cis_bulk_insert_array_size and cis_cursor_rows and on IQ start server with -p 16000 and use insert location ... command to insert data from ASE into IQ

[saroj_bagai]

make sure  your IQ server is  15.4 esd3.9 or above or 15.4  esd4.

  CR      Description
   -----  ------  ------------------------------------------------------
745900  Slow perfromance for certain operations after upgrading to
                  15.4.

  CR      Description
   ------  ------  ------------------------------------------------------
   720539  INSERT LOCATION performace drops 10 times slower from 15.2
                   to 15.4 esd 2

[Former Member]

Hi sarog,

Our IQ version is eas 1, woudl you think by applaying the patch we shall get a better performance?

@@version

'Sybase IQ/15.4.0.3014/120310/P/ESD 1/Sun_x64/OS 5.10/64bit/2012-03-10 11:23:59'

I rather go in teh sense of what Mark writes, teh problem must come from row by row insert into IQ table.

[saroj_bagai]

Yes, you do need to use insert location and also upgrade to 154 esd3.9 or above or 154 ESD4.

[Former Member]

Thank you.

Yes, but how can I call the insert location from inside of an ASE stored procedure?

that is how I try to insert data from ASE to IQ.