Link to home
Create AccountLog in
Avatar of phani_82
phani_82

asked on

Sybase performance issue

Hello,
I have a performance issue with a stored procedure being run in ASE 12.5.4.
The stored procedure is taking nearly 30 minutes to finish execution and I am trying to make it run faster.

The stored procedure calls a view within a while loop which is being executed 155 times.The view is has been defined to get data from three tables, namely A,B and C.
The number of rows within these three tables has been given below:-

A - 13674148
B - 797
C - 3390906

Definition of the view is given below:-

select A.<col1>,A.<col2>...
from A,B,C
where A.<col1> = B.<col1>
and B.<col2> = C.<col2> and so on...

Within the stored procedure, the view is being executed 155 times within a while loop, by taking a batch size of 100000.Sample code is given below:-

insert #table
select <col1>,<col2> from view
where <conditions>...

Since the view is returning huge data, it is taking long time to complete.
Any pointers in tuning this ? Please advise


Avatar of maneksh
maneksh

Hi,

Could you please share the plan of the query ?
set the showplan on and execute the proc.. We would have a better idea once we see the plan.

Some tips could be like .

1) make sure the queries are using the right indexes
2) Make sure your variable data types  are in line with corresponding  coulmn data types
3) statistics of the tables are up to date  etc


Thanks
Maneksh
Avatar of phani_82

ASKER

Hi,
The query performance got improved after I implemented 'Index covering', I created a covering non clustered index on the columns being used in the query. The time of execution got reduced from 30 minutes to less than 10 minutes. But I observe some wierd behaviour after creation of the index.Sometimes the stored proc executes in 10 minutes and sometimes it executes in 3 minutes flat ! What could be the reason  for this wierd behaviour?

ASKER CERTIFIED SOLUTION
Avatar of alpmoon
alpmoon
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
We will need to redesign the application for better performance.It was not an issue with the code.