SQLAnywhere performance problem

We have a complicated general performance problem in SQL Anywhere:

The problem only occurs when retrieveing data to a datawindow in
Powerbuilder
(both 5.04 and 6.0) from a SQL Anywhere (5.5) database. Not with other
databases and not when retrieveing the same data in another way (Embedded
SQL with cursor).

Large packets of what seems to be garbage data is sent from the client to
the server in between small packets of real data from the server to the
client. In fact more data is sent from the client than what is received (by
a factor of four).
We�re only reading data, no updates, so almost all data should be travelling
from the server to the client.
We've analyzed the packets on the network with a Network-analyzer, but the
problem can also be seen is the SQL Anywhere Server statistics window (bytes
sent vs. bytes received).

The problem is much worse for some of our customers who are connecting two
sites
with an ISDN link (64kb). Almost all bandwidth is wasted on garbage data.

If we use an Adaptive Server database, there's no garbage packets.
Performance improves enormously!

It seems to be a general problem of datawindows connected to a SQL Anywhere
DB.
Does anyone have a clue about this? Is it a bug or is there some options
that can be tweaked. (Or is it an intentional performance degradation of SQL
Anywhere to make us upgrade to Adaptive Server? (conspiration theory :) )



0
Stefan
5/27/1998 4:08:06 PM
sybase.powerbuilder.database 9855 articles. 1 followers. Follow

4 Replies
584 Views

Similar Articles

[PageSpeed] 24

We might have found a solution ourselves:
Setting the DBParam BLOCK=1 (it defaults to 1000) reduced the Network
traffic greatly.

We tried opening a windows in our PB application with many small datawindows
on it and logged the sum of bytes sent and received by the database server.
With the default value for BLOCK about 90000 bytes were transfered.
With BLOCK=1 this was reduced to about 13000 bytes.

The performance became noticeably better on our LAN, but it should be more
obvious on a low bandwidth connection.
We will try this solution on an ISDN connection tomorrow and see if it
improves performance.

One question we haven't investigated yet is if there are situations when
the default blocking factor is better. If not, using 1000 as the default
seems like a strange choice by Sybase. And why does the client send so much
data back to
the server with BLOCK=1000? That still looks like a bug.



0
Stefan
5/28/1998 5:08:23 PM
This is entirely counter-intuitive.  Block=1 basically indicates that the ODBC
driver will use SQLFetch rather than SQLExtendedFetch, so basically you would be
initiating network traffice for each row retrieved rather than getting a 'bunch'
of rows at a time.  I would then expect increased network traffic rather than
reduced.  If what you are saying is correct, it sounds like there may be some
sort of problem in the way the SQLAnywhere ODBC driver is handling things,
PowerBuilder, other than setting a blocking factor (which may or may not be
accepted by the driver), is out of the picture.

Jim O'Neil
Sybase Technical Support
joneil@sybase.com

Stefan wrote:

> We might have found a solution ourselves:
> Setting the DBParam BLOCK=1 (it defaults to 1000) reduced the Network
> traffic greatly.
>
> We tried opening a windows in our PB application with many small datawindows
> on it and logged the sum of bytes sent and received by the database server.
> With the default value for BLOCK about 90000 bytes were transfered.
> With BLOCK=1 this was reduced to about 13000 bytes.
>
> The performance became noticeably better on our LAN, but it should be more
> obvious on a low bandwidth connection.
> We will try this solution on an ISDN connection tomorrow and see if it
> improves performance.
>
> One question we haven't investigated yet is if there are situations when
> the default blocking factor is better. If not, using 1000 as the default
> seems like a strange choice by Sybase. And why does the client send so much
> data back to
> the server with BLOCK=1000? That still looks like a bug.



