Skip to main content
A
Anonymous
February 25, 2010
Question

Datasource Advanced Settings: Direct or Cursor?

  • February 25, 2010
  • 4 replies
  • 1445 views

Using MSSQL compatibility level 100.  CF8 server, load balanced to a

single SQL server.  Is there a recommendation as to which mode to

choose for "Select Method"   Is direct the best way to go?  We have

been experiencing issues with jRUN being a total memory hog.  I

observe now that there are about 50 data sources and 1/2 use direct

and half use cursor.  Could this be the root of my out of memory

issues, and if so, Which style is causing the issue?  Should I be

placing the burden on SQL server to handle the load therefore using

Direct?  We use coldfusion recordsets heavily, with very complex 10+

table joins.   Is Cursor choking out my jrun instance?  We often see

the cannot find prepared statement error and then I have to run from

my blackberry to my PC and kill task jRun and piss everyone off...

Including me.

So please, an honest and substantive response is really needed.

Thanks,

Dennis

This topic has been closed for replies.

4 replies

A
Anonymous
February 26, 2010

Please see my last post in this series for the explanation of what I did to fix it.

A
Anonymous
February 26, 2010

ok, so anyone wondering what I did to resolve this:   All data sources have been set to Direct.  Apparently Microsoft has provided guidance that Cursor should not be used since MS-SQL 2000.  So use Direct.

Also, turn off RDS and disable all debug output on the production servers.  RDS can be disabled from the web.xml file by commenting out the appropriate section for RDS.

My memory pressure has gone from an always increasing value o f 800-1200 or more megs down to 285 megs solid and consistent for the past 4 hours.  We take more than 500 000 page hits per day with some pages running 20 to 120 queries per page.  Some with hundreds of parameters per query, averaging about 5 to 15.

I also believe that the changes to the jvm config were patrially to blame as well.

got rid of these:

-XX:+UseParallelOldGC

-Xmn128m

-XX:SurvivorRatio=8

in favor of this:

-XX:+UseParallelGC

HTH,

D.

A
Anonymous
February 26, 2010

So it's still happening.  I found these differences in the jvm config file... Does this shed any light on things...?  diffs in bold

Machine 1 - usually no problem

-Xms1024m

-Xmx1228m

-Dsun.io.useCanonCaches=false

-XX:MaxPermSize=192m

-XX:+UseParallelGC

-Dcoldfusion.rootDir={application.home}/../

-Dcoldfusion.libPath={application.home}/../lib

-Dcoldfusion.classPath={application.home}/../lib/updates,{application.home}/../lib,{application.home}/../gateway/lib/,{application.home}/../wwwroot/WEB-INF/flex/jars,{application.home}/../wwwroot/WEB-INF/cfform/jars

Machine 2 - usually theres an issue on this one

-Xms1024m

-Xmx1228m

-Dsun.io.useCanonCaches=false

-XX:MaxPermSize=192m

-XX:+UseParallelOldGC

-Xmn128m

-XX:SurvivorRatio=8

-Dcoldfusion.rootDir={application.home}/../

-Dcoldfusion.libPath={application.home}/../lib

-Dcoldfusion.classPath={application.home}/../lib/updates,{application.home}/../lib,{application.home}/../gateway/lib/,{application.home}/../wwwroot/WEB-INF/flex/jars,{application.home}/../wwwroot/WEB-INF/cfform/jars

A
Adam Cameron.
Inspiring
February 25, 2010

I can't answer your specific question, as I always use "direct" for my SQL Server DSNs.  That said, I've never used SQL Server for a heavy-load site (those ones always seem to use Oracle... it's never my decision...), and indeed I wasn't even aware there was the option there!  So one learns something every day.

However I think - that aisde - you should be doing some server monitoring, either with CF's inbuilt monitor or FusionReactor, and get to the bottom of where your memory's getting chewed up.

How much RAM have you allocated to your heap?

--

Adam

A
Anonymous
February 25, 2010

the max a java stack will allow on 32 bit windows..  1200 megs.

This is a systemic chronic issue.  I do have seeFusion, but its not all that helpful unless it does something useful that I can't yet understand.

Anyhoo...  In the last two days it popped up again, reaching max memory and I decided to compare all the data sources because it appears to only happen to one client.  They all run the same source with the same schema, so the only difference I have found so far, just today, is that cursor option.

Direct is supposed to be like the ADO forward only record set.  Cursor is the client ADO side cursor...  atleast thats what I surmise.  It seems intuitive to thing that the cursor based data sources would require more memory on the CF Server side thus the issues with "Cannot find prepared statement" meaning that JRun threw an exception due to an inability to malloc some ram and the statement truly is not available cause its a null pointer.

Can anyone speak from experience about cursor vs direct?

Thanks for your reply Adam.

Dennis.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices