Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Primary key ignoring

13 posts in General Discussion Last posting was on 2007-03-17 16:09:34.0Z
Piotr Ma³ecki Posted on 2007-03-15 00:49:22.0Z
From: "Piotr Ma³ecki" <piotr.malecki@adinfo.pl>
Newsgroups: ianywhere.public.general
Subject: Primary key ignoring
Lines: 31
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: abcq65.neoplus.adsl.tpnet.pl
X-Original-NNTP-Posting-Host: abcq65.neoplus.adsl.tpnet.pl
Message-ID: <45f89812@forums-1-dub>
Date: 14 Mar 2007 16:49:22 -0800
X-Trace: forums-1-dub 1173919762 83.6.232.65 (14 Mar 2007 16:49:22 -0800)
X-Original-Trace: 14 Mar 2007 16:49:22 -0800, abcq65.neoplus.adsl.tpnet.pl
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5881
Article PK: 1861

ASA 10.0.0.2788

I have a table Signals with 6 mln rows with primary key
on BigInt type column: IdSignal.

When I put the query:

SELECT TOP 1 IdSignal INTO ll_var FROM Signals
ORDER BY IdSignal

database server waits 3 minutes doing much disk operations.

Then I rewrite the query, suggesting that primary key is a really
usefull thing:

SELECT TOP 1 IdSignal INTO ll_var FROM Signals
FORCE INDEX (Signals)
ORDER BY IdSignal

(Signals is the name of primary key)

Server scans the table, a result is visible after serveral minutes.
Then I changed the database option: User_estimates = Enabled,
also Prefetch = Off and no results.

I give up.


Piotr Malecki


Rob Waywell Posted on 2007-03-15 12:59:35.0Z
From: "Rob Waywell" <rwaywell_no_spam_please@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <45f89812@forums-1-dub>
Subject: Re: Primary key ignoring
Lines: 57
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: rwaywell-xp2.sybase.com
X-Original-NNTP-Posting-Host: rwaywell-xp2.sybase.com
Message-ID: <45f94337$1@forums-1-dub>
Date: 15 Mar 2007 04:59:35 -0800
X-Trace: forums-1-dub 1173963575 10.25.98.235 (15 Mar 2007 04:59:35 -0800)
X-Original-Trace: 15 Mar 2007 04:59:35 -0800, rwaywell-xp2.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5884
Article PK: 1864

The behaviour does sound strange even if you are running this on a cold
cache. Can you post the graphical plan with statistics for both versions of
the query?

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

"Piotr Ma³ecki" <piotr.malecki@adinfo.pl> wrote in message
news:45f89812@forums-1-dub...
> ASA 10.0.0.2788
>
> I have a table Signals with 6 mln rows with primary key
> on BigInt type column: IdSignal.
>
> When I put the query:
>
> SELECT TOP 1 IdSignal INTO ll_var FROM Signals
> ORDER BY IdSignal
>
> database server waits 3 minutes doing much disk operations.
>
> Then I rewrite the query, suggesting that primary key is a really
> usefull thing:
>
> SELECT TOP 1 IdSignal INTO ll_var FROM Signals
> FORCE INDEX (Signals)
> ORDER BY IdSignal
>
> (Signals is the name of primary key)
>
> Server scans the table, a result is visible after serveral minutes.
> Then I changed the database option: User_estimates = Enabled,
> also Prefetch = Off and no results.
>
> I give up.
>
>
> Piotr Malecki
>


Piotr Ma³ecki Posted on 2007-03-15 14:14:06.0Z
From: "Piotr Ma³ecki" <piotr.malecki@adinfo.pl>
Newsgroups: ianywhere.public.general
References: <45f89812@forums-1-dub> <45f94337$1@forums-1-dub>
Subject: Re: Primary key ignoring
Lines: 583
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: eo110.internetdsl.tpnet.pl
X-Original-NNTP-Posting-Host: eo110.internetdsl.tpnet.pl
Message-ID: <45f954ae@forums-1-dub>
Date: 15 Mar 2007 06:14:06 -0800
X-Trace: forums-1-dub 1173968046 80.53.14.110 (15 Mar 2007 06:14:06 -0800)
X-Original-Trace: 15 Mar 2007 06:14:06 -0800, eo110.internetdsl.tpnet.pl
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5885
Article PK: 1868

