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.

TABLE datatype

8 posts in Product Futures Discussion Last posting was on 2002-09-05 11:09:53.0Z
Sybase_User Posted on 2002-06-23 16:48:35.0Z
From: Sybase_User
Date: Sun, 23 Jun 2002 12:48:35 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: TABLE datatype
Message-ID: <0BF0C712D5CED5DE005C56D985256BE1.005C56FA85256BE1@webforums>
Lines: 6
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:413
Article PK: 93584

I *WISH* Sybase can implement a new datatype which can temporary hold a set
of result for later processing. Much like ARRAY in C
It can reduce the contention for creating #temp table in tempdb.

For your information, it has been implemented in MS SQL Server 2000, they
called it TABLE datatype.


J Posted on 2002-06-26 12:52:46.0Z
From: "J" <rs2@rocketmail.com>
References: <0BF0C712D5CED5DE005C56D985256BE1.005C56FA85256BE1@webforums>
Subject: Re: TABLE datatype
Date: Wed, 26 Jun 2002 14:52:46 +0200
Lines: 25
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <JdRoxHRHCHA.654@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 158.76.48.122
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:405
Article PK: 93576

I wish sybase would fix tempdb, to allow it to BE memory only, and only use
disk if / when overflowing.
That would make this request unnecesarry.
I understand there are moves to address this, but the exact details are
scarce.

MS introduced the tempdb in memory option long before 2000.
I reckon their table variable will use heap space, which may be dynamically
allocated.
No need for this in my opinion if the tempdb issues are sorted.

J

<Sybase_User> wrote in message
news:0BF0C712D5CED5DE005C56D985256BE1.005C56FA85256BE1@webforums...
> I *WISH* Sybase can implement a new datatype which can temporary hold a
set
> of result for later processing. Much like ARRAY in C
> It can reduce the contention for creating #temp table in tempdb.
>
> For your information, it has been implemented in MS SQL Server 2000, they
> called it TABLE datatype.


Sethu M Posted on 2002-07-17 04:08:35.0Z
From: "Sethu M" <sethu@sybase.com>
References: <0BF0C712D5CED5DE005C56D985256BE1.005C56FA85256BE1@webforums> <JdRoxHRHCHA.654@forums.sybase.com>
Subject: Re: TABLE datatype
Date: Tue, 16 Jul 2002 21:08:35 -0700
Lines: 32
MIME-Version: 1.0
Content-Type: text/plain; charset="Windows-1252"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
Message-ID: <mvBIBlULCHA.1004@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
NNTP-Posting-Host: 10.22.120.60
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:392
Article PK: 93560

why can't you create tempdb on ramdisk ?

sethu

"J" <rs2@rocketmail.com> wrote in message
news:JdRoxHRHCHA.654@forums.sybase.com...
I wish sybase would fix tempdb, to allow it to BE memory only, and only use
disk if / when overflowing.
That would make this request unnecesarry.
I understand there are moves to address this, but the exact details are
scarce.

MS introduced the tempdb in memory option long before 2000.
I reckon their table variable will use heap space, which may be dynamically
allocated.
No need for this in my opinion if the tempdb issues are sorted.

J

<Sybase_User> wrote in message
news:0BF0C712D5CED5DE005C56D985256BE1.005C56FA85256BE1@webforums...
> I *WISH* Sybase can implement a new datatype which can temporary hold a
set
> of result for later processing. Much like ARRAY in C
> It can reduce the contention for creating #temp table in tempdb.
>
> For your information, it has been implemented in MS SQL Server 2000, they
> called it TABLE datatype.


j Posted on 2002-07-17 15:07:52.0Z
From: J
Date: Wed, 17 Jul 2002 11:07:52 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: TABLE datatype
Message-ID: <8EFF8C6DF2F2833C00531E0685256BF9.001A76DF85256BF9@webforums>
References: <0BF0C712D5CED5DE005C56D985256BE1.005C56FA85256BE1@webforums> <JdRoxHRHCHA.654@forums.sybase.com> <mvBIBlULCHA.1004@forums.sybase.com>
Lines: 56
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:387
Article PK: 93555

My point is, when tempdb has a named cache of the same size of the tempdb
database,
why do we bother to flush anything to disk. ?
..and why bother to have a cache replacement strategy,
although I guess relaxed LRU will take care of that one though.

If Cost of ownership, and ease of use is really important, this could
actually count,
as double buffering is not real efficient use of available hardware.
I fully understand that most core customers has a whole team of DBA's armed
with scripts and all kinds of usefull stuff,
but none of the smaller clients are not amused... and they are voting with
their feet.

