Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Stored Procedure .. performance question.

5 posts in JDBC Connect (product renamed to JConnect) Last posting was on 1997-04-29 01:23:32.0Z
Joseph White Posted on 1997-04-17 01:47:22.0Z
Message-ID: <3355811F.DD039EBE@rwd.com>
Date: Wed, 16 Apr 1997 21:47:22 -0400
From: Joseph White <jwhite@rwd.com>
Reply-To: jawhite@ibm.net
Organization: RWD Technologies, Inc.
X-Mailer: Mozilla 4.0b3 [en] (WinNT; I)
MIME-Version: 1.0
Subject: Stored Procedure .. performance question.
X-Priority: 3 (Normal)
Content-Type: text/plain; charset=iso-8859-1
Newsgroups: sybase.public.jdbcconnect
Lines: 71
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:981
Article PK: 252733

What controls the number of result rows that are returned at one time
to the client (via jConnect) from a stored procedure? The stored
procedure is used to return data (select only, no updates, inserts, or
deletes involved).

I think I understand how to handle returning rows if I used a named
cursor (with CURSOR_ROWS).

Client is Windows NT v4.0 using JDK v1.0.2 and jConnect v2.0 "GA";
server is UnixWare running Sybase v11.0.2.

The behavior that I have noticed is the following:
Case 1 -- stepping through the result set with "next row" only
** When no values are obtained for the rows, the Windows NT
performance monitor indicates that the CPU is about 100%.

Case 2 -- stepping through each value for each row
** When values are obtained, the CPU utilization falls to about 55%, and
the time per row increases by a factor of 10 (moving from 0.011 seconds
to 0.140 seconds).

Any suggestions? I have included a code fragment below.

Regards,
Joe White

---------------------- code fragment
tocList = new SpcGetTocInfoList(theConnection); //<-----
This is a wrapper class to prepare the stored procedure call for
execution;

//<------ The wrapper class defines other stuff like: int getTocId()
{return getInt(1);...) etc.
rowCount = 0;
loadSuccess = false;

try
{
loadSuccess = tocList.executeSP(vid,tabid);
if(loadSuccess)
{
tStart = tRows.startTiming();
while (tocList.getNextResult())
{
if (flowControl > 0)
{
toc_id = tocList.getTocId();
toc_parent = tocList.getTocParent();
info_id = tocList.getInfoElementId();
author_key = tocList.getAuthorKey();
infoElementPath = tocList.getInfoElementPath();
infoElementTitle = tocList.getInfoElementTitle();

while (author_key.length() < 10)
author_key = "0" + author_key;
}

rowCount++;
tStart = tRows.stopTiming(tStart);
if (lRows != null && rowCount%moduloValue == 0)
{
lRows.setText(Integer.toString(rowCount));
}

}
loadSuccess = rowCount > 0;
}
}
// toclist.close();
catch (DbException ex)
{
loadSuccess = false;
}
finally
{
tocList.close();
}
theConnection.close();
loadComplete = true;
return true;
}


David Clegg Posted on 1997-04-17 21:59:18.0Z
Message-ID: <33569D36.573C0A59@sybase.com>
Date: Thu, 17 Apr 1997 14:59:18 -0700
From: David Clegg <davec@sybase.com>
X-Mailer: Mozilla 2.01 (X11; I; Linux 1.2.13 i586)
MIME-Version: 1.0
To: jawhite@ibm.net
Subject: Re: Stored Procedure .. performance question.
References: <3355811F.DD039EBE@rwd.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.jdbcconnect
Lines: 109
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:970
Article PK: 252722

All the rows are returned at once - they just sit there in your
network buffers until you use rs.next() and rs.getXXX() to retrieve
them. If you do some other operation on the connection using a
different Statement, then jConnect will try to cache the remainder
of the resultSets - but you do not mention doing anything else at
the same time on the same connection.

