Store the result of a subquery for re-use

Question

Using ASP Classic and MS Access (don't ask), how can I temporarily store the result of a subquery for re-use? For example, I want to run multiple queries from my ASP script like:

SELECT stuff FROM (subquery) WHERE ... conditions

SELECT otherstuff FROM (subquery INNER JOIN othertable) WHERE ... different conditions

And display the results of each. The subquery is the same in each case, and is quite expensive to run so I would like to run it just once. What is the best way to do this?

score 3 · Answer 1 · answered Jan 09 '12 at 12:46

Perhaps an alternative approach would suit? You can apply filters to disconnected recordsets, so perhaps your subquery could be used as a disconnected recordset with more detailed filters applied to that set. Some notes:

adOpenStatic = 3
adLockBatchOptimistic = 4
adUseClient = 3

Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseClient

ssql = "SELECT * From Table1 WHERE SomeField = 'Text'"
rs.Open ssql, ocon, adOpenStatic, adLockBatchOptimistic

''Disconnect recordset
Set rs.ActiveConnection = Nothing

''Filter : http://www.w3schools.com/ado/prop_rs_filter.asp
rs.Filter = "Code LIKE 'a%'"

Do While Not rs.EOF
    Response.Write rs.Fields("Code") & "<br>"
    rs.MoveNext
Loop

rs.Filter = "Code LIKE 'c%'"

Do While Not rs.EOF
    Response.Write rs.Fields("Code") & "<br>"
    rs.MoveNext
Loop

I need to `JOIN` the subquery with additional tables in each query - can I do this somehow? — Flash, Jan 09 '12 at 23:56
Not as it stands, but there may be different ways of looking at that, too. It depends to a certain extent on how much data you are looking at and whether the whole thing has a future with much more data. There is the possibility of extracting an already joined set, or of updating a new field appended to the disconnected set, and so on. It is not possible to give an alternative without more exact information. — Fionnuala, Jan 10 '12 at 00:02

score 1 · Answer 2 · answered Jan 09 '12 at 05:43

1

I'd say the best bet would be to insert the results of your subquery into a temporary table, then select against that table until you are done with those results.

INSERT INTO tmp_subQuery (col1, col2, ....) SELECT col1, col2, ... FROM... 

SELECT stuff from tmp_subQuery WHERE ... conditions
SELECT otherstuff from tmp_subQuery WHERE ... different conditions

answered Jan 09 '12 at 05:43

Jake Feasel

16,785
5
53
66

Thanks, do I need to explicitly drop the temporary table when I'm finished with it? – Flash Jan 09 '12 at 06:02
I would either do that, or empty it out (delete from tmp_subQuery) so you can re-use it again later. Obviously, this is not something that I would recommend for a concurrent environment, but the same could be said for Access itself. – Jake Feasel Jan 09 '12 at 06:04
Access does not support temporary tables. Perhaps you meant a permanent / base / scratch table? – onedaywhen Jan 09 '12 at 08:52
...and if so, what effect will re-using it repeatedly have on the database file between compact and repair exercises? – onedaywhen Jan 09 '12 at 09:01

onedaywhen · Answer 3 · 2012-01-10T07:07:56.957

When you mean "store" do you mean physically or logically?

For physical storage, you could create a scratch table, clear it, insert the subquery results, then clear it afterwards; this will cause the database file to increase and require regular compacting. Another variation is to create a new database, create a new table, etc then drop the database when done, no file bloat problems.

However, you could logically 'save' the sunquery SQL as a VIEW to be queries multiple times:

CREATE VIEW MyView AS <subquery here>;

SELECT stuff FROM MyView WHERE <conditions here>;

SELECT otherstuff FROM MyView WHERE <different conditions here>;

UPDATE:

Using CREATE VIEW requires the engine to be in ANSI-92 Query Mode e.g. use an ADO Connection object to execute the SQL. The result of a CREATE VIEW statement will be a Query object in the Access UI, so if you prefer not to change the ANSI Query Mode of your UI then just create a Query object in the usual way ;)

This looks like what I am after but I can't get `CREATE VIEW` to work in Access or through my ASP script. — Flash, Jan 09 '12 at 23:57

score 0 · Answer 4 · answered Jan 09 '12 at 05:44

0

Create a DataTable to hold the results of the subquery and use Linq to create and process the result sets for each query.

answered Jan 09 '12 at 05:44

ron tornambe

10,452
7
33
60

1

OP says ASP classic. Linq is probably not an option. – Jake Feasel Jan 09 '12 at 05:46

Store the result of a subquery for re-use

4 Answers4