|
Home > Archive > SQL Anywhere database > December 2005 > Stored Procedure Performance
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Stored Procedure Performance
|
|
| Jon Watson 2005-12-23, 11:23 am |
| I've got a large database(4 GIG) that has two particular
tables with a huge amount of records in both(4 million and
40 million rows respectively). I've also got a stored
procedure that references these tables. The sp_ performance
was poor until I recreated the statistics(CREATE STATISTICS
table_name) on both tables and then the performance of the
procedure was very fast. After the procedure executes about
2000 times or so, the performance starts to degrade rapidly
until I recreate the statistics again and then the
performance is again fast. The stored procedure inserts
records into numerous other tables but the only thing it
does with these two tables is the update of a foreign key in
each one to point to new records. Does anyone have an idea
why I keep having to recreate the statistics in order to get
good performance?
Thanks.
| |
| David Kerber 2005-12-23, 11:23 am |
| In article <43ac2da8.37c0.1681692777@sybase.com>, Jon Watson says...
> I've got a large database(4 GIG) that has two particular
> tables with a huge amount of records in both(4 million and
> 40 million rows respectively). I've also got a stored
> procedure that references these tables. The sp_ performance
> was poor until I recreated the statistics(CREATE STATISTICS
> table_name) on both tables and then the performance of the
> procedure was very fast. After the procedure executes about
> 2000 times or so, the performance starts to degrade rapidly
> until I recreate the statistics again and then the
> performance is again fast. The stored procedure inserts
> records into numerous other tables but the only thing it
> does with these two tables is the update of a foreign key in
> each one to point to new records. Does anyone have an idea
> why I keep having to recreate the statistics in order to get
> good performance?
What version of ASA? The way statistics are maintained has changed from
ASA 7 to ASA 9. I'm not sure which one ASA 8 is more similar to.
--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).
| |
|
| > What version of ASA? The way statistics are maintained
> has changed from ASA 7 to ASA 9. I'm not sure which one
> ASA 8 is more similar to.
Sorry, ASA 8.0.2.4339.
engine start line: "C:\Program Files\Sybase\SQL Anywhere
8\win32\dbSrv8.exe" -ct- -ti0 -tl0 -gd ALL -n "test"
"C:\CPDB\catapult"
| |
| Stephen Rice 2005-12-23, 8:23 pm |
| The Engineering team has done a lot of work in this area since v8.0.2
(almost 2 years ago)
This behaviour sounds familiar but I can't point to a specific bug fix
off hand
I suggest you try
a) the latest 8.0.3 (8.0.3 is just a bug fix release)
b) the latest 9.0.2
If the behaviour exists in 9.0.2 please open a tech support case because
it definitely sounds like a "bad thing"
/steve
Jon wrote:
>
>
> Sorry, ASA 8.0.2.4339.
>
> engine start line: "C:\Program Files\Sybase\SQL Anywhere
> 8\win32\dbSrv8.exe" -ct- -ti0 -tl0 -gd ALL -n "test"
> "C:\CPDB\catapult"
--
Stephen Rice
Technical Services Manager
iAnywhere Solutions
--- Please Post ---
Whitepapers, Tech Docs, Solved Cases, Bug Fixes and
"Report a bug" are all available on www.ianywhere.com
| |
|
| I've found the problem..but still need help.
The stored procedure selects some records into a dynamic
scroll cursor. The command that starts taking more and more
time is the first Fetch out of this cursor. Does this
indicate anything to anybody? It seems to point towards an
environment/RAM issue to me...
| |
| Glenn Paulley 2005-12-27, 9:23 am |
| Jon wrote in news:43ac6016.4a16.1681692777@sybase.com:
> I've found the problem..but still need help.
>
> The stored procedure selects some records into a dynamic
> scroll cursor. The command that starts taking more and more
> time is the first Fetch out of this cursor. Does this
> indicate anything to anybody? It seems to point towards an
> environment/RAM issue to me...
>
That could mean that the access plan generated for the query is sub-
optimal, and needs investigation. You need to output the graphical plan
used by that cursor and then determine what can be done to improve that
query's performance.
--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
| |
| Jon Watson 2005-12-27, 1:23 pm |
| > That could mean that the access plan generated for the
> query is sub- optimal, and needs investigation. You need
> to output the graphical plan used by that cursor and then
> determine what can be done to improve that query's
> performance.
>
> --
> Glenn Paulley
Hi, Glenn. I understand that the query itself might not be
optimal, but I'm still trying to understand how this could
happen. A shutdown/restart of the database engine does not
improve performance when it gets to this point. Also, the
create statistics does not allways succeed in getting the
performance of these Fetches back to where they were
before...So I'm just trying to figure out how these Fetches
could start taking longer and longer and how that could not
be "fixed" by simply restarting the engine(i.e. clearing up
resources..)....
| |
| Glenn Paulley 2005-12-28, 11:23 am |
| The access plan chosen by the optimizer depends on the schema of the
database, the characteristics of your particular instance (number of rows
in each table, distribution of values in each column, etc.) and some
execution time context: cache size, number of active requests, number of
concurrent users, and so on. Hence you need to investigate why the
optimizer the optimizer is choosing a suboptimal plan, and then determine
what can be done about it.
Glenn
Jon Watson wrote in news:43b17af7.a5e.1681692777@sybase.com:
>
> Hi, Glenn. I understand that the query itself might not be
> optimal, but I'm still trying to understand how this could
> happen. A shutdown/restart of the database engine does not
> improve performance when it gets to this point. Also, the
> create statistics does not allways succeed in getting the
> performance of these Fetches back to where they were
> before...So I'm just trying to figure out how these Fetches
> could start taking longer and longer and how that could not
> be "fixed" by simply restarting the engine(i.e. clearing up
> resources..)....
>
--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
|
|
|
|
|