Parallel first time calls to stored proc

Anthony Mandic · *nix forums Guru Wannabe Joined: 05 Jul 2005 Posts: 164

CJ wrote:

CJ · *nix forums beginner Joined: 01 Apr 2005 Posts: 1

I am having a problem with a stored proc which is dropping out of the
procedure cache and thus being re-resolved the first time it is called
after it has dropped out. The problem is that the first time the
stored proc is called, it can be called N times in parallel (ie
through N different sessions). This will cause N copies of the query
plan to be generated and stored in the cache.

I can accept this as it is the way Sybase works, however what is
surprising is that the time for each stored proc to complete is of
O(N). i.e. if it takes 10 seconds for just one call, then it will
take 100 seconds when there are 10 parallel calls. The stored proc is
a read-only proc, so there are no exclusive locks taken out by it.

Has anyone else come across this problem and found a solution?

CJ

Google