Does your procedure specify rowlocks, nolocks, etc? Without that you may be at the mercy of the client (or driver in this case)
Cheers, Kris > 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? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:328284 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
