Temp table size

Posted by cristinad on 18-Sep-2019 22:01

Hello!

In our system, we use temp-tables frequently. Recently we identified a business need to receive data from a 3rd party, and this data arrives in a JSON file to our system. Currently, I am parsing  that JSON into one temp-table, to be processed and stored on the database. This temp table has around 250 fields.

Question:  Is there a performance issue with a data structure this size? What is your experience?

Thank you

All Replies

Posted by onnodehaan on 18-Sep-2019 23:19

Hi,

It might depend on the Numbers of records; hoe many records do you expect?

Posted by ChUIMonster on 19-Sep-2019 00:15

I don't think there should be an "issue" per se but there might be opportunities for some improvement.

If the access to the eventual db table uses all 250 fields most of the times that you read it then this design is probably fine.  (In my opinion that is unlikely.)

OTOH if the fields tend to be used in easily defined sub-groups it might be beneficial to break it into multiple smaller records.   Smaller records will be somewhat more efficient on several fronts.  Although it is not obvious if that will actually make a noticeable difference from a performance perspective.  That would really depend on what your application actually does with the data.  (And if you are a client/server app a FIELDS list might be just as effective.)

If you do lots of FOR EACH ... NO-LOCK queries that return large result sets of this data over a client server connection then smaller records (or a FIELDS list) can pack a lot more data into a message (especially with large -Mm and the various -prefecth* parameters) and that can be significant.  Otherwise?  You probably don't care.  

Posted by goo on 19-Sep-2019 04:58

We did a xml receiver system for a company where we dynamic put the structure into temp-tables. Some of these was pretty big regarding fields. It also received a lot of records. Works swell....

Sendt fra min iPad

19. sep. 2019 kl. 00:03 skrev cristinad <bounce-cristinad@community.progress.com>:

Update from Progress Community
<4U5MHPR3D31V-jpg_2D00_70x70x2-jpg>
cristinad

Hello!

In our system, we use temp-tables frequently. Recently we identified a business need to receive data from a 3rd party, and this data arrives in a JSON file to our system. Currently, I am parsing  that JSON into one temp-table, to be processed and stored on the database. This temp table has around 250 fields.

Question:  Is there a performance issue with a data structure this size? What is your experience?

Thank you

View online

 

You received this notification because you subscribed to the forum.  To stop receiving updates from only this thread, go here.

Flag this post as spam/abuse.

This thread is closed