ASE Performance

Dear all,

I'm using ASE 11.9. Does anyone know which is the most efficient whay to 
speed up the ASE server ?  I have a stored procedure carries out following 
things using a CURSOR:

- Read record from table_a
- Do some data manipulation to the record read
- Insert record to table_b if record is not exist in table_b
- Update record to table_b if record is exist in table_b

table_a contains around 40,000 records and table_b contains around 300,000 
records.

Originally, my coding in the stored procedure is as follow:
SELECT .... FROM table_b WHERE ...
IF @@rowcount > 0
   UPDATE table_b SET ....
ELSE
   INSERT INTO table_b VALUES ....

However, the speed is too slow, several hours to process 40,000 records (I 
think it may because the procedure needs to submit the "SELECT" each time). 
 So, I changed to:

INSERT INTO table_b VALUES ....
IF @@error = 2601
   UPDATE table_b SET....

The above approach speed up nearly 50% (needs around 1 hr to process 40,000 
records). However, it still take times and I feel that when the procedure 
run after around several thousands record, the speed starts to slow down.

I've changed some ASE configure as follow:
- default data cache: 36000
- add a 6MB's 16K pool to the "defaul data cache"
I didn't bound the data cache to table_a or table_b because this procedure 
only run once a day.

My Sybase server has 256M RAM. The database data size is 500MB and log size 
is 200MB (I think it's already enough).

Also, I found that even I added the 16k pool to the server, but it seems 
that the stored procedure cannot make use of it each time.  Because I 
experience a case that I run this stored procedure in 2 days with same data 
source and there is no change in the server configuration. But,
Day 1: only takes around 10min to finish 20,000 records
Day 2: takes around 45min to finish 20,000 records
I didn't change anything but the time required to finish is totally 
different.

Can anyone give me some advices ???

Many many thanks!
Fanny
0
Fanny
6/23/2001 3:14:02 PM
sybase.ase.performance+tuning 2395 articles. 0 followers. Follow

4 Replies
613 Views

Similar Articles

[PageSpeed] 48

Fanny wrote...
> Dear all,
> 
> I'm using ASE 11.9. Does anyone know which is the most efficient whay to 
> speed up the ASE server ?  I have a stored procedure carries out following 
> things using a CURSOR:
> 
> - Read record from table_a
> - Do some data manipulation to the record read
> - Insert record to table_b if record is not exist in table_b
> - Update record to table_b if record is exist in table_b
> 
> table_a contains around 40,000 records and table_b contains around 300,000 
> records.
> 
> Originally, my coding in the stored procedure is as follow:
> SELECT .... FROM table_b WHERE ...
> IF @@rowcount > 0
>    UPDATE table_b SET ....
> ELSE
>    INSERT INTO table_b VALUES ....
> 
> However, the speed is too slow, several hours to process 40,000 records (I 
> think it may because the procedure needs to submit the "SELECT" each time). 
>  So, I changed to:
> 
> INSERT INTO table_b VALUES ....
> IF @@error = 2601
>    UPDATE table_b SET....
> 
> The above approach speed up nearly 50% (needs around 1 hr to process 40,000 
> records). However, it still take times and I feel that when the procedure 
> run after around several thousands record, the speed starts to slow down.
> 
> I've changed some ASE configure as follow:
> - default data cache: 36000
> - add a 6MB's 16K pool to the "defaul data cache"
> I didn't bound the data cache to table_a or table_b because this procedure 
> only run once a day.
> 
> My Sybase server has 256M RAM. The database data size is 500MB and log size 
> is 200MB (I think it's already enough).
> 
> Also, I found that even I added the 16k pool to the server, but it seems 
> that the stored procedure cannot make use of it each time.  Because I 
> experience a case that I run this stored procedure in 2 days with same data 
> source and there is no change in the server configuration. But,
> Day 1: only takes around 10min to finish 20,000 records
> Day 2: takes around 45min to finish 20,000 records
> I didn't change anything but the time required to finish is totally 
> different.

I wouldn't touch the configuration until you change your process.  Instead of using a 
cursor try to perform the process in two steps:

UPDATE table_b
FROM table_a
WHERE EXISTS (SELECT * FROM table_b sub_b WHERE sub_b.key = table_a.key)

INSERT INTO table_b
SELECT .....
FROM table_a
WHERE NOT EXISTS (SELECT * FROM table_b sub_b WHERE sub_b.key = table_a.key)

Be sure to do the UPDATE first.  Depending how you code the UPDATE the subquery may not be 
necessary.

-- 
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services

Sybase TechWave 2001 http://www.sybase.com/events/techwave2001
August 12th - 16th, San Diego
0
Jim
6/23/2001 11:02:13 PM
Depending on your process logic, you can still avoid cursor. I would go for
writing serveral sql instead of cursor.

In your process, you can select each column from table_a into a #temp table,
create proper index , do calculations,
and then call another proc (so that index on #temp will be used) in which
insert or update table_b.

PB

<Fanny> wrote in message
news:7A844DE81FDB05620052D59E85256A77.0080A9FF85256A74@webforums...
> thanks. I also wish I can submit several SQL instead of using CURSOR.
> However, I need to use it because I need to manipulate nearly each field
in
> table_a.
>
> The value in table_a is downloaded from AS400. I need to do some
> formatting, data massaging & calculation before update/insert to table_b.
> Therefore, I can't directly INSERT/UPDATE to table_b by SELECTING data
from
> table_b.
>


0
PB
6/26/2001 3:02:02 PM
thanks. I also wish I can submit several SQL instead of using CURSOR. 
However, I need to use it because I need to manipulate nearly each field in 
table_a.

The value in table_a is downloaded from AS400. I need to do some 
formatting, data massaging & calculation before update/insert to table_b. 
Therefore, I can't directly INSERT/UPDATE to table_b by SELECTING data from 
table_b.

0
Fanny
6/26/2001 3:04:46 PM
Or if you really want to get fancy, you can avoid the temporary tables and
use characteristic functions or even processes on Java objects to convert
your data.

Roger

--
Roger Broadbent
Technical Consultant
Wilco International Ltd

PB <bagewadi@yahoo.com> wrote in message
news:LD#FXPl$AHA.193@forums.sybase.com...
> Depending on your process logic, you can still avoid cursor. I would go
for
> writing serveral sql instead of cursor.
>
> In your process, you can select each column from table_a into a #temp
table,
> create proper index , do calculations,
> and then call another proc (so that index on #temp will be used) in which
> insert or update table_b.
>
> PB
>
> <Fanny> wrote in message
> news:7A844DE81FDB05620052D59E85256A77.0080A9FF85256A74@webforums...
> > thanks. I also wish I can submit several SQL instead of using CURSOR.
> > However, I need to use it because I need to manipulate nearly each field
> in
> > table_a.
> >
> > The value in table_a is downloaded from AS400. I need to do some
> > formatting, data massaging & calculation before update/insert to
table_b.
> > Therefore, I can't directly INSERT/UPDATE to table_b by SELECTING data
> from
> > table_b.
> >
>
>


0
Roger
6/27/2001 11:47:56 AM
Reply:

Similar Artilces:

ASE and ASE
Could it be possible to take scripts from ASA and load them in an ASE Any information is welcome jean-fran�ois ASA supports a fairly large sub-set of Transact-SQL, so if you write your stored procedures and triggers in ASA using T-SQL, you should be able to create scripts that will run against both ASE and ASA. If you're planning to do this though, I would suggest developing your database schema against ASA, since everything you write in T-SQL in ASA will be supported on ASE, but the reverse is not true. Check out the section in the ASA documentation entitled "Tra...

ASE to ASE
Hi, Is anyone out there doing ASE to ASE replication using SQL remote? If you are have created your own version of the SSEXTRACT utility to set up a replicant database in ASE rather than ASA? Or does anyone know if SYBASE has created a version SSEXTRACT for use with ASE to ASE replication? Thanks in advance Doug Trainer Hi Douglas, I thought the SQL Remote Replication support in ASE11.5 was meant for a consolidate-database only (by design)! At least that was what Sybase said when they announced support for SQL Remote technology support in ASE11.5. I will be happy to...

ASE performance
Hi, I'm new to Sybase, so excuse me if this is a stupid question, and I guess I might not supply enough information to make an informed judgement ... We've got ASE 12.0 running on a Sun Sparc server with 2 x 450Mhz processsors and 2 gig RAM + RAID. We run a script which updates a join table - i.e. a table with two integer columns with keys in them. The sql script inserts a total of 1.8 million rows into the table and the entire operation takes around 18 minutes to complete. Does this sound like reasonable performance - it feels slow to me, but I don't have any comp...

ASE 15 Performance and Tuning Guides
Greetings, Does anyone know when the updated ASE 15 versions of "Performance and Tuning Guides" will be released? The guides on sybooks are for ASE12.5.1 Thanks in advance. Alberto > Greetings, > > Does anyone know when the updated ASE 15 versions of > "Performance and Tuning Guides" will be released? I *think* that they were supposed to be released sometime later this year, but I don't have any clear information. Michael I'm not aware of when these will be released. I'm guessing some time after the 15.0.2 release. Ho...

ASE performance
Hi there, ask a rather beginner question here. we have this ASE12.0 on Solaris 7. it's a enterprise 3000 machine, with 1G RAM, 2 CPUs both 168MHz. via /etc/system assigned 800M to ASE. All other settings in the configure file are mostly DEFAULT. we donot have many concurrent users on system most of the time, and the workload/ transaction is not very high. (most time, just 50 to 60 users online). however, lately users complains performance problem. esp just only one user was running a little big large report. and when I check sp_lock, actually very few locks, and just some shared...

ASE 12.5 vs ASE 11.5 performance issues
Hi All, Again my question is about performance of ASE 12.5 against 11.5.01 We have a huge production client-server application running on W2K server with ASE 11.5.0.1 database (more than 400 tables, 2000 stored procedures). The database was placed on raw disk partitions (no RAID) and split between different physical drives as follows: data, log, tempdb and master database are located on different drives. We have two PIII, 1GHz, 1 GB of memory and give 800MB for ASE. We create separate tempdb cache and bind tempdb to it. Database was bind to default data cache. Then we want to switc...

Performance Degradation after ASE 12.0 upgrade to ASE 12.5
Hello, There was a issue posted on 08/06/2002 by 'Bob' related to performance degradation after upgrade of ASE 11.9.2 to ASE 12.5. I would like to know whether anyone has found any solution/workaround to resolve those issues without making any changes in the application code. We are also facing the same problems here after upgrade of ASE 12.0 to ASE 12.5. The program which takes 10 mins. on ASE 12.0 is taking more than 3 hrs to finish on ASE 12.5 despite of using the same indexes and the program which takes 3 hrs on ASE 12.0 is taking more than 24 hours on ASE 12.5. ...

Performance advantage of ASE 64 Bit over ASE 32 Bit version
Hi Folks, Is any there any performance gains when using ASE 64 bit versus ASE 32 bit version like larger memory gains hence good performance? Thanks Jayesh The more memory , the more data you can cache, the better performance. <Jayesh> wrote in message news:4460bb13.194c.1681692777@sybase.com... > Hi Folks, > > Is any there any performance gains when using ASE 64 bit > versus ASE 32 bit version like larger memory gains hence > good performance? > > > Thanks > Jayesh > The more memory , the more data you can cache, the better...

Performance issues after upgrate ASE 11.5 to ASE 12.5
Hi How can we improve performance? We're moving a database that was operating fairly well on a HP-UX 10.20 with Adaptive Server Enterprise v. 11.5 (32 bits) to a HP-UX 11.0 with Adaptive Server Enterprise v. 12.5 (32 bits) Our problem is the slower performance of the database on the new, more powerful and faster machine with newer software (in both operative system and sybase version) We've noted that it starts really fast but slows down gradually. We�ve tried many ways to improve the performance: -Increase the max memory. -Increase the number of engines at startup...

Performance issues after upgrate ASE 11.5 to ASE 12.5 #2
Hi How can we improve performance? We're moving a database that was operating fairly well on a Sun 3000 with=20 Adaptive Server Enterprise v. 11.5 (32 bits) to a Sun 10000 with Adaptive=20 Server Enterprise v. 12.5 (64 bits) and Solaris v. 8 Our problem is the slower performance of the database on the new, more=20 powerful and faster machine with newer software (in both operative system=20 and sybase version) A particular query with insert used to work for about 2 hours in a Sun 3000= =20 with Adaptive Server Enterprise v. 11.5 (32 bits).=20 In a Sun 10000 with Adaptive ...

ASE optimizer behavior differences and Performance differences between ASEs: 11.50/ 11.9.2
Hi, I wish I could report that the 11.9.2.4 ASE is performing better then our 11.50 AS instances, but then I would probably not be submitting this post. I have two similar ASE instances which appear to have identical table,indexes,segments,devices. The row counts of the two tables are only off by 1000 and that is expected. Showplan reveals that the optimizer is behaviing very differently between the two instances , not supprised. The new 11.9.2.4/1175/P/SWR/9520 ASE is performing poorly and I can not proced with upgrading my other servers to 11.9.2 untill I accertain the performanc...

Diferrence of performance on queries betwenne a ASE 11.5.1.1 and ASE 12.0
I'm on the way to migrate from ASE 11.5.1.1 (on HP-UX 10.0) to ASE 12.0 (64= =20 bits on HP-UX 11). My tests shows good and bad performance with queries. I'm very interesting with yours. Can you tell me about them. Thank you. Fran=E7oise GOISLARD DBA=20 =20 Francoise, There are major functional changes in ASE 11.9.2 and above. I'm glad to see you're testing. How did you get the data to 12.0? Have you turned on any of the 12.0 options? HAve you attempted any tuning? What, if anything, have you done so far? If you have a specific problem query that can b...

Check List for ASE 11.9.2 Performance Tuning
Dear all, We need to troubleshoot the performance issue in an ASE 11.9.2 server. During the system in a slow response time state, about 20 Sh_page locks (Non Cursor Lock) found on the syslogins when I execute the sp_lock. Can anyone give me some advise on which areas I should check first (may be a check list) ? Cheers, Jack You might want to read through the Performance & Tuning guide for things like query performance management with things like sp_showplan and sp_sysmon. What you could consider is this. Any running task may be running with a bad query plan. Her...

ASA To ASE - VS
What are the differents in terms of configuring SQL Remote Between ASA to ASE and ASE To ASE ? You should start with the Help file and then ask specific questions: Data Replication with SQL Remote PART 5. Appendix APPENDIX A. Enterprise and Anywhere: Differences There are many differences, so begin there. -- David Fishburn Sybase Please only post to the newsgroup BH Ong <bhong@tm.net.my> wrote in message news:01bf4b98$33183580$7ccdc8c8@virtual-branch... > What are the differents in terms of configuring SQL Remote Between ASA to > ASE and ASE To ASE ...

Web resources about - ASE Performance - sybase.ase.performance+tuning

Performance - Wikipedia, the free encyclopedia
A performance , in performing arts , generally comprises an event in which a performer or group of performers behave in a particular way for ...

Australia v West Indies: Tourists earn support from SCG crowd with battling performance in third Test ...
The SCG crowd shows desire for the contest, and for much of the first day of the third Test they got it, writes Geoff Lemon.

BMW recaps the glory days of M3 performance
Filed under: Videos , BMW , Convertible , Coupe , Sedan , Classics , Luxury , Performance BMW continues it documentary chronicle of the M3 lineage ...

Old Spice Ups Innovation, Performance Ante with New Hardest Working Collection Line of Anti-Perspirant/Deodorants ...
Old Spice is taking performance to legendary extremes with the introduction of the new Hardest Working Collection product line, offering the ...

Keep two Macs running at peak performance with this award-winning app
Like all of us, our computers can develop habits over time, not all of them great. Many computers basically become hoarders, getting distracted, ...

‘The Revenant’: Cast & Director On The “Intensity” Of Performances – Featurette
EXCLUSIVE: Making it through Alejandro G. Iñárritu ’s brutal period film The Revenant is as much an endurance test for the audience as it is ...

Weak iPhone sales affect December performance at Taiwan panel makers
Several Taiwan touch panel makers have reported more than 30% on-month revenue declines for December 2015 as a result of weak iPhone 6s and 6s ...

AccuWeather Gets Big Material Design Update, Improved Performance
... that it is available globally to all. The update introduces Google’s Material Design language to weather fans, but also includes performance ...

Jimmy Fallon, J.K. Simmons and Billy Joel Pull Off an Epic Impromptu Performance During a Commercial ...
The 'Tonight Show' host and actor joined the musician in a rendition of his doo-wop hit 'The Longest Time.'

Measure Performance With a Scoreboard Index
The scoreboard index could be your next key performance indicator.

Resources last updated: 1/7/2016 7:24:43 PM