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.

Best way to read an event log table?

11 posts in General Discussion Last posting was on 2010-12-08 15:23:59.0Z
Jon Saxton Posted on 2010-12-07 16:51:43.0Z
Sender: 1a5.4cfe6315.1804289383@sybase.com
From: Jon Saxton
Newsgroups: sybase.public.ase.general
Subject: Best way to read an event log table?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4cfe661f.2a3.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 7 Dec 2010 08:51:43 -0800
X-Trace: forums-1-dub 1291740703 10.22.241.41 (7 Dec 2010 08:51:43 -0800)
X-Original-Trace: 7 Dec 2010 08:51:43 -0800, 10.22.241.41
Lines: 14
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29762
Article PK: 78991

Using CT/CS library calls within a C++ program I have to
read a certain table every few seconds and get whatever rows
have been added. I am wondering what is the best approach.
There is no primary key, indeed no index at all. Each row
has a timestamp (datetime) so I could select for timestamps
> last one but I am worried that this might be equivalent to
re-reading the entire table each time. So I thought about
maintaining an open read-only cursor on the table and using
that to fetch the new rows each time but the documentation
says that cursor performance is not so good. Nevertheless
is the cursor approach better than a select based on
timestamp?

Opinions?


Rob V [ Sybase ] Posted on 2010-12-07 17:06:15.0Z
From: "Rob V [ Sybase ]" <robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: robv@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV / TeamSybase / Sybase
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.12) Gecko/20101027 Thunderbird/3.1.6
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Best way to read an event log table?
References: <4cfe661f.2a3.1681692777@sybase.com>
In-Reply-To: <4cfe661f.2a3.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4cfe6987$1@forums-1-dub>
Date: 7 Dec 2010 09:06:15 -0800
X-Trace: forums-1-dub 1291741575 10.22.241.152 (7 Dec 2010 09:06:15 -0800)
X-Original-Trace: 7 Dec 2010 09:06:15 -0800, vip152.sybase.com
Lines: 48
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29763
Article PK: 78995


On 07-Dec-2010 17:51, Jon Saxton wrote:
> Using CT/CS library calls within a C++ program I have to
> read a certain table every few seconds and get whatever rows
> have been added. I am wondering what is the best approach.
> There is no primary key, indeed no index at all. Each row
> has a timestamp (datetime) so I could select for timestamps
>> last one but I am worried that this might be equivalent to
> re-reading the entire table each time. So I thought about
> maintaining an open read-only cursor on the table and using
> that to fetch the new rows each time but the documentation
> says that cursor performance is not so good. Nevertheless
> is the cursor approach better than a select based on
> timestamp?
>
> Opinions?

This looks like a case where 'readpast' could be useful. This skips over
anythign locked instead of waiting for the lock as it would norally do.
For example, to pick the first accessible item:

select top 1 *
from your_table readpast
where ...filter criteria...
order by ...sort criteria...

You might want to ensure a particular index is always used so as to
guarantee the most efficient access path.


HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


jobless Posted on 2010-12-08 00:34:23.0Z
Sender: 1749.4cfeaa31.1804289383@sybase.com
From: jobless
Newsgroups: sybase.public.ase.general
Subject: Re: Best way to read an event log table?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4cfed28f.2221.1681692777@sybase.com>
References: <4cfe661f.2a3.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 7 Dec 2010 16:34:23 -0800
X-Trace: forums-1-dub 1291768463 10.22.241.41 (7 Dec 2010 16:34:23 -0800)
X-Original-Trace: 7 Dec 2010 16:34:23 -0800, 10.22.241.41
Lines: 35
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29771
Article PK: 78998

Jon, not expert in cursors, hv some exp with cursors in 11.x
and 12.x; Typically the cursor is insensitive and often
involves worktables, how would this help your case when the
new rows are added to the tables? Is the cursor resultset
automatically updated to reflect the new inserts!

Sometimes back i was reading on the 15.x doc, the default
semi-sensitive cursors may or may not reflect the updated
data(if i recall right from the documents). For new rows to
become part of cursort result-set you might have to close
and re-open the cursor, how would cursor be faster (other
than the code is parsed and compiled), it still would work
same as seperate select(I might be wrong but want to check).

