dsk1234
asked on
cfset using tempory table
i have a report generating from dynamic sql (500 lines) in coldfusion that is taking long time to run, in this process of fine tuning ,i have created temporary table for a view as this view is used in about 5 times in the query , after making this change and the query executed in less time.To implement this in coldfusion I need to know how to declare below query using temp table using cfset in coldfusion
SELECT * INTO #temp
FROM VW_sample MAIN
INNER JOIN table_A
Note: In database, after we reran query with temporary table We will get the following message
'There is already an object named '#temp' in the database'.
So we need to make sure in coldfusion not to repeat this error.
SELECT * INTO #temp
FROM VW_sample MAIN
INNER JOIN table_A
Note: In database, after we reran query with temporary table We will get the following message
'There is already an object named '#temp' in the database'.
So we need to make sure in coldfusion not to repeat this error.
This may be due to connection pooling. Temp tables exist for the life of the db session / connection. If you're using connection pooling, the session (and temp table) will persist beyond a single http request. You need to drop the #temp table at the end of your query.
ASKER
my question is how to write above query using cfset in coldfusion (particularly temp table (#temp))
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hmmm, I'm a bit confused by the question. agx, you may have it right, but I am thinking he is not trying to do dynamic sql, but perhaps store values into temp using codlfusion?
dsk1234, if agx hasn't resolved the issue, could you please expand more on what you're trying to accomplish. My feeling is that CFSET is not what you want.
I think you want to write a cfquery and use multiple SQL statements within it using BEGIN or END. Alternatively, create a SQL Procedure to do the entire thing...
... but i could be missing the obejective
dsk1234, if agx hasn't resolved the issue, could you please expand more on what you're trying to accomplish. My feeling is that CFSET is not what you want.
I think you want to write a cfquery and use multiple SQL statements within it using BEGIN or END. Alternatively, create a SQL Procedure to do the entire thing...
... but i could be missing the obejective
<cfquery name="myQuery" datasource="#request.datasource#">
BEGIN
SELECT * INTO #temp
FROM VW_sample MAIN
INNER JOIN table_A
do your other queries
END;
</cfquery>
Hm.. sounded like they were already running a bunch of dynamic sql statements w/cfquery's. The only difference in adding temp tables would be the need to escape the pound # sign in the table name. And of course drop the temp table at the end...
ie Use ##temp instead of #temp
But ... you could be right ;-) Though really .. this sounds like a job for a stored proc.
ie Use ##temp instead of #temp
But ... you could be right ;-) Though really .. this sounds like a job for a stored proc.
ASKER
Thanks