SQL Server JDBC - JDBC Driver Settings

Author

JDBC Driver Settings

chinn

2005-04-26, 3:23 am

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.

Carb Simien [MSFT]

2005-04-26, 1:23 pm

--------------------
| Thread-Topic: JDBC Driver Settings
| thread-index: AcVKCoOrRDJWhsIWSTSR
vlg4RlTrKQ==
| 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@microso
ft.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

2005-04-26, 8:23 pm

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: AcVKCoOrRDJWhsIWSTSR
vlg4RlTrKQ==
> | 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@microso
ft.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.
>
>

Shelby Goerlitz [MSFT]

2005-05-03, 1:23 pm

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@microso
ft.com...[color=darkred]
> 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:
>

chinn

2005-05-04, 9:23 am

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@microso
ft.com...
>
>
>

Shelby Goerlitz [MSFT]

2005-05-04, 1:23 pm

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@microso
ft.com...[color=darkred]
> 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

2005-05-05, 3:23 am

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@microso
ft.com...
>
>
>

Kamil Sykora [MSFT]

2005-05-24, 11:23 am

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/secur_ity.

--------------------
| 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@microso
ft.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@microso
ft.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: AcVKCoOrRDJWhsIWSTSR
vlg4RlTrKQ==
| > >> >> | 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@microso
ft.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.
| > >> >>
| > >> >>
| > >>
| > >>
| > >>
| >
| >
| >
|

Sponsored Links