Re: How to bypass referential integrity

I should be more specific. The developers had a ColdFusion page (script?, code? I don't know the official name for this) which included SQL selects. They were building a matrix for a web page using data from one of my Oracle databases. They were doing a select for one cell, then a select for the next cell, etc.!! Not surprisingly, things ran so slowly that it took an end user two minutes to see any results. I wrote a stored procedure which found all the cells and popped them, along with row and column headings, into a global temporary table. Then all ColdFusion had to do was call the procedure and present the temporary table on the web page. Users got their results in a second or two.
Where the problems began was when multiple users hit the ColdFusion page simultaneously. Since Oracle sees all the users as not only a single user, but as a single user in the same session, the global temporary tables have everyone's matrices mixed together and are useless. I was looking for a workaround so that Oracle would see each different user doing work as a separate session. As you can tell I'm not a ColdFusion expert, but then, apparently, neither are the developers.

Van

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:991989084.8277.1.nnrp-14.9e984b29_at_news.demon.co.uk...
>
> I'm going to plead a lot of ignorance and wave
> my hands around a lot here, but:
>
> Cold Fusion is one of those web server engines
> that talks to Oracle, so the dialogue path is:
>
>
> User -> Cold Fusion -> Oracle -> Cold Fusion -> User
>
> Since the whole web thing is stateless, doesn't
> Cold Fusion assume it will never hear from the
> client again after the end of one dialogue, and
> as such shouldn't the connection from Cold Fusion
> to Oracle be cleared down (but obviously not
> broken because of the overhead of connecting).
>
> Some of the web-servers do this with a
> commit/rollback and a call to the
> 'reset all package states' procedure (whose
> name I can't remember).
>
>
> If Cold Fusion does something similar, then
> you can define and Global Temporary Tables
> as 'on commit release rows'. (In fact, the
> call to reset packages would not be needed
> for this specific purpose). On the other hand,
> if the data has to be available for another
> "oh what a nice surprise you came back"
> visit from the same client, then the data
> should be stored in a permanent table with
> a cookie-based marker, as the client could
> come in on a completely different session
> anyway (assuming Cold Fusion opens a
> connection pool, rather than just one
> connection).
>
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
> Publishers: Addison-Wesley
>
> Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
>
>
> Van Messner wrote in message

 <_YyT6.46424$gA.2013008_at_monger.newsread.com>...
> >Thanks you for the good advice. I did post it to Metalink, without much
> >feedback so far, although I believe anyone could replicate the problem.
> >There is a similar known bug with reverse indexes, nut no mention of the
 one
> >I found, or I should say ran into.
> >
> >Also, if I might presume to ask you a question. Session-based global
> >temporary tables can do some very nice things in some of our apps.
> >Unfortunately, everyone who comes in through ColdFusion is hitting the
> >database as a single session, which makes the temp tables a lot less
 useful.
> >ColdFusion doesn't;t have any way around this. Any ideas?
> >
> >Van
> >
> >
 

> >
>
>
Received on Fri Jun 08 2001 - 12:31:16 CDT