I have attached the plan (afeter 20 minutes of preparation by the server).
The table's name differs but the table stores signals.

What is interesting - in the same time the database works fine and process
signals
in real time: up to 60-100 signals/sec. Every signal is being inserted
at the end ot this table. The processor has about 30% reseve.

Piotr Malecki

> The behaviour does sound strange even if you are running this on a cold
> cache. Can you post the graphical plan with statistics for both versions
> of
> the query?
>
> --
> -----------------------------------------------
> Robert Waywell
> Sybase Adaptive Server Anywhere Developer - Version 8
> Sybase Certified Professional
>
> Sybase's iAnywhere Solutions
>
> Please respond ONLY to newsgroup
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports:
> http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=Bug
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288
>
> "Piotr Ma³ecki" <piotr.malecki@adinfo.pl> wrote in message
> news:45f89812@forums-1-dub...
>> ASA 10.0.0.2788
>>
>> I have a table Signals with 6 mln rows with primary key
>> on BigInt type column: IdSignal.
>>
>> When I put the query:
>>
>> SELECT TOP 1 IdSignal INTO ll_var FROM Signals
>> ORDER BY IdSignal
>>
>> database server waits 3 minutes doing much disk operations.
>>
>> Then I rewrite the query, suggesting that primary key is a really
>> usefull thing:
>>
>> SELECT TOP 1 IdSignal INTO ll_var FROM Signals
>> FORCE INDEX (Signals)
>> ORDER BY IdSignal
>>
>> (Signals is the name of primary key)
>>
>> Server scans the table, a result is visible after serveral minutes.
>> Then I changed the database option: User_estimates = Enabled,
>> also Prefetch = Off and no results.
>>
>> I give up.
>>
>>
>> Piotr Malecki
>>
>
>


Rob Waywell Posted on 2007-03-15 15:49:44.0Z
From: "Rob Waywell" <rwaywell_no_spam_please@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <45f89812@forums-1-dub> <45f94337$1@forums-1-dub> <45f954ae@forums-1-dub>
Subject: Re: Primary key ignoring
Lines: 98
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: rwaywell-xp2.sybase.com
X-Original-NNTP-Posting-Host: rwaywell-xp2.sybase.com
Message-ID: <45f96b18$1@forums-1-dub>
Date: 15 Mar 2007 07:49:44 -0800
X-Trace: forums-1-dub 1173973784 10.25.98.235 (15 Mar 2007 07:49:44 -0800)
X-Original-Trace: 15 Mar 2007 07:49:44 -0800, rwaywell-xp2.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5886
Article PK: 1867

The plan shows that we are using the primary key to locate the records. Can
you provide the CREATE TABLE statement to verify the column(s) in the
primary key?

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

"Piotr Ma³ecki" <piotr.malecki@adinfo.pl> wrote in message
news:45f954ae@forums-1-dub...
>I have attached the plan (afeter 20 minutes of preparation by the server).
> The table's name differs but the table stores signals.
>
> What is interesting - in the same time the database works fine and process
> signals
> in real time: up to 60-100 signals/sec. Every signal is being inserted
> at the end ot this table. The processor has about 30% reseve.
>
> Piotr Malecki
>
>> The behaviour does sound strange even if you are running this on a cold
>> cache. Can you post the graphical plan with statistics for both versions
>> of
>> the query?
>>
>> --
>> -----------------------------------------------
>> Robert Waywell
>> Sybase Adaptive Server Anywhere Developer - Version 8
>> Sybase Certified Professional
>>
>> Sybase's iAnywhere Solutions
>>
>> Please respond ONLY to newsgroup
>>
>> EBF's and Patches: http://downloads.sybase.com
>> choose SQL Anywhere Studio >> change 'time frame' to all
>>
>> To Submit Bug Reports:
>> http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=Bug
>>
>> SQL Anywhere Studio Supported Platforms and Support Status
>> http://my.sybase.com/detail?id=1002288
>>
>> "Piotr Ma³ecki" <piotr.malecki@adinfo.pl> wrote in message
>> news:45f89812@forums-1-dub...
>>> ASA 10.0.0.2788
>>>
>>> I have a table Signals with 6 mln rows with primary key
>>> on BigInt type column: IdSignal.
>>>
>>> When I put the query:
>>>
>>> SELECT TOP 1 IdSignal INTO ll_var FROM Signals
>>> ORDER BY IdSignal
>>>
>>> database server waits 3 minutes doing much disk operations.
>>>
>>> Then I rewrite the query, suggesting that primary key is a really
>>> usefull thing:
>>>
>>> SELECT TOP 1 IdSignal INTO ll_var FROM Signals
>>> FORCE INDEX (Signals)
>>> ORDER BY IdSignal
>>>
>>> (Signals is the name of primary key)
>>>
>>> Server scans the table, a result is visible after serveral minutes.
>>> Then I changed the database option: User_estimates = Enabled,
>>> also Prefetch = Off and no results.
>>>
>>> I give up.
>>>
>>>
>>> Piotr Malecki
>>>
>>
>>
>
>
>


