Quite, and using semi-colons did not help in this case. With all the
drivers, using the 'result' attribute worked for getting the newly created
id. However, I implemented the BEGIN... END syntax as it was the cleanest
and quickest to implement without introducing bugs - having no budget for
the
heap memory usage is dropping
from an average 800-1000 Mb with the CF shipped driver to
around 300Mb with
the jTDS driver. Youch.
Very interesting. If it is really not arbitrary, I wonder what causes such a
significant difference. I will have to do some more reading on jTDS.
Quite, and
Yes, you're right. Just making sure that whoever read this knew it was an
explored avenue ;)
The issue with the driver is documented:
http://www.alagad.com/blog/post.cfm/mssql-driver-issue-with-cf-8-0-1
Using either the MS or the jTDS driver eliminates the issue.
Dominic
On 2 February 2010
I recently switched to using the latest Microsoft JDBC Driver for MSSQL 2005
and immediately saw a performance increase (over using the standard driver
w/ ColdFusion 8.01). However, all our queries that have multiple statements
are failing so I've had to switch back. e.g.
cfquery
cfquery datasource=my-datasource name=my-query
UPDATE foo SET bar = 1
SELECT bar FROM foo
/cfquery
Any difference if you surround the entire block with a SET NOCOUNT ON/OFF?
~|
Want to reach the ColdFusion
It appears to be a limitation of the M$ jdbc driver. Now using the jTDS
driver and all works well and with the same performance improvements
(specifically, reduced memory usage). Hoorah for OSS!
Dominic
On 1 February 2010 13:23, Leigh cfsearch...@yahoo.com wrote:
cfquery
It appears to be a limitation of the M$ jdbc driver.
I do not know that it is a limitation of the driver. From everything I have
read, I have always felt it was due to CF's handling of multiple statements
within cfquery, and the whole one query/resultset limitation. Though I prefer
Right, and thanks for the response btw. The main trouble is that we could
not afford the time to be checking through and changing all the possibly
affected queries in our rather sprawling legacy application (getting less
sprawled by the day) - the jTDC driver gives us the needed performance
affected queries in our rather sprawling legacy application
(getting less sprawled by the day)
I hear you. For a legacy application, it is probably not worth the trouble to
make all those changes.
You may also want to check for multiple queries with @@IDENTITY and
SCOPE_IDENTITY(). I
I recently switched to using the latest Microsoft JDBC Driver for
MSSQL 2005
and immediately saw a performance increase (over using the standard
driver
w/ ColdFusion 8.01). However, all our queries that have multiple
statements
are failing so I've had to switch back. e.g.
I know with
Yup, spot on Leigh, they're not working right with the jDTS driver either :O
Wrapping the whole statement with BEGIN .. END, or indeed SET NOCOUNT ON/OFF
does make them work though. Looks like I'm going to have to bite the bullet
and get coding; the performance issues with the default CF driver
Yup, spot on Leigh, they're not working right with the jDTS
driver either :O
Ugh. Is this with CF8 or CF9? A lot of the weirdness I saw in CF8 had to do
with INSERT/VALUES statements in particular, due to the generated keys feature
of the result attribute.
I'm just curious: don't you need to separate the statements with
semicolons? I've never seen multiple SQL statements without semicolons
before.
As others have noted tho', this behavior is driver-specific anyway...
Sean
On Mon, Feb 1, 2010 at 4:29 AM, Dominic Watson
I'm just curious: don't you need to separate the statements
with semicolons?
I do not think it is technically required with SQL Server. Though it may be in
the ANSI specs. In which case, it is probably not a bad idea to use them.
14 matches
Mail list logo
