Hello,

In our program, we've used this program for years and years and years (V6,
V8, V9)

When pressing insert into our program, it's sub second.

All we did was convert to V11 and now it's 2 seconds.

It's the same code.

It's taking long on reading a record backwards:

0149CH(2) 09:41:12.012 Fetching Row from Statement 03BE7EB0H Return Code: 0
Time Taken:1.17 secs
0149CH(2) 09:41:13.182 PREVIOUS(DBA.BILLHEAD:0EF0A3CH) Time Taken:1.17 secs

It's actually taking 1.17 seconds to read the last record. I have no
control what this driver does and how it does it because it's a Clarion
application. Meaning, you can't tell me to change my code or change
something because I can't.

This RAD tool does it this way.

So now what do I do?

-Robert

Hi,

This is the statement.

On V9.02, it's sub-second
On V11.01, it takes 1.17 seconds

Same database, same machine, same number of records, same everything.

SELECT
CUSTNUMBER,INVOICENUMBER,ENTERBY,TAXABLESALE,ORDER TYPE,INVOICEDATE,INVOICETIME,TOTALSALE,TOTALPAYMEN TS,TAXAMOUNT,REIMBURSEMENTS,DESCTAX,INVOICENOTE,SH IFT4INVOICE,SHIPNAME,SHIPSTREET,SHIPCITY,SHIPSTATE ,SHIPZIPCODE,SHIPCOUNTRY,SHIPCOMPANY,PROMOCODE
FROM DBA.BILLHEAD ORDER BY INVOICENUMBER DESC

First, the basics, make sure the physical database file isn't greatly
suffering from fragmentation (file, or internal database), and that
the database server has a reasonable amount of RAM cache.

Then, look at the "Detailed and node statistics" plan (see below)

If you want, send me the plan via email: breck dot carter at gmail dot
etcetera

How to Save a Graphical Plan

For Version 11:

- If desired, run CALL sa_flush_cache() in dbisql.

- Type or paste your query into dbisql.

- Choose Tools - Plan Viewer.

- Select Statisics level: Detailed and node statistics.

- Select Cursor type: Asensitive.

- Select Update status: Read-only.

- Click "Get Plan".

- When the plan appears, click "Save As...".

- Use Save as type Execution plan (*.saplan).


On 23 Oct 2009 06:51:10 -0700, "Robert Paresi"
<FirstInitialLastName (AT) innquest (DOT) com> wrote:

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter (AT) risingroad (DOT) com

Breck,

How could the database be suffering from Fragmentation when it was unloaded
and reloaded the day before?

It's doing this on a table with just 86,000 records in it.

-Robert

"Breck Carter [TeamSybase]" <NOSPAM__breck.carter (AT) gmail (DOT) com> wrote in
message news:0ch3e51r12u2kag99gj9thh6un5nkif68f (AT) 4ax (DOT) com...

If it is on Windows, it could easily be suffering from file
fragmentation if the file was not defragmented after it grew to its
full final size. See
http://files.diskeeper.com/pdf/HowFi...nWindowsXP.pdf

I detect a sense of panic, never helpful when things are going badly.
We are here to help!

Breck

On 23 Oct 2009 08:35:49 -0700, "Robert Paresi"
<FirstInitialLastName (AT) innquest (DOT) com> wrote:

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter (AT) risingroad (DOT) com

Hi Robert,

Which exact version of 11.0.1 is this? There were a number of issues
fixed since 11.0.1 GA pertaining to optimizer fixes that moving to a
later EBF may be able to solve.

Otherwise, which indexes are defined on this table? Posting a graphical
plan will help us to diagnose why this operation is suddenly
more expensive - if we are doing a table scan, it would make sense that
this is taking longer.

Regards,

Robert Paresi wrote:
--
Jeff Albion, Sybase iAnywhere

iAnywhere Developer Community :
http://www.sybase.com/developer/libr...ere-techcorner
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
SQL Anywhere Patches and EBFs :
http://downloads.sybase.com/swd/summ...&timeframe =0
Report a Bug/Open a Case : http://case-express.sybase.com/cx/

I can think of several reasons why you might be seeing the behaviour
that you are; as Jeff indicated, a graphical plan will assist us greatly
to determine the cause and then recommend a solution.

Glenn

Robert Paresi wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

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

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

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

Hi,

The build on the server is the latest EBF for 11.01.2324

-Robert


"Jeff Albion [Sybase iAnywhere]" <firstname.lastname (AT) ianywhere (DOT) com> wrote in
message news:4ae21ce7 (AT) forums-1-dub (DOT) ..

Hi,

Can you tell me how you want to plan (ISQL or Sybase Central or ODBC trace)
and where you want me to send it?

-Robert

"Glenn Paulley [Sybase iAnywhere]" <paulley (AT) ianywhere (DOT) com> wrote

I want to see the plan WITH STATISTICS from DBISQL:

1. Type or paste your query into dbisql.
2. Choose Tools - Plan Viewer.
3. Select Statisics level: Detailed and node statistics.
4. Select Cursor type: Asensitive.
5. Select Update status: Read-only.
6. Click "Get Plan".
7. When the plan appears, click "Save As...".
8. Use Save as type Execution plan (*.saplan).

You can post it here as an attachment.

Glenn

Robert Paresi wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

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

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

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport