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.

CREATE / DROP Statistics

2 posts in General Discussion Last posting was on 2004-01-27 14:37:32.0Z
Frederic Hoornaert Posted on 2004-01-26 17:15:04.0Z
From: "Frederic Hoornaert" <Frederic.Hoornaert@nospam.com>
Newsgroups: ianywhere.public.general
Subject: CREATE / DROP Statistics
Lines: 52
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4522.1200
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200
NNTP-Posting-Host: 217.136.171.137
X-Original-NNTP-Posting-Host: 217.136.171.137
Message-ID: <40154b18@forums-1-dub>
Date: 26 Jan 2004 09:15:04 -0800
X-Trace: forums-1-dub 1075137304 217.136.171.137 (26 Jan 2004 09:15:04 -0800)
X-Original-Trace: 26 Jan 2004 09:15:04 -0800, 217.136.171.137
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2393
Article PK: 4598

Hello,

We have a C++ application that connects to the ASA 8.0.2.4302 database on
the server using ODBC drivers.
This app inserts serveral thousands of records per day in a couple of
tables.
A second application, written in PowerBuilder, connects to the database to
generate reports on the inserted data.

We used to use Sql Anywhere 5.05.05.2817 as back-end database, but moved
over to ASA 8.0.2 due to performance issues with large databases.
With the SQL Anywhere 5.05.05 database, we issued the statement 'DROP
OPTIMIZER STATISTICS' each night (from within the C++ app) to have our
statistics up to date, and obtain better reponse times in the PB
application.

Since this statement is not supported in ASA, we replaced it by the 'CREATE
STATISTICS' equivalent.
This was going great for a while, until the database got rather big. Queries
that usually return their results in 1 à 2 sec, suddenly took 30 à 40 sec.

Looking into the help, we tried 'DROP STATISTICS on ..' , and have written a
stored procedure which executes the 'DROP STATISTICS on ...' for each table.
Running this statement once a day was also working great, with nice response
times in the PB application. But suddenly it makes the C++ application hang.
We thought about locking problems, and the SA_LOCKS revealed that some
tables were indeed being locked. Removing the locks on the table made the
C++ app continue. The locks come from PB users which are updating data in
the same database. Since the C++ executes the Stored Proc for the 'drop
statistics' in its main thread, the application pauses until all locks on
the table are released.
To avoid the problem with the tables being locked, and the C++ app being
paused, we issued 'SET BLOCKING OFF' statement on the connection , just
before running the stored procedure. When the procedure is finished, we
issue a 'SET BLOCKING ON', but this makes the DB Service crash. Trying to
locate what makes the service crash, we couldn't indicate the reason. It
just happens 4 times on 10 executes.

Does anybody have the same problems we experience right now ?
Has anybody found a solution for either
- the performance problems which can occur with the 'create statistics'
- the problem with the table locking on the 'DROP STATISTICS' statement
- the problem with the 'SET BLOCKING ON/OFF => crash'

If this isn't the right way to handle the updates of the statistics, which
way should it be done ?

--
Greetz,
Frederic Hoornaert


Chris Keating (iAnywhere Solutions) Posted on 2004-01-27 14:37:32.0Z
From: "Chris Keating \(iAnywhere Solutions\)" <FightSpam_keating@iAnywhere.com>
Newsgroups: ianywhere.public.general
References: <40154b18@forums-1-dub>
Subject: Re: CREATE / DROP Statistics
Lines: 91
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: keating-xp.sybase.com
X-Original-NNTP-Posting-Host: keating-xp.sybase.com
Message-ID: <401677ac$1@forums-1-dub>
Date: 27 Jan 2004 06:37:32 -0800
X-Trace: forums-1-dub 1075214252 172.31.141.1 (27 Jan 2004 06:37:32 -0800)
X-Original-Trace: 27 Jan 2004 06:37:32 -0800, keating-xp.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2396
Article PK: 4601

This is already being discussed in the SQL Anywhere group.

--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

****************************************************************************
*
Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
and try out the market-leading database for mobile, embedded and small to
medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

****************************************************************************
*

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use CaseXpress to report bugs http://casexpress.sybase.com

****************************************************************************
*

"Frederic Hoornaert" <Frederic.Hoornaert@nospam.com> wrote in message
news:40154b18@forums-1-dub...
> Hello,
>
> We have a C++ application that connects to the ASA 8.0.2.4302 database on
> the server using ODBC drivers.
> This app inserts serveral thousands of records per day in a couple of
> tables.
> A second application, written in PowerBuilder, connects to the database to
> generate reports on the inserted data.
>
> We used to use Sql Anywhere 5.05.05.2817 as back-end database, but moved
> over to ASA 8.0.2 due to performance issues with large databases.
> With the SQL Anywhere 5.05.05 database, we issued the statement 'DROP
> OPTIMIZER STATISTICS' each night (from within the C++ app) to have our
> statistics up to date, and obtain better reponse times in the PB
> application.
>
> Since this statement is not supported in ASA, we replaced it by the
'CREATE
> STATISTICS' equivalent.
> This was going great for a while, until the database got rather big.
Queries
> that usually return their results in 1 à 2 sec, suddenly took 30 à 40 sec.
>
> Looking into the help, we tried 'DROP STATISTICS on ..' , and have written
a
> stored procedure which executes the 'DROP STATISTICS on ...' for each
table.
> Running this statement once a day was also working great, with nice
response
> times in the PB application. But suddenly it makes the C++ application
hang.
> We thought about locking problems, and the SA_LOCKS revealed that some
> tables were indeed being locked. Removing the locks on the table made the
> C++ app continue. The locks come from PB users which are updating data in
> the same database. Since the C++ executes the Stored Proc for the 'drop
> statistics' in its main thread, the application pauses until all locks on
> the table are released.
> To avoid the problem with the tables being locked, and the C++ app being
> paused, we issued 'SET BLOCKING OFF' statement on the connection , just
> before running the stored procedure. When the procedure is finished, we
> issue a 'SET BLOCKING ON', but this makes the DB Service crash. Trying to
> locate what makes the service crash, we couldn't indicate the reason. It
> just happens 4 times on 10 executes.
>
> Does anybody have the same problems we experience right now ?
> Has anybody found a solution for either
> - the performance problems which can occur with the 'create statistics'
> - the problem with the table locking on the 'DROP STATISTICS' statement
> - the problem with the 'SET BLOCKING ON/OFF => crash'
>
> If this isn't the right way to handle the updates of the statistics, which
> way should it be done ?
>
> --
> Greetz,
> Frederic Hoornaert
>
>