I honestly do not see how this process can benefit from dynamically created static tables.
1. The CSV fields are not known at design time, they are not known at the start of the process. At the end of the process they must be static tables. So there is no other way than determine which fields there are and construct the table from this.
So this is not only a benefit, but a requirement.
2. Creating and 'removing' files in a process makes the process independ of other processes and creation routines. So the code becomes selfcontained.
3. Static tables (as instead of temporary tables) resulted in a far better performance.
1. What is the purpose of those static tables at the end of the process?
What can read data from tables having an unpredictable number of columns of unpredictable data types?
It's a dead end.
At the end of the process the data should go into static data interfaces with a well known set of columns and data types. Otherwise it's a dead end.
In my system I used 4 column EAV type of table, where FileID was in Column1, Row Numbers were in "Field names" were in column3, and "Field Values" in Column4.
At the end I "pivoted" it (using cross-tab queries) into whatever set of columns the interface was mapped to.
2. Did you mean "fields", not "files"?
Parsing delimiter strings into a "vertical" static table makes the process not any less self-contained.
It even allows you to have different numbers of columns for different rows.
When my process saw this happening it knew there is a mistake which needs to be rectified or, if known methods did not work, reported upstairs (means - to me).
3. I never saw a single case of dynamically created static tables performing better than properly defined temp tables.
Is it that you do not bother creating constraints and indexes on temp tables?
I don't see why the clustered index has to be removed.
Can you name a single reason for doing that?
I did a fair number of tests. Did not test all combinations and did not test the tested combinations extensively. Most time was consumed filling the table, without the clustered index this worked reasonable so I stuck with that. Haven't retested the endsolution with a clustered index during the fill.
So the reason for removing the clustered index was that in a specific solution filling was faster without the clustered index.
Thanks for you reply,
Ben Brugman
From my experience (quite extended one) I can tell that if you apply a correct ORDER BY clause to the inserted recordset (matching the definition of the clustered index) there would not be any noticeable overhead caused by the existence of clustered index.
This part should not be too hard to verify. 🙂
_____________
Code for TallyGenerator