Re: why excute query through JDBC much slow than query analyzer?

From: Carb Simien [MSFT] (CarbinoS_at_online.microsoft.com)
Date: 11/22/04 

Date: Mon, 22 Nov 2004 18:30:19 GMT

--------------------
| Thread-Topic: why excute query through JDBC much slow than query analyzer?
| thread-index: AcTQZPB2r5/CZ3pBT2+Q8N5HdzagcQ==
| X-WBNR-Posting-Host: 203.116.61.131
| From: =?Utf-8?B?QW5kcmV3?= <Andrew@discussions.microsoft.com>
| References: <C14CE83B-A2A2-4DEB-9E03-00F9893458C8@microsoft.com>
<eYCEgTnxEHA.3908@TK2MSFTNGP12.phx.gbl>
<#kauUJpzEHA.260@TK2MSFTNGP11.phx.gbl>
| Subject: Re: why excute query through JDBC much slow than query analyzer?
| Date: Sun, 21 Nov 2004 23:29:02 -0800
| Lines: 47
| Message-ID: <33D4A0B8-AB36-47D1-BEDC-6B3E97FD673C@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6498
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Thanks for all of your answers, Yes,
"SendStringParametersAsUnicode=false"
| can help me to solve the problem. But I am still curious:
|
| 1. How does this key-value-pair help? Does it force my jdbc query to make
| use of index, or it simly speed up unicode data transmitting?
|
| 2. Is it MS SQL server specific? I could not find any info about this in
| JDBC spec.
|
| Regards
| Wang
|
| "Frank Brouwer" wrote:
|
| > If you use that be aware of the fact that language specific characters
(like
| > the Euro sign) are not stored in the database because they are unicode.
| > Either do not use language specific characters or use nvarchar for your
| > strings (ntext).
| >
| > Regards,
| >
| > Frank.
| >
| >
| > "Pete Loveall" <psl@ametx.com.NO_SPAM> wrote in message
| > news:eYCEgTnxEHA.3908@TK2MSFTNGP12.phx.gbl...
| > > Set SendStringParametersAsUnicode=false This defaults to true which
| > causes
| > > searches to be done as N'text on non-unicode keys. This can cause
massive
| > > slow downs in execution.
| > >
| > > Pete Loveall
| > > AME Corp.
| > >
| > > "Andrew" <Andrew@discussions.microsoft.com> wrote in message
| > > news:C14CE83B-A2A2-4DEB-9E03-00F9893458C8@microsoft.com...
| > > >
| > > > I run a query through JDBC driver, it takes me about 60 seconds.
But I
| > > > simpy
| > > > cut/paste the query to the query analyzer (on the same machine), I
got
| > > > less
| > > > than 1 second. Why got so big difference?
| > >
| > >
| >
| >
| >
|

The SendStringParametersAsUnicode is a connection string attribute that is
implemented in the Microsoft JDBC driver. By default, all string
parameters are sent as Unicode (SendStringParametersAsUnicode=true).

If you capture the Execution Plan in a SQL Profiler trace while executing
your code, you will see the datatype nvarchar(4000) being used for your
string parameters. If your database table doesn't use Unicode data types,
then a conversion is necessary from nvarchar to varchar. This can result
in suboptimal performance.

If you set SendStringParametersAsUnicode=false in your code, the same query
will use varchar(8000) in the execution plan. Since no extra conversions
are required, this typically results in a faster execution.

Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

Relevant Pages

  • Re: Writing a query using VBA - Variable where clause
    ... but I'm still alittle lost on execution. ... that collection and set its member to true for member items IN the "temp" ... 2- Make an ad hoc query from a string. ...
    (microsoft.public.access.queries)
  • Re: JDBC Question: Getting data from tables with columns that have the same name
    ... > I'm making a JDBC SQL query that looks something like this: ... > I get reasonable values in my JDBC result set. ... > String st1 = new String; ... > query changes. ...
    (comp.lang.java.databases)
  • Re: Setting Query Timeout through JDBC (CA Driver problem?)
    ... I suggest to you not using commands like ""set lockmode ..." ... inside jdbc because they are not intended for being used like that nor ... About query timeout we have not used it, but I do not remember any ...
    (comp.databases.ingres)
  • Re: database toolbox problem
    ... I have found a workaround for this problem. ... This bug is already listed in the Matlab bug reports and ... query = 'ALTER TABLE test ADD test_col VARCHARNOT NULL'; ... "The JDBC spec states that you must either use ...
    (comp.soft-sys.matlab)
  • Re: SQL Developer - Selects
    ... JDBC offers to set the maximum number of rows returned by the query, and I assume that SQL Developer is using that feature - especially as you cannot see any limits when you trace the statement. ... My assumption is that the JDBC driver only sends the defined limit to the server, and the server is able to take this into account when returning the result. ...
    (comp.databases.oracle.server)