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.

Delete from a table is taking too much time

7 posts in General Discussion Last posting was on 2006-07-07 17:07:38.0Z
Luis Porras Posted on 2006-07-06 13:48:13.0Z
From: "Luis Porras" <luis.porras@dakotaimaging.com>
Newsgroups: ianywhere.public.general
Subject: Delete from a table is taking too much time
Lines: 20
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1807
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1807
X-Original-NNTP-Posting-Host: 200.91.73.205
Message-ID: <44ad133e$1@forums-2-dub>
X-Original-Trace: 6 Jul 2006 06:42:22 -0700, 200.91.73.205
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 6 Jul 2006 06:42:23 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 6 Jul 2006 06:48:13 -0700
X-Trace: forums-1-dub 1152193693 10.22.108.75 (6 Jul 2006 06:48:13 -0700)
X-Original-Trace: 6 Jul 2006 06:48:13 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5481
Article PK: 1527

Hi,

I have some history tables that I want to clean-up, these tables are small
(+691000, +325000, +450000 records). But deleting from them are taking a
long time... I'm using a datetime column to perform the delete, there's no
index over these columns. I'm using an statement like this:

delete from table where date_column between @from_date and @to_date

Environment:

- ASA 8.0.3.5307 on Windows
- 800MB cache
- DB 1.2Gb

Thanks!

~Luis


Breck Carter [Team iAnywhere] Posted on 2006-07-06 14:05:59.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Delete from a table is taking too much time
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <975qa29qpbqa9vgc5k61o03ov35mq2csj3@4ax.com>
References: <44ad133e$1@forums-2-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 6 Jul 2006 07:05:59 -0700
X-Trace: forums-1-dub 1152194759 64.7.134.118 (6 Jul 2006 07:05:59 -0700)
X-Original-Trace: 6 Jul 2006 07:05:59 -0700, bcarter.sentex.ca
Lines: 56
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5482
Article PK: 1528

If the rows were originally inserted in (roughly) date_column order,
creating a CLUSTERED index on that column may make the "range query"
(e.g., BETWEEN) run much faster, without even having to do a
REORGANIZE TABLE after you create the index. Clustered indexes were
introduced in 8.0.2.

Also, if your initial range is very large, try using separate DELETE
statements with smaller ranges followed by COMMIT statements to keep
the rollback log from growing the main .DB file enormously.

Defragmentig the hard drive, and defragmenting the database itself,
are always good ideas, as is having a sackload of free space in the
database file (ALTER DBSPACE SYSTEM ADD whateverM).

Moving to Version 9 is also an excellent idea, performance-wise, and
then you can make use of Chapter 10 Tuning in my book :)

There is a whitepaper on iAnywhere.com about "high throughput
databases" that might interest you, and I think there is a Techwave
session on the same subject (same author?).

Do this Google Web search:

high throughput site:ianywhere.com

Breck

On 6 Jul 2006 06:48:13 -0700, "Luis Porras"

<luis.porras@dakotaimaging.com> wrote:

>Hi,
>
>I have some history tables that I want to clean-up, these tables are small
>(+691000, +325000, +450000 records). But deleting from them are taking a
>long time... I'm using a datetime column to perform the delete, there's no
>index over these columns. I'm using an statement like this:
>
>delete from table where date_column between @from_date and @to_date
>
>Environment:
>
>- ASA 8.0.3.5307 on Windows
>- 800MB cache
>- DB 1.2Gb
>
>Thanks!
>
>~Luis
>

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com


Luis Porras Posted on 2006-07-07 15:21:15.0Z
From: "Luis Porras" <luis.porras@dakotaimaging.com>
Newsgroups: ianywhere.public.general
References: <44ad133e$1@forums-2-dub> <975qa29qpbqa9vgc5k61o03ov35mq2csj3@4ax.com>
Subject: Re: Delete from a table is taking too much time
Lines: 77
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1807
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1807
X-Original-NNTP-Posting-Host: 200.91.73.205
Message-ID: <44ae7a86@forums-2-dub>
X-Original-Trace: 7 Jul 2006 08:15:18 -0700, 200.91.73.205
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 7 Jul 2006 08:15:18 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 7 Jul 2006 08:21:15 -0700
X-Trace: forums-1-dub 1152285675 10.22.108.75 (7 Jul 2006 08:21:15 -0700)
X-Original-Trace: 7 Jul 2006 08:21:15 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5486
Article PK: 1531

Looks like I found my problem:

Looking at the database properties, I found that the database version is 7.0
and all of the capabilities were disabled except one but the database server
is 8.0.3xxx. So, I proceed to run the upgrade option inside the same Sybase
Central. After run and reboot, the database remains with the same version,
so I unloaded the data, create a new database and reload the data and now
everything (including my delete script) is running faster.