Piotr Ma³ecki Posted on 2007-03-15 16:00:01.0Z
From: "Piotr Ma³ecki" <piotr.malecki@adinfo.pl>
Newsgroups: ianywhere.public.general
References: <45f89812@forums-1-dub> <45f94337$1@forums-1-dub> <45f954ae@forums-1-dub> <45f96b18$1@forums-1-dub>
Subject: Re: Primary key ignoring
Lines: 193
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: eo110.internetdsl.tpnet.pl
X-Original-NNTP-Posting-Host: eo110.internetdsl.tpnet.pl
Message-ID: <45f96d81@forums-1-dub>
Date: 15 Mar 2007 08:00:01 -0800
X-Trace: forums-1-dub 1173974401 80.53.14.110 (15 Mar 2007 08:00:01 -0800)
X-Original-Trace: 15 Mar 2007 08:00:01 -0800, eo110.internetdsl.tpnet.pl
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5887
Article PK: 1865

Table definition. The PK is not the first because of historical reasons.

--
Piotr Malecki

> The plan shows that we are using the primary key to locate the records.
> Can
> you provide the CREATE TABLE statement to verify the column(s) in the
> primary key?
>
> --
> -----------------------------------------------
> Robert Waywell
> Sybase Adaptive Server Anywhere Developer - Version 8
> Sybase Certified Professional
>
> Sybase's iAnywhere Solutions
>
> Please respond ONLY to newsgroup
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports:
> http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=Bug
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288
>
> "Piotr Ma³ecki" <piotr.malecki@adinfo.pl> wrote in message
> news:45f954ae@forums-1-dub...
>>I have attached the plan (afeter 20 minutes of preparation by the server).
>> The table's name differs but the table stores signals.
>>
>> What is interesting - in the same time the database works fine and
>> process
>> signals
>> in real time: up to 60-100 signals/sec. Every signal is being inserted
>> at the end ot this table. The processor has about 30% reseve.
>>
>> Piotr Malecki
>>
>>> The behaviour does sound strange even if you are running this on a cold
>>> cache. Can you post the graphical plan with statistics for both versions
>>> of
>>> the query?
>>>
>>> --
>>> -----------------------------------------------
>>> Robert Waywell
>>> Sybase Adaptive Server Anywhere Developer - Version 8
>>> Sybase Certified Professional
>>>
>>> Sybase's iAnywhere Solutions
>>>
>>> Please respond ONLY to newsgroup
>>>
>>> EBF's and Patches: http://downloads.sybase.com
>>> choose SQL Anywhere Studio >> change 'time frame' to all
>>>
>>> To Submit Bug Reports:
>>> http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=Bug
>>>
>>> SQL Anywhere Studio Supported Platforms and Support Status
>>> http://my.sybase.com/detail?id=1002288
>>>
>>> "Piotr Ma³ecki" <piotr.malecki@adinfo.pl> wrote in message
>>> news:45f89812@forums-1-dub...
>>>> ASA 10.0.0.2788
>>>>
>>>> I have a table Signals with 6 mln rows with primary key
>>>> on BigInt type column: IdSignal.
>>>>
>>>> When I put the query:
>>>>
>>>> SELECT TOP 1 IdSignal INTO ll_var FROM Signals
>>>> ORDER BY IdSignal
>>>>
>>>> database server waits 3 minutes doing much disk operations.
>>>>
>>>> Then I rewrite the query, suggesting that primary key is a really
>>>> usefull thing:
>>>>
>>>> SELECT TOP 1 IdSignal INTO ll_var FROM Signals
>>>> FORCE INDEX (Signals)
>>>> ORDER BY IdSignal
>>>>
>>>> (Signals is the name of primary key)
>>>>
>>>> Server scans the table, a result is visible after serveral minutes.
>>>> Then I changed the database option: User_estimates = Enabled,
>>>> also Prefetch = Off and no results.
>>>>
>>>> I give up.
>>>>
>>>>
>>>> Piotr Malecki
>>>>
>>>
>>>
>>
>>
>>
>
>


