Well, clustered indexes are only created on tables with primary keys, so it sounds like that might not be a problem here. What I mean by key order is that keys can be anything. Say the key was numeric, and you had records 1-1000000 listed out there, with their number as the primary key.
MOST databases store data OUT OF ORDER, and have a LINKED set of pages that hold the keys, and pointers to their tables. The overall hit on a read isn’t that noticeable, and a write can be VERY fast.
A CLUSTERED index on Sybase, and the older versions of MS/SQL Server is different. They store data IN ORDER, and kind of use THAT as the index. Reads are a bit faster, and a write can be very slow, if written out of order.
In my example, with a non clustered index, writes and reads would be roughly the same whereever they are. As I said though, that index, and the random order will slow down reads a bit. If you wrote record 1000000 or later, the write would actually be a bit faster than the unclustered. If you wrote record 1, it could be MUCH slower, because it has to make room for that record.
It has been a long while since I have used sybase, or older versions of MS SQL. I believe you can just use DESCRIBE mytablename
Stephen:
Aha. Well, that makes sense. “Clustered” generally means “index stored with or within the row”, so it does stand to reason that an unsophisticated implementation would have to physically rewrite a bunch of stuff if you wrote things out of order. I imagine if I had an integer PK clustered (ascending) and wrote records {10,9,8,7,6,5,4,3,2,1} in that order, the last row written would take 9 read-writes to move everything down before it could write row 1 at the top.
So, I verified, none of the indexes are clustered. Plus we’re not writing.
So this is something different. We just now managed to provoke it into doing it again, but still don’t have enough information to be able to reproduce it. Fun!! (Not.)
Yeah, I think sybase made it the default on the idea that people would always have a write once mentality, where changes would have a reversing entry, or things would be only read. It is a nice concept, but I HATE that they made it the default. It gave me a lot of grief because the DBA insisted on doing ALL changes, and just wouldn’t listen. So that computer probably did over 2 months work for NOTHING. Luckily that was generally on the weekends, so it wan’t as bad as it could have been.
Outside of transactions, locks, and deadlocks, I think the database should be relatively fast, meaning that you wouldn’t notice a significant pause. And I am ASSUMING that that is what you mean. The page size at least WAS small, so it would do more page splits than say M/S SQL TODAY, or Oracle. So there might be more gaps where it seems to almost hesitate, but we are talking barely susceptible hesitations that may be tens or hundreds of milliseconds.
If you can, it might be a good idea to bring it up with sybase. It COULD be a jdbc problem, or even some oddity in sybase. If you are doing enough processing, it could even be garbage collection.
It could be a windows problem. Windows NEVER handled virtual memory well, and at least earlier versions of windows didn’t generally handle memory over a certain amount properly. They always wanted people to pay more to be able to do that. And what else is happening on that windows system?