If you do not specify the NO_REPEAT_READ connection property, then
the ResultSet does not know what order you will be doing the getXXX's
in on the columns of the row nor if you will be doing getXXX more than
once on the same column. In this case, as you fetch each column the
ResultSet makes buffered copies of the raw column data (in case you
ask to see it again). If you do not call getXXX() on anything in
a given row, then the ResultSet doesn't make a copy - and since
the succeeding rs.next() means that you can never go back to that
row the data is just skipped.

So, I imagine that the extra time you are seeing is overhead involved
with creating all the intermediate objects associated with this
column-by-column buffering. If you are always reading the columns
in the order they are selected, and you never try to get the same
column more than once per row, then you may get a significant
performance boost by using the NO_REPEAT_READ property (2-3 times
faster).

dave

>
> What controls the number of result rows that are returned at one time
> to the client (via jConnect) from a stored procedure? The stored
> procedure is used to return data (select only, no updates, inserts, or
> deletes involved).
>
> I think I understand how to handle returning rows if I used a named
> cursor (with CURSOR_ROWS).
>
> Client is Windows NT v4.0 using JDK v1.0.2 and jConnect v2.0 "GA";
> server is UnixWare running Sybase v11.0.2.
>
> The behavior that I have noticed is the following:
> Case 1 -- stepping through the result set with "next row" only
> ** When no values are obtained for the rows, the Windows NT
> performance monitor indicates that the CPU is about 100%.
>
> Case 2 -- stepping through each value for each row
> ** When values are obtained, the CPU utilization falls to about 55%, and
> the time per row increases by a factor of 10 (moving from 0.011 seconds
> to 0.140 seconds).
>
> Any suggestions? I have included a code fragment below.
>
> Regards,
> Joe White
>
> ---------------------- code fragment
> tocList = new SpcGetTocInfoList(theConnection); //<-----
> This is a wrapper class to prepare the stored procedure call for
> execution;
>
> //<------ The wrapper class defines other stuff like: int getTocId()
> {return getInt(1);...) etc.
> rowCount = 0;
> loadSuccess = false;
>
> try
> {
> loadSuccess = tocList.executeSP(vid,tabid);
> if(loadSuccess)
> {
> tStart = tRows.startTiming();
> while (tocList.getNextResult())
> {
> if (flowControl > 0)
> {
> toc_id = tocList.getTocId();
> toc_parent = tocList.getTocParent();
> info_id = tocList.getInfoElementId();
> author_key = tocList.getAuthorKey();
> infoElementPath = tocList.getInfoElementPath();
> infoElementTitle = tocList.getInfoElementTitle();
>
> while (author_key.length() < 10)
> author_key = "0" + author_key;
> }
>
> rowCount++;
> tStart = tRows.stopTiming(tStart);
> if (lRows != null && rowCount%moduloValue == 0)
> {
> lRows.setText(Integer.toString(rowCount));
> }
>
> }
> loadSuccess = rowCount > 0;
> }
> }
> // toclist.close();
> catch (DbException ex)
> {
> loadSuccess = false;
> }
> finally
> {
> tocList.close();
> }
> theConnection.close();
> loadComplete = true;
> return true;
> }


Joseph White Posted on 1997-04-18 21:25:47.0Z
Message-ID: <3357E6D6.ACCF9DA9@rwd.com>
Date: Fri, 18 Apr 1997 17:25:47 -0400
From: Joseph White <jwhite@rwd.com>
Reply-To: jawhite@ibm.net
Organization: RWD Technologies, Inc.
X-Mailer: Mozilla 4.0b3 [en] (WinNT; I)
MIME-Version: 1.0
Subject: Re: Stored Procedure .. performance question.
X-Priority: 3 (Normal)
References: <3355811F.DD039EBE@rwd.com> <33569D36.573C0A59@sybase.com>
Content-Type: multipart/alternative; boundary="------------BD334E35ACCAD22142F075F3"
Newsgroups: sybase.public.jdbcconnect
Lines: 401
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:963
Article PK: 252715

Dave --
Thanks for your response! 

We are experiencing significantly slower performance using jConnect (GA) versus jdbcConnect (beta from about four weeks ago).  I have a few clarifications and questions on the response you made before.  I have embedded my questions into your response.

