Problem with SQLServer connections

[David Aden]

I have an application that does a ridiculous number of queries -- its purpose
is to extract records from one database and insert them into a different
schema. It runs very long, is admittedly very clunky but was intended as a
backend, batch type of process run infrequently.

In trying to run it on CF7 (patched with recent updates including the driver
update), the process kicks off and initially runs quickly but the memory CF
(actually JRun) is using runs up to 500+ meg. Everything comes to a screaching
halt there, the processor is pegged at 100% and killing CF takes a long time.
This takes only takes about 2 minutes to happen -- by that point in time it has
probably run 3,000 queries -- a combination of requesting data from the source
database, running DLL to build tables in the target database and INSERT
statements to stick the data in.

I'm running on Windows XP Pro SP2 on an AMD64 processor with CFMX7 with a gig
of RAM (when JRun hits the 500+ mark, total RAM used is about 1 gig). I've
tried using the JDBC SQL Server connector as well as an ODBC datasource -- same
general result. I've tried limiting the database connections.

Anyone else having a problem with what appears to be a memory leak when
running a large number of queries against SQL Server?

d

[SafariTECH]

So, you are running CF + SQL on the same machine with only a single processor?
They are both intensive processor applications and it is almost always
recommended to have a major DBMS on a separate box.

However, that being said, I actually run a CF/SQL server combo for JUST
backend management stuff with only 384Mb Ram, BUT it has 2 processors to
distribute the SQL processing.

In addition, by using XP rather than an actual server OS, you are missing some
key settings and abilities that benefit a server over a desktop including some
memory management.

If you are using the XP system as a server and nothing else, the first thing
you should do is set the environment to optimize for performance rather than
appearance.

You mentioned using a DLL call for creating tables. Does the SQL server not
accept the commandsets for creating tables? If it does you could do this via CF
as well, rather than calling a third application in the process.

The last thing would be to review the code and see if there is any way to
optimize it by using cached queries, queries of queries, etc rather than
calling completely unique queries each and every time.

Just my 2 cents

[David Aden]

Thanks for your suggestions and you're right on all counts -- I didn't mention
the configuration I'm talking about is my laptop I'm just running this stuff on
to test it. The real application is on something closer to a server and the
code will be optimized in the next year or so (this was protoype code that
worked well enough that it ended up being adopted).

But the point is that CF should be well behaved enough not to max memory and
crash. I've run this same thing for two years on CF 6.x on lesser laptops as
well as in the production environment. Actually, originally with 6.x I had a
similar problem which turned out to be the database connector. Switching to the
ODBC connector got around the problem; now with CF7 the problem shows up with
both the JDBC and ODBC connectors.

btw the "DLL" was a typo on my part -- I should have written "DDL." All the
database actions are being called from CF.

d

[David Aden]

Okay, I seem to have solved this -- I just dropped the "Maximum JVM Heap Size
(MB)" down to 300 from 512 and it worked like a champ.

Apparently, the JVM was grabbing up to the limit which, I'm assuming, pushed
it over the hardware memory and apparently caused the machine to slow way down.