Breck Carter [Team iAnywhere] Posted on 2007-03-15 16:37:41.0Z
From: "Breck Carter [Team iAnywhere]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Primary key ignoring
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <1itiv2pj8mtfjkg9r41r17o2ciqg1448si@4ax.com>
References: <45f89812@forums-1-dub> <45f94337$1@forums-1-dub> <45f954ae@forums-1-dub> <45f96b18$1@forums-1-dub> <45f96d81@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 15 Mar 2007 08:37:41 -0800
X-Trace: forums-1-dub 1173976661 64.7.134.118 (15 Mar 2007 08:37:41 -0800)
X-Original-Trace: 15 Mar 2007 08:37:41 -0800, bcarter.sentex.ca
Lines: 13
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5888
Article PK: 1870

What is the page size of the database?

On 15 Mar 2007 08:00:01 -0800, "Piotr Ma³ecki"

<piotr.malecki@adinfo.pl> wrote:

>Table definition. The PK is not the first because of historical reasons.

--
Breck Carter [Team iAnywhere]
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
The book: http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
breck.carter@risingroad.com


Piotr Malecki Posted on 2007-03-15 16:46:36.0Z
From: "Piotr Malecki" <piotr.malecki@adinfo.pl>
Newsgroups: ianywhere.public.general
References: <45f89812@forums-1-dub> <45f94337$1@forums-1-dub> <45f954ae@forums-1-dub> <45f96b18$1@forums-1-dub> <45f96d81@forums-1-dub> <1itiv2pj8mtfjkg9r41r17o2ciqg1448si@4ax.com>
Subject: Re: Primary key ignoring
Lines: 23
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: eo110.internetdsl.tpnet.pl
X-Original-NNTP-Posting-Host: eo110.internetdsl.tpnet.pl
Message-ID: <45f9786c$1@forums-1-dub>
Date: 15 Mar 2007 08:46:36 -0800
X-Trace: forums-1-dub 1173977196 80.53.14.110 (15 Mar 2007 08:46:36 -0800)
X-Original-Trace: 15 Mar 2007 08:46:36 -0800, eo110.internetdsl.tpnet.pl
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5889
Article PK: 1872

4096 bytes.

Should I reload the table?

--
Piotr Malecki

> What is the page size of the database?
>
> On 15 Mar 2007 08:00:01 -0800, "Piotr Ma³ecki"
> <piotr.malecki@adinfo.pl> wrote:
>
>>Table definition. The PK is not the first because of historical reasons.
>
> --
> Breck Carter [Team iAnywhere]
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
> The book:
> http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
> breck.carter@risingroad.com


Rob Waywell Posted on 2007-03-16 14:34:36.0Z
From: "Rob Waywell" <rwaywell_no_spam_please@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <45f89812@forums-1-dub> <45f94337$1@forums-1-dub> <45f954ae@forums-1-dub> <45f96b18$1@forums-1-dub> <45f96d81@forums-1-dub> <1itiv2pj8mtfjkg9r41r17o2ciqg1448si@4ax.com> <45f9786c$1@forums-1-dub>
Subject: Re: Primary key ignoring
Lines: 75
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
NNTP-Posting-Host: rwaywell-xp2.sybase.com
X-Original-NNTP-Posting-Host: rwaywell-xp2.sybase.com
Message-ID: <45faaafc$1@forums-1-dub>
Date: 16 Mar 2007 06:34:36 -0800
X-Trace: forums-1-dub 1174055676 10.25.98.235 (16 Mar 2007 06:34:36 -0800)
X-Original-Trace: 16 Mar 2007 06:34:36 -0800, rwaywell-xp2.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5893
Article PK: 1876

