Hi
all,
I have a query which takes 4 seconds to execute. The query looks
like:
SELECT DISTINCT tfc_fct_value
FROM
PD_TMP_AGG_VALS_5071_544,PD_OUTPUT_ITEMS,tp_fact_ctl
WHERE OUI_OUT_ID=5071
AND OUI_FACT_ID IS NOT NULL
AND
oui_item_type=tfc_item_type
AND vd17=oui_fact_id
ORDER BY tfc_fct_value
Another query
follows this :
SELECT DISTINCT tfc_fct_value
FROM PD_TMP_AGG_VALS_5071_544,PD_OUTPUT_ITEMS,tp_fact_ctl
WHERE OUI_OUT_ID=5071
AND OUI_FACT_ID IS NOT
NULL
AND
oui_item_type=tfc_item_type
AND vd18=oui_fact_id
ORDER BY tfc_fct_value
Each of these
queries take approximately 4 seconds to execute.
As you can see above, the two
queries differ in the vd columns. I need to execute the same queries atleast
twice for various conditions.
The
queries return 4 rows.
Initially I was
creating a temporary table to hold the result set and then query the
table.
Later it was
decided that there could be few more queries with differing vd, taking values
like VD19, VD20 etc..
Now, my question is
will it be alright if i create say 5 temporary tables for each of the distinct
VD values or execute the query without creating tables.
Suppose i have 5 vd
values viz VD17, VD18, VD19, VD20 & VD21... If i am not creating temporary
tables, and i am running the same queries twice ( this is for some other
condition ), it would take around 2* 4* 5 = 40
seconds...
Whereas if i create
tables and then query through them, it would take around 22 seconds.. This is
because once the table is created for the first time,,
then querying it is
faster..
Now can anyone
please tell me if creating temporary tables is fine or is there any method
to cache the query or some other way...
Please put forward
your comments and suggestions. Sorry for the big mail.
Thanks in
advance
keshav
Regards
Kesh
" I've always felt it was not up to anyone else to make me
give my best "
This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful.
Visit us at http://www.cognizant.com