Any help is greatly appreciated!

David Clegg wrote:

All the rows are returned at once - they just sit there in your
network buffers until you use rs.next() and rs.getXXX() to retrieve
them.  If you do some other operation on the connection using a
different Statement, then jConnect will try to cache the remainder
of the resultSets - but you do not mention doing anything else at
the same time on the same connection.
Does this mean that the result set is in the network buffers on the server side?
Is it worth trying another statement (e.g. "set CURSOR_ROWS 1" or some other cheap statement) just to force cacheing of the result set on the client? (Is that where the caching takes place?)

If you do not specify the NO_REPEAT_READ connection property, then
the ResultSet does not know what order you will be doing the getXXX's
in on the columns of the row nor if you will be doing getXXX more than
once on the same column.  In this case, as you fetch each column the
ResultSet makes buffered copies of the raw column data (in case you
ask to see it again).  If you do not call getXXX() on anything in
a given row, then the ResultSet doesn't make a copy - and since
the succeeding rs.next() means that you can never go back to that
row the data is just skipped.

So, I imagine that the extra time you are seeing is overhead involved
with creating all the intermediate objects associated with this
column-by-column buffering.  If you are always reading the columns
in the order they are selected, and you never try to get the same
column more than once per row, then you may get a significant
performance boost by using the NO_REPEAT_READ property (2-3 times
faster).
The case we saw shows LOWER cpu utilization when retrieving each column over just moving row to row.  That leads me to suspect some sort of network chit-chat issue.  NO_REPEATABLE_READ being wrong should increase the client cpu utilization, right?  We have also explicitly set NO_REPEATABLE_READ without improving our performance.

dave
>
> What controls the number of result rows that are returned at one time
> to the client (via jConnect) from a stored procedure?  The stored
> procedure is used to return data (select only, no updates, inserts, or
> deletes involved).
>
> I think I understand how to handle returning rows if I used a named
> cursor (with CURSOR_ROWS).
>
> Client is Windows NT v4.0 using JDK v1.0.2 and jConnect v2.0 "GA";
> server is UnixWare running Sybase v11.0.2.
>
> The behavior that I have noticed is the following:
> Case 1 -- stepping through the result set with "next row" only
>   ** When no values are obtained for the rows, the Windows NT
> performance monitor indicates that the CPU is about 100%.
>
> Case 2 -- stepping through each value for each row
> ** When values are obtained, the CPU utilization falls to about 55%, and
> the time per row increases by a factor of 10 (moving from 0.011 seconds
> to 0.140 seconds).
>
> Any suggestions?  I have included a code fragment below.
>
> Regards,
> Joe White
>
> ----------------------  code fragment
>         tocList     = new SpcGetTocInfoList(theConnection);  //<-----
> This is a wrapper class to prepare the stored procedure call for
> execution;
>
> //<------ The wrapper class defines other stuff like: int getTocId()
> {return getInt(1);...) etc.
>         rowCount    = 0;
>         loadSuccess = false;
>
>         try
>         {
>             loadSuccess = tocList.executeSP(vid,tabid);
>          if(loadSuccess)
>             {
>                 tStart = tRows.startTiming();
>              while (tocList.getNextResult())
>              {
>                  if (flowControl > 0)
>                  {
>                  toc_id              = tocList.getTocId();
>                  toc_parent          = tocList.getTocParent();
>                     info_id             = tocList.getInfoElementId();
>                     author_key          = tocList.getAuthorKey();
>                  infoElementPath     = tocList.getInfoElementPath();
>                  infoElementTitle    = tocList.getInfoElementTitle();
>
>                     while (author_key.length() < 10)
>                         author_key = "0" + author_key;
>                     }
>
>                     rowCount++;
>                     tStart = tRows.stopTiming(tStart);
>                     if (lRows != null && rowCount%moduloValue == 0)
>                     {
>                         lRows.setText(Integer.toString(rowCount));
>                     }
>
>              }
>                 loadSuccess = rowCount > 0;
>             }
>         }
>     // toclist.close();
>         catch (DbException ex)
>         {
>             loadSuccess = false;
>         }
>         finally
>         {
>             tocList.close();
>         }
>         theConnection.close();
>         loadComplete = true;
>         return true;
>     }
  