Ok, you do have a 4 level index, but I don't think that is enough to explain
the behaviour. There is a little confusion in the scenario since you seem to
have switched between a TOP 1 and TOP 11, but regardless using the index,
we only need to retrieve the first 1 (or 11) records. Having verified that
the primary key is on the IdKomunikatu column, we've verified that the data
is already sorted by primary key which is consisten with what the plan is
showing.

So the plan looks good, but it doesn't explain why it is taking 533 seconds
to retrieve 11 records, or why the estimated time was actually much larger
at over 2000 seconds. A couple of things that look weird are:

1) Large value for IndAdd
2) # of Disk Reads

One thing that you could try as a diagnostic would be to re-run this
query at a time when the Signals are not being added to the table. I can see
that you have 1.6GB of cache allocated to the database but I looked back
through the thread and I don't see how large your database is. You do
mention that the CPU has about 30% "available" which if I turn it around
would mean that you are only using 70% of CPU capacity. That sounds healthy.
One possibility is that your system is actually disk bound and this
particular request is getting starved for disk I/O and thrashing. Usually if
the system is disk bound I see lower CPU utilization (eg: 20-30% vs 70-80%).
Re-running the query on a quiet system would clarify if that is the case.

That said, we are interested in looking at the database if you would
like to open a Support case and submit the database to us.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

"Piotr Malecki" <piotr.malecki@adinfo.pl> wrote in message
news:45f9786c$1@forums-1-dub...
> 4096 bytes.
>
> Should I reload the table?
>
> --
> Piotr Malecki
>
>> What is the page size of the database?
>>
>> On 15 Mar 2007 08:00:01 -0800, "Piotr Ma³ecki"
>> <piotr.malecki@adinfo.pl> wrote:
>>
>>>Table definition. The PK is not the first because of historical reasons.
>>
>> --
>> Breck Carter [Team iAnywhere]
>> RisingRoad SQL Anywhere and MobiLink Professional Services
>> www.risingroad.com
>> The book:
>> http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
>> breck.carter@risingroad.com
>
>


Piotr Ma³ecki Posted on 2007-03-17 16:07:08.0Z
From: "Piotr Ma³ecki" <piotr.malecki@adinfo.pl>
Newsgroups: ianywhere.public.general
References: <45f89812@forums-1-dub> <45f94337$1@forums-1-dub> <45f954ae@forums-1-dub> <45f96b18$1@forums-1-dub> <45f96d81@forums-1-dub> <1itiv2pj8mtfjkg9r41r17o2ciqg1448si@4ax.com> <45f9786c$1@forums-1-dub> <45faaafc$1@forums-1-dub>
Subject: Re: Primary key ignoring
Lines: 105
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: eo110.internetdsl.tpnet.pl
X-Original-NNTP-Posting-Host: eo110.internetdsl.tpnet.pl
Message-ID: <45fc122c@forums-1-dub>
Date: 17 Mar 2007 08:07:08 -0800
X-Trace: forums-1-dub 1174147628 80.53.14.110 (17 Mar 2007 08:07:08 -0800)
X-Original-Trace: 17 Mar 2007 08:07:08 -0800, eo110.internetdsl.tpnet.pl
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5896
Article PK: 1878

I validated the database. I've got three messages about
orphaned pages and validation process stopped with
information, that database cannot be validated.
I tried to unload the database but it didnt work.

Probably such strange behaviour was a result of some
inconsistency in database pages.

I recreated the database from the initial scripts. So far it works OK.
I see on performance monitor that with processing steady stream
of 100 signals/second disks and processors have reserve.
I observe saturation of resources over 150 signals/seconds
but our customer expects less signal frequency.
The database is for evealuating throughput of our system using ASA10.

If the described situation comes back I'll open a Support case.

--
Piotr Malecki

AD INFO Bazy Danych
Warszawa, ul. Mehoffera 53
tel. 022-676-75-93
fax 022-814-27-88

U¿ytkownik "Rob Waywell" <rwaywell_no_spam_please@ianywhere.com> napisa³ w
wiadomo¶ci news:45faaafc$1@forums-1-dub...

