We are having a problem with SA 5.0.0.3 slowing down over a period of time
on an NT 4.0 (Service Pak 2) server
Searches that used to take 5-15 secs start taking longer and longer,
sometimes as long as 2-3 MINUTES.
The only "solution" we have found is to shutdown the server. A warm reboot
is not sufficient to return the performance to normal. We must power off
the machine and restart. Once we do this, we are back to seconds for
searches.
Does anybody else have any input for this problem....Have already opened an
issue with Sybase, seeking additional info to help diagnose the problem.
We are not in a position to move to 5.5 x because of the new testing cycle
that would be involved.

Steve,
	The fact that a cold boot is required to resolve the problem may
indicate a problem at the network level. Network cards often require a
cold boot to reset their state. Check with your network card vendor to
determine if they have updated drivers. 

Steve Radman, Sybase Inc.

I friend of mine reported problemes similar to this and later found out
that speed went down if connections to the database was not shut down and
stayed open if a client-computer got hung. The problem was solved if he
dissconnected these "empty" connections on the server. I belive this
problem has something to do with TCP/IP.
Hope this solves the problem.
Best regards
Ketil Tuft
Norway

Steve hood <steve.hood@riseinc.com> wrote in article
<01bc50d6$ae911b60$1696bfcc@qapentium>...
> We are having a problem with SA 5.0.0.3 slowing down over a period of
time
> on an NT 4.0 (Service Pak 2) server
> Searches that used to take 5-15 secs start taking longer and longer,
> sometimes as long as 2-3 MINUTES.
> The only "solution" we have found is to shutdown the server. A warm
reboot
> is not sufficient to return the performance to normal. We must power off
> the machine and restart. Once we do this, we are back to seconds for
> searches.
> Does anybody else have any input for this problem....Have already opened
an
> issue with Sybase, seeking additional info to help diagnose the problem.
> We are not in a position to move to 5.5 x because of the new testing
cycle
> that would be involved.
> 
> 

We've seen something similar on our WinNT4 server(s). The only difference
is that an extraordinary simple query (delete 1 row from a simple 3col
table with 50-60 records) take "forever" to execute (read: upto 50mins). We
found that removing the foreign key "solves" the problem - the query then
takes only a few secs to complete. Taking this anomaly to Sybase (Norway),
has lead to the conclusion that there may be some rather "serious"/peculiar
bugs in the SA engine, which could seem to slow down the entire engine as
it makes it scan the entire leaf level of the index trees (B-trees) in
search for even a single row. Our case has been reproduced at Sybase and
they're going to look further into it.

Of course, this may not be what's happening on your server, but I thought
maybe you'd want to know...

So, 'till further notice from Sybase, we're "standing by" (hope it doesn't
take too long before they come up with a solution).




Steve hood <steve.hood@riseinc.com> wrote in article
<01bc50d6$ae911b60$1696bfcc@qapentium>...
> We are having a problem with SA 5.0.0.3 slowing down over a period of
time
> on an NT 4.0 (Service Pak 2) server
> Searches that used to take 5-15 secs start taking longer and longer,
> sometimes as long as 2-3 MINUTES.
> The only "solution" we have found is to shutdown the server. A warm
reboot
> is not sufficient to return the performance to normal. We must power off
> the machine and restart. Once we do this, we are back to seconds for
> searches.
> Does anybody else have any input for this problem....Have already opened
an
> issue with Sybase, seeking additional info to help diagnose the problem.
> We are not in a position to move to 5.5 x because of the new testing
cycle
> that would be involved.
> 
> 

Steve hood wrote:
> 
> We are having a problem with SA 5.0.0.3 slowing down over a period of time
> on an NT 4.0 (Service Pak 2) server
> Searches that used to take 5-15 secs start taking longer and longer,
> sometimes as long as 2-3 MINUTES.
> The only "solution" we have found is to shutdown the server. A warm reboot
> is not sufficient to return the performance to normal. We must power off
> the machine and restart. Once we do this, we are back to seconds for
> searches.
> Does anybody else have any input for this problem....Have already opened an
> issue with Sybase, seeking additional info to help diagnose the problem.
> We are not in a position to move to 5.5 x because of the new testing cycle
> that would be involved.
Take care what kind of stuff is used in this programs !!
We have really complicated stored procedure to execute witch needs
temporary tables. With more than one user we had (WinNT Server) the same
problems !! No answers for the Server for 20 Minutes !!
I had written the procedure with 'DECLARE LOCAL TEMPORARY TABLES'.
After no more other ideas I rewrote all stuff with GLOBAL TEMPORARY
TABLES and all problems went away !!! 
This was on Rel 5.0.02 Now we work on 5.5 but I can't say wheter the
problem persist or not, I sure will not go rewrite everything !!
Anyone have an explanation ??
Bye
R. Somaini
rosoma@mail.tic.ch

How long are your keys?  In SQL Anywhere the length of the key can have a
dramatic affect on performance.
-- 
Jim Egan [TeamPS]
Dealer Solutions, LLC
Houston, TX

In article <01bc53f9$1a45dec0$d11dd6c1@coyote>, "Ketil Tuft" <ktuft@sn.no> wrote:
>I friend of mine reported problemes similar to this and later found out
>that speed went down if connections to the database was not shut down and
>stayed open if a client-computer got hung. The problem was solved if he
>dissconnected these "empty" connections on the server. I belive this
>problem has something to do with TCP/IP.
>Hope this solves the problem.
>Best regards
>Ketil Tuft
>Norway
>
>Steve hood <steve.hood@riseinc.com> wrote in article
><01bc50d6$ae911b60$1696bfcc@qapentium>...
>> We are having a problem with SA 5.0.0.3 slowing down over a period of
>time
>> on an NT 4.0 (Service Pak 2) server
>> Searches that used to take 5-15 secs start taking longer and longer,
>> sometimes as long as 2-3 MINUTES.
>> The only "solution" we have found is to shutdown the server. A warm
....

Try applying the TCPIP-FIX hot fix to your NT Service Pack 2. It's available 
at <http://w3.ime.net/~peteb/ntserv.htm>. We experienced similar symptoms 
(100% CPU utilization) even without an SA server running. Since applying the 
patch, the problem has not reocurred.

We use both local and global temp tables.  We haven't seen this problem in
5.5/5.5.01.
-- 
Jim Egan [TeamPS]
Dealer Solutions, LLC
Houston, TX

In the table mentioned previously, the key is comprised of two integers. 

After some dicussing with Sybase, we've tried regenerating our database
using triggers for RI (instead of the normal "foreign key type RI"). This
has lead to a DRAMATIC performance improvement (at least concerning the
example posted earlier in this thread). We're now down to under .5s, which
is pretty darn close to what we'd expect in this case. 

When "converting" relationships to triggers, we had to create individual
indexes for all the previous foreign keys (as they're really a special kind
of index in SA). This has really improved (at least some of) our queries
and response is now far, far, far better. 

But; there's still more to be done to improve performance.... Guess we're
not satisfied until we're down to 0 secs on all queries!! ;-)

(Sybase confirmed what we suspected all along; SA isn't very good at index
scanning when there are lots and lots of them, and that they'll try to
improve this algorithm in the next release....)

Morten Myrvold
Sysdeco Profdoc AS,
Kongsberg, Norway

Jim Egan <eganjp@compuserve.com> wrote in article
<01bc5659$e43e56a0$3aa3b9ce@jegan.carman.com>...
> How long are your keys?  In SQL Anywhere the length of the key can have a
> dramatic affect on performance.
> -- 
> Jim Egan [TeamPS]
> Dealer Solutions, LLC
> Houston, TX
>