David Clegg Posted on 1997-04-28 17:02:34.0Z
Message-ID: <3364D82A.652A7B3B@sybase.com>
Date: Mon, 28 Apr 1997 10:02:34 -0700
From: David Clegg <davec@sybase.com>
X-Mailer: Mozilla 2.01 (X11; I; Linux 1.2.13 i586)
MIME-Version: 1.0
To: jawhite@ibm.net
Subject: Re: Stored Procedure .. performance question.
References: <3355811F.DD039EBE@rwd.com> <33569D36.573C0A59@sybase.com> <3357E6D6.ACCF9DA9@rwd.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.jdbcconnect
Lines: 84
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:919
Article PK: 252671


Joseph White wrote:
>
> Dave --
> Thanks for your response!
>
> We are experiencing significantly slower performance using jConnect (GA)
> versus jdbcConnect (beta from about four weeks ago). I have a few
> clarifications and questions on the response you made before. I have
> embedded my questions into your response.
>
> Any help is greatly appreciated!
>
> David Clegg wrote:
>
> > All the rows are returned at once - they just sit there in your
> > network buffers until you use rs.next() and rs.getXXX() to retrieve
> > them. If you do some other operation on the connection using a
> > different Statement, then jConnect will try to cache the remainder
> > of the resultSets - but you do not mention doing anything else at
> > the same time on the same connection.
>
> Does this mean that the result set is in the network buffers on the
> server side?
> Is it worth trying another statement (e.g. "set CURSOR_ROWS 1" or some
> other cheap statement) just to force cacheing of the result set on the
> client? (Is that where the caching takes place?)

network buffers are the magic netherworld of queuing that makes TCP/IP
work under the covers. They hold everything between when your SQL
Server calls write()/flush() and when you call read(). Data can
get buffered up in your client machine, in routers and gateways, and
on the server side. setting cursor-rows to 1 would keep the amount
of network buffering down (assuming you are always using cursors), but
at the expense of many more network round trips and lots of wasted
space in the network buffers you do use.

I don't think that this is something you need to worry about.

> >
> > If you do not specify the NO_REPEAT_READ connection property, then
> > the ResultSet does not know what order you will be doing the
> > getXXX's
> > in on the columns of the row nor if you will be doing getXXX more
> > than
> > once on the same column. In this case, as you fetch each column the
> >
> > ResultSet makes buffered copies of the raw column data (in case you
> > ask to see it again). If you do not call getXXX() on anything in
> > a given row, then the ResultSet doesn't make a copy - and since
> > the succeeding rs.next() means that you can never go back to that
> > row the data is just skipped.
> >
> > So, I imagine that the extra time you are seeing is overhead
> > involved
> > with creating all the intermediate objects associated with this
> > column-by-column buffering. If you are always reading the columns
> > in the order they are selected, and you never try to get the same
> > column more than once per row, then you may get a significant
> > performance boost by using the NO_REPEAT_READ property (2-3 times
> > faster).
>
> The case we saw shows LOWER cpu utilization when retrieving each column
> over just moving row to row. That leads me to suspect some sort of
> network chit-chat issue. NO_REPEATABLE_READ being wrong should increase
> the client cpu utilization, right? We have also explicitly set
> NO_REPEATABLE_READ without improving our performance.

The property is NO_REPEAT_READ, and must be spelled exactly that way.
I just do not believe that you would not see a performance boost
when you use this property if you are EVER retrieving any row-results.


