Hello Greg, Tuesday, October 24, 2006, 2:00:23 AM, you wrote:
> I have a related but less technical question on temporary tables. > I've only recently made use of Ingres 'declare global temporary table'. > The programme involved required frequent temporary updates of data in a small > table. > My intention (hope?) is that the table will always reside in memory. Ingres caches currently used data in memory. It uses an LRU algorithm to post less recently used data to disk when it is not actively in use. If your purpose is only to improve performance of otherwise ordinary tables then you may be barking up the wrong tree. Type 1 temporary tables as implemented by Ingres are really intended to be ephemeral, for use like program variables or to pass sets of rows as arguments to database procedures. They don't work very well when used for any other purpose. There is a related issues to consider: make sure your data cache (DMF cache) is sufficiently large. Most as-delivered Ingres installations prior to r3 are excessively parsimonious with memory. I was looking at a 300-user system just last week that cached only 7Mb of currently active data in memory! > My understanding of 'how they work' is that ONLY the creating session can see > the table at all. That is correct. > Am I missing something, as I would really like to be able to share > temporary tables between a programmes. Yes, I fear you are missing something, but that should now be obvious from what I wrote above. Temporary tables are wholly unsuitable for sharing data. They are invisible outside the session where they are created for a good reason; it is not an accident or a design oversight. If they were visible outside the session that created them they would have to be subject to locking and transaction logging in order to ensure logical consistency. But they were introduced precisely to avoid the very significant overhead of locking and logging. Incidentally, this is the behaviour dictated by the SQL standard itself. Bottom line: continue to use normal tables for sharing between sessions and concentrate on techniques to improve performance when using them. > The system has other frequently updating data (1 second updates) > which must be shared between programmes. 1 update per second should be well within the capabilities of a normal table. I think we need to know more about what you need to do if this isn't working for you. (For example, the problem may be easily solved by choosing a less burdensome isolation level.) > Perhaps I don't understand the Ingres 'session' correctly? > The documentation says different 'sessions' can create temporary tables > having the same name. > As they are invisible to other 'sessions', this is not a problem. > As a 'session' is within a 'connection', a temporary table should > only be seen by the creating programme. That's sorta correct but in fact as far as Ingres is concerned, a session and a connection are the same thing. They are two names for the same thing. > So I expected that running queries from other sessions would always fail? Yes, as intended. > Anyway, can someone let me know if my understanding is correct? > I've read the Ingres documentation, and tested from isql and I can't see the > temporary table. Roy _______________________________________________ Users mailing list [email protected] http://lists.ingres.com/mailman/listinfo/users