For some curious reason Sybase does NOT see this as valid point.

Until Sybase addresses this issue I see some use for a table variable.

If tempdb was fully in memory, and did not flush to disk for very commit
then # tables WILL BE a memory structure,
which would close this issue.
I agree that with dsync we can cause the write to remain buffered,
but that increases complexity, and puts us at the mercy of available memory
for use by the Filesystem.
Have you seen how tempdb on buffered non-dsync'ed devices (unix) can screw
up when someone else moves / creates files on the same box ?
Also What about tempdb on NT ? How do I buffer that, or do we just not
bother to be competitive ?
...yes I've created NT in-memory devices for tempdb, which raises the
double buffering issue again.


For the lack of a more elegant solution:
Why can't we discard the device-I/O if the tempdb named cache is the same
size as tempdb ?

J

>why can't you create tempdb on ramdisk ?
>
>sethu

>> "J" <rs2@rocketmail.com> wrote in message
>> news:JdRoxHRHCHA.654@forums.sybase.com...
>> I wish sybase would fix tempdb, to allow it to BE memory only, and only
use
>> disk if / when overflowing.
>> That would make this request unnecesarry.
>> I understand there are moves to address this, but the exact details are
>> scarce.
>>
>> MS introduced the tempdb in memory option long before 2000.
>> I reckon their table variable will use heap space, which may be
dynamically
>> allocated.
>> No need for this in my opinion if the tempdb issues are sorted.
>>
>> J


Mike Harrold Posted on 2002-07-30 16:03:32.0Z
Subject: Re: TABLE datatype
References: <0BF0C712D5CED5DE005C56D985256BE1.005C56FA85256BE1@webforums> <JdRoxHRHCHA.654@forums.sybase.com> <mvBIBlULCHA.1004@forums.sybase.com> <8EFF8C6DF2F2833C00531E0685256BF9.001A76DF85256BF9@webforums>
X-Newsreader: trn 4.0-test75 (Feb 13, 2001)
From: ao@shell.core.com (Mike Harrold)
Originator: ao@shell.core.com (Mike Harrold)
Message-ID: <LrtWnK#NCHA.306@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Tue, 30 Jul 2002 12:03:32 -0400
Lines: 65
NNTP-Posting-Host: shell.core.com 169.207.1.89
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:355
Article PK: 93525

In article <8EFF8C6DF2F2833C00531E0685256BF9.001A76DF85256BF9@webforums>,

<J> wrote:
>
>My point is, when tempdb has a named cache of the same size of the tempdb
>database,
>why do we bother to flush anything to disk. ?
>..and why bother to have a cache replacement strategy,
>although I guess relaxed LRU will take care of that one though.
>
>If Cost of ownership, and ease of use is really important, this could
>actually count,
>as double buffering is not real efficient use of available hardware.
>I fully understand that most core customers has a whole team of DBA's armed
>with scripts and all kinds of usefull stuff,
>but none of the smaller clients are not amused... and they are voting with
>their feet.
>
>For some curious reason Sybase does NOT see this as valid point.
>
>Until Sybase addresses this issue I see some use for a table variable.
>
>If tempdb was fully in memory, and did not flush to disk for very commit
>then # tables WILL BE a memory structure,
>which would close this issue.
>I agree that with dsync we can cause the write to remain buffered,
>but that increases complexity, and puts us at the mercy of available memory
>for use by the Filesystem.
>Have you seen how tempdb on buffered non-dsync'ed devices (unix) can screw
>up when someone else moves / creates files on the same box ?
>Also What about tempdb on NT ? How do I buffer that, or do we just not
>bother to be competitive ?
>...yes I've created NT in-memory devices for tempdb, which raises the
>double buffering issue again.
>
>
>For the lack of a more elegant solution:
>Why can't we discard the device-I/O if the tempdb named cache is the same
>size as tempdb ?
>
>J

Umm, what Sethu was suggesting was to place tempdb on a ramdisk, ie _IN
MEMORY_ so that there is no disk I/O.

Which accomplishes just what you want...

/Mike

>
>>why can't you create tempdb on ramdisk ?
>>
>>sethu
>
>>> "J" <rs2@rocketmail.com> wrote in message
>>> news:JdRoxHRHCHA.654@forums.sybase.com...
>>> I wish sybase would fix tempdb, to allow it to BE memory only, and only
>use
>>> disk if / when overflowing.
>>> That would make this request unnecesarry.
>>> I understand there are moves to address this, but the exact details are
>>> scarce.
>>>
>>> MS introduced the tempdb in memory option long before 2000.
>>> I reckon their table variable will use heap space, which may be
>dynamically
>>> allocated.
>>> No need for this in my opinion if the tempdb issues are sorted.
>>>
>>> J


J Posted on 2002-08-30 13:34:50.0Z
From: J <rs2@rocketmail.com>
Date: Fri, 30 Aug 2002 09:34:50 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: TABLE datatype
Message-ID: <EC0ED94F3344CD7A004A99C585256C25.005BC44685256C06@webforums>
References: <0BF0C712D5CED5DE005C56D985256BE1.005C56FA85256BE1@webforums> <JdRoxHRHCHA.654@forums.sybase.com> <mvBIBlULCHA.1004@forums.sybase.com> <8EFF8C6DF2F2833C00531E0685256BF9.001A76DF85256BF9@webforums> <LrtWnK#NCHA.306@forums.sybase.com>
Lines: 47
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:311
Article PK: 93481

>Which accomplishes just what you want...

No it does NOT quite accomplish what I stated, Mike.

What Sethu proposed would waste CPU cycles performing a "bogus I/O", only
to memory, and in the new ASE 12 schema would put your task to sleep to
perform a memory I/O asyncronously. Not QUITE what you or I want, Eh ?

J/

>Umm, what Sethu was suggesting was to place tempdb on a
>ramdisk, ie _IN MEMORY_ so that there is no disk I/O.
>
>Which accomplishes just what you want....
>
>/Mike

Re: TABLE datatype

In article <8EFF8C6DF2F2833C00531E0685256BF9.001A76DF85256BF9@webforums>,
wrote: > >My point is, when tempdb has a named cache of the same size of
the tempdb >database, >why do we bother to flush anything to disk. ? >..and
why bother to have a cache replacement strategy, >although I guess relaxed
LRU will take care of that one though. > >If Cost of ownership, and ease of
use is really important, this could >actually count, >as double buffering
is not real efficient use of available hardware. >I fully understand that
most core customers has a whole team of DBA's armed >with scripts and all
kinds of usefull stuff, >but none of the smaller clients are not amused...
and they are voting with >their feet. > >For some curious reason Sybase
does NOT see this as valid point. > >Until Sybase addresses this issue I
see some use for a table variable. > >If tempdb was fully in memory, and
did not flush to disk for very commit >then # tables WILL BE a memory
structure, >which would close this issue. >I agree that with dsyn
c we can cause the write to remain buffered, >but that increases
complexity, and puts us at the mercy of available memory >for use by the
Filesystem. >Have you seen how tempdb on buffered non-dsync'ed devices
(unix) can screw >up when someone else moves / creates files on the same
box ? >Also What about tempdb on NT ? How do I buffer that, or do we just
not >bother to be competitive ? >...yes I've created NT in-memory devices
for tempdb, which raises the >double buffering issue again. > > >For the
lack of a more elegant solution: >Why can't we discard the device-I/O if
the tempdb named cache is the same >size as tempdb ? > >J


Mike Harrold Posted on 2002-09-03 19:08:35.0Z
Subject: Re: TABLE datatype
References: <0BF0C712D5CED5DE005C56D985256BE1.005C56FA85256BE1@webforums> <8EFF8C6DF2F2833C00531E0685256BF9.001A76DF85256BF9@webforums> <LrtWnK#NCHA.306@forums.sybase.com> <EC0ED94F3344CD7A004A99C585256C25.005BC44685256C06@webforums>
X-Newsreader: trn 4.0-test75 (Feb 13, 2001)
From: ao@shell.core.com (Mike Harrold)
Originator: ao@shell.core.com (Mike Harrold)
Message-ID: <J#CTN13UCHA.74@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Tue, 03 Sep 2002 15:08:35 -0400
Lines: 69
NNTP-Posting-Host: shell.core.com 169.207.1.89
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:309
Article PK: 93478

In article <EC0ED94F3344CD7A004A99C585256C25.005BC44685256C06@webforums>,

J <rs2@rocketmail.com> wrote:
>
>>Which accomplishes just what you want...
>
>No it does NOT quite accomplish what I stated, Mike.
>
>What Sethu proposed would waste CPU cycles performing a "bogus I/O", only
>to memory, and in the new ASE 12 schema would put your task to sleep to
>perform a memory I/O asyncronously. Not QUITE what you or I want, Eh ?
>
>J/

