SQL Anywhere database - Slow Response on bulk data in ASA

Author

Slow Response on bulk data in ASA

Naimesh Trivedi

2005-06-28, 9:23 am

Hello Friends ! I m using Adaptive Server Anywhere, the one coming with
EAStudio (PB 7.x & ASA 6.0.2).

In my application there are around 25 tables out of which
In 8-10 tables data increasing and going in lacs around e.g. 3-5 lacs and on
changing any row, thro' trigger/Sp rows in other tables gets
inserted/updated/deleted.

Now in this case response in very slow, while saving one entry it takes 1-2
mins. and some reports also taking considerable time where specifically one
to many relation in simple betn 2 table query is there.

Pls guide me

1) is ASA is not for these volume, is it for very less school projects ?
2) Should i need ASE (Adaptive Server Enterprise) ?
3) Is direct data conversion from ASA to ASE possible ? Any tool for that
or pipeline of PB will do ?

Thanks & Regards,

--
Naimesh Trivedi
Raj Micro Pvt. Ltd.
Tel. +91.79.55230616
Mob. +91.94265 73044
Email : nbtriivedi@rajmicro.com

Dmitri

2005-06-28, 9:23 am

Naimesh Trivedi wrote:

> Hello Friends ! I m using Adaptive Server Anywhere, the one coming with
> EAStudio (PB 7.x & ASA 6.0.2).

Latest ASA6 revision is 6.0.4.3799.

> In my application there are around 25 tables out of which
> In 8-10 tables data increasing and going in lacs around e.g. 3-5 lacs and on
> changing any row, thro' trigger/Sp rows in other tables gets
> inserted/updated/deleted.
>
> Now in this case response in very slow, while saving one entry it takes 1-2
> mins. and some reports also taking considerable time where specifically one
> to many relation in simple betn 2 table query is there.

How big is your database in terms of its size and quantity of rows in
tables? Also, hardware specs are of interest, too ;).

Dmitri.

Manoj

2005-06-28, 11:23 am

Naimesh,
Would appreciate if you could use the international nomenclature while
describing problem especially bigger numbers like 3-5 lacs (300k - 500k
records) as most of the people who have subscribed to this news group may
not know indian arithmetic system.
Now for the response to be slow, it looks like more of the application issue
or DB performance based on the server specs as pointed by dmitri.
Pls chk our this URL
http://www.ianywhere.com/whitepaper...tml#sqlanywhere for all the
further details u need for the product. Its quite useful.
Pls also chk the below urls might be useful for u
http://www.sybase.com/detail?id=1028746
http://www.sybase.com/detail?id=1025129

Regds,
Manoj.

"Naimesh Trivedi" <nbtrivedi@hotmail.com> wrote in message
news:42c1499c$1@foru
ms-1-dub...
> Hello Friends ! I m using Adaptive Server Anywhere, the one coming with
> EAStudio (PB 7.x & ASA 6.0.2).
>
> In my application there are around 25 tables out of which
> In 8-10 tables data increasing and going in lacs around e.g. 3-5 lacs and
> on
> changing any row, thro' trigger/Sp rows in other tables gets
> inserted/updated/deleted.
>
> Now in this case response in very slow, while saving one entry it takes
> 1-2
> mins. and some reports also taking considerable time where specifically
> one
> to many relation in simple betn 2 table query is there.
>
> Pls guide me
>
> 1) is ASA is not for these volume, is it for very less school projects ?
> 2) Should i need ASE (Adaptive Server Enterprise) ?
> 3) Is direct data conversion from ASA to ASE possible ? Any tool for
> that
> or pipeline of PB will do ?

>
> Thanks & Regards,
>
> --
> Naimesh Trivedi
> Raj Micro Pvt. Ltd.
> Tel. +91.79.55230616
> Mob. +91.94265 73044
> Email : nbtriivedi@rajmicro.com
>
>

Naimesh Trivedi

2005-06-29, 9:23 am

Hello ! Well the size of my database is 45 MB and rows in e.g. affected main
tables are

