query very slow on JDBC adapter

, ,
1

I have a query on SQL server, the table has 9,000,000 records, and has index. The reture results has only 1 record (select top 1). If I run query from SQL Analyzer, it’s very fast. However, if I query on webMethods through jdbc adapter, it’s very slow. It takes me about 15 seconds to get the result.

At beginning, I thought it was a network problem. Then I try to run a query against a small table, and it’s fast.

My question is, if the delay response is because of the large table, it should slow on SQL Analyzer too.

                    SQL Analyzer                       WebMethods

large table fast (< 1s ) very slow (15 seconds)
small table fast fast

base on the above results, it looks jdbc adapter hold all results back to client side, and select the top 1 on clilent, but that’s not make sense at all.

Any suggestion?

Thanks,

Shuren

2

hi shuren,
what kind of a adapter service is this one? Are you specifying the top 1 as part of the query. If thats the case, you can verify logging into sqlserver to see what is the query supplied by wM? if it includes the top1 or not.

Hope this helps.
Anitha.

3

It’s a customer sql query.

the query is like “select top 1 * from table1 where …”

you want me to check “what is the query supplied by wM?”?

Shuren

4

yes…if you login to the sqlserver and check for the runtime activities…you can check to see what was the query executed by the wM user (jdbc adapter userid)…
that way you can verify to see if “select top 1 * from table1 where …” is the same query which is getting executed or not.

Hope this helps.
Anitha.

5

the query is same.

What i found is if I hard code the where clause, like "select … from table1 where name = ‘Mike’ "‘, it’s fast. When I use ‘?’ to pass variable, like "select … from table1 where name = ? "’, then it becomes slow.

Shuren

6

Have you tried using stored procedures? Is that a possibility?

7

I found the solution. I need to set sendStringParametersAsUnicode=false jdbc driver other property.

In Microsoft JDBC driver, if sendStringParametersAsUnicode is not set, the default is true. This means, by default, the string parameters are sent as Unicode. If fields in database don’t use nvarchar (unicode), then the data has to convert from nvarchar to varchar.

If sendStringParametersAsUnicode is set to false, string parameters are sent to the server in native TDS collation format of the database, not in Unicode.


The difference of my query under this seeting is < 1 second vs 10-15 seconds!

Shuren