Can we drop the index on the pzInskey on the application specific feeds processing logging table?
Hi All,
Our application built on Pega 6.3 Sp1 and using oracle database 11g.
We are using one table for logging purpose which is to log the processing inbound feeds. We are not using this table for loading the work object or making updates in pega. This is just to insert the received feeds for logging purpose.
This table will have lot of records and everyday there will be 7K+ records will be inserted. We are experiencing some slowness in this feeds processing and noticed that pzInsKey column is an indexed column and this is not used for any query purpose. We are suspecting whether because of this indexed column causing the slowness in the insert operation and want to make sure whether pzInskey should be indexed column in all the Pega tables?
Please confirm whether this pzInsKey column index can be removed?
Pegasystems Inc.
IN
Hi,
pzInskey also known as the instance handle — appears almost in every table and identifies the unique key of rows of that table. So i think you cant achieve what you are looking for !
Alternatively , is it possible to create an external database table and then map your data there ? Just a thought !
Pegasystems Inc.
IN
Hi Karthikeyan, Good morning!
- are we referring to Database index created on pzInskey - generally (or most of the times) a primary key column of the table considered for pega internal operations?
- DROP INDEX index_name??
- doing so would affect the query performance while retrieving
- considering the scope of use case to be an insert statements only...
- it may help in gaining performance by avoiding ...
- more indexes, slower rate of INSERT statement...
- it may help in gaining performance by avoiding ...
- considering the scope of use case to be an insert statements only...
- doing so would affect the query performance while retrieving
- DROP INDEX index_name??
>>> We are using one table for logging purpose which is to log the processing inbound feeds
-
Is the custom log table cloned with PPRC pr_Data table?
- with pzInskey as primary key?
Please share your thoughts/comments, Thank you!
psahukaru
US Bank
US
This is not cloned with PRPC pr_data table.
This class and table was created with PRPC class mapping tool and it created the table with pzInsKey primary key by default.
But this column pzInsKey not used in any query retrieval used for any retrieval .
Note : The issue happening only in Production, and it is difficult and try the option to create the table as an external table and map it.
Pegasystems Inc.
IN
>>> This class and table was created with PRPC class mapping tool ...
- which tool is referenced here..
- Designer Studio --> Data Model --> Classes & Properties --> Database Class Mappings
- Designer Studio --> Integration --> Connectors --> Connector and Metadata wizard
-
in either case -- if not wrong -- the PRPC specific column 'pzInskey' won't be applied unless
- we either clone the custom table with pr_Data
- or manually create the column...
could we double check the creation, please!
again - more the indexes, slower rate of INSERT statement could be noticed. So, can we take a backup & do a quick test on pre-prod by dropping the index?