0
Jim
5/29/1998 2:12:54 PM
Seems like you're right, the problem is in the SQL Anywhere driver.
I made a test program in C that uses SQLFetch and SQLExtendedFetch. This
exhibited the same behaviour.
Traffic to and from database server (on a small select that returns 4 rows):
SQLFetch: 700 bytes
SQLExtendedFetch:  9000 bytes
That's a massive overhead if you're on a low bandwidth connection...
It seems to be a bug in the driver, or am I missing something?

Jim O'Neil [Sybase] wrote:

> This is entirely counter-intuitive.  Block=1 basically indicates that the ODBC
> driver will use SQLFetch rather than SQLExtendedFetch, so basically you would be
> initiating network traffice for each row retrieved rather than getting a 'bunch'
> of rows at a time.  I would then expect increased network traffic rather than
> reduced.  If what you are saying is correct, it sounds like there may be some
> sort of problem in the way the SQLAnywhere ODBC driver is handling things,
> PowerBuilder, other than setting a blocking factor (which may or may not be
> accepted by the driver), is out of the picture.
>
> Jim O'Neil
> Sybase Technical Support
> joneil@sybase.com
>



0
Stefan
6/2/1998 8:39:54 AM
Hi Stefan,

Compare the performance of a datawindow declared as external and using stored
procedures for
create, read, update , delete, list (CRUDL) operations to a datawindow based on
a SQL select.

We found that by using stored procedures for our CRUDL ops with external
datawindows are a lot faster than
having PB generate the SQL.


Kind Regards


Csaba Toth


Stefan wrote:

> We have a complicated general performance problem in SQL Anywhere:
>
> The problem only occurs when retrieveing data to a datawindow in
> Powerbuilder
> (both 5.04 and 6.0) from a SQL Anywhere (5.5) database. Not with other
> databases and not when retrieveing the same data in another way (Embedded
> SQL with cursor).
>
> Large packets of what seems to be garbage data is sent from the client to
> the server in between small packets of real data from the server to the
> client. In fact more data is sent from the client than what is received (by
> a factor of four).
> We�re only reading data, no updates, so almost all data should be travelling
> from the server to the client.
> We've analyzed the packets on the network with a Network-analyzer, but the
> problem can also be seen is the SQL Anywhere Server statistics window (bytes
> sent vs. bytes received).
>
> The problem is much worse for some of our customers who are connecting two
> sites
> with an ISDN link (64kb). Almost all bandwidth is wasted on garbage data.
>
> If we use an Adaptive Server database, there's no garbage packets.
> Performance improves enormously!
>
> It seems to be a general problem of datawindows connected to a SQL Anywhere
> DB.
> Does anyone have a clue about this? Is it a bug or is there some options
> that can be tweaked. (Or is it an intentional performance degradation of SQL
> Anywhere to make us upgrade to Adaptive Server? (conspiration theory :) )



--
Csaba Toth
Programmer [Development]
csaba@addease.com.au

Addease Pty. Ltd.
P.O. Box 1484
COLLINGWOOD VIC 3066
Australia

Ph : +61 3 9416 2944
Fax: +61 3 9416 2580
web: http://www.addease.com.au


0
Csaba
6/2/1998 11:42:14 PM
Reply:

Similar Artilces:

Problem with PowerBuilder dynamic cursor in sql-Anywhere and performance problems
Please help ! I have developed a statistic program with PowerBuilder Vers. 6. This program gives the user much freedome in choosing filters (for the where-clause) and the fields to retrieve from the database. Therefore I use the possibility of dynamic cursors, because I am only able to build the select-statement at runtime, when the user has entered his parameters. When using the 16-Bits Data engine of SQL_Anywhere the program works without any troubles. When using the 32-Bit engine I receive the following Error-Message: SQLSTATE = S1000 [Sybase][ODBC Driver] General err...

