tracker issue : CF-3775860

select a category, or use search below
(searches all categories and all time range)
Title:

ORMExecuteQuery() bad performance

| View in Tracker

Status/Resolution/Reason: Closed/Withdrawn/CannotReproduce

Reporter/Name(from Bugbase): Luis Majano / Luis Majano (Luis Majano)

Created: 06/13/2014

Components: ORM Support

Versions: 11.0

Failure Type: Performance Issue

Found In Build/Fixed In Build: CF11_Final /

Priority/Frequency: Major / All users will encounter

Locale/System: ALL / Platforms All

Vote Count: 2

Problem Description:
We have discovered that ORMExecuteQuery() is very bad on performance in comparison to normal cfquery and also ORM hibernate criteria queries (ColdBox).  Here is a little report we did:

h1.15 records with ColdFusion
* cfquery: 3ms 
* ORMExecuteQuery: 36ms 
* ColdBox Criteria Queries: 8ms 

h1. 200 records with ColdFusion
* cfquery: 33ms 
* ORMExecuteQuery: 443ms 
* ColdBox Criteria: 17ms

The ColdBox criteria queries leverages the internal Hibernate Criteria interface. However, also entityload is very fast as well, sometimes faster than cfquery.  So the only culprit is ORMExecuteQuery(). 

To test this we did a ContentBox install up with a SQL Server 2008 R2 database. I populated 500 blog entries and 3,000 comments (6 per blog entry).

Thoughts?

----------------------------- Additional Watson Details -----------------------------

Watson Bug ID:	3775860

External Customer Info:
External Company:  
External Customer Name: LuisMajano
External Customer Email:  
External Test Config: My Hardware and Environment details:

Attachments:

Comments:

[subscribe] ////////////////
Vote by External U.
11942 | September 01, 2014 03:20:06 AM GMT
More information on this is available here: http://www.andyscott.id.au/blog/coldfusion-orm-and-things-one-should-consider-and-is-criteria-building-a-good-option The numbers reported by Luis are from my comment at the bottom of the page where you will also find a Gist with my exact test code which is to be run inside ContentBox. I'm sure an example could be extracted to run standalone if necessary. The blog entry was dealing with ContentBox so my sample code was ContentBox-specific. It should also be noted that Andrew's blog is running at Hostek which use Sandbox security and they have reported it to be at least 3x slower across the board when enabled. I believe Adobe is currently working with them on a fix. That is quite likely affecting the tests that Andrew ran. My tests were run on a local development machine that does not have sandbox security enabled.
Comment by External U.
11938 | September 02, 2014 09:21:24 AM GMT
Tested in CF11 Update 3 and following is the findings: Select query:- ORMExecuteQuery.cfm: <cfset ORMExecuteQuery("select id, comment from Perf", true, {datasource="ORM_Northwind_Modify"})> CFQuery.cfm: <cfquery datasource="ORM_Northwind_Modify">select id, comment from Perf</cfquery> The table Perf has 1000 records and here we are doing select query. Over 100 Samples, average request completion time comes out as: cfquery : 484 ormexecutequery: 551 Update query:- ORMExecuteQuery.cfm: <cfloop index="i" from="1" to="1000"><cfset ORMExecuteQuery("update Perf set comment='updatevalue' where id=#i#", true, {datasource="ORM_Northwind_Modify"})></cfloop> CFQuery.cfm: <cfloop index="i" from="1" to="1000"><cfquery datasource="ORM_Northwind_Modify">update Perf set comment='updatevalue' where id=#i#</cfquery></cfloop> Over 3 samples, average request completion time is: cfquery : 5214 ormexecutequery: 7113 (Comment added from ex-user id:nawani)
Comment by Adobe D.
11939 | December 11, 2014 08:00:22 AM GMT
Hi Luis, We are unable to see much differnce. Is it possible if you can share the repro case along with sample data? I tried accessing the URL provided below but it is under maintenance.
Comment by Nimit S.
11940 | September 21, 2015 08:27:31 AM GMT
It has been a while and we have not heard anything back on this issue. If you are still facing performance issue. Please feel free to write back to us so that we can re-open this bug.
Comment by Nimit S.
11941 | September 23, 2015 11:55:45 AM GMT
Vote must be between 25 and 4000 characters
Vote by External U.
11943 | September 23, 2015 04:29:09 PM GMT