1 frghtrcpt > 25K rows
2 Inwarddtl > 60K rows
3 Ledger > 75K rows
4 Stock > 70K rows

and this is a sample of only 5 months data which gets increases, and on save
from 1st table data gets updated/inserted into 3-4 tables, where it takes
more than 1.5 min.

And Primary Key and foriegn keys are there on these tables, even i tried
removing foreign keys.

i open database using dbsrv6/dbeng6 -c 64m even tried with more 128 and
more.

In my other appl. where i m using ole to store any word/excel etc. objects
in blob and open them, where the database is only 30MB still opening time is
very much around 2-3 mins.

The systems are all Pentium III, 256 MB ram with Windows 2K installed.

I m conncecting via odbc driver to ASA. All these are in one table default
table space. and page size used is 4096 bytes.

Pls guide me for any setting if i need to do more.

Thanks & Regards,

Naimesh Trivedi

--
Naimesh Trivedi
Raj Micro Pvt. Ltd.
Tel. +91.79.55230616
Mob. +91.94265 73044
Email : nbtriivedi@rajmicro.com
"Dmitri" <NOdimSPAM@mail15.com> wrote in message
news:42c14b57$1@foru
ms-1-dub...
> Naimesh Trivedi wrote:
>
>
> Latest ASA6 revision is 6.0.4.3799.
>
and on[color=darkred]
1-2[color=darkred]
one[color=darkred]
>
> How big is your database in terms of its size and quantity of rows in
> tables? Also, hardware specs are of interest, too ;).
>
> Dmitri.

Rob Waywell

2005-06-29, 9:23 am

So to answer your basic question on scale - no your database is not larger,
the largest customer databases that we work with are over 100gb+ and we have
done performance benchmarks with customers up to approx. 1500 concurrent
users with 3 active connections per user.

Now, the question is what are you doing that is dragging down the database.
Granted your hardware isn't new, but a PIII is still fairly capable.

One option for you would be to download a copy of the 9.x Developer Edition
which would then let you run the stored procedure profiling (which includes
triggers). This would provide you with a lot of insight into where the time
is going.

Another valuable diagnostic step with current software is to use a Request
Level Log and the sa_get_request_times
() and sa_get_request_profi
le()
procedures to both verify how much time is actually spent in the database
engine as opposed to the application and to identify the more expensive
operations being executed against the database.

--
-----------------------------------------------
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/c...sc?CASETYPE=Bug

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

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer
"Naimesh Trivedi" <nbtrivedi@hotmail.com> wrote in message
news:42c2ad13$1@foru
ms-2-dub...
> Hello ! Well the size of my database is 45 MB and rows in e.g. affected
> main
> tables are
>
> 1 frghtrcpt > 25K rows
> 2 Inwarddtl > 60K rows
> 3 Ledger > 75K rows
> 4 Stock > 70K rows
>
> and this is a sample of only 5 months data which gets increases, and on
> save
> from 1st table data gets updated/inserted into 3-4 tables, where it takes
> more than 1.5 min.
>
> And Primary Key and foriegn keys are there on these tables, even i tried
> removing foreign keys.
>
> i open database using dbsrv6/dbeng6 -c 64m even tried with more 128 and
> more.
>
> In my other appl. where i m using ole to store any word/excel etc. objects
> in blob and open them, where the database is only 30MB still opening time
> is
> very much around 2-3 mins.
>
> The systems are all Pentium III, 256 MB ram with Windows 2K installed.
>
> I m conncecting via odbc driver to ASA. All these are in one table default
> table space. and page size used is 4096 bytes.
>
> Pls guide me for any setting if i need to do more.
>
> Thanks & Regards,
>
> Naimesh Trivedi
>
> --
> Naimesh Trivedi
> Raj Micro Pvt. Ltd.
> Tel. +91.79.55230616
> Mob. +91.94265 73044
> Email : nbtriivedi@rajmicro.com
> "Dmitri" <NOdimSPAM@mail15.com> wrote in message
> news:42c14b57$1@foru
ms-1-dub...
> and on
> 1-2
> one
>
>

Sponsored Links