[Reactor for CF] Proposed patch to fix (maybe) to many lastInsertedIdSyntax() calls

[Tom Chiverton]

I recently observed very long run times for pages that inserted into a table 
without a primary key field defined.

I think this is because the project DAO always looks up the last inserted 
primary key id, even if there is no such beast.
On Informix 9, for instance, this causes every row from the table to be 
returned (via Convention.lastInsertedIdSyntax()) !

I've moved the last inserted id queries inside tests for the existence of a 
primary key (identity), as below. 
Now that page runs fine.

There is no point, even in RDMS that don't have this 'return all rows' 
problem, querying for the last inserted id if there is no PK column, right ?

The only place the result of the last inserted id query is used, is in code 
that implicitly assumes there is a primary key field, so it should be OK to 
guard the queries themselves with the same test, right ?

If no one screams at me to tell me this sucks, I'll add a Trac ticket.

Index: /home/chivertont/Projects/reactor/trunk/xsl/dao.project.xsl
===================================================================
--- /home/chivertont/Projects/reactor/trunk/xsl/dao.project.xsl (svn)
+++ /home/chivertont/Projects/reactor/trunk/xsl/dao.project.xsl (working copy)
@@ -119,19 +119,22 @@
                        ) 
                         
                        <!-- some dbms require the last inserted id syntax to 
be run at the same 
time as the query --> 
+                       <xsl:if test="object/fields/[EMAIL PROTECTED] = 
'true']">        
                        &lt;cfif Convention.supportsMultiStatementQueries() AND 
Convention.supportsIdentity() &gt; 
                                
#Convention.lastInsertedIdSyntax(getObjectMetadata())# 
                        &lt;/cfif&gt;    
+                       </xsl:if> 
                &lt;/cfquery&gt; 
                 
-               <!-- other dbms require this in a seperate query --> 
-               &lt;cfif NOT Convention.supportsMultiStatementQueries() AND 
Convention.supportsIdentity() &gt; 
-                       &lt;cfquery name="qCreate" 
datasource="#_getConfig().getDsn()#" 
username="#_getConfig().getUsername()#" 
password="#_getConfig().getPassword()#"&gt;      
-                               
#Convention.lastInsertedIdSyntax(getObjectMetadata())# 
-                       &lt;/cfquery&gt;                 
-               &lt;/cfif&gt; 
+               <!-- other dbms require this in a seperate query -->     
+               <xsl:if test="object/fields/[EMAIL PROTECTED] = 'true']">       
+                       &lt;cfif NOT Convention.supportsMultiStatementQueries() 
AND 
Convention.supportsIdentity() &gt;
+                               &lt;cfquery name="qCreate" 
datasource="#_getConfig().getDsn()#" 
username="#_getConfig().getUsername()#" 
password="#_getConfig().getPassword()#"&gt;     
+                                       
#Convention.lastInsertedIdSyntax(getObjectMetadata())#
+                               &lt;/cfquery&gt;                
+                       &lt;/cfif&gt;
+                       
                 
-               <xsl:if test="object/fields/[EMAIL PROTECTED] = 'true']"> 
                        &lt;cfif Convention.supportsIdentity() AND 
qCreate.recordCount&gt; 
                                &lt;cfset arguments.to.<xsl:value-of 
select="object/fields/[EMAIL PROTECTED] = 'true']/@alias" /> = qCreate.id /&gt; 
                        &lt;/cfif&gt; 

-- 
Tom Chiverton
Helping to interactively cultivate dot-com information

****************************************************

This email is sent for and on behalf of Halliwells LLP.

Halliwells LLP is a limited liability partnership registered in England and 
Wales under registered number OC307980 whose registered office address is at St 
James's Court Brown Street Manchester M2 2JF.  A list of members is available 
for inspection at the registered office. Any reference to a partner in relation 
to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law 
Society.

CONFIDENTIALITY

This email is intended only for the use of the addressee named above and may be 
confidential or legally privileged.  If you are not the addressee you must not 
read it and must not use any information contained in nor copy it nor inform 
any person other than Halliwells LLP or the addressee of its existence or 
contents.  If you have received this email in error please delete it and notify 
Halliwells LLP IT Department on 0870 365 8008.

For more information about Halliwells LLP visit www.halliwells.com.



-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Reactor for ColdFusion Mailing List
reactor@doughughes.net
Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --