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.

select into query

2 posts in General Discussion Last posting was on 2012-11-01 16:44:13.0Z
Raj Posted on 2012-10-28 02:23:18.0Z
Sender: 5145.508c9569.1804289383@sybase.com
From: Raj
Newsgroups: sybase.public.ase.general
Subject: select into query
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <508c9716.5187.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 27 Oct 2012 19:23:18 -0700
X-Trace: forums-1-dub 1351390998 172.20.134.41 (27 Oct 2012 19:23:18 -0700)
X-Original-Trace: 27 Oct 2012 19:23:18 -0700, 172.20.134.41
Lines: 14
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31475
Article PK: 74364

Hi,

select into from a table(note: it has IMAGE column)
which has 2.3 million rows and 13 GB in size, which takes
around 1 hr. Another select into from a table(NO image
column) which has 22 million rows and 13 GB in size, which
takes 15 minutes. It's on the same database and server.

Why is this difference ? Is this expected from a table which
has 'image' column ? Anything we can do to run faster ?


Thanks,
Raj


Bret Halford Posted on 2012-11-01 16:44:13.0Z
From: Bret Halford <bret.halford@sap.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:16.0) Gecko/20121010 Thunderbird/16.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: select into query
References: <508c9716.5187.1681692777@sybase.com>
In-Reply-To: <508c9716.5187.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: <5092a6dd$1@forums-1-dub>
Date: 1 Nov 2012 08:44:13 -0800
X-Trace: forums-1-dub 1351788253 172.20.134.152 (1 Nov 2012 08:44:13 -0800)
X-Original-Trace: 1 Nov 2012 08:44:13 -0800, vip152.sybase.com
Lines: 62
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31477
Article PK: 74365


On 10/27/2012 8:23 PM, Raj wrote:
> Hi,
>
> select into from a table(note: it has IMAGE column)
> which has 2.3 million rows and 13 GB in size, which takes
> around 1 hr. Another select into from a table(NO image
> column) which has 22 million rows and 13 GB in size, which
> takes 15 minutes. It's on the same database and server.
>
> Why is this difference ? Is this expected from a table which
> has 'image' column ? Anything we can do to run faster ?
>
>
> Thanks,
> Raj
>

Hi Raj,

SELECT INTO without TEXT/IMAGE (LOB) just has to copy
rows from one page to another as fast as it can.

With LOB columns, the row has to be copied
over, then pages allocated for the LOB value,
then the textptr value in the row has to be updated
with the page number of the first page allocated for
the LOB value. So there is more work involved.

I don't have any performance benchmarking data for
this, so I don't know if the difference in timing
you are experiencing is typical.

You might be able to squeeze out a bit more performance
by configuring "preallocated extents" to 31.
You can also create user-defined segments on device fragments
that are currently empty and specifying those segments
in the SELECT INTO command, which can speed up the allocation
process.

[why: each allocation unit holds 32 extents. The first
extent is "short", only 7 pages available for data as
the first page holds the allocation page. SELECT INTO
uses a process called large-scale allocation (LSA), which
can't use short extents. ASE uses a structure called the
GAM during allocation, the GAM contains one bit for each
allocation unit, the bit is set on when the extent is
completely full. The allocation process knows it can
skip that allocation unit when the bit is on. However,
if only the short extent is free, the bit will still
be off and LSA will look for free extents on that allocation
unit. If the database contains a lot of allocation units
with only the short extent available, LSA spends a lot of
time looking through these allocation units every time it
need to find a new allocation unit with free extents. By
pointing the process to empty devices, the number of
such all-but-full allocation units is minimized, and allocation
can get a full 31 extents one each allocation unit before
having to repeat the process of searching for a new allocation
unit with free space.]

Cheers,
-bret