Joseph White Posted on 1997-04-29 01:23:32.0Z
Message-ID: <33654D90.6F1FE50@rwd.com>
Date: Mon, 28 Apr 1997 21:23:32 -0400
From: Joseph White <jwhite@rwd.com>
Reply-To: jawhite@ibm.net
Organization: RWD Technologies, Inc.
X-Mailer: Mozilla 4.0b3 [en] (WinNT; I)
MIME-Version: 1.0
To: David Clegg <davec@sybase.com>
Subject: Re: Stored Procedure .. performance question.
X-Priority: 3 (Normal)
References: <3355811F.DD039EBE@rwd.com> <33569D36.573C0A59@sybase.com> <3357E6D6.ACCF9DA9@rwd.com> <3364D82A.652A7B3B@sybase.com>
Content-Type: text/plain; charset=iso-8859-1
Newsgroups: sybase.public.jdbcconnect
Lines: 95
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:904
Article PK: 252659


David Clegg wrote:

> Joseph White wrote:
> >
> > Dave --
> > Thanks for your response!
> >
> > We are experiencing significantly slower performance using
> jConnect (GA)
> > versus jdbcConnect (beta from about four weeks ago). I have a few
>
> > clarifications and questions on the response you made before. I
> have
> > embedded my questions into your response.
> >
> > Any help is greatly appreciated!
> >
> > David Clegg wrote:
> >
> > > All the rows are returned at once - they just sit there in your
> > > network buffers until you use rs.next() and rs.getXXX() to
> retrieve
> > > them. If you do some other operation on the connection using a
> > > different Statement, then jConnect will try to cache the
> remainder
> > > of the resultSets - but you do not mention doing anything else
> at
> > > the same time on the same connection.
> >
> > Does this mean that the result set is in the network buffers on
> the
> > server side?
> > Is it worth trying another statement (e.g. "set CURSOR_ROWS 1" or
> some
> > other cheap statement) just to force cacheing of the result set on
> the
> > client? (Is that where the caching takes place?)
>
> network buffers are the magic netherworld of queuing that makes
> TCP/IP
> work under the covers. They hold everything between when your SQL
> Server calls write()/flush() and when you call read(). Data can
> get buffered up in your client machine, in routers and gateways, and
>
> on the server side. setting cursor-rows to 1 would keep the amount
> of network buffering down (assuming you are always using cursors),
> but
> at the expense of many more network round trips and lots of wasted
> space in the network buffers you do use.
>
> I don't think that this is something you need to worry about.
>
> > >
> > > If you do not specify the NO_REPEAT_READ connection property,
> then
> > > the ResultSet does not know what order you will be doing the
> > > getXXX's
> > > in on the columns of the row nor if you will be doing getXXX
> more
> > > than
> > > once on the same column. In this case, as you fetch each column
> the
> > >
> > > ResultSet makes buffered copies of the raw column data (in case
> you
> > > ask to see it again). If you do not call getXXX() on anything
> in
> > > a given row, then the ResultSet doesn't make a copy - and since
> > > the succeeding rs.next() means that you can never go back to
> that
> > > row the data is just skipped.
> > >
> > > So, I imagine that the extra time you are seeing is overhead
> > > involved
> > > with creating all the intermediate objects associated with this
> > > column-by-column buffering. If you are always reading the
> columns
> > > in the order they are selected, and you never try to get the
> same
> > > column more than once per row, then you may get a significant
> > > performance boost by using the NO_REPEAT_READ property (2-3
> times
> > > faster).
> >
> > The case we saw shows LOWER cpu utilization when retrieving each
> column
> > over just moving row to row. That leads me to suspect some sort
> of
> > network chit-chat issue. NO_REPEATABLE_READ being wrong should
> increase
> > the client cpu utilization, right? We have also explicitly set
> > NO_REPEATABLE_READS without improving our performance.
>
> The property is NO_REPEAT_READ, and must be spelled exactly that
> way.
> I just do not believe that you would not see a performance boost
> when you use this property if you are EVER retrieving any
> row-results.

Dave,
We will try NO_REPEAT_READ ... though your documentation explicitly
states that the property is NO_REPEATABLE_READS (see
"/jConnect/docs/prjdbc_4.htm#HEADING4-69").

Regards,
Joe White