Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Query Much Slower in CF9 Compared to Oracle

Posted on 2012-09-17
18
Medium Priority
?
789 Views
Last Modified: 2012-09-21
Running a query in Toad is much, much faster than running the same query thorugh the cfquery tag in CF9.  The query in Toad runs in 1-2 seconds, compared to 60+ seconds when CF processes it.  Anyone have any ideas, or is this just normal behavior?
0
Comment
Question by:theideabulb
  • 6
  • 5
  • 4
  • +2
18 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 38406871
Toad buffers the output so it appears that the data is coming back faster that it actually is.

Are you sure Toad brings back the entire resultset in 1-2 seconds or does it just start displaying it in 1-2 seconds?
0
 

Author Comment

by:theideabulb
ID: 38406944
I am seeing the same result using AquaData and SqlPlus.   Sometimes its just 3 records in total its bringing back which these apps get it in .00xxx seconds - 2 sec, while CF is take 25-40 seconds.
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 500 total points
ID: 38406946
Also, is one using a bind variable and the other using actual values?  That can produce a drastically different execution plan.

However, I suspect what slightwv suggested is more of the issue.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38406966
Looks like the issue might be a CF one.  Unfortunately I'm not a CF person so will have to defer to other Experts.

We have one excellent CF Expert.  Hopefully he will be alone soon.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 38407018
by any chance are you fetching clob or blob data?

in addition to the array fetch sizes slightwv mentioned above,  Toad has the option to only fetch lob pointers rather than all of the data being pointed to.

With only 3 rows I doubt the array fetch size is the problem, because both systems should default to more than that, and even if they didn't,  The over head of making 2 extra calls for the 3 rows should be minimal.  Not a factor of 20.

If, on the other hand you are fetching large LOB data and Toad is only getting a pointer to it then it will seem artificially fast, until you try to view the contents of that LOB then it'll have to pull the data "for real" at that point.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38407031
sql plus has a similar option LONG that will fetch only the first few bytes of a LONG,CLOB, or BLOB column.

I don't know about AquaData or what options ColdFusion might have though.
0
 

Author Comment

by:theideabulb
ID: 38407072
All the data is Varchar and numeric. Basically first name, last name, etc. Nothing large.  We are using the built in Oracle connectors to connect to the database (10g), i was wondering if anyone that uses CF and maybe used the JDBC connector method.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38407111
you could try checking the jdbc   setFetchSize.  With only 3 records in the result set it still seems unlikely that would be it but there's no harm in looking.

Is it set to something 3 or larger?
i.e. will it fetch everything in one trip to the database? or does it need to fetch and then fetch again and maybe fetch again?

I'm afraid I'm with the others that I don't know much about CF to tell you where to look or how to change the FetchSize, but I do know it's an attribute of your jdbc communication.
0
 
LVL 53

Assisted Solution

by:_agx_
_agx_ earned 500 total points
ID: 38407420
I know CF, but unfortunately not much about Oracle.

It's hard to say without knowing more about your queries and config.  A few thoughts

1) Are ALL queries slower, or just that one?
2) Are they simple queries or complex JOINs?
3) Do the queries you've tested use cfqueryparam? If yes, how do the cfquery execution times compare *without* the cfqueryparam ?
4) Has this always been a problem or did you recently switch drivers or upgrade? If so, from what - to what?

If it's just 1 query, it might be a indexing problem. If it's ALL queries ALL the time, it might be a connection or driver problem.  Setting up a DSN is pretty simple,  so you might create a "test" dsn with an alternate driver to see how it compares.

If all else fails, I'd try tracing the statements to see how they're processed in the db. On the CF side, DSN's have a logging feature.  On the db side, I don't know what tools oracle has, but I'm sure there's something similar to MS SQL's profiler.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38409464
>>> I know CF, but unfortunately not much about Oracle.

No problem,  slightwv and I can cover the Oracle end

>>> If it's just 1 query, it might be a indexing problem

The same query executes quickly when run from other tools, so it's not indexing.  Unless there is something called "indexing" within the context of CF that is distinct from indexes in the database.

Simple/complex shouldn't matter either since the query is already established as being fast from other tools.
0
 
LVL 53

Expert Comment

by:_agx_
ID: 38409857
> The same query executes quickly when run from other tools, so it's not indexing.

Not necessarily.  All queries are not alike ;-) If the query uses parameters, what values you pass in from CF and more importantly how they're passed, can affect the execution plan. I've seen that before with ms sql/jdbc.  That's why it's important to establish if it's just 1 specific query or ALL queries. Edit The likelihood is it's some sort of connection or driver problem, but running through the quick checklist above would help confirm or disprove that theory.

     > Unless there is something called "indexing" within the context of CF
     > that is distinct from indexes in the database.

No, CF doesn't do any special indexing. It just prepares the statements (inserts any parameters) and sends them to the database. What I've done before w/ms sql is trace the query w/ms sql's profiler. So I can see how ms sql interprets the query and also how much time is spent on CF vs db operations. But that's usually a last resort after I've tried everything else.
0
 

Author Comment

by:theideabulb
ID: 38409936
The DBA rebuilt an index and created a profile for the query.  That has greatly increased the performance.  I was lost because of the difference in speed between the sql editor and getting a result in CF. I just couldn't understand the difference in execution.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38409975
If changing the indexes or the execution plan helped then what you were doing in CF is not the same thing you were doing in sql*plus, toad or other tools.

The oracle optimizer doesn't know or care which tool you're using.  So, if it behaves differently it's because something else changed.  Maybe not intentionally but you did something different.

For instance, if in one tool you use bind variables and the other used substitution variables.  That makes a difference, because it's effectively a different query.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38409993
>>> All queries are not alike ;-)

correct, but the method of invocation for each makes no difference.
SQL in Toad is the same SQL in sql*plus is the same SQL in CF.

If you do something differently in the tools that makes the SQL different then, of course they can behave differently, but that's not really worthy of discussion.  Do two different actions get two different results.  end of story.
0
 
LVL 53

Expert Comment

by:_agx_
ID: 38410126
> correct, but the method of invocation for each makes no difference.
> SQL in Toad is the same SQL in sql*plus is the same SQL in CF.

No, I think you misunderstood me. I wasn't referring to a plain SQL string. CF connections typically use jdbc and bind variables. How you construct those parameters, specifically the types, can make a huge difference in how the query is interpreted.  (CF wraps a lot of the jdbc stuff. So the nitty gritty details aren't always obvious.) Anyway, we're saying the same thing ... different query - different results.
0
 

Author Comment

by:theideabulb
ID: 38410217
I don't know what you mean by different query.  I would do a CFDUMP to get my results.  take the query that it gave me and then paste it into the sql editor.  So to me, i was using the same query in CF that i was using in the editor
0
 
LVL 53

Expert Comment

by:_agx_
ID: 38410284
What is the actual SQL from the cfdump?
0
 

Author Closing Comment

by:theideabulb
ID: 38423874
thanks for the education in this.  Everyone offered some assistance.  I am not sure what the right answer was and why it was different. the execution path the DBA did seemed to do the trick, it is lightning fast now.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

571 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