Now, why the upgrade option in sybase central didn't work?

Thanks!

"Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com> wrote in
message news:975qa29qpbqa9vgc5k61o03ov35mq2csj3@4ax.com...
> If the rows were originally inserted in (roughly) date_column order,
> creating a CLUSTERED index on that column may make the "range query"
> (e.g., BETWEEN) run much faster, without even having to do a
> REORGANIZE TABLE after you create the index. Clustered indexes were
> introduced in 8.0.2.
>
> Also, if your initial range is very large, try using separate DELETE
> statements with smaller ranges followed by COMMIT statements to keep
> the rollback log from growing the main .DB file enormously.
>
> Defragmentig the hard drive, and defragmenting the database itself,
> are always good ideas, as is having a sackload of free space in the
> database file (ALTER DBSPACE SYSTEM ADD whateverM).
>
> Moving to Version 9 is also an excellent idea, performance-wise, and
> then you can make use of Chapter 10 Tuning in my book :)
>
> There is a whitepaper on iAnywhere.com about "high throughput
> databases" that might interest you, and I think there is a Techwave
> session on the same subject (same author?).
>
> Do this Google Web search:
>
> high throughput site:ianywhere.com
>
> Breck
>
> On 6 Jul 2006 06:48:13 -0700, "Luis Porras"
> <luis.porras@dakotaimaging.com> wrote:
>
> >Hi,
> >
> >I have some history tables that I want to clean-up, these tables are
small
> >(+691000, +325000, +450000 records). But deleting from them are taking a
> >long time... I'm using a datetime column to perform the delete, there's
no
> >index over these columns. I'm using an statement like this:
> >
> >delete from table where date_column between @from_date and @to_date
> >
> >Environment:
> >
> >- ASA 8.0.3.5307 on Windows
> >- 800MB cache
> >- DB 1.2Gb
> >
> >Thanks!
> >
> >~Luis
> >
>
> --
> Breck Carter [Team iAnywhere]
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
> The book:
http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
> breck.carter@risingroad.com


Glenn Paulley Posted on 2006-07-07 15:34:01.0Z
Newsgroups: ianywhere.public.general
Subject: Re: Delete from a table is taking too much time
From: Glenn Paulley <paulley@ianywhere.com>
References: <44ad133e$1@forums-2-dub> <975qa29qpbqa9vgc5k61o03ov35mq2csj3@4ax.com> <44ae7a86@forums-2-dub>
Organization: iAnywhere Solutions
Message-ID: <Xns97F974A85D731paulleyianywherecom@10.22.241.106>
User-Agent: Xnews/5.04.25
X-Original-NNTP-Posting-Host: paulley-t41.sybase.com
X-Original-Trace: 7 Jul 2006 08:28:04 -0700, paulley-t41.sybase.com
Lines: 104
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 7 Jul 2006 08:28:05 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 7 Jul 2006 08:34:01 -0700
X-Trace: forums-1-dub 1152286441 10.22.108.75 (7 Jul 2006 08:34:01 -0700)
X-Original-Trace: 7 Jul 2006 08:34:01 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5487
Article PK: 1533

"Upgrade" does not perform a complete unload/reload after initializing a
new database. For the most part, "Upgrade" merely updates the database's
catalog to look like an 8.0.3 database.

Glenn

"Luis Porras" <luis.porras@dakotaimaging.com> wrote in
news:44ae7a86@forums-2-dub:

> Looks like I found my problem:
>
> Looking at the database properties, I found that the database version
> is 7.0 and all of the capabilities were disabled except one but the
> database server is 8.0.3xxx. So, I proceed to run the upgrade option
> inside the same Sybase Central. After run and reboot, the database
> remains with the same version, so I unloaded the data, create a new
> database and reload the data and now everything (including my delete
> script) is running faster.
>
> Now, why the upgrade option in sybase central didn't work?
>
> Thanks!
>
>
> "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com> wrote
> in message news:975qa29qpbqa9vgc5k61o03ov35mq2csj3@4ax.com...
>> If the rows were originally inserted in (roughly) date_column order,
>> creating a CLUSTERED index on that column may make the "range query"
>> (e.g., BETWEEN) run much faster, without even having to do a
>> REORGANIZE TABLE after you create the index. Clustered indexes were
>> introduced in 8.0.2.
>>
>> Also, if your initial range is very large, try using separate DELETE
>> statements with smaller ranges followed by COMMIT statements to keep
>> the rollback log from growing the main .DB file enormously.
>>
>> Defragmentig the hard drive, and defragmenting the database itself,
>> are always good ideas, as is having a sackload of free space in the
>> database file (ALTER DBSPACE SYSTEM ADD whateverM).
>>
>> Moving to Version 9 is also an excellent idea, performance-wise, and
>> then you can make use of Chapter 10 Tuning in my book :)
>>
>> There is a whitepaper on iAnywhere.com about "high throughput
>> databases" that might interest you, and I think there is a Techwave
>> session on the same subject (same author?).
>>
>> Do this Google Web search:
>>
>> high throughput site:ianywhere.com
>>
>> Breck
>>
>> On 6 Jul 2006 06:48:13 -0700, "Luis Porras"
>> <luis.porras@dakotaimaging.com> wrote:
>>
>> >Hi,
>> >
>> >I have some history tables that I want to clean-up, these tables are
> small
>> >(+691000, +325000, +450000 records). But deleting from them are
>> >taking a long time... I'm using a datetime column to perform the
>> >delete, there's
> no
>> >index over these columns. I'm using an statement like this:
>> >
>> >delete from table where date_column between @from_date and @to_date
>> >
>> >Environment:
>> >
>> >- ASA 8.0.3.5307 on Windows
>> >- 800MB cache
>> >- DB 1.2Gb
>> >
>> >Thanks!
>> >
>> >~Luis
>> >
>>
>> --
>> Breck Carter [Team iAnywhere]
>> RisingRoad SQL Anywhere and MobiLink Professional Services
>> www.risingroad.com
>> The book:
> http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
>> breck.carter@risingroad.com
>
>
>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