SQLAnywhere-performance-problem
We are using a windows-software for dialysis units based on an SQLAnywhere 5.505 Server running under Novell 5.0 ( SP5). After the last software-update we have performance-problems using a few querys ( server-utilisation more than 90%, poor performance of the query-lasting 2-3 times longer etc. ). Now the software-house told us, that the performance of the Novell-version oft ASA 5.505 would be much lower ( using the same hardware ) than the windows-version and we should switch our server to W2K ( or switch to ASA 7.02 and use SMP under W2K additionally! ). We do not know what to...

Performance problems + Other problems
Hi, Im running a Netware 6 server for the novonyx webserver, And its almost only used for www. 1st service patch installed. Total memory is 767 MB, UW SCSI disk with Adaptec card, And its a Celeron 2ghz, 10/100 network card on a 10mbit connection. Because i have huge traffic (200-600+kb/s almost every time) i have looked around a bit regarding performance. But now i dont know the standard values of. Maximun Simultaneous requests Listen Queue Size HTTP Persistent timeout And the cache size in obj.conf file. Does anyone know what this values should be as default and in...

Performance problem on 1 database
Hi, I have a performance problem with only 1 database, each database are indentical, the indexes are recompiled every night, but the performance in that database is poor compared with another database in the same server. ...

Database Design and Performance Problems
I have created an internal “e-mail” type system in our business application. The idea was pretty basic have a message table which would hold the data such as To, FROM, blah blah. Then there would be an Inbox table which would have the User Name and the Message.ID from the message table. By joining these two you would have the person’s inbox.  Each table has an ID column which is the clustered index, The Inbox table has an additional Index on the User Name. These tables are very busy and large, here are the stats.   Messages – 860,000 rows est. Total Inbox – 2,584,000 rows est. T...

SqlAnywhere Database connection Problem
Using both, ASA 8.0 and 9.0 but unble to connect to the database (either of this version). From the ODBC connection page my user getting error mesasage as follows:- [Sybase][ODBC Driver][Adaptive Server Anywhere] Unable to start specified database: I/O error Error code=-82 SQL state=08001 Connection parameters: User=dba Password=*** (n.b. Password is sql) DBF=C:\SIZING\CVCALC\CVCALC.DB Please help me to find a solution. From where Can I Download ASA 8.0 or 9.0 Driver for ODBC, if it is a driver issue? This is not a driver issue. There is a problem with the database f...

Powerbuilder database configuration problem
Recently I was given some source code that uses a Sybase SQL Anywhere database as its back end. I can view the source code but if I try and go into a data window Powerbuilder is not connecting to this database, rather I believe it trys to go to the Powerbuilder example database. The database that corresponds to the source code mentioned was not originally created on my machine. How can I configure Powerbuilder to recognize a database that was not originally created on that machine? Thanks in Advance. What you want to do is configure an ODBC datasource for that database...

problem in performing backup of database
Dear Readers I m using powerbuilder 7 with sqlserver 6.5. In application I have built an option to perform backup of database, to perform backup of database I m using a stored procedure Which script is "DUMP DATABASE databasename TO dumpdevicename WITH INIT " It is working fine. Now I want to switch my application (built in powerbuilder 7) to sqlserver 2000 but I m facing a problem that is, when I want to create backup using application then sqlserver2000 prompt an error that is " can not perform backup or restore operation within a transaction" Plz repl...

Performance Problems in PowerBuilder 7 -- FIXED
Many applications migrated from PB 6.x to PB 7 experienced severe performance degradation. The problem was most pronounced when the application was on a server and the client machines did not have the application local. Sybase engineering has isolated a problem which appeared to have contributed to the performance issues. If your application is experiencing performance problems after migration or your new application is very slow you will have to contact Sybase support and ask for PowerBuilder 7 build 7017 ...