Is there some CT-LIB cursor-sepcific call which does
magically appends new rows to the resultset?

if a non-indexed table with few hundread thousand rows, how
long the table scan take as you are accepting the results in
few seconds...

> Using CT/CS library calls within a C++ program I have to
> read a certain table every few seconds and get whatever
> rows have been added. I am wondering what is the best
> approach. There is no primary key, indeed no index at
> all. Each row has a timestamp (datetime) so I could
> > select for timestamps last one but I am worried that
> this might be equivalent to re-reading the entire table
> each time. So I thought about maintaining an open
> read-only cursor on the table and using that to fetch the
> new rows each time but the documentation says that cursor
> performance is not so good. Nevertheless is the cursor
> approach better than a select based on timestamp?
>
> Opinions?


Jon Saxton Posted on 2010-12-08 15:23:59.0Z
Sender: 53d5.4cff9663.1804289383@sybase.com
From: Jon Saxton
Newsgroups: sybase.public.ase.general
Subject: Re: Best way to read an event log table?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4cffa30f.5702.1681692777@sybase.com>
References: <4cfed28f.2221.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 8 Dec 2010 07:23:59 -0800
X-Trace: forums-1-dub 1291821839 10.22.241.41 (8 Dec 2010 07:23:59 -0800)
X-Original-Trace: 8 Dec 2010 07:23:59 -0800, 10.22.241.41
Lines: 73
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29777
Article PK: 79005

Thanks for the feedback.

I do not have timing information for the table scan. The
live database is in a financial institution in a distant
country so there some issues in running any sort of test
(think politics, security, logistics). All I can say is
that the current code (which does the table scan based on
timestamp comparison) is reported to impact the server.
That is very vague and may not even be accurate. However
your point is valid and I shall consider it.

I had noticed that there is a difference between sensitive
and insensitive cursors. For what it is worth I am using
the 15.5 libraries and declaring the cursor as
semi-sensitive. My reading of the documentation is that new
rows would be included in the result set. Of course that is
subject to experiment.

Another possibility would be a scrollable cursor. If I
really did need to close and reopen the cursor then a bit of
simple housekeeping would let me position to the row I
wanted. However it is not altogether clear from the manual
what level of support for scrollable cursors is offered.
The documentation for ct_cursor() and ct_scroll_fetch()
suggests that it may work and even provides an example. The
caveat is that the example uses an insensitive cursor so it
may be that semi-sensitive and scrollable don't play
together very nicely. Again, all subject to experiment.

I am a few days away from writing the code. Regardless of
what happens I need to process the rows and I decided that
it makes sense to do the classes and methods for that first.
I am just trying to get advice on the database side in the
meantime.

> Jon, not expert in cursors, hv some exp with cursors in
> 11.x and 12.x; Typically the cursor is insensitive and
> often involves worktables, how would this help your case
> when the new rows are added to the tables? Is the cursor
> resultset automatically updated to reflect the new
> inserts!
>
> Sometimes back i was reading on the 15.x doc, the default
> semi-sensitive cursors may or may not reflect the updated
> data(if i recall right from the documents). For new rows
> to become part of cursort result-set you might have to
> close and re-open the cursor, how would cursor be faster
> (other than the code is parsed and compiled), it still
> would work same as seperate select(I might be wrong but
> want to check).
>
> Is there some CT-LIB cursor-sepcific call which does
> magically appends new rows to the resultset?
>
> if a non-indexed table with few hundread thousand rows,
> how long the table scan take as you are accepting the
> results in few seconds...
>
> > Using CT/CS library calls within a C++ program I have to
> > read a certain table every few seconds and get whatever
> > rows have been added. I am wondering what is the best
> > approach. There is no primary key, indeed no index at
> > all. Each row has a timestamp (datetime) so I could
> > > select for timestamps last one but I am worried that
> > this might be equivalent to re-reading the entire table
> > each time. So I thought about maintaining an open
> > read-only cursor on the table and using that to fetch
> > the new rows each time but the documentation says that
> > cursor performance is not so good. Nevertheless is the
> > cursor approach better than a select based on timestamp?
> >
> > Opinions?