| Table and View performance [message #60223] |
Mon, 26 January 2004 05:51  |
Mike Muldoon
Messages: 24
Registered: March 2003
Location: Edinburgh
|
Junior Member |
|
|
Hi
I have a scenario where I have a process (P1) running on a schema. P1 is retreiving data from a set of tables and storing records on a new table (T1). After x number of minutes or y number of records have been stored I require another process (P2) to be called in order to write the stored records to a file, meanwhile the initial P1 must continue to do it's job of writing the 'next set' of records.
Obviously there are a number of ways I can do this. My first thought would be to have a table (T2) which just stores a toggle value (A or B). The table being written to would have an additional column on it which stores the toggle value written by P1. At x or y P1 changes the toggle value on T2 and calls P2 before continuing to write records.
P2 uses one of two views on T1, depending on which value for the toggle P1 passes it (View A or View B). The only difference between the two views being the select on the 'toggle column'.
P2 will use this view to read the records from the table in order to create the extract file. Once the file has been created successfully it will remove the records from the used view.
Before I get too far into design I would like to find out the answers to the following questions:
Will having P1 adding records to the table and P2 reading and removing records from the table (via a view) badly impact peformance?
Would it be more performant to have P1 switch between writing to two separate tables rather than just switch a flag - that way P1 and P2 would never be operating on the same table?
For some reason I have a natural urge to avoid removing records from a view, is this because I have read somewhere that it is not the correct thing to be doing?
Thanks in advance for a bit of guidance.
Mike
|
|
|
|
| Re: Table and View performance [message #60225 is a reply to message #60223] |
Mon, 26 January 2004 09:13  |
croca
Messages: 50
Registered: January 2004
|
Member |
|
|
I think it would be better doing this from within ONE sotord procedure:
when inserting new records into table T1, just
write the same record to an OS file!! instead
of storing a set of records into table
and writing them back to a file
Regards.....
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