Ok, I'll give... it doesn't give you _exactly_ what you want, but it
comes pretty close. It's also available now and doesn't require an
enhancement to accomplish. I don't think anyone (including Sethu) thinks
that your TABLE datatype is a bad idea, it's just a matter of
priorities, right?

Regards,

/Mike


>
>
>>Umm, what Sethu was suggesting was to place tempdb on a
>>ramdisk, ie _IN MEMORY_ so that there is no disk I/O.
>>
>>Which accomplishes just what you want....
>>
>>/Mike
>
>
>
>Re: TABLE datatype
>
>In article <8EFF8C6DF2F2833C00531E0685256BF9.001A76DF85256BF9@webforums>,
>wrote: > >My point is, when tempdb has a named cache of the same size of
>the tempdb >database, >why do we bother to flush anything to disk. ? >..and
>why bother to have a cache replacement strategy, >although I guess relaxed
>LRU will take care of that one though. > >If Cost of ownership, and ease of
>use is really important, this could >actually count, >as double buffering
>is not real efficient use of available hardware. >I fully understand that
>most core customers has a whole team of DBA's armed >with scripts and all
>kinds of usefull stuff, >but none of the smaller clients are not amused...
>and they are voting with >their feet. > >For some curious reason Sybase
>does NOT see this as valid point. > >Until Sybase addresses this issue I
>see some use for a table variable. > >If tempdb was fully in memory, and
>did not flush to disk for very commit >then # tables WILL BE a memory
>structure, >which would close this issue. >I agree that with dsyn
>c we can cause the write to remain buffered, >but that increases
>complexity, and puts us at the mercy of available memory >for use by the
>Filesystem. >Have you seen how tempdb on buffered non-dsync'ed devices
>(unix) can screw >up when someone else moves / creates files on the same
>box ? >Also What about tempdb on NT ? How do I buffer that, or do we just
>not >bother to be competitive ? >...yes I've created NT in-memory devices
>for tempdb, which raises the >double buffering issue again. > > >For the
>lack of a more elegant solution: >Why can't we discard the device-I/O if
>the tempdb named cache is the same >size as tempdb ? > >J
>
>


J Posted on 2002-09-05 11:09:53.0Z
From: J <rs2@rocketmail.com>
Date: Thu, 5 Sep 2002 07:09:53 -0400
Newsgroups: sybase.public.ase.product_futures_discussion
Subject: Re: TABLE datatype
Message-ID: <B4432DA5D302A624003D546A85256C2B.006C9AD785256C29@webforums>
References: <0BF0C712D5CED5DE005C56D985256BE1.005C56FA85256BE1@webforums> <8EFF8C6DF2F2833C00531E0685256BF9.001A76DF85256BF9@webforums> <LrtWnK#NCHA.306@forums.sybase.com> <EC0ED94F3344CD7A004A99C585256C25.005BC44685256C06@webforums> <J#CTN13UCHA.74@forums.sybase.com>
Lines: 50
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!webforums.sybase.com!news
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:308
Article PK: 93480

This started as a posting about a table datatype by some Sybase_user (not
me).
Sethu responded saying that a #table does effectively provide exactly that.
I agreed with his statement, except that the current tempdb-#table
implimentation is not quite watertight in my opinion, and cannot be
compared to a memory I/O.

I agree that the current work-around will do the trick, but at a cost in
wasted memory or performance or both....
I agree that this question then comes down to how much the stated changes
will cost in terms of engineering-time.

Furthermore, even if nothing is done, can one at least change the tempdb
device attributed (when "in-RAM") to make the "disk" I/O to the RAM-device
synchronous again, which will save us the context switch while performing
the memory read/write ?

In the unlikely event of these changes actually being implemented, it may
still be useful for 32-bit sites to use the current aproach, to effectively
allow them to address more memory.

The point here is to clarify the argument, not to punt a proposed solution.
(Which I got from someone else)

Cheers

J

> Ok, I'll give... it doesn't give you _exactly_ what you want,
> but it comes pretty close. It's also available now and doesn't
> require an enhancement to accomplish. I don't think anyone
> (including Sethu) thinks
> that your TABLE datatype is a bad idea,
> it's just a matter of priorities, right?
>
> Regards,
>
>/Mike

>>In article , J wrote:
>>> Which accomplishes just what you want...

>> No it does NOT quite accomplish what I stated, Mike.
>> What Sethu proposed would waste CPU cycles performing
>> a "bogus I/O", only to memory,
>> and in the new ASE 12 schema would put your task to sleep to
>> perform a memory I/O asyncronously. Not QUITE what you or I want, Eh ?
>>J/