> Ok, you do have a 4 level index, but I don't think that is enough to
> explain the behaviour. There is a little confusion in the scenario since
> you seem to have switched between a TOP 1 and TOP 11, but regardless
> using the index, we only need to retrieve the first 1 (or 11) records.
> Having verified that the primary key is on the IdKomunikatu column, we've
> verified that the data is already sorted by primary key which is consisten
> with what the plan is showing.
>
> So the plan looks good, but it doesn't explain why it is taking 533
> seconds to retrieve 11 records, or why the estimated time was actually
> much larger at over 2000 seconds. A couple of things that look weird are:
>
> 1) Large value for IndAdd
> 2) # of Disk Reads
>
> One thing that you could try as a diagnostic would be to re-run this
> query at a time when the Signals are not being added to the table. I can
> see that you have 1.6GB of cache allocated to the database but I looked
> back through the thread and I don't see how large your database is. You do
> mention that the CPU has about 30% "available" which if I turn it around
> would mean that you are only using 70% of CPU capacity. That sounds
> healthy. One possibility is that your system is actually disk bound and
> this particular request is getting starved for disk I/O and thrashing.
> Usually if the system is disk bound I see lower CPU utilization (eg:
> 20-30% vs 70-80%). Re-running the query on a quiet system would clarify if
> that is the case.
>
> That said, we are interested in looking at the database if you would
> like to open a Support case and submit the database to us.
>
> --
> -----------------------------------------------
> Robert Waywell
> Sybase Adaptive Server Anywhere Developer - Version 8
> Sybase Certified Professional
>
> Sybase's iAnywhere Solutions
>
> Please respond ONLY to newsgroup
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports:
> http://case-express.sybase.com/cx/cx.stm?starturl=casemessage.ssc?CASETYPE=Bug
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288
>
> "Piotr Malecki" <piotr.malecki@adinfo.pl> wrote in message
> news:45f9786c$1@forums-1-dub...
>> 4096 bytes.
>>
>> Should I reload the table?
>>
>> --
>> Piotr Malecki
>>
>>> What is the page size of the database?
>>>
>>> On 15 Mar 2007 08:00:01 -0800, "Piotr Ma³ecki"
>>> <piotr.malecki@adinfo.pl> wrote:
>>>
>>>>Table definition. The PK is not the first because of historical reasons.
>>>
>>> --
>>> Breck Carter [Team iAnywhere]
>>> RisingRoad SQL Anywhere and MobiLink Professional Services
>>> www.risingroad.com
>>> The book:
>>> http://www.risingroad.com/SQL_Anywhere_Studio_9_Developers_Guide.html
>>> breck.carter@risingroad.com
>>
>>
>
>


Lucjan Chmura Posted on 2007-03-15 17:26:29.0Z
Newsgroups: ianywhere.public.general
Subject: Re: Primary key ignoring
From: Lucjan Chmura <myname@company.com>
References: <45f89812@forums-1-dub>
Organization: iAnywhere
Message-ID: <Xns98F488BBDC649lchmurasybasecom@10.22.241.106>
User-Agent: Xnews/5.04.25
NNTP-Posting-Host: lchmura-pc.sybase.com
X-Original-NNTP-Posting-Host: lchmura-pc.sybase.com
Date: 15 Mar 2007 09:26:29 -0800
X-Trace: forums-1-dub 1173979589 10.25.98.124 (15 Mar 2007 09:26:29 -0800)
X-Original-Trace: 15 Mar 2007 09:26:29 -0800, lchmura-pc.sybase.com
Lines: 9
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5890
Article PK: 1875

Hi there,
try the following, logically the same. Instead of SELECT top 1, use SELECT
MIN(..) or Max().. depending how you order in 'ORDER' clause.

SELECT MIN(IdSignal) INTO ll_var FROM Signals
ORDER BY IdSignal

Lucjan