PowerBuilder 7.0 Performance Problems
28 December, 1999 After installing PowerBuilder 7.0 it is very slow on Pentium II 233 MHz computer. Even editing script and pressing Left, Right arrow takes too long. Access 97 database connectivity is OK, but connecting to MS SQL Server 6.5 takes more than 45 Seconds. On a Pentium II 350 MHz once I had started to build Executable files but after 1 hour I terminated it. So now on I am issuing PBD files to clients. I have downloaded PB701EBF.ZIP & PB7017023.ZIP from Sybase site. Will it solve the problem? I have EAS 3.0.1 containing PowerBuilder, PowerSite etc. but I am using...

Problems with comments in the PowerBuilder database painter
I am having a problem with the database painter in PowerBuilder 7.03. When I use eithe the // or the /* */ comments in the database painter, it seems to ignore the comments and run the commented text. This used to work fine and ignore the commented text but all of a sudden it started ignoring the comment. For instance, if I commented out the line //select * from {table_name} it would return the results as if there was no comment there. Did I change a setting somewhere or is there something else that could have caused this? Many thanks in advance!!! Todd Nirenstein ...

Powerbuilder 6.0 database problem!
Dear all, Since I need to export the data in our database in order to take it process in other machine in the future, we decide to export it to a text file first, then call this database through powerbuilder again. But how to do it? I have tried to use datawindow to retrieve the data that we need first and save into a "CSV" format file, but when I use the Intersolv text driver and successfully connect it to my powerbuilder, I find that I can only see the tables in the database, but I can't open it out to see the records. the error message is "table xxx has no colu...

Problems with comments in the PowerBuilder database painter
I am having a problem with the database painter in PowerBuilder 7.03. When I use eithe the // or the /* */ comments in the database painter, it seems to ignore the comments and run the commented text. This used to work fine and ignore the commented text but all of a sudden it started ignoring the comment. For instance, if I commented out the line //select * from {table_name} it would return the results as if there was no comment there. Did I change a setting somewhere or is there something else that could have caused this? Many thanks in advance!!! Todd Nirenstein Todd....

Web resources about - SQLAnywhere performance problem - sybase.powerbuilder.database

IBM Tivoli Storage Manager - Wikipedia, the free encyclopedia
IBM Tivoli Storage Manager ( TSM or ITSM ) is a centralized, policy-based, enterprise class, data backup and recovery package. The software enables ...

Archives - Caelum's Blog
Caelum's Blog Random Stuff Navigation Home - Articles Tags 256colors 64 64bit 8 activeperl activestate advent ajax alsa amd64 asa asus automation ...

keynote bingo - Google Search
Search Images Maps Play YouTube News Gmail Drive More Calendar Translate Mobile Books Wallet Shopping Blogger Finance Photos Videos Even more ...

Mobile and Wireless Partners - Partners - Sybase Inc
Thanks for visiting the Partners section of Sybase.com. Here you will find information about Mobile and Wireless Partners - Partners. For more ...

Datensynchronisierung - sqlanywhere
„Good Partner - quick and reliable answers! Fast "delivery" by Email. Everybody can count on them." Tímea Steigervald, Product Manager Kvazar-Micro ...

Browse file extension list beginning with letter A
Browse file extension list beginning with letter A - File-Extensions.org search page

OpenLink ODBC Adapter for Ruby on Rails: OpenLink ODBC Adapter for Ruby on Rails: Downloads
OpenLink ODBC Adapter for Ruby on Rails: OpenLink ODBC Adapter for Ruby on Rails: Downloads

Developer Edition - sqlanywhere
„Good Partner - quick and reliable answers! Fast "delivery" by Email. Everybody can count on them." Tímea Steigervald, Product Manager Kvazar-Micro ...

IBM - sqlanywhere
„Good Partner - quick and reliable answers! Fast "delivery" by Email. Everybody can count on them." Tímea Steigervald, Product Manager Kvazar-Micro ...

Datenaustausch - sqlanywhere
„Good Partner - quick and reliable answers! Fast "delivery" by Email. Everybody can count on them." Tímea Steigervald, Product Manager Kvazar-Micro ...

Resources last updated: 12/18/2015 6:10:03 PM