JDBC Driver Settings

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.
| Thread-Topic: JDBC Driver Settings
| thread-index: AcVKCoOrRDJWhsIWSTSRvlg4RlTrKQ==
| X-WBNR-Posting-Host: 12.214.173.82
| From: "=?Utf-8?B?Y2hpbm4=?=" <chinn@.discussions.microsoft.com>
| Subject: JDBC Driver Settings
| Date: Mon, 25 Apr 2005 19:49:06 -0700
| Lines: 20
| Message-ID: <7959E226-7B55-4C1E-BD44-F74307B59BF2@.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.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.
|||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@.discussions.microsoft.com>
> | Subject: JDBC Driver Settings
> | Date: Mon, 25 Apr 2005 19:49:06 -0700
> | Lines: 20
> | Message-ID: <7959E226-7B55-4C1E-BD44-F74307B59BF2@.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.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.
>
|||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@.discussions.microsoft.com> wrote in message
news:BDF3530D-CF92-43E6-85B2-9E0D0DAFF400@.microsoft.com...[vbcol=seagreen]
> 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:
|||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@.discussions.microsoft.com> wrote in message
> news:BDF3530D-CF92-43E6-85B2-9E0D0DAFF400@.microsoft.com...
>
>
|||Chinn:
The main tweak w/ cursor mode is the setFetchSize setting discussed below..
-shelby
Shelby Goerlitz
Microsoft, SQL Server
"chinn" <chinn@.discussions.microsoft.com> wrote in message
news:93F68045-55C3-4276-AA2D-70B47877A26A@.microsoft.com...[vbcol=seagreen]
> 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:
|||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@.discussions.microsoft.com> wrote in message
> news:93F68045-55C3-4276-AA2D-70B47877A26A@.microsoft.com...
>
>
|||Hello Chinn,
The setFetchSize setting allows you to fetch more rows at a time. Setting
it to a non-default value (larger than 1) will result in fewer calls to
sp_cursorfetch. You can reduce the number of round-trips to the database by
doing so. If you will be fetching a lot of rows this could significantly
improve the performance of your queries.
You will probably have to experiment with the right setting for you but,
for example, if your application typically works with 10 rows at a time,
you might want to set it to 10, etc.
Thanks,
Kamil
Kamil Sykora
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/securXity.
| From: "=?Utf-8?B?Y2hpbm4=?=" <chinn@.discussions.microsoft.com>
| Subject: Re: JDBC Driver Settings
| Date: Wed, 4 May 2005 18:49:13 -0700
| Lines: 195
|
| 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@.discussions.microsoft.com> wrote in message
| > news:93F68045-55C3-4276-AA2D-70B47877A26A@.microsoft.com...
| > > 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@.discussions.microsoft.com> wrote in message
| > >> news:BDF3530D-CF92-43E6-85B2-9E0D0DAFF400@.microsoft.com...
| > >> > 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@.discussions.microsoft.com>
| > >> >> | Subject: JDBC Driver Settings
| > >> >> | Date: Mon, 25 Apr 2005 19:49:06 -0700
| > >> >> | Lines: 20
| > >> >> | Message-ID: <7959E226-7B55-4C1E-BD44-F74307B59BF2@.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.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.
| > >> >>
| > >> >>
| > >>
| > >>
| > >>
| >
| >
| >
|