Re: JDBC Driver Settings
- From: "Shelby Goerlitz [MSFT]" <shelbyg@xxxxxxxxxxxxx>
- Date: Wed, 4 May 2005 11:30:26 -0700
Chinn:
The main tweak w/ cursor mode is the setFetchSize setting discussed below..
-shelby
Shelby Goerlitz
Microsoft, SQL Server
"chinn" <chinn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:93F68045-55C3-4276-AA2D-70B47877A26A@xxxxxxxxxxxxxxxx
> Shelby
>
> Thanks Very Much For Explaining that.
> Are there any settings we can tweak when using SelectMethod=Cursor..IF so
> what are good and bad
>
>
> Thanks,
>
> "Shelby Goerlitz [MSFT]" wrote:
>
>> Chinn:
>>
>> Yes, this is correct -- see below a quote from the Connection String
>> Properties section of the docs.
>>
>> ===
>> Direct-The direct method sends the complete result set in one
>>
>> request to the driver. It is useful for queries that only produce a
>>
>> small amount of data that you fetch completely. You should avoid
>>
>> using direct when executing queries that produce a large amount
>>
>> of data, as the result set is cached completely on the client and
>>
>> constrains memory. In this mode, each statement requires its own
>>
>> connection to the database. This is accomplished by "cloning"
>>
>> connections. Cloned connections use the same connection
>>
>> properties as the original connection; however, because
>>
>> transactions must occur on a single connection, auto commit
>>
>> mode is required. Due to this, JTA is not supported in direct mode.
>>
>> In addition, some operations, such as updating an insensitive
>>
>> result set, are not supported in direct mode because the driver
>>
>> must create a second statement internally. Exceptions generated
>>
>> due to the creation of cloned statements usually return an error
>>
>> message similar to "Cannot start a cloned connection while in
>>
>> manual transaction mode."
>>
>> ===
>>
>> -shelby
>>
>> Shelby Goerlitz
>> Microsoft, SQL Server
>>
>>
>>
>> "chinn" <chinn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:BDF3530D-CF92-43E6-85B2-9E0D0DAFF400@xxxxxxxxxxxxxxxx
>> > Carb,
>> >
>> > But somebody is saying this..
>> >
>> > Is this correct..
>> >
>> > Microsoft JDBC driver doesn't support 'DIRECT' select mode for
>> > XADatasources
>> > (XA database connections). So we have to use cursor method.
>> > When I tried to switch to Direct, App didn't start with the error that
>> > 'direct' select method can not be used for XA Connections.
>> >
>> > thanks,
>> >
>> > ""Carb Simien [MSFT]"" wrote:
>> >
>> >>
>> >> --------------------
>> >> | Thread-Topic: JDBC Driver Settings
>> >> | thread-index: AcVKCoOrRDJWhsIWSTSRvlg4RlTrKQ==
>> >> | X-WBNR-Posting-Host: 12.214.173.82
>> >> | From: "=?Utf-8?B?Y2hpbm4=?=" <chinn@xxxxxxxxxxxxxxxxxxxxxxxxx>
>> >> | Subject: JDBC Driver Settings
>> >> | Date: Mon, 25 Apr 2005 19:49:06 -0700
>> >> | Lines: 20
>> >> | Message-ID: <7959E226-7B55-4C1E-BD44-F74307B59BF2@xxxxxxxxxxxxx>
>> >> | 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.2.250
>> >> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>> >> | Xref: TK2MSFTNGXA01.phx.gbl
>> >> microsoft.public.sqlserver.jdbcdriver:1049
>> >> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
>> >> |
>> >> | Hello,
>> >> | I saw the below in one of settings..where do i find them and where
>> >> are
>> >> they
>> >> | defined..
>> >> |
>> >> |
>> >> | see lot of select statements with sp_cursoropen with thousands of
>> >> reads
>> >> in
>> >> | the
>> >> | profiler but if i take the query and run it in the QA it will be 1/4
>> >> of
>> >> | those reads .I
>> >> | am wondering we will have to tweak somethings like you mentioned
>> >> below.Could
>> >> | you help me to find out where i could find those settings like
>> >> fetchsize
>> >> and
>> >> | selectmode..
>> >> |
>> >> |
>> >> | If you do decide to use cursor mode with ResultSets defined as
>> >> | TYPE_FORWARD_ONLY and either CONCUR_READONLY or CONCUR_UPDATABLE,
>> >> then
>> >> you,
>> >> | you can additionally test various values for the fetch size to tune
>> >> the
>> >> | performance. Otherwise, your fetch size will only be 1.
>> >> |
>> >> |
>> >> |
>> >>
>> >> You can set the fetch size using the setFetchSize() method from your
>> >> Statement/PreparedStatement/CallableStatement object. SelectMethod
>> >> can
>> >> be
>> >> set to either Direct or Cursor, and this is done in the connection
>> >> string:
>> >>
>> >> SelectMethod=cursor
>> >>
>> >> or
>> >>
>> >> SelectMethod=direct
>> >>
>> >> The default is Direct.
>> >>
>> >> 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.
>> >>
>> >>
>>
>>
>>
.
- Follow-Ups:
- Re: JDBC Driver Settings
- From: chinn
- Re: JDBC Driver Settings
- References:
- Re: JDBC Driver Settings
- From: Shelby Goerlitz [MSFT]
- Re: JDBC Driver Settings
- From: chinn
- Re: JDBC Driver Settings
- Prev by Date: Re: JDBC Driver Settings
- Next by Date: Re: JDBC Driver Settings
- Previous by thread: Re: JDBC Driver Settings
- Next by thread: Re: JDBC Driver Settings
- Index(es):
Relevant Pages
|
