Re: JDBC Driver Settings
- From: "chinn" <chinn@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 4 May 2005 18:49:13 -0700
Hi Shelby..
Thanks For Your Pateince..
Can you tell me what do i set the value of fetchsize and what are the pro's
and con's
of that or is there a link where i can look up this information ..would be
great
if you can help me with this..
Thx
"Shelby Goerlitz [MSFT]" wrote:
> 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: Kamil Sykora [MSFT]
- Re: JDBC Driver Settings
- References:
- Re: JDBC Driver Settings
- From: Shelby Goerlitz [MSFT]
- Re: JDBC Driver Settings
- From: chinn
- Re: JDBC Driver Settings
- From: Shelby Goerlitz [MSFT]
- Re: JDBC Driver Settings
- Prev by Date: Re: JDBC Driver Settings
- Next by Date: scb_*.tmp Files
- Previous by thread: Re: JDBC Driver Settings
- Next by thread: Re: JDBC Driver Settings
- Index(es):
Relevant Pages
|
