Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (1)

Resolved Best approach

  1. Issue
0
Votes
Undo
  1. John Vanleeuwe
    John Vanleeuwe
  2. PowerBuilder
  3. Sunday, 20 August 2017 12:45 PM UTC

Hi all,

We have a master table with detail records. Each detail record has another level of detailed records.

Our current print function is a retrieve of a composite datawindow. Printing is done for each master table record , but also reprinting afterwards is done a lot.

With millions of records , our retrieve function is getting too slow ( due to inherited bad key settings on the table).

 

I have the idea now of performing a describe of the datawindow after the first time it's printed and saving the syntax in the database on master table level. When a reprint is needed i can do a create of the saved syntax.

 

I am a bit hesitant of saving the syntax in the master table with millions of records.

Should i maybe better create a new table ? Or ... any other idea's ?

 

TIA

John

  1. DataWindow
  2. Retrieve
Which platform 32 bit or 64 bit should be chosen w...
Datawindow export format reverting back in PB2017
Responses (1)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 21 August 2017 13:15 PM UTC
  2. PowerBuilder
  3. # 1

Hi John;

  Your report sounds like its working fine. The question back at you be from my perspective ... what are you always reporting on millions of primary keys in the Master table? Say for example the Master tables were customers ... why would I report on all of them all the time vs "customers that purchased something in the last month or year", "customers who purchased in the last six months over a certain $ amount", "customers who are active and live in a certain geographic area", etc.

  To me, the key would be to get a list of Master table primary keys that match a certain criteria you want to report on - then feed those keys into the comprehensive report you have. That should (I think) reduce your reporting to 1,000's or 10,000's of master table entries vs millions. For very active reporting requirements - maybe creating more Master indexes might also be prudent or adding triggers to the Master table to trap various update scenarios and place the primary key in a reporting table for later use.

  So maybe just rethinking how you are approaching the reporting needs vs blaming the performance of the existing report could be the long term solution. Food for thought.

Regards ... Chris

Comment
John Vanleeuwe
  1. John Vanleeuwe
  2. Sunday, 27 August 2017 06:37 AM UTC
Hi Chris,



 



As usual , thanks for your reply/comment.



The fact is that the master table holds a million records , and due to bad indexing which we inherited , the retrieve of this composite datawindow is just taking too much time.  We don't need to print millions of records, user just can select one master record and print it (with all the detail records) , but the actual retrieve of this composite dw is too slow.



So users print once the record has a certain status. But later on , this can be weeks/months/years later they need to do a reprint ( i know ). To speed up this reprint functionality i was thinking of doing a dw_describe of the composite dw, saving the syntax on the master table level when they first print it. So in case when they need to do a reprint we only need to retrieve this syntax from the master table (which has good indexing) instead of re-retrieving the composite datawindow, having 20 retrieves in different detail tables again.



 



It was just an idea i was having , not yet to sure if i will create the blob column on my master table ( ever tried adding a blob column to a database that has million records, resulting also in an empty blob column for the already created/printed records)  or i will have a separate table with the describe syntax saved into ... ?



 



TIA



John



 



 

  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.