SQL query runs slow in railo but not in MSSQL Management Studio

[scottdoc]

Hi,

Has anyone experienced this issue with railo? We have some queries that will take 3 minutes or more to run in railo on our application, but when running at the same time in the same database via the SQL Management Studio, in runs in less than a second. The query is a select statement. It's non-trivial, but still should run quickly - or at least with similar performance on the same database.

Could it be a driver issue? Does anyone know of any issues that might cause this, or have seen this before?

We are using:

Railo: 4.1.2.005

Java: 1.7.0_45

OS: Linux 3.11.0-12 64bit

DB: Microsoft SQL Server 2014

DB driver: MSSQL - Microsoft SQL Server (Vendor Microsoft) - not sure the driver "version" whatever shipped with this release of railo.

This is a production environment (which, unfortunately, is the only environment where we see these issues) - so updating versions etc. is not something I can just do. Unless someone has found and solved this by a particular upgrade?

Thanks,

Scott

[Martin Webb]

Hi Scott,

If your queries involve strings, one thing that you might want to check is the 'Send String Parameters as Unicode' setting on the DSN. In our case, we had this set to true where the fields in the database were generally varchar and not nvarchar. Setting this to false made a dramatic difference.

We noticed that this can have a huge impact on performance so it might be worth looking at.

All the best,

Martin

[Nando Breiter]

Stab in the dark, but could memory allocation be an issue? If these queries are consuming enough memory, one way or another, to cause them to be run via swap space on disk, that would slow things down.

Aria Media Sagl

+41 (0)76 303 4477 cell
skype: ariamedia

--
Did you find this reply useful? Help the Railo community and add it to the Railo Server wiki at https://github.com/getrailo/railo/wiki
---
You received this message because you are subscribed to the Google Groups "Railo" group.
To view this discussion on the web visit https://groups.google.com/d/msgid/railo/6c2bdcad-6cc0-4b59-b345-6a882d9b4446%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[scottdoc]

Thanks guys, the unicode parameter I could fix - but not sure how to help the memory allocation. We solved it temporarily by adding WITH (NOLOCK) to the query. Since this is a simple read to grab a list for display, dirty reads would be ok - and probably very unlikely.

I will have a look at both of your suggestions next week and let you know how I go.

Thanks again,

Scott