Exactly one week ago I encountered the same problem. Very fast on Oracle and MySQL and terribly slow when deployed on MS SQL Server 2000.

After a day of debugging and searching, I finally figured it out.

We use the jTDS driver and one of the properties you can give to the URL is the "sendStringParametersAsUnicode". If you don't specify it, it takes the default value, which is "TRUE". But in a SQL Server 2000 environment, this can have a dramatic performance influence as you can read in this faq: http://jtds.sourceforge.net/faq.html#urlFormat.

We had a speed-up from 8-10 times by setting this flag to false.

Regards,

Jos

Jos,
It can be that you increase performance with any parameter, but from architecture of MS SQL i see that it have to be slow in OLTP application - mysql is quick when you work without transaction and it is another story.

It is simple for explanation, but it isn't easy for test (you can test database with more users)
when you have more users and more read/write operations and users read/write different data sql server is fine
when they read (only read, but special write) to same page then first reader block others

it is much worst when you have complex system (complex procedures, functions , triggers
and all read/write many things)

and again - if this parameter make SQL server quicker, why MS don't set this to default - it work for english language only (maybe)

Some more info on the unicode vs ascii parameters issue. As jTDS is using sp_prepare and sp_execute, and according to reference

http://jtds.sourceforge.net/apiCursors.html

these commands alwasy only take unicode parameters. And so, if your columns involved in the search is in ascii, I doubted if the sendStringParametersAsUnicode would help. In fact, I had similiar problem when during with MS JDBC driver, which uses sp_executesql, and which takes only unicode parameters too. My solution was to change all search involved columns to nchar or nvarchar, and I was able to verify through SQL profiler that my sqls were running properly.

I am a SQL Server developer. My suspicion based on what you say about unicode and non unicode flag is that you are querying the database with unicode strings which will cause SQL not to use the indexes properly.

Say you have a table
table1(
col_code varchar(4) not null,
name varchar(50),
address varchar(100))

and you have an index on col_code.

If you query col_code with a unicode string then SQL Server has to tranlate the nvarchar (your unicode) to a varchar. By doing the tranlation your are not utilising the index well. If you pass a varchar though as the parameter then you are fine. Basically you need to make sure that you are querying a column of the same data type. SQL will make the translation but you are penalised.

Regarding the other discussion about locking. There are 5 different levels of locking in 2005, maybe 6. If you want dirty reads you can set the locking for the whole server so there are no locks placed for reads, etc. So this is possible, also there is a new locking type for 2005. You can also implement optimistic concurrency if you know from the beginning there are 90% inserts and hardly any updates or deletes. So loads of options.

Panos.