Piotr Ma³ecki Posted on 2007-03-15 17:36:06.0Z
From: "Piotr Ma³ecki" <piotr.malecki@adinfo.pl>
Newsgroups: ianywhere.public.general
References: <45f89812@forums-1-dub> <Xns98F488BBDC649lchmurasybasecom@10.22.241.106>
Subject: Re: Primary key ignoring
Lines: 22
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: eo110.internetdsl.tpnet.pl
X-Original-NNTP-Posting-Host: eo110.internetdsl.tpnet.pl
Message-ID: <45f98406@forums-1-dub>
Date: 15 Mar 2007 09:36:06 -0800
X-Trace: forums-1-dub 1173980166 80.53.14.110 (15 Mar 2007 09:36:06 -0800)
X-Original-Trace: 15 Mar 2007 09:36:06 -0800, eo110.internetdsl.tpnet.pl
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5891
Article PK: 1871

The same: several minutes.

--
Piotr Malecki

AD INFO Bazy Danych
Warszawa, ul. Mehoffera 53
tel. 022-676-75-93
fax 022-814-27-88
U¿ytkownik "Lucjan Chmura" <myname@company.com> napisa³ w wiadomo¶ci
news:Xns98F488BBDC649lchmurasybasecom@10.22.241.106...

> Hi there,
> try the following, logically the same. Instead of SELECT top 1, use SELECT
> MIN(..) or Max().. depending how you order in 'ORDER' clause.
>
> SELECT MIN(IdSignal) INTO ll_var FROM Signals
> ORDER BY IdSignal
>
> Lucjan
>


Lucjan Chmura Posted on 2007-03-16 14:51:44.0Z
Newsgroups: ianywhere.public.general
Subject: Re: Primary key ignoring
From: Lucjan Chmura <myname@company.com>
References: <45f89812@forums-1-dub> <Xns98F488BBDC649lchmurasybasecom@10.22.241.106> <45f98406@forums-1-dub>
Organization: iAnywhere
Message-ID: <Xns98F56E7EC978Dlchmurasybasecom@10.22.241.106>
User-Agent: Xnews/5.04.25
NNTP-Posting-Host: lchmura-pc.sybase.com
X-Original-NNTP-Posting-Host: lchmura-pc.sybase.com
Date: 16 Mar 2007 06:51:44 -0800
X-Trace: forums-1-dub 1174056704 10.25.98.124 (16 Mar 2007 06:51:44 -0800)
X-Original-Trace: 16 Mar 2007 06:51:44 -0800, lchmura-pc.sybase.com
Lines: 13
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5894
Article PK: 1874

"Piotr Ma³ecki" <piotr.malecki@adinfo.pl> wrote in news:45f98406@forums-1-
dub:

> The same: several minutes.
>

can you try to create stats.. e.g.

create statistics on komunikaty

and then run it.


Piotr Ma³ecki Posted on 2007-03-17 16:09:34.0Z
From: "Piotr Ma³ecki" <piotr.malecki@adinfo.pl>
Newsgroups: ianywhere.public.general
References: <45f89812@forums-1-dub> <Xns98F488BBDC649lchmurasybasecom@10.22.241.106> <45f98406@forums-1-dub> <Xns98F56E7EC978Dlchmurasybasecom@10.22.241.106>
Subject: Re: Primary key ignoring
Lines: 27
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: eo110.internetdsl.tpnet.pl
X-Original-NNTP-Posting-Host: eo110.internetdsl.tpnet.pl
Message-ID: <45fc12be$1@forums-1-dub>
Date: 17 Mar 2007 08:09:34 -0800
X-Trace: forums-1-dub 1174147774 80.53.14.110 (17 Mar 2007 08:09:34 -0800)
X-Original-Trace: 17 Mar 2007 08:09:34 -0800, eo110.internetdsl.tpnet.pl
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5897
Article PK: 1880

I tried it also, with reorganize table. After rebuilding database
works normally.

--
Piotr Malecki

AD INFO Bazy Danych
Warszawa, ul. Mehoffera 53
tel. 022-676-75-93
fax 022-814-27-88
U¿ytkownik "Lucjan Chmura" <myname@company.com> napisa³ w wiadomo¶ci
news:Xns98F56E7EC978Dlchmurasybasecom@10.22.241.106...

> "Piotr Ma³ecki" <piotr.malecki@adinfo.pl> wrote in news:45f98406@forums-1-
> dub:
>
>> The same: several minutes.
>>
>
> can you try to create stats.. e.g.
>
> create statistics on komunikaty
>
> and then run it.
>
>