Re: Using temporary tables for query results

Different databases have different ways of working. So use the method that is most efficient for that database. Being "database independent" is a nice academic goal, but you are not using what you are paying for, instead you are coding for the lowest common denominator. (Like driving a Ferrari in first gear)

Use conditional code in your application to take advantage of each databases strengths and limitations. Let me guess you are probably using autocommmit. (ugh)
Jim
"situ" <sry_at_hotmail.com> wrote in message news:31c0a2d1.0212130255.5fc2f1ac_at_posting.google.com...
> Hi,
>
> We have the following requirement that needs to be supported across
> multiple databases (Oracle and SQLServer to start with)
>
> 1. We do a complex query that returns some results. This query is a
> UNION of multiple SQLs.
> a) We had initially thought of using Views, but because we needed to
> pass parameters to this View, and parametrized Views are not supported
> in all datbases, we could not use this option.
> b) We thought of using Functions, but again, this is a DB-specific
> feature, and support varies between databases (SQLServer supports use
> of UDF (User Defined Functions) in the FROM clause in SELECTs, Oracle
> does not)
> c) We have currently decided that the best way to implement this is to
> generate the SQL in our program (Java code) and execute it against the
> database.
>
> Question:
> Please let me know if you have a better solution for this above
> requirement, than to dynamically generate the SQL in Java.
>
> 2. Secondly, we are using the above complex query's results, as input
> in a second complex query. The 2nd query goes like:
>
> SELECT * from COMPLEX_QUERY_1, OTHER_TABLES_1 WHERE CONSTRAINTS_1
> UNION
> SELECT * from COMPLEX_QUERY_1, OTHER_TABLES_2 WHERE CONSTRAINTS_2
> UNION
> SELECT * from COMPLEX_QUERY_1, OTHER_TABLES_3 WHERE CONSTRAINTS_3
>
> [FYI: we tried using OR and JOINs, but LEFT OUTER JOIN etc did not
> solve this requirement, since different tables (OTHER_TABLES_1 etc)
> are required in each case; therefore, we had to settle for UNION]
>
> Effectively, we are having to execute COMPLEX_QUERY_1 multiple times.
>
> One option I'm currently thinking of is to use a temporary table which
> would cache the results of COMPLEX_QUERY_1, which can then be simply
> queried in COMPLEX_QUERY_2
>
> Question:
> Is there a more efficient approach than using Temporary tables?
> Is this feature standardized across all databases? I read that
> SQLServer deletes temp tables at the end of the transaction, while
> Oracle does not. What are the performance-related implications of
> using Temporary tables?
>
> Thanks in advance!
> situ
Received on Fri Dec 13 2002 - 17:25:04 CET