I’ve been in IT for over 10 years now and I can’t see any purpose to these temporary tables (or InMemory tables as AX 2012 calls them). I’ve read the MS TechNet page that explains what their alleged benefits are and it seems like it’s all blowing smoke. I was trying to modify one simple report and I completely hit a stop because the report uses a stupid temporary table as it’s source instead of just the physical table. So apparently you have to add a field to the temp table in the AOT and THEN modify the class that inserts the data into the temp table. I am dumbfounded by the reasoning here. Is MS just completely opposed to making things simple for the end user?? Why is it this gosh darn hard to change one simple report?!? The data that you’re putting into the temp table has to pull from the physical table anyway! So where then is the performance benefit that MS claims? Has anyone else thought about this??
Temporary tables is a (better) alternative to storing temporary data in memory, not to saving persistent data to database. If you read data from database and do some processing, you normally don’t want to save them back to database. You would have to write data to database every time you’re printing the report, you would have to add an additional key to distinguish different (possibly concurrent) prints, you would need a procedure to clean up data from old prints etc.
You don’t want to keep everything in memory either - it’s a limited resource.
InMemory tables stores the data in files, but you still can access them through the usual means such as select statements.
I can’t comment the particular report, because I don’t know which one it is.
When you say “If you read data from the database and do some processing…” what do you mean exactly? Why would processing be done in a report and why would data ever have to be written back to a table when just running a report??
For example, look at SalesInvoiceDP.insertIntoSalesInvoiceTmp() in AX 2012. You’ll find fields filled in by X++ methods, conditionally filled fields, values takes from the state of the class (not from database), fields from several different DB tables (so you don’t have to deal with the complexity in the report itself) and so on.
the job of report is to run and show the data and if we use the regular table then data incositency will be there… say i am using a diffrent parameters and using the regular table then some datas will be captured in the table and then it will get stored in the tables so next time when you re run the repot it will show the exisiting data+ new datas… which is wrong if a report is supposed to run single time we can understand this case but report is only used to see the data which you require and it may change during course of period , more over temp table is very fast in operation too… when you fetch hundred of records gthen it will take lot of time… for degugging we can make a temp table as regular and debug to see the data
Your english is a little hard to understand but you’re saying a report that reads straight from the regular table will produce inconsistent data? I’m sorry but how in the world can that possibly be correct? A report reads data…that’s it. And how can a temp table be faster than a regular table when the data that is inserted into a temp table comes from the physical table?? I’m sorry guys but I’m seriously lost on the purpose of these temp tables and I don’t understand why MS constantly makes things way more difficult than they have to be ensuring that you basically need a PhD to use any of their gosh darn software!
yes… every time you run the report the data inside the temp table changes… old data get flushed out and new data comes in this will happen every time you run the report… but when you use a regular table then say you run first time you get 2 set of columns and next time when you run you will get 4 column so what happens is that in regular table the old data does not get flushed out and new data comes in so end result is old data + new data i hope i am making sense…
Why would you get two sets of columns if reading direct from a physical table?
I believe he means rows, but I am not technical! However pushing cut down data from the parameter from multiple tables into one temporary table means you are looking at a subset of data to enable the report to run quickly. Rather than looking at the lines with millions of rows across multiple tables and thousands of fields the report ends up looking at one table with limited fields and less data making it quicker. That is what I believe is being said above.
the tables which u use it for temp. data storage pupose…as simple as dat.
Yeah Adam is right,
run a report by by making tmp table as regular , you will see the differences in values… evry time you run that report the columns will get added up in the table…
I’m sorry guys but I am so not seeing at all what you’re saying. What I’m hearing you say is if a report read directly from a physical table then all hell will break loose?? Come on… 99% of companies are not the size of Google, we aren’t going to have millions of rows returned for a simple report that the post above indicated. AX 2012 uses MSSQL as the backend and the indexing is very strong, the amount of data you would have to pull to where the indexes wouldn’t be able to keep up with speed is insanely big, so I don’t see why reading direct from a table would really be that much of a problem that temp tables are required? You really don’t think that’s overkill just a little bit? I understand that storing something in memory is faster than reading from a physical table if you are going to be reading that exact same data again several times. But something like a pay statement report for a weekly payroll seems ridiculous.
Think about what the end user has to go through in order to modify just one field on a report that uses temp tables?? I have to find the class that populates the table, be able to understand X++ code to find the place where it’s populating the temp table, add the field and then add X++ code to populate that field, then go back and modify your report… all instead of just modifying the report and changing the field name if you were reading direct from a physical table. Does anyone else not feel this way? I don’t mean that sarcastically, I’m really asking…
You can get data directly from the OLTP or OLAP database and many reports do that. It’s not clear to me why you think that temporary tables are mandatory.
But if you need more processing than you can easily do in a query and/or you need to call X++ etc. and you want to store temporary data somewhere, temporary tables are the right place.
Horses for courses, you adopt the best option given the requirement. Write your report from the main table if you want, that was not your question, it was the benefit of temporary tables, which I believe has been explained in set circumstances, and the fact they are not mandatory means you dont have to use them.
No I know they aren’t mandatory, what I’m saying is I’m an end user and to change a simple report I have to become an AX developer. That is what I’m saying and that is why I don’t get why MS makes this so difficult. The pay statement report I’m working with is out of the box designed to be read from a temp table so unless I wanted to redesign the entire report and find every place that references it, then yes it kind of is mandatory.
Sorry, I thought you wanted to know “what is the benefit of temporary tables”, not just to complain that a particular report is more complex than you would like. I wouldn’t have bothered answering that because it has no solution.
True… my question was what the purpose/benefit of them and it appears the answer is none unless you are doing large queries that make that much of a performance difference. In so many words that is what I’m hearing.
I wanted to know what the benefit was and complain at the same time because I think they are one in the same. Temp tables don’t seem to make any performance increase unless it’s a large amount of data and you seem to be confirming that without actually directly saying it. I used my specific instance as an example of why they don’t seem to have any benefit and only makes things more complicated. And yes I think it’s a little ridiculous that so far nobody seems to think it’s any problem that because of temp tables in order to change one field on a simple report it takes an AX engineer…including you sir.