Okay, I'm observing a very strange phenomena.
Following the bulk insert of a fairly complex xml document, I run a stored
procedure which parses through the document passing the values into
relational tables. This part is all done in SQL, inside the stored
procedure.
Platform:
Windows Server 2008 64bit
SQL Server 2008
Coldfusion 8
If I do this from the query analyzer, it executes instantly, parses the
document happily and moves on, with row locks on the database never more
than 50 or so:
exec myProcedure @id=somevalue, blah other variables
However, if I call the following code in coldfusion, I end up with
potentially _thousands_ of row locks on the table, and the database becomes
unusable within seconds:
<cfstoredproc procedure="myProcedure " datasource="#application.maindsn#">
<cfprocparam type="in" value="#somevalue#"
cfsqltype="cf_SQL_INTEGER">
... blah blah other parameters
</cfstoredproc>
That wasn't working out, so I tried this instead, so that I had isolated the
_exact_ same code as from query analyzer:
<cfquery datasource="#application.maindsn#">
exec myProcedure @id=somevalue, blah other variables
</cfquery>
The result this way is even worse.
This leads me to think there may be some extra locking being forced by the
jdbc driver, or I'm just doing something really wrong. I am not wrapping
this in cftransaction at this point. Has anyone encountered anything like
this?
--
-------------------------------
Charles Sheehan-Miles
202-412-2433 | char...@sheehanmiles.net
http://www.linkedin.com/in/sheehanmiles
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328261
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