Register now for TechWave 2006 August 6-10 in Las Vegas. New! 2-day
and 4-day options for SQL Anywhere technical training. Sessions will
focus on data management, data movement, and SQL Anywhere 10. Visit
www.sybase.com/techwave

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all


Luis Porras Posted on 2006-07-07 15:35:42.0Z
From: "Luis Porras" <luis.porras@dakotaimaging.com>
Newsgroups: ianywhere.public.general
References: <44ad133e$1@forums-2-dub> <975qa29qpbqa9vgc5k61o03ov35mq2csj3@4ax.com> <44ae7a86@forums-2-dub> <Xns97F974A85D731paulleyianywherecom@10.22.241.106>
Subject: Re: Delete from a table is taking too much time
Lines: 112
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1807
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1807
NNTP-Posting-Host: 200.91.73.205
X-Original-NNTP-Posting-Host: 200.91.73.205
Message-ID: <44ae7f4e$1@forums-1-dub>
Date: 7 Jul 2006 08:35:42 -0700
X-Trace: forums-1-dub 1152286542 200.91.73.205 (7 Jul 2006 08:35:42 -0700)
X-Original-Trace: 7 Jul 2006 08:35:42 -0700, 200.91.73.205
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5488
Article PK: 1535

So, the procedure I did was the right one?

"Glenn Paulley" <paulley@ianywhere.com> wrote in message
news:Xns97F974A85D731paulleyianywherecom@10.22.241.106...
> "Upgrade" does not perform a complete unload/reload after initializing a
> new database. For the most part, "Upgrade" merely updates the database's
> catalog to look like an 8.0.3 database.
>
> Glenn
>
> "Luis Porras" <luis.porras@dakotaimaging.com> wrote in
> news:44ae7a86@forums-2-dub:
>
> > Looks like I found my problem:
> >
> > Looking at the database properties, I found that the database version
> > is 7.0 and all of the capabilities were disabled except one but the
> > database server is 8.0.3xxx. So, I proceed to run the upgrade option
> > inside the same Sybase Central. After run and reboot, the database
> > remains with the same version, so I unloaded the data, create a new
> > database and reload the data and now everything (including my delete
> > script) is running faster.
> >
> > Now, why the upgrade option in sybase central didn't work?
> >
> > Thanks!
> >
> >
> > "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com> wrote
> > in message news:975qa29qpbqa9vgc5k61o03ov35mq2csj3@4ax.com...
> >> If the rows were originally inserted in (roughly) date_column order,
> >> creating a CLUSTERED index on that column may make the "range query"
> >> (e.g., BETWEEN) run much faster, without even having to do a
> >> REORGANIZE TABLE after you create the index. Clustered indexes were
> >> introduced in 8.0.2.
> >>
> >> Also, if your initial range is very large, try using separate DELETE
> >> statements with smaller ranges followed by COMMIT statements to keep
> >> the rollback log from growing the main .DB file enormously.
> >>
> >> Defragmentig the hard drive, and defragmenting the database itself,
> >> are always good ideas, as is having a sackload of free space in the
> >> database file (ALTER DBSPACE SYSTEM ADD whateverM).
> >>
> >> Moving to Version 9 is also an excellent idea, performance-wise, and
> >> then you can make use of Chapter 10 Tuning in my book :)
> >>
> >> There is a whitepaper on iAnywhere.com about "high throughput
> >> databases" that might interest you, and I think there is a Techwave
> >> session on the same subject (same author?).
> >>
> >> Do this Google Web search:
> >>
> >> high throughput site:ianywhere.com
> >>
> >> Breck
> >>
> >> On 6 Jul 2006 06:48:13 -0700, "Luis Porras"
> >> <luis.porras@dakotaimaging.com> wrote:
> >>
> >> >Hi,
> >> >
> >> >I have some history tables that I want to clean-up, these tables are
> > small
> >> >(+691000, +325000, +450000 records). But deleting from them are
> >> >taking a long time... I'm using a datetime column to perform the
> >> >delete, there's
> > no
> >> >index over these columns. I'm using an statement like this:
> >> >
> >> >delete from table where date_column between @from_date and @to_date
> >> >
> >> >Environment:
> >> >
> >> >- ASA 8.0.3.5307 on Windows
> >> >- 800MB cache
> >> >- DB 1.2Gb
> >> >
> >> >Thanks!
> >> >
> >> >~Luis
> >> >
> >>
> >> --
> >> Breck Carter [Team iAnywhere]
> >> RisingRoad SQL Anywhere and MobiLink Professional Services
> >> www.risingroad.com
> >> The book:
> > http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
> >> breck.carter@risingroad.com
> >
> >
> >
>
>
>
> --
> Glenn Paulley
> Research and Development Manager, Query Processing
> iAnywhere Solutions Engineering
>
> Register now for TechWave 2006 August 6-10 in Las Vegas. New! 2-day
> and 4-day options for SQL Anywhere technical training. Sessions will
> focus on data management, data movement, and SQL Anywhere 10. Visit
> www.sybase.com/techwave
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all


Greg Fenton Posted on 2006-07-07 17:07:38.0Z
From: Greg Fenton <greg.fenton_nospam_@googles-mail-site.com>
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Delete from a table is taking too much time
References: <44ad133e$1@forums-2-dub> <975qa29qpbqa9vgc5k61o03ov35mq2csj3@4ax.com> <44ae7a86@forums-2-dub> <Xns97F974A85D731paulleyianywherecom@10.22.241.106> <44ae7f4e$1@forums-1-dub>
In-Reply-To: <44ae7f4e$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: cpe00096b10fe8a-cm000f212f9e50.cpe.net.cable.rogers.com
X-Original-NNTP-Posting-Host: cpe00096b10fe8a-cm000f212f9e50.cpe.net.cable.rogers.com
Message-ID: <44ae94da$1@forums-1-dub>
Date: 7 Jul 2006 10:07:38 -0700
X-Trace: forums-1-dub 1152292058 69.193.72.112 (7 Jul 2006 10:07:38 -0700)
X-Original-Trace: 7 Jul 2006 10:07:38 -0700, cpe00096b10fe8a-cm000f212f9e50.cpe.net.cable.rogers.com
Lines: 20
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5489
Article PK: 1534


Luis Porras wrote:
> So, the procedure I did was the right one?
>

The "proper" (at least, strongly suggested) way of upgrading is to use
the newer version's dbunload tool to unload the old .db file and then
reload that data into a new .db file (also created with the newer
version's tools).

This can be done by using the steps in the "rebuild.bat" file that ships
with SQLAnywhere or using dbunload with the "-an" or "-ar" switches.

g.f
--
Greg Fenton
Random Dude
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/


Reg Domaratzki (iAnywhere Solutions) Posted on 2006-07-06 15:17:04.0Z
From: "Reg Domaratzki \(iAnywhere Solutions\)" <FirstName.LastName@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <44ad133e$1@forums-2-dub>
Subject: Re: Delete from a table is taking too much time
Lines: 40
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: rdomarat-xp.sybase.com
X-Original-NNTP-Posting-Host: rdomarat-xp.sybase.com
Message-ID: <44ad2970@forums-1-dub>
Date: 6 Jul 2006 08:17:04 -0700
X-Trace: forums-1-dub 1152199024 10.25.99.188 (6 Jul 2006 08:17:04 -0700)
X-Original-Trace: 6 Jul 2006 08:17:04 -0700, rdomarat-xp.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5483
Article PK: 1529

Please do not crosspost. This discussion should continue in the
sybase.public.sqlanywhere.general newsgroup, where I've posted Breck's
response on this newsgroup as well.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swd/base.do
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

"Luis Porras" <luis.porras@dakotaimaging.com> wrote in message
news:44ad133e$1@forums-2-dub...
> Hi,
>
> I have some history tables that I want to clean-up, these tables are small
> (+691000, +325000, +450000 records). But deleting from them are taking a
> long time... I'm using a datetime column to perform the delete, there's no
> index over these columns. I'm using an statement like this:
>
> delete from table where date_column between @from_date and @to_date
>
> Environment:
>
> - ASA 8.0.3.5307 on Windows
> - 800MB cache
> - DB 1.2Gb
>
> Thanks!
>
> ~Luis
>
>