[Resolved] Table: can't be permanent nor temporary help!

ppowell

I have to create a temporary table to store the results of a very large and complicated query in order to do a fulltext search query upon.

Problem is that I need to keep the temporary table, somehow, past the disconnect. I must, of course, disconnect from the db since application is done evaluating, however, upon doing so the temp table is gone.

The other problem: Although I will need to keep the temp table, it can't be permanent either because I will ONLY need this table upon doing the same search query over and over again. The client requests the ability to sort the results of a search after the search has been done.

For example: The client will search for instances of "New York". The results will come back with the queried search results of all rows responding to "New York". However, the client now wants to take the EXISTING results and resort them by descending date, or ascending associated event names, or descending alpha order or... you get the idea.. this to generate reports based upon the results sent back.

Based on this, I can't use either a permanent table nor a temporary table. So I need a "semi-permanent" table.

Help!

Thanx
Phil

mourisj

Your user has to log in.
You have to define a session-ID for each user logged in.
Store the results of the user with his login.
At logout, clean up the table.

You have to take care that if the user does not logout correctly, he will have an alert at next login! You might also count invalid logouts and lock the user after x invalid logouts.

ppowell

I don't think we're understanding each other, I don't understand what you mean. Why would I store the results in a session? I need to store a database table in a session, how?

Phil

Sxooter

Can you use sessions? If so use the session ID concatted into the end of the standard table name to make a unique one, then drop it when you're done. Be sure to check in case it's already there when you start the script.

ppowell

So what you're saying is that I create a "permanent" table instead of a temp table? That would mean creating the table and inserting it with the data I got from the first query, and then I'd have to, at some time, delete the table (else the resources would be completely used up by "temp" tables floating around), how would I do that considering that the process would have to take place within the users' using the search engine within the app?

Sorry I'm not picturing this one.

However, I thought of this: a means of storing the entire temporary table, data, structure and all, as a flat file. Is that possible?

Phil

Sxooter

Using files raises the same problem as using non-temp tables, you're gonna have to write some kind of "butler" function to clean things up every so often.

ppowell

if (!$_REQUEST['isFromSearch']) @unlink("/path/to/flat_file");

Just stick that somewhere in my main script to always attempt to delete this file if I'm not in the search engine portion, otherwise, don't touch it.

Phil

Sxooter

But if you only have one flat file, and more than one user running a seach at the same time...

ppowell

It's for a single-signon single-user internal web application. There won't be any other users, only 1 user logged in can use this.

Phil

Sxooter

Oh, then just create an empty table for it, and delete from table at the beginning each time.

ppowell

Ultimately, there is no gained performance from retaining the temp table, and that's what I am going to explain to them, since I found out just recently of a requirements change (I love midstream late-night requirements changes!): They want this application to be multi-user-friendly now, which means you'll have bunches of very large flat files or db tables floating around that will only be used for sorting an existing keyword search (and serve no other purpose).

I'm proposing they do away with retaining anything and always generating a fresh temp table each time, sure it'll be a bit slower in another aspect, but it doesn't hog up so much memory and allocated resources unnecessarily either.

Phil

Sxooter

Yes, you're making a fatal mistake of letting them define your methods, not your goals.

Don't let the non-programmer folks tell you methods (use SQL, don't use SQL, use flat files, don't use flat files) let them tell you what they want, i.e. 1 person, 10 people, 100 people, response times, data to be remembered across pages, etc... HOW it gets done should not be in their domain.

Make it clear to them that if they put you in shackles, so to speak, and then things need to be refactored, it won't be your fault, but theirs, for artificially limiting your choices through their inadequate knowledge of the underlying technology.

ppowell

You don't work for the federal government, right?

🙁

Phil

Roger_Ramjet

Let's see if I've got this right?

complex query 1 --> RS1
RS1 --> full text search --> RS2
RS2 --> sorting --> print

OK. Sorting you can do on the client side with JAVA, as you can the print layout. This would eliminate server side retention of the search results RS2. Depends on your JAVA, but I bet there is something you can customise on http://javascript.internet.com/

From what I gather, you can not open a permanent connection to the db. If you could keep it alive, you would keep your temp table too.

Otherwise, you will have to save the results in a table, or rerun the text search each time. As I'm sure you realise, if RS2 is stored as a table you can keep re-using a very simple query to vary the sort order. But that means a good clean-up routine. How to detect when the user has finished playing with the results?? This is the real problem. Once you know this you know when to delete the table.

Of the 3 I prefer the first. Everyone seems to forget the vast amount of resources lying unused on the client-side. Save your server and let the client do the sorting and printing layout.

NB. If your result set is in virtual memory it is being paged on and off a disk anyway. Many operating systems use lazy-write to speed up file access, so your 'file' may already be in RAM. Some RDBMS can retain query result sets in transient tables in case the query is run again. Juging performance really is guesswork without detailed knowledge of the platform, and still probably requires benchmarking to decide.

mourisj

Sorry,

I was out for some time, so I could not add my 2 cents to this important matter, where there has been a misunderstanding in the beginning:

What I meant was not storing the data in a seesion, but storing the query result in a (permanent) table, along with the session-id (in each row). After log-off, delete all the rows of the concerned session.

This way, you can work multi-user, multi-session and yet work with one single (permanent) table containing temporary data.

Have a nice day,

JJ Mouris

Roger_Ramjet

Nice solution JJ.