Re: Where are the error code

In article <D5EG55.6tv_at_nntpa.cb.att.com>, Steve Y LU <ylu_at_jolt.mt.att.com.> wrote:
>We have some Sybase performance problem for Sybase 10.
>
>We have database on two machines, with same CPU and configuration.
>On machine 1.
>The number of buffers in buffer cache is 5751.
>When I run "select count(*) from "table name"
>with set statistics io on
>logical reads: 7259, physical reads: 7259
>
>On machine 2.
>The number of buffers in buffer cache is 4800
>When I run "select count(*) from "table name"
>with set statistics io on
>logical reads: 4627, physical reads: 0
>
>The data on machine 2 is bcp from machine 1. The data on machine 1 is
>loaded directly from loader.
>
>Questions:
>1. Why the number of logical reads on machine 2 is less than machine 1.

    On the assumption that you don't have a nonclustered index on the table... If the table has a clustered index and has had many rows deleted, or has no clustered index and has had many rows either deleted or updated, the data pages can have a lot of "holes." For examplt, let's say that a page can hold 10 rows. You put 1000 rows in the table, then delete 900. But it happens that you leave in the table exactly one row on each data page. Presto, your 100 rows occupy 100 data pages.

    After you BCP the data out from machine 1 you BCP it into machine 2. Machine 2 only sees the 100 final rows and neatly packs them onto 10 pages. Now, you select * (or count(*)) from the table on machine 1. The table does not fit into cache, so each page is read from disk. On machine 2, the table does fit into cache and _was already in cache_, so no disk reads are done. Is it clear from the statistics IO that this is the case?

>2. We run some reports on machine 1 and machine 2. The report on machine
>1 is much slower than machine 2. Why?

    Because machine 2 is having a bad day? No, really. If the query has to scan the table on both machines, but on machine 2 it is already in cache, you do fewer disk reads on machine 2 so it's faster. This can be _greatly_ magnified if the query calls for multiple table scans. Machine 2 may have to read int the table for scan 1, but subsequent scans will find it in memory. On machine 1, even if the table was partially in cache before the first scan, subsequent scans will find _no_ pages in memory because the end of the table in the first scan forced the start of the table out of memory before the second scan looked for it. I hope that this is understandable; I have always had a hard time explaining this poing without drawing a picture and pointing to it.

    Anyway. Try BCPing the table out on machine 1, truncating it, and BCPing it back in. Or creating a clustered index. Or dropping and recreating the clustered index. Any of these operations will re-pack the empty space.

    Also, you need more data cache.

    I'd be happy to look at more specifics, or answer particular requests for clarification, if this starts in the right direction but doesn't go far enough. I have restricted follow-up to COMP.DATABASES.SYBASE; I don't like to crosspost.

    Regards

-- 
| Peter A. Lynn      |  "The only thing necessary for the triumph of evil  |
| plynn_at_panix.com    |   is for good men to do nothing."   - Edmund Burke  |
|     My opinions are my own, and may not reflect those of my employer (or |
| anyone else, for that matter.)  I reserve the right to change my mind.   |