I have been trying to work out for ages what the sas_wdetemptable in the spm schema in the Mysql database is there for.
The reason I wanted to know is that over time there seems to be records in there and the table seems to just keep growing, especially in our development environment when we were doing lots of test SPM loads and calcs. When you are adding 10,000 odd rows to SPM every week this table had me almost as concerned as the sas_elementcolumn table.
A track to SAS Tech Support on this came back with nothing should be in there, truncate it.
We are just finishing moving all our SPM loads from our tried and true ‘out-of-the-box’ SAS DI Studio jobs (which no longer works with the latest SAS 9.1.3 Foundation patch), to the new ‘build it yourself’ BMF loads (please don’t ask!)
So I was watching the Mysql database today to see if I could diagnose what was causing a performance issue with the calcs, and finally I think I undertsand one of the uses for the sas_wdetemptable.
So this is what I think happens…..
When you submit the calc, SPM:
1) Reads any rows in the sas_elementcolumn table for the project/period you are calcing for (so in our case 120,000 reads).
2) Writes these records to the sas_wdetemptable (120,000 writes)
3) Deletes the records from the sas_wdetemptable (120,000 deletes)
4) Somehow merges the records in the sas_wdetemptable with the new calc’d value (no idea on this one as I couldn’t see it ) and then inserts them back into the sas_elementcolumn table (120,000 inserts)
5) Deletes these records from the sas_wdetemptable (120,000 writes)
So now I know why the calc takes 40 minutes and the Mysql database seems to get fragmented.
And the reason we had some left, I think it is because every now and again the Web Server or Mysql instance would get stopped mid calc in Dev, and then the records in sas_wdetemptables won’t get deleted (its got me think about whats in, or more importantly not in sas_elementcolumns though when this happens)
Weird thing is the Mysql database is not using a lot of memory or cpu for this work, so need to read up on tuning this beast me thinks.
Hi there
I (for reasons I can’t recall) thought the “wde” stood for “web data entry”, and that this was the table that stores any metrics that have been captured through web data entry forms, if the project-level option to eyeball before “publishing” wde-captured data is checked.
Since we never used the (rather clunky) web data entry forms, I never really got a chance to check this assumption. It hadn’t occurred to me that it might be involved in calculating a project. I’m inspired to check our sas_wdetemptable contents next time I’m looking at the back end, and do some of my own sleuthing on this.
BMF loads shouldn’t touch this table (but nor should DI load jobs have done). How are/were most of your 10K records being added each week?
I’m keen for you to keep this post updated as you learn more!
Cheers
sasfrog
I agree I always thought this table was only for Web Data Entry as well, which we don’t use, and that is also what SAS Tech Support said.
I am still investigating this to see if we are doing something weird, because as you would expect we want the calc’s to be as fast as possible and the Mysql database to be as un-fragmented as possible given it current size. Having said that I am always amazed at how fast the SPM Portlets are returning values given the size of our projects.
I don’t know if the calc’s after the DI Studio loads did this either, because I never bothered to look at what was happening.
Let me know if you find anything and ill post any updates I find as well.
Cheers
Shane
OK, just a quick update:
I looked at our wdetemptable and it had 1790 observations. [Mysteriously, many of the values for "last modified date" were prior to the date we first installed (indeed, even purchased) SPM.]
I then ran a Calculate for a period that encompasses all the data we have in the main project on site… now there are (wait for it) 1790 observations in there. The same ones, I’d reckon.
I haven’t had time to explore & profile the records in there (and probably won’t until the alleged ‘quiet time’ around Christmas) – possibly, they’re not from the main project for which I did the Calculate.
Maybe we could go offline from this blog to to- and fro- on this one, and post any major insights.
Cheers Shane
sasfrog
Did some more testing and this table isnt used when submitting the calc from within the SPM administrator web page.
So im guessing it only gets used when you use the projcalc function via code. Will test that when I get a chance.
Also the records get inserted into the table and then deleted once the calc is completed so you wlil need to watch it via mysql admin during the calc.