Solved

cfset using tempory table

Posted on 2011-03-08
6
451 Views
Last Modified: 2012-05-11
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.

0
Comment
Question by:dsk1234
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 35070713
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.

0
 

Author Comment

by:dsk1234
ID: 35070806
my question is how to  write above query using cfset  in coldfusion (particularly temp table (#temp))
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 250 total points
ID: 35070881
Actually it looks like there were 2 questions:

1. I need to know how to declare  below query using temp table using  cfset in coldfusion

It's just like setting any other string. The one difference is you need to escape the # sign so CF doesn't think it's a variable. Use two ## signs instead

<cfset x = "SELECT * INTO ##temp FROM VW_sample MAIN INNER JOIN table_A ">

Btw: The disadvantage to using dynamic sql is you can't use cfqueryparam to help protect against sql injection. So be sure to scrub your input.

2. 'There is already an object named '#temp' in the database'.
So we need to make sure in coldfusion not to repeat this error.


Like I said, you have to drop the temp table at the end of the query. So it doesn't live on past the current request.
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
LVL 39

Expert Comment

by:gdemaria
ID: 35072370
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


<cfquery name="myQuery" datasource="#request.datasource#">
 BEGIN
  SELECT * INTO #temp 
  FROM VW_sample MAIN
    INNER JOIN table_A
    
  do your other queries  
 END;
</cfquery>

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 35072549
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.
0
 

Author Closing Comment

by:dsk1234
ID: 35097184
Thanks
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Coldfusion CFMESSAGEBOX Passing Variables 6 137
<cffile cannot delete a file 4 65
Detect and combat possible robot 7 119
code conversion assistance required 1 34
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question