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.

Problem dropping a thousand views at once

10 posts in General Discussion Last posting was on 2005-11-24 15:10:06.0Z
Michael Krueger Posted on 2005-11-16 21:04:35.0Z
Sender: 27b2.437b84d3.1804289383@sybase.com
From: Michael Krueger
Newsgroups: ianywhere.public.general
Subject: Problem dropping a thousand views at once
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <437b9d57.2b75.1681692777@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 16 Nov 2005 12:57:59 -0800, 10.22.241.42
Lines: 38
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 16 Nov 2005 12:58:00 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 16 Nov 2005 13:04:35 -0800
X-Trace: forums-1-dub 1132175075 10.22.108.75 (16 Nov 2005 13:04:35 -0800)
X-Original-Trace: 16 Nov 2005 13:04:35 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4958
Article PK: 8617

We have a script that drops all non-system views and creates
each one again. This process takes a long time to drop
(approximately 1000 drops), but is very quick in the
subsequent create statements. The drops get progressively
slower during the process. Does anybody know what could be
causing the drops to take so long?

I've included the procedure that creates the drops:
CREATE PROCEDURE BTI.tmpHWReportView ( )
BEGIN
DECLARE err_notfound EXCEPTION FOR SQLSTATE VALUE '02000';
DECLARE sViewName VARCHAR(100);

DECLARE curView DYNAMIC SCROLL CURSOR FOR
SELECT st.table_name
FROM systable st
KEY JOIN sysuserperm sup
WHERE sup.user_name = 'BTI'
AND st.table_type = 'VIEW'
ORDER BY st.table_name;

OPEN curView WITH HOLD;
loopView:
LOOP
FETCH NEXT curView INTO sViewName;

IF SQLSTATE = err_notfound THEN
LEAVE loopView;
END IF;

MESSAGE 'Dropping view: ' || sViewName TO CLIENT;
EXECUTE IMMEDIATE 'DROP VIEW BTI.' || TRIM ( sViewName
);

END LOOP loopView;
CLOSE curView;
END
GO


Breck Carter [TeamSybase] Posted on 2005-11-24 15:10:06.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Problem dropping a thousand views at once
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <sdlbo19712npolgfsfev871egt95ogbih6@4ax.com>
References: <437b9d57.2b75.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
X-Original-Trace: 24 Nov 2005 07:02:48 -0800, bcarter.sentex.ca
Lines: 55
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 24 Nov 2005 07:02:48 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 24 Nov 2005 07:10:06 -0800
X-Trace: forums-1-dub 1132845006 10.22.108.75 (24 Nov 2005 07:10:06 -0800)
X-Original-Trace: 24 Nov 2005 07:10:06 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4986
Article PK: 31501

Question: Has this drop and create view process been run many times on
this database since it was created, by the time it slows down?

Was the database created from scratch recently, or was it "upgraded"
to version 9?

Breck

On 16 Nov 2005 13:04:35 -0800, Michael Krueger wrote:

>We have a script that drops all non-system views and creates
>each one again. This process takes a long time to drop
>(approximately 1000 drops), but is very quick in the
>subsequent create statements. The drops get progressively
>slower during the process. Does anybody know what could be
>causing the drops to take so long?
>
>I've included the procedure that creates the drops:
>CREATE PROCEDURE BTI.tmpHWReportView ( )
>BEGIN
> DECLARE err_notfound EXCEPTION FOR SQLSTATE VALUE '02000';
> DECLARE sViewName VARCHAR(100);
>
> DECLARE curView DYNAMIC SCROLL CURSOR FOR
> SELECT st.table_name
> FROM systable st
> KEY JOIN sysuserperm sup
> WHERE sup.user_name = 'BTI'
> AND st.table_type = 'VIEW'
> ORDER BY st.table_name;
>
> OPEN curView WITH HOLD;
> loopView:
> LOOP
> FETCH NEXT curView INTO sViewName;
>
> IF SQLSTATE = err_notfound THEN
> LEAVE loopView;
> END IF;
>
> MESSAGE 'Dropping view: ' || sViewName TO CLIENT;
> EXECUTE IMMEDIATE 'DROP VIEW BTI.' || TRIM ( sViewName
>);
>
> END LOOP loopView;
> CLOSE curView;
>END
>GO

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com


Rob Waywell Posted on 2005-11-17 14:39:01.0Z
From: "Rob Waywell" <rwaywell_no_spam_please@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <437b9d57.2b75.1681692777@sybase.com>
Subject: Re: Problem dropping a thousand views at once
Lines: 67
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: rwaywell-d610.sybase.com
X-Original-NNTP-Posting-Host: rwaywell-d610.sybase.com
Message-ID: <437c9605$1@forums-1-dub>
Date: 17 Nov 2005 06:39:01 -0800
X-Trace: forums-1-dub 1132238341 10.25.98.235 (17 Nov 2005 06:39:01 -0800)
X-Original-Trace: 17 Nov 2005 06:39:01 -0800, rwaywell-d610.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4959
Article PK: 31505

I don't see what version and build # you are testing this with.

There is nothing obvious that comes to mind that would explain why the drop
statements are taking progressively longer. How big a time difference are
you seeing?

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

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

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

<Michael Krueger> wrote in message
news:437b9d57.2b75.1681692777@sybase.com...
> We have a script that drops all non-system views and creates
> each one again. This process takes a long time to drop
> (approximately 1000 drops), but is very quick in the
> subsequent create statements. The drops get progressively
> slower during the process. Does anybody know what could be
> causing the drops to take so long?
>
> I've included the procedure that creates the drops:
> CREATE PROCEDURE BTI.tmpHWReportView ( )
> BEGIN
> DECLARE err_notfound EXCEPTION FOR SQLSTATE VALUE '02000';
> DECLARE sViewName VARCHAR(100);
>
> DECLARE curView DYNAMIC SCROLL CURSOR FOR
> SELECT st.table_name
> FROM systable st
> KEY JOIN sysuserperm sup
> WHERE sup.user_name = 'BTI'
> AND st.table_type = 'VIEW'
> ORDER BY st.table_name;
>
> OPEN curView WITH HOLD;
> loopView:
> LOOP
> FETCH NEXT curView INTO sViewName;
>
> IF SQLSTATE = err_notfound THEN
> LEAVE loopView;
> END IF;
>
> MESSAGE 'Dropping view: ' || sViewName TO CLIENT;
> EXECUTE IMMEDIATE 'DROP VIEW BTI.' || TRIM ( sViewName
> );
>
> END LOOP loopView;
> CLOSE curView;
> END
> GO