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.

SQL Anywhere 9.0.2 for Netware slow

12 posts in General Discussion Last posting was on 2009-01-06 18:13:16.0Z
forums.sybase.com Posted on 2008-12-16 22:22:25.0Z
From: "forums.sybase.com" <magotici@labstat.com>
Newsgroups: ianywhere.public.general
Subject: SQL Anywhere 9.0.2 for Netware slow
Lines: 6
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.3790.3959
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4325
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49482a21$1@forums-1-dub>
Date: 16 Dec 2008 14:22:25 -0800
X-Trace: forums-1-dub 1229466145 10.22.241.152 (16 Dec 2008 14:22:25 -0800)
X-Original-Trace: 16 Dec 2008 14:22:25 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7259
Article PK: 5475

After I updated to ASA 9.0.2 Netware EBF 3508, the DB got slower. Is there
any known issue?
I did the upgrade because of the problem with the log file from backup
image.


David Fishburn Posted on 2008-12-17 15:56:15.0Z
Newsgroups: ianywhere.public.general
Subject: Re: SQL Anywhere 9.0.2 for Netware slow
From: David Fishburn <fishburn_spam_off@ianywhere.com>
References: <49482a21$1@forums-1-dub>
Organization: iAnywhere Solutions
Message-ID: <Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106>
User-Agent: Xnews/2005.10.18
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 17 Dec 2008 07:56:15 -0800
X-Trace: forums-1-dub 1229529375 10.22.241.152 (17 Dec 2008 07:56:15 -0800)
X-Original-Trace: 17 Dec 2008 07:56:15 -0800, vip152.sybase.com
Lines: 40
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7263
Article PK: 5478

"forums.sybase.com" <magotici@labstat.com> wrote in
news:49482a21$1@forums-1-dub of ianywhere.public.general:

fsc> After I updated to ASA 9.0.2 Netware EBF 3508, the DB got slower. Is
fsc> there any known issue?
fsc> I did the upgrade because of the problem with the log file from backup
fsc> image.

For these types of issues, it is generally not the ENTIRE database, it
is usually certain queries.

What have you done to figure out _which_ queries are slower?
Don't know what your app does, but it is usually a portion of a page
in your app is much slower than before. This usually points to an
individual query.

--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng11 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/

Developer Community / Whitepapers
http://www.ianywhere.com/developer

Case-Express - to report bugs
http://case-express.sybase.com

CodeXchange - Free samples
http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList

Documentation
http://dcx.sybase.com/


magotici Posted on 2008-12-18 14:45:16.0Z
From: "magotici" <magotici@labstat.com>
Newsgroups: ianywhere.public.general
References: <49482a21$1@forums-1-dub> <Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106>
Subject: Re: SQL Anywhere 9.0.2 for Netware slow
Lines: 98
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.3790.3959
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4325
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <494a61fc$1@forums-1-dub>
Date: 18 Dec 2008 06:45:16 -0800
X-Trace: forums-1-dub 1229611516 10.22.241.152 (18 Dec 2008 06:45:16 -0800)
X-Original-Trace: 18 Dec 2008 06:45:16 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7274
Article PK: 5490

Hi David,
I have the querry that's slow now but was'nt in the previous version:

SELECT "project"."project",
"project"."client_due_date",
"project"."brands_assignment",
"project"."tot_obs",
"project"."number_block",
"project"."number_test_brands",
"project"."control",
"project"."product_arrival",
"project"."due_date_2",
"project_objectives"."year",
"project_objectives"."month",
"project_objectives"."percent",
"project"."type",
"project_types"."description",
"project_types"."colour_bkg",
"project_types"."colour_text",
(select count(schedule_id) from p_info where p_info.project =
project.project) as tot_units,
(select count(repeat_date) from p_info where p_info.project =
project.project) as tot_repeats,
(select count(*) from p_info where p_info.project = project.project
and month(repeat_date) > 0 and status = 100) as repeats_done,
(select sum(percent) from project_objectives where
project_objectives.project = project.project ) as total_objectives,
"project"."project_closed",
"project"."description"
FROM {oj "project" LEFT OUTER JOIN "project_objectives" ON
"project"."project" = "project_objectives"."project"},
"project_types"
WHERE ("project"."project_closed" is null OR
:pn_closed = 1 and "project"."project_closed" >= :pd_start and
"project"."project_closed" <= :pd_end) AND
("project"."product_arrival" is not null OR
:pn_pending = 1) AND
( "project"."type" = "project_types"."type" ) and
(("year" = year(:pd_start) AND
"month" >= month(:pd_start) OR
"year" > year(:pd_start)) AND
("year" = year(:pd_end) AND
"month" <= month(:pd_end) OR
"year" < year(:pd_end)) OR
"percent" is null OR
"percent" = 0) AND
"project"."special" = 'N'

Thanks,
magotici

"David Fishburn" <fishburn_spam_off@ianywhere.com> wrote in message
news:Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106...
> "forums.sybase.com" <magotici@labstat.com> wrote in
> news:49482a21$1@forums-1-dub of ianywhere.public.general:
>
> fsc> After I updated to ASA 9.0.2 Netware EBF 3508, the DB got slower. Is
> fsc> there any known issue?
> fsc> I did the upgrade because of the problem with the log file from
> backup
> fsc> image.
>
> For these types of issues, it is generally not the ENTIRE database, it
> is usually certain queries.
>
> What have you done to figure out _which_ queries are slower?
> Don't know what your app does, but it is usually a portion of a page
> in your app is much slower than before. This usually points to an
> individual query.
>
> --
> David Fishburn
> Certified ASA Developer Version 8
> iAnywhere Solutions - Sybase
> Professional Services
> Please only post to the newsgroup
> Please ALWAYS include version and MORE importantly BUILD number with
> EACH post (dbeng11 -v).
>
> EBFs and Maintenance Releases
> http://downloads.sybase.com/
>
> Developer Community / Whitepapers
> http://www.ianywhere.com/developer
>
> Case-Express - to report bugs
> http://case-express.sybase.com
>
> CodeXchange - Free samples
> http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList
>
> Documentation
> http://dcx.sybase.com/
>


"Chris Keating [Sybase iAnywhere]" <keating_no Posted on 2008-12-18 15:33:11.0Z
From: "Chris Keating [Sybase iAnywhere]" <keating_no@spam_sybase.com>
User-Agent: Thunderbird 2.0.0.18 (Windows/20081105)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: SQL Anywhere 9.0.2 for Netware slow
References: <49482a21$1@forums-1-dub> <Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106> <494a61fc$1@forums-1-dub>
In-Reply-To: <494a61fc$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <494a6d37@forums-1-dub>
Date: 18 Dec 2008 07:33:11 -0800
X-Trace: forums-1-dub 1229614391 10.22.241.152 (18 Dec 2008 07:33:11 -0800)
X-Original-Trace: 18 Dec 2008 07:33:11 -0800, vip152.sybase.com
Lines: 119
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7275
Article PK: 5492

Can you get a query plan for this?

To generate a Graphical Plan with Statistics

1. From DBISQL, go to Tools | Options and set the "Plan" option to
Graphical Plan with Statistics
2. Press Shift-F5 to get the plan
3. For ASA 8 and ASA9, Select File | Save As... and
Set the file type to XML
4. For SA 10, Select File | Save Plan...
5. provide a file name
6. Click Ok

You can view a saved graphical plan by

1. For ASA 8 and ASA9, from DBISQL, select File | Open and
Set the file type to XML
2. For SA 10, select File | Open Plan...
3. Select the Graphical plan file

magotici wrote:
> Hi David,
> I have the querry that's slow now but was'nt in the previous version:
>
> SELECT "project"."project",
> "project"."client_due_date",
> "project"."brands_assignment",
> "project"."tot_obs",
> "project"."number_block",
> "project"."number_test_brands",
> "project"."control",
> "project"."product_arrival",
> "project"."due_date_2",
> "project_objectives"."year",
> "project_objectives"."month",
> "project_objectives"."percent",
> "project"."type",
> "project_types"."description",
> "project_types"."colour_bkg",
> "project_types"."colour_text",
> (select count(schedule_id) from p_info where p_info.project =
> project.project) as tot_units,
> (select count(repeat_date) from p_info where p_info.project =
> project.project) as tot_repeats,
> (select count(*) from p_info where p_info.project = project.project
> and month(repeat_date) > 0 and status = 100) as repeats_done,
> (select sum(percent) from project_objectives where
> project_objectives.project = project.project ) as total_objectives,
> "project"."project_closed",
> "project"."description"
> FROM {oj "project" LEFT OUTER JOIN "project_objectives" ON
> "project"."project" = "project_objectives"."project"},
> "project_types"
> WHERE ("project"."project_closed" is null OR
> :pn_closed = 1 and "project"."project_closed" >= :pd_start and
> "project"."project_closed" <= :pd_end) AND
> ("project"."product_arrival" is not null OR
> :pn_pending = 1) AND
> ( "project"."type" = "project_types"."type" ) and
> (("year" = year(:pd_start) AND
> "month" >= month(:pd_start) OR
> "year" > year(:pd_start)) AND
> ("year" = year(:pd_end) AND
> "month" <= month(:pd_end) OR
> "year" < year(:pd_end)) OR
> "percent" is null OR
> "percent" = 0) AND
> "project"."special" = 'N'
>
> Thanks,
> magotici
>
>
>
> "David Fishburn" <fishburn_spam_off@ianywhere.com> wrote in message
> news:Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106...
>> "forums.sybase.com" <magotici@labstat.com> wrote in
>> news:49482a21$1@forums-1-dub of ianywhere.public.general:
>>
>> fsc> After I updated to ASA 9.0.2 Netware EBF 3508, the DB got slower. Is
>> fsc> there any known issue?
>> fsc> I did the upgrade because of the problem with the log file from
>> backup
>> fsc> image.
>>
>> For these types of issues, it is generally not the ENTIRE database, it
>> is usually certain queries.
>>
>> What have you done to figure out _which_ queries are slower?
>> Don't know what your app does, but it is usually a portion of a page
>> in your app is much slower than before. This usually points to an
>> individual query.
>>
>> --
>> David Fishburn
>> Certified ASA Developer Version 8
>> iAnywhere Solutions - Sybase
>> Professional Services
>> Please only post to the newsgroup
>> Please ALWAYS include version and MORE importantly BUILD number with
>> EACH post (dbeng11 -v).
>>
>> EBFs and Maintenance Releases
>> http://downloads.sybase.com/
>>
>> Developer Community / Whitepapers
>> http://www.ianywhere.com/developer
>>
>> Case-Express - to report bugs
>> http://case-express.sybase.com
>>
>> CodeXchange - Free samples
>> http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList
>>
>> Documentation
>> http://dcx.sybase.com/
>>
>
>


magotici Posted on 2008-12-18 16:30:39.0Z
From: "magotici" <magotici@labstat.com>
Newsgroups: ianywhere.public.general
References: <49482a21$1@forums-1-dub> <Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106> <494a61fc$1@forums-1-dub> <494a6d37@forums-1-dub>
Subject: Re: SQL Anywhere 9.0.2 for Netware slow
Lines: 373
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.3790.3959
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4325
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <494a7aaf@forums-1-dub>
Date: 18 Dec 2008 08:30:39 -0800
X-Trace: forums-1-dub 1229617839 10.22.241.152 (18 Dec 2008 08:30:39 -0800)
X-Original-Trace: 18 Dec 2008 08:30:39 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7276
Article PK: 5493

teHi,
Here is what I've got:

SELECT

SELECT "project"."project",
"project"."client_due_date",
"project"."brands_assignment",
"project"."tot_obs",
"project"."number_block",
"project"."number_test_brands",
"project"."control",
"project"."product_arrival",
"project"."due_date_2",
"project_objectives"."year",
"project_objectives"."month",
"project_objectives"."percent",
"project"."type",
"project_types"."description",
"project_types"."colour_bkg",
"project_types"."colour_text",
(select count(schedule_id) from p_info where p_info.project =
project.project) as tot_units,
(select count(repeat_date) from p_info where p_info.project =
project.project) as tot_repeats,
(select count(*) from p_info where p_info.project = project.project
and month(repeat_date) > 0 and status = 100) as repeats_done,
(select sum(percent) from project_objectives where
project_objectives.project = project.project ) as total_objectives,
"project"."project_closed",
"project"."description"
FROM "project" LEFT OUTER JOIN "project_objectives"
ON "project"."project" = "project_objectives"."project",
"project_types"
WHERE ("project"."project_closed" is null OR
0 = 1 and "project"."project_closed" >= '20080901' and
"project"."project_closed" <= '20090101') AND
("project"."product_arrival" is not null OR
0 = 1) AND
( "project"."type" = "project_types"."type" ) and
(("year" = year('20080901') AND
"month" >= month('20080901') OR
"year" > year('20080901')) AND
("year" = year('20090101') AND
"month" <= month('20090101') OR
"year" < year('20090101')) OR
"percent" is null OR
"percent" = 0) AND
"project"."special" = 'N'
Node Statistics
Estimates Actual Description
Invocations - 1 Number of times the result was computed
RowsReturned 70.062 111 Number of rows returned
PercentTotalCost 99.862 99.894 Run time as a percent of total
query time
RunTime 7.9344 13.229 Time to compute the results
CPUTime 7.9344 - Time required by CPU
DiskReadTime 0 - Time to perform reads from disk
DiskWriteTime 0 - Time to perform writes to disk
CacheHits - 3.6929e+06 Cache Hits
CacheRead - 3.6929e+06 Cache reads
CacheReadTable - 3.6929e+06 Cache table reads
DiskRead 0 0 Disk reads
DiskWrite 0 0 Disk writes
IndLookup - 1 Number of index lookups
ExprCacheStarts - 4 Number of times the expression cache was
started
ExprCacheLookups - 428 Number of lookups done in the expression
cache
ExprCacheHits - 192 Number of hits in the expression cache
ExprCacheInserts - 236 Number of values inserted into the
expression cache
RelocatableHeapPages - 19 Relocatable heap pages


Subtree Statistics
Estimates Actual Description
Invocations - 1 Number of times the result was computed
RowsReturned 70.062 111 Number of rows returned
PercentTotalCost 100 100 Run time as a percent of total query
time
RunTime 7.9453 13.243 Time to compute the results
CPUTime 7.9453 - Time required by CPU
DiskReadTime 0 - Time to perform reads from disk
DiskWriteTime 0 - Time to perform writes to disk
CacheHits - 3.6951e+06 Cache Hits
CacheRead - 3.6951e+06 Cache reads
CacheReadTable - 3.695e+06 Cache table reads
CacheReadIndLeaf - 188 Cache index leaf reads
DiskRead 0 0 Disk reads
DiskWrite 0 0 Disk writes
IndLookup - 113 Number of index lookups
ExprCacheStarts - 4 Number of times the expression cache was
started
ExprCacheLookups - 428 Number of lookups done in the expression
cache
ExprCacheHits - 192 Number of hits in the expression cache
ExprCacheInserts - 236 Number of values inserted into the
expression cache
HashRowsFiltered - 1439 Rate at which probe rows are rejected by
bit-vector filters
RelocatableHeapPages - 21 Relocatable heap pages


Optimizer statistics
Value Description
Costed subplans 34 Number of different enumeration strategies
considered by the optimizer
Estimated cache pages 37110 Estimated cache pages available for
this statement
CurrentCacheSize 153924 Current cache size in kilobytes
Isolation_level 0 Controls the locking isolation level
Optimization_goal First-row Optimize queries for first row or all
rows
Optimization_level 9 Reserved
Optimization_workload Mixed Controls whether optimizing for OLAP
or mixed queries
ProductVersion 9.0.2.3508 Product version
User_estimates Override-magic Controls whether to respect user
estimates


Select list
expr() varchar(20)
expr() date
expr() date
expr() unsigned smallint
expr() smallint
expr() smallint
expr() varchar(20)
expr() date
expr() date
expr() unsigned smallint
expr() tinyint
expr() decimal(5,2)
expr() char(3)
project_types.description varchar(15)
project_types.colour_bkg unsigned int
project_types.colour_text unsigned int
expr() int
expr() int
expr() int
expr() numeric(13,2)
expr() date
expr() varchar(1000)


Options
Value Description
Alias labstat Mounted database name
Ansi_blanks Off Controls truncation errors
Ansi_integer_overflow Off Controls whether integer overflow causes
an error
Ansi_update_constraints Cursors Controls the range of updates that
are permitted
Ansinull On Controls interpretation of NULL values
BlankPadding OFF Blank padding
BlobArenas ON Blob extension pages are stored separately from
table pages
Blocking On Controls response to locking conflicts
Capabilities 4087FFDD Database capability bits
CaseSensitive OFF Case sensitivity
CharSet cp850 Character Set
Checksum OFF Page checksum
ClusteredIndexes OFF Clustered indexes
Collation 850LATIN1 Collation name
Collect_statistics_on_dml_updates On Controls collection of
statistics during INSERT/UPDATE/DELETE statement execution
CommandLine -n labstat -x tcpip{myip=192.168.10.209} -ti 0
syb:\asany\labstat.db -n labstat Expanded command line used to start the
server
CompressedBTrees ON Compressed B-Tree indexes are supported
Compression OFF Compression enabled
Conversion_error On Controls datatype conversion errors
Date_format YYYY-MM-DD Controls format for DATE values
Date_order YMD Controls order of date components
DBFileFragments 0 Database file fragments
DefaultCollation 850LATIN1 Default Collation
Divide_by_zero_error On Controls divide-by-zero errors
Encryption None Encryption type
Extended_join_syntax On Controls errors when using duplicate
correlation names in joins
File syb:\asany\labstat.db Database file
FileSize 77423 File size in pages
FileVersion 41 Database file version number
FreePageBitMaps ON Free database pages managed via bitmaps
FreePages 116 Number of free pages in dbspace
HistogramHashFix OFF Fixed histogram hash implementation
Histograms ON Optimizer statistics maintained as histograms
IndexStatistics OFF Index Statistics
IsJavaAvailable NO Java available
JavaObjectsEnabled NO Java objects enabled
IsNetworkServer YES Is Network Server
IsRuntimeServer NO Is Runtime Server
Language us_english
LicenseCount 300 Number of licensed seats
LicensedCompany Labstat Int Inc Name of the licensed company
LicensedUser Manuel Agotici Name of the licensed user
LicenseType networked seat (per-seat) License type
LivenessTimeout 120 Client liveness timeout default
LogFileFragments 0 Log file fragments
MachineName S1 Name of the machine
MainHeapBytes 2052272 Main heap bytes in cache
Max_plans_cached 20 Maximum number of cached execution plans for a
connection
MaxCacheSize 153924 Maximum cache size in kilobytes
MinCacheSize 153924 Minimum cache size in kilobytes
MultiByteCharSet OFF Multi Byte Character Set ( on/off )
Name labstat
NamedConstraints OFF Named Constraints
NumProcessorsAvail 1 Number of processors on server
NumProcessorsMax 0 Maximum number of processors used
PageSize 2048 Database page size
PeakCacheSize 153924 Peak cache size in kilobytes
Platform Netware Operating system platform
PlatformVer NetWare 6.0, Support Pack 5 Operating system platform
version
Precision 40 Maximum number of digits in decimal arithmetic
Prefetch On Controls prefetching of rows
ProcedureProfiling OFF Procedure profiling
ProcessorArchitecture X86 Processor architecture
ReadOnly OFF Database read-only mode
RequestLogging NONE Request logging
Row_counts Off Controls whether row counts are estimates or exact
Scale 20 Minimum number of digits after decimal point
SeparateCheckpointLog ON Checkpoint log maintained at end of
system dbspace
SeparateForeignKeys ON Primary and foreign key indexes are stored
separately
StartTime 2008-12-15 11:34:14.399 Server start time
String_rtruncation Off Controls truncation errors on INSERT or
UPDATE
StringHistogramsFix OFF Fixed string histograms implementation
TableBitMaps ON Table bit maps supported
TempDir . Temporary directory
TempFileName .\asat0000.tmp Database temporary file name
Threads 20 Server thread count
Time_format HH:NN:SS.SSS Controls format for TIME values
Timestamp_format YYYY-MM-DD HH:NN:SS.SSS Controls format for
TIMESTAMP values
Update_statistics On Controls collection of statistics during
query execution
Userid DBA User ID
VariableHashSize ON Hash length may be specified for BTree indexes

"Chris Keating [Sybase iAnywhere]" <keating_no@spam_sybase.com> wrote in
message news:494a6d37@forums-1-dub...
> Can you get a query plan for this?
>
> To generate a Graphical Plan with Statistics
>
> 1. From DBISQL, go to Tools | Options and set the "Plan" option to
> Graphical Plan with Statistics
> 2. Press Shift-F5 to get the plan
> 3. For ASA 8 and ASA9, Select File | Save As... and
> Set the file type to XML
> 4. For SA 10, Select File | Save Plan...
> 5. provide a file name
> 6. Click Ok
>
> You can view a saved graphical plan by
>
> 1. For ASA 8 and ASA9, from DBISQL, select File | Open and
> Set the file type to XML
> 2. For SA 10, select File | Open Plan...
> 3. Select the Graphical plan file
>
> magotici wrote:
>> Hi David,
>> I have the querry that's slow now but was'nt in the previous version:
>>
>> SELECT "project"."project",
>> "project"."client_due_date",
>> "project"."brands_assignment",
>> "project"."tot_obs",
>> "project"."number_block",
>> "project"."number_test_brands",
>> "project"."control",
>> "project"."product_arrival",
>> "project"."due_date_2",
>> "project_objectives"."year",
>> "project_objectives"."month",
>> "project_objectives"."percent",
>> "project"."type",
>> "project_types"."description",
>> "project_types"."colour_bkg",
>> "project_types"."colour_text",
>> (select count(schedule_id) from p_info where p_info.project =
>> project.project) as tot_units,
>> (select count(repeat_date) from p_info where p_info.project =
>> project.project) as tot_repeats,
>> (select count(*) from p_info where p_info.project =
>> project.project and month(repeat_date) > 0 and status = 100) as
>> repeats_done,
>> (select sum(percent) from project_objectives where
>> project_objectives.project = project.project ) as total_objectives,
>> "project"."project_closed",
>> "project"."description"
>> FROM {oj "project" LEFT OUTER JOIN "project_objectives" ON
>> "project"."project" = "project_objectives"."project"},
>> "project_types"
>> WHERE ("project"."project_closed" is null OR
>> :pn_closed = 1 and "project"."project_closed" >= :pd_start and
>> "project"."project_closed" <= :pd_end) AND
>> ("project"."product_arrival" is not null OR
>> :pn_pending = 1) AND
>> ( "project"."type" = "project_types"."type" ) and
>> (("year" = year(:pd_start) AND
>> "month" >= month(:pd_start) OR
>> "year" > year(:pd_start)) AND
>> ("year" = year(:pd_end) AND
>> "month" <= month(:pd_end) OR
>> "year" < year(:pd_end)) OR
>> "percent" is null OR
>> "percent" = 0) AND
>> "project"."special" = 'N'
>>
>> Thanks,
>> magotici
>>
>>
>>
>> "David Fishburn" <fishburn_spam_off@ianywhere.com> wrote in message
>> news:Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106...
>>> "forums.sybase.com" <magotici@labstat.com> wrote in
>>> news:49482a21$1@forums-1-dub of ianywhere.public.general:
>>>
>>> fsc> After I updated to ASA 9.0.2 Netware EBF 3508, the DB got slower.
>>> Is
>>> fsc> there any known issue?
>>> fsc> I did the upgrade because of the problem with the log file from
>>> backup
>>> fsc> image.
>>>
>>> For these types of issues, it is generally not the ENTIRE database, it
>>> is usually certain queries.
>>>
>>> What have you done to figure out _which_ queries are slower?
>>> Don't know what your app does, but it is usually a portion of a page
>>> in your app is much slower than before. This usually points to an
>>> individual query.
>>>
>>> --
>>> David Fishburn
>>> Certified ASA Developer Version 8
>>> iAnywhere Solutions - Sybase
>>> Professional Services
>>> Please only post to the newsgroup
>>> Please ALWAYS include version and MORE importantly BUILD number with
>>> EACH post (dbeng11 -v).
>>>
>>> EBFs and Maintenance Releases
>>> http://downloads.sybase.com/
>>>
>>> Developer Community / Whitepapers
>>> http://www.ianywhere.com/developer
>>>
>>> Case-Express - to report bugs
>>> http://case-express.sybase.com
>>>
>>> CodeXchange - Free samples
>>> http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList
>>>
>>> Documentation
>>> http://dcx.sybase.com/
>>>
>>


"Chris Keating [Sybase iAnywhere]" <keating_no Posted on 2008-12-18 16:52:46.0Z
From: "Chris Keating [Sybase iAnywhere]" <keating_no@spam_sybase.com>
User-Agent: Thunderbird 2.0.0.18 (Windows/20081105)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: SQL Anywhere 9.0.2 for Netware slow
References: <49482a21$1@forums-1-dub> <Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106> <494a61fc$1@forums-1-dub> <494a6d37@forums-1-dub> <494a7aaf@forums-1-dub>
In-Reply-To: <494a7aaf@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <494a7fde@forums-1-dub>
Date: 18 Dec 2008 08:52:46 -0800
X-Trace: forums-1-dub 1229619166 10.22.241.152 (18 Dec 2008 08:52:46 -0800)
X-Original-Trace: 18 Dec 2008 08:52:46 -0800, vip152.sybase.com
Lines: 376
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7277
Article PK: 5494

Can you post the saved XML (see my previous instructions Step 3)? It is
very difficult to use this output.

magotici wrote:
> teHi,
> Here is what I've got:
>
> SELECT
>
> SELECT "project"."project",
> "project"."client_due_date",
> "project"."brands_assignment",
> "project"."tot_obs",
> "project"."number_block",
> "project"."number_test_brands",
> "project"."control",
> "project"."product_arrival",
> "project"."due_date_2",
> "project_objectives"."year",
> "project_objectives"."month",
> "project_objectives"."percent",
> "project"."type",
> "project_types"."description",
> "project_types"."colour_bkg",
> "project_types"."colour_text",
> (select count(schedule_id) from p_info where p_info.project =
> project.project) as tot_units,
> (select count(repeat_date) from p_info where p_info.project =
> project.project) as tot_repeats,
> (select count(*) from p_info where p_info.project = project.project
> and month(repeat_date) > 0 and status = 100) as repeats_done,
> (select sum(percent) from project_objectives where
> project_objectives.project = project.project ) as total_objectives,
> "project"."project_closed",
> "project"."description"
> FROM "project" LEFT OUTER JOIN "project_objectives"
> ON "project"."project" = "project_objectives"."project",
> "project_types"
> WHERE ("project"."project_closed" is null OR
> 0 = 1 and "project"."project_closed" >= '20080901' and
> "project"."project_closed" <= '20090101') AND
> ("project"."product_arrival" is not null OR
> 0 = 1) AND
> ( "project"."type" = "project_types"."type" ) and
> (("year" = year('20080901') AND
> "month" >= month('20080901') OR
> "year" > year('20080901')) AND
> ("year" = year('20090101') AND
> "month" <= month('20090101') OR
> "year" < year('20090101')) OR
> "percent" is null OR
> "percent" = 0) AND
> "project"."special" = 'N'
> Node Statistics
> Estimates Actual Description
> Invocations - 1 Number of times the result was computed
> RowsReturned 70.062 111 Number of rows returned
> PercentTotalCost 99.862 99.894 Run time as a percent of total
> query time
> RunTime 7.9344 13.229 Time to compute the results
> CPUTime 7.9344 - Time required by CPU
> DiskReadTime 0 - Time to perform reads from disk
> DiskWriteTime 0 - Time to perform writes to disk
> CacheHits - 3.6929e+06 Cache Hits
> CacheRead - 3.6929e+06 Cache reads
> CacheReadTable - 3.6929e+06 Cache table reads
> DiskRead 0 0 Disk reads
> DiskWrite 0 0 Disk writes
> IndLookup - 1 Number of index lookups
> ExprCacheStarts - 4 Number of times the expression cache was
> started
> ExprCacheLookups - 428 Number of lookups done in the expression
> cache
> ExprCacheHits - 192 Number of hits in the expression cache
> ExprCacheInserts - 236 Number of values inserted into the
> expression cache
> RelocatableHeapPages - 19 Relocatable heap pages
>
>
> Subtree Statistics
> Estimates Actual Description
> Invocations - 1 Number of times the result was computed
> RowsReturned 70.062 111 Number of rows returned
> PercentTotalCost 100 100 Run time as a percent of total query
> time
> RunTime 7.9453 13.243 Time to compute the results
> CPUTime 7.9453 - Time required by CPU
> DiskReadTime 0 - Time to perform reads from disk
> DiskWriteTime 0 - Time to perform writes to disk
> CacheHits - 3.6951e+06 Cache Hits
> CacheRead - 3.6951e+06 Cache reads
> CacheReadTable - 3.695e+06 Cache table reads
> CacheReadIndLeaf - 188 Cache index leaf reads
> DiskRead 0 0 Disk reads
> DiskWrite 0 0 Disk writes
> IndLookup - 113 Number of index lookups
> ExprCacheStarts - 4 Number of times the expression cache was
> started
> ExprCacheLookups - 428 Number of lookups done in the expression
> cache
> ExprCacheHits - 192 Number of hits in the expression cache
> ExprCacheInserts - 236 Number of values inserted into the
> expression cache
> HashRowsFiltered - 1439 Rate at which probe rows are rejected by
> bit-vector filters
> RelocatableHeapPages - 21 Relocatable heap pages
>
>
> Optimizer statistics
> Value Description
> Costed subplans 34 Number of different enumeration strategies
> considered by the optimizer
> Estimated cache pages 37110 Estimated cache pages available for
> this statement
> CurrentCacheSize 153924 Current cache size in kilobytes
> Isolation_level 0 Controls the locking isolation level
> Optimization_goal First-row Optimize queries for first row or all
> rows
> Optimization_level 9 Reserved
> Optimization_workload Mixed Controls whether optimizing for OLAP
> or mixed queries
> ProductVersion 9.0.2.3508 Product version
> User_estimates Override-magic Controls whether to respect user
> estimates
>
>
> Select list
> expr() varchar(20)
> expr() date
> expr() date
> expr() unsigned smallint
> expr() smallint
> expr() smallint
> expr() varchar(20)
> expr() date
> expr() date
> expr() unsigned smallint
> expr() tinyint
> expr() decimal(5,2)
> expr() char(3)
> project_types.description varchar(15)
> project_types.colour_bkg unsigned int
> project_types.colour_text unsigned int
> expr() int
> expr() int
> expr() int
> expr() numeric(13,2)
> expr() date
> expr() varchar(1000)
>
>
> Options
> Value Description
> Alias labstat Mounted database name
> Ansi_blanks Off Controls truncation errors
> Ansi_integer_overflow Off Controls whether integer overflow causes
> an error
> Ansi_update_constraints Cursors Controls the range of updates that
> are permitted
> Ansinull On Controls interpretation of NULL values
> BlankPadding OFF Blank padding
> BlobArenas ON Blob extension pages are stored separately from
> table pages
> Blocking On Controls response to locking conflicts
> Capabilities 4087FFDD Database capability bits
> CaseSensitive OFF Case sensitivity
> CharSet cp850 Character Set
> Checksum OFF Page checksum
> ClusteredIndexes OFF Clustered indexes
> Collation 850LATIN1 Collation name
> Collect_statistics_on_dml_updates On Controls collection of
> statistics during INSERT/UPDATE/DELETE statement execution
> CommandLine -n labstat -x tcpip{myip=192.168.10.209} -ti 0
> syb:\asany\labstat.db -n labstat Expanded command line used to start the
> server
> CompressedBTrees ON Compressed B-Tree indexes are supported
> Compression OFF Compression enabled
> Conversion_error On Controls datatype conversion errors
> Date_format YYYY-MM-DD Controls format for DATE values
> Date_order YMD Controls order of date components
> DBFileFragments 0 Database file fragments
> DefaultCollation 850LATIN1 Default Collation
> Divide_by_zero_error On Controls divide-by-zero errors
> Encryption None Encryption type
> Extended_join_syntax On Controls errors when using duplicate
> correlation names in joins
> File syb:\asany\labstat.db Database file
> FileSize 77423 File size in pages
> FileVersion 41 Database file version number
> FreePageBitMaps ON Free database pages managed via bitmaps
> FreePages 116 Number of free pages in dbspace
> HistogramHashFix OFF Fixed histogram hash implementation
> Histograms ON Optimizer statistics maintained as histograms
> IndexStatistics OFF Index Statistics
> IsJavaAvailable NO Java available
> JavaObjectsEnabled NO Java objects enabled
> IsNetworkServer YES Is Network Server
> IsRuntimeServer NO Is Runtime Server
> Language us_english
> LicenseCount 300 Number of licensed seats
> LicensedCompany Labstat Int Inc Name of the licensed company
> LicensedUser Manuel Agotici Name of the licensed user
> LicenseType networked seat (per-seat) License type
> LivenessTimeout 120 Client liveness timeout default
> LogFileFragments 0 Log file fragments
> MachineName S1 Name of the machine
> MainHeapBytes 2052272 Main heap bytes in cache
> Max_plans_cached 20 Maximum number of cached execution plans for a
> connection
> MaxCacheSize 153924 Maximum cache size in kilobytes
> MinCacheSize 153924 Minimum cache size in kilobytes
> MultiByteCharSet OFF Multi Byte Character Set ( on/off )
> Name labstat
> NamedConstraints OFF Named Constraints
> NumProcessorsAvail 1 Number of processors on server
> NumProcessorsMax 0 Maximum number of processors used
> PageSize 2048 Database page size
> PeakCacheSize 153924 Peak cache size in kilobytes
> Platform Netware Operating system platform
> PlatformVer NetWare 6.0, Support Pack 5 Operating system platform
> version
> Precision 40 Maximum number of digits in decimal arithmetic
> Prefetch On Controls prefetching of rows
> ProcedureProfiling OFF Procedure profiling
> ProcessorArchitecture X86 Processor architecture
> ReadOnly OFF Database read-only mode
> RequestLogging NONE Request logging
> Row_counts Off Controls whether row counts are estimates or exact
> Scale 20 Minimum number of digits after decimal point
> SeparateCheckpointLog ON Checkpoint log maintained at end of
> system dbspace
> SeparateForeignKeys ON Primary and foreign key indexes are stored
> separately
> StartTime 2008-12-15 11:34:14.399 Server start time
> String_rtruncation Off Controls truncation errors on INSERT or
> UPDATE
> StringHistogramsFix OFF Fixed string histograms implementation
> TableBitMaps ON Table bit maps supported
> TempDir . Temporary directory
> TempFileName .\asat0000.tmp Database temporary file name
> Threads 20 Server thread count
> Time_format HH:NN:SS.SSS Controls format for TIME values
> Timestamp_format YYYY-MM-DD HH:NN:SS.SSS Controls format for
> TIMESTAMP values
> Update_statistics On Controls collection of statistics during
> query execution
> Userid DBA User ID
> VariableHashSize ON Hash length may be specified for BTree indexes
>
>
>
>
>
>
> "Chris Keating [Sybase iAnywhere]" <keating_no@spam_sybase.com> wrote in
> message news:494a6d37@forums-1-dub...
>> Can you get a query plan for this?
>>
>> To generate a Graphical Plan with Statistics
>>
>> 1. From DBISQL, go to Tools | Options and set the "Plan" option to
>> Graphical Plan with Statistics
>> 2. Press Shift-F5 to get the plan
>> 3. For ASA 8 and ASA9, Select File | Save As... and
>> Set the file type to XML
>> 4. For SA 10, Select File | Save Plan...
>> 5. provide a file name
>> 6. Click Ok
>>
>> You can view a saved graphical plan by
>>
>> 1. For ASA 8 and ASA9, from DBISQL, select File | Open and
>> Set the file type to XML
>> 2. For SA 10, select File | Open Plan...
>> 3. Select the Graphical plan file
>>
>> magotici wrote:
>>> Hi David,
>>> I have the querry that's slow now but was'nt in the previous version:
>>>
>>> SELECT "project"."project",
>>> "project"."client_due_date",
>>> "project"."brands_assignment",
>>> "project"."tot_obs",
>>> "project"."number_block",
>>> "project"."number_test_brands",
>>> "project"."control",
>>> "project"."product_arrival",
>>> "project"."due_date_2",
>>> "project_objectives"."year",
>>> "project_objectives"."month",
>>> "project_objectives"."percent",
>>> "project"."type",
>>> "project_types"."description",
>>> "project_types"."colour_bkg",
>>> "project_types"."colour_text",
>>> (select count(schedule_id) from p_info where p_info.project =
>>> project.project) as tot_units,
>>> (select count(repeat_date) from p_info where p_info.project =
>>> project.project) as tot_repeats,
>>> (select count(*) from p_info where p_info.project =
>>> project.project and month(repeat_date) > 0 and status = 100) as
>>> repeats_done,
>>> (select sum(percent) from project_objectives where
>>> project_objectives.project = project.project ) as total_objectives,
>>> "project"."project_closed",
>>> "project"."description"
>>> FROM {oj "project" LEFT OUTER JOIN "project_objectives" ON
>>> "project"."project" = "project_objectives"."project"},
>>> "project_types"
>>> WHERE ("project"."project_closed" is null OR
>>> :pn_closed = 1 and "project"."project_closed" >= :pd_start and
>>> "project"."project_closed" <= :pd_end) AND
>>> ("project"."product_arrival" is not null OR
>>> :pn_pending = 1) AND
>>> ( "project"."type" = "project_types"."type" ) and
>>> (("year" = year(:pd_start) AND
>>> "month" >= month(:pd_start) OR
>>> "year" > year(:pd_start)) AND
>>> ("year" = year(:pd_end) AND
>>> "month" <= month(:pd_end) OR
>>> "year" < year(:pd_end)) OR
>>> "percent" is null OR
>>> "percent" = 0) AND
>>> "project"."special" = 'N'
>>>
>>> Thanks,
>>> magotici
>>>
>>>
>>>
>>> "David Fishburn" <fishburn_spam_off@ianywhere.com> wrote in message
>>> news:Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106...
>>>> "forums.sybase.com" <magotici@labstat.com> wrote in
>>>> news:49482a21$1@forums-1-dub of ianywhere.public.general:
>>>>
>>>> fsc> After I updated to ASA 9.0.2 Netware EBF 3508, the DB got slower.
>>>> Is
>>>> fsc> there any known issue?
>>>> fsc> I did the upgrade because of the problem with the log file from
>>>> backup
>>>> fsc> image.
>>>>
>>>> For these types of issues, it is generally not the ENTIRE database, it
>>>> is usually certain queries.
>>>>
>>>> What have you done to figure out _which_ queries are slower?
>>>> Don't know what your app does, but it is usually a portion of a page
>>>> in your app is much slower than before. This usually points to an
>>>> individual query.
>>>>
>>>> --
>>>> David Fishburn
>>>> Certified ASA Developer Version 8
>>>> iAnywhere Solutions - Sybase
>>>> Professional Services
>>>> Please only post to the newsgroup
>>>> Please ALWAYS include version and MORE importantly BUILD number with
>>>> EACH post (dbeng11 -v).
>>>>
>>>> EBFs and Maintenance Releases
>>>> http://downloads.sybase.com/
>>>>
>>>> Developer Community / Whitepapers
>>>> http://www.ianywhere.com/developer
>>>>
>>>> Case-Express - to report bugs
>>>> http://case-express.sybase.com
>>>>
>>>> CodeXchange - Free samples
>>>> http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList
>>>>
>>>> Documentation
>>>> http://dcx.sybase.com/
>>>>
>


magotici Posted on 2008-12-18 17:01:02.0Z
From: "magotici" <magotici@labstat.com>
Newsgroups: ianywhere.public.general
References: <49482a21$1@forums-1-dub> <Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106> <494a61fc$1@forums-1-dub> <494a6d37@forums-1-dub> <494a7aaf@forums-1-dub> <494a7fde@forums-1-dub>
Subject: Re: SQL Anywhere 9.0.2 for Netware slow
Lines: 2795
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.3790.3959
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4325
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <494a81ce@forums-1-dub>
Date: 18 Dec 2008 09:01:02 -0800
X-Trace: forums-1-dub 1229619662 10.22.241.152 (18 Dec 2008 09:01:02 -0800)
X-Original-Trace: 18 Dec 2008 09:01:02 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7279
Article PK: 5498

Attached is the requested file

"Chris Keating [Sybase iAnywhere]" <keating_no@spam_sybase.com> wrote in
message news:494a7fde@forums-1-dub...
> Can you post the saved XML (see my previous instructions Step 3)? It is
> very difficult to use this output.
>
> magotici wrote:
>> teHi,
>> Here is what I've got:
>>
>> SELECT
>>
>> SELECT "project"."project",
>> "project"."client_due_date",
>> "project"."brands_assignment",
>> "project"."tot_obs",
>> "project"."number_block",
>> "project"."number_test_brands",
>> "project"."control",
>> "project"."product_arrival",
>> "project"."due_date_2",
>> "project_objectives"."year",
>> "project_objectives"."month",
>> "project_objectives"."percent",
>> "project"."type",
>> "project_types"."description",
>> "project_types"."colour_bkg",
>> "project_types"."colour_text",
>> (select count(schedule_id) from p_info where p_info.project =
>> project.project) as tot_units,
>> (select count(repeat_date) from p_info where p_info.project =
>> project.project) as tot_repeats,
>> (select count(*) from p_info where p_info.project =
>> project.project
>> and month(repeat_date) > 0 and status = 100) as repeats_done,
>> (select sum(percent) from project_objectives where
>> project_objectives.project = project.project ) as total_objectives,
>> "project"."project_closed",
>> "project"."description"
>> FROM "project" LEFT OUTER JOIN "project_objectives"
>> ON "project"."project" = "project_objectives"."project",
>> "project_types"
>> WHERE ("project"."project_closed" is null OR
>> 0 = 1 and "project"."project_closed" >= '20080901' and
>> "project"."project_closed" <= '20090101') AND
>> ("project"."product_arrival" is not null OR
>> 0 = 1) AND
>> ( "project"."type" = "project_types"."type" ) and
>> (("year" = year('20080901') AND
>> "month" >= month('20080901') OR
>> "year" > year('20080901')) AND
>> ("year" = year('20090101') AND
>> "month" <= month('20090101') OR
>> "year" < year('20090101')) OR
>> "percent" is null OR
>> "percent" = 0) AND
>> "project"."special" = 'N'
>> Node Statistics
>> Estimates Actual Description
>> Invocations - 1 Number of times the result was computed
>> RowsReturned 70.062 111 Number of rows returned
>> PercentTotalCost 99.862 99.894 Run time as a percent of total
>> query time
>> RunTime 7.9344 13.229 Time to compute the results
>> CPUTime 7.9344 - Time required by CPU
>> DiskReadTime 0 - Time to perform reads from disk
>> DiskWriteTime 0 - Time to perform writes to disk
>> CacheHits - 3.6929e+06 Cache Hits
>> CacheRead - 3.6929e+06 Cache reads
>> CacheReadTable - 3.6929e+06 Cache table reads
>> DiskRead 0 0 Disk reads
>> DiskWrite 0 0 Disk writes
>> IndLookup - 1 Number of index lookups
>> ExprCacheStarts - 4 Number of times the expression cache was
>> started
>> ExprCacheLookups - 428 Number of lookups done in the
>> expression
>> cache
>> ExprCacheHits - 192 Number of hits in the expression cache
>> ExprCacheInserts - 236 Number of values inserted into the
>> expression cache
>> RelocatableHeapPages - 19 Relocatable heap pages
>>
>>
>> Subtree Statistics
>> Estimates Actual Description
>> Invocations - 1 Number of times the result was computed
>> RowsReturned 70.062 111 Number of rows returned
>> PercentTotalCost 100 100 Run time as a percent of total query
>> time
>> RunTime 7.9453 13.243 Time to compute the results
>> CPUTime 7.9453 - Time required by CPU
>> DiskReadTime 0 - Time to perform reads from disk
>> DiskWriteTime 0 - Time to perform writes to disk
>> CacheHits - 3.6951e+06 Cache Hits
>> CacheRead - 3.6951e+06 Cache reads
>> CacheReadTable - 3.695e+06 Cache table reads
>> CacheReadIndLeaf - 188 Cache index leaf reads
>> DiskRead 0 0 Disk reads
>> DiskWrite 0 0 Disk writes
>> IndLookup - 113 Number of index lookups
>> ExprCacheStarts - 4 Number of times the expression cache was
>> started
>> ExprCacheLookups - 428 Number of lookups done in the
>> expression
>> cache
>> ExprCacheHits - 192 Number of hits in the expression cache
>> ExprCacheInserts - 236 Number of values inserted into the
>> expression cache
>> HashRowsFiltered - 1439 Rate at which probe rows are rejected
>> by
>> bit-vector filters
>> RelocatableHeapPages - 21 Relocatable heap pages
>>
>>
>> Optimizer statistics
>> Value Description
>> Costed subplans 34 Number of different enumeration strategies
>> considered by the optimizer
>> Estimated cache pages 37110 Estimated cache pages available for
>> this statement
>> CurrentCacheSize 153924 Current cache size in kilobytes
>> Isolation_level 0 Controls the locking isolation level
>> Optimization_goal First-row Optimize queries for first row or
>> all
>> rows
>> Optimization_level 9 Reserved
>> Optimization_workload Mixed Controls whether optimizing for
>> OLAP
>> or mixed queries
>> ProductVersion 9.0.2.3508 Product version
>> User_estimates Override-magic Controls whether to respect user
>> estimates
>>
>>
>> Select list
>> expr() varchar(20)
>> expr() date
>> expr() date
>> expr() unsigned smallint
>> expr() smallint
>> expr() smallint
>> expr() varchar(20)
>> expr() date
>> expr() date
>> expr() unsigned smallint
>> expr() tinyint
>> expr() decimal(5,2)
>> expr() char(3)
>> project_types.description varchar(15)
>> project_types.colour_bkg unsigned int
>> project_types.colour_text unsigned int
>> expr() int
>> expr() int
>> expr() int
>> expr() numeric(13,2)
>> expr() date
>> expr() varchar(1000)
>>
>>
>> Options
>> Value Description
>> Alias labstat Mounted database name
>> Ansi_blanks Off Controls truncation errors
>> Ansi_integer_overflow Off Controls whether integer overflow
>> causes
>> an error
>> Ansi_update_constraints Cursors Controls the range of updates
>> that
>> are permitted
>> Ansinull On Controls interpretation of NULL values
>> BlankPadding OFF Blank padding
>> BlobArenas ON Blob extension pages are stored separately from
>> table pages
>> Blocking On Controls response to locking conflicts
>> Capabilities 4087FFDD Database capability bits
>> CaseSensitive OFF Case sensitivity
>> CharSet cp850 Character Set
>> Checksum OFF Page checksum
>> ClusteredIndexes OFF Clustered indexes
>> Collation 850LATIN1 Collation name
>> Collect_statistics_on_dml_updates On Controls collection of
>> statistics during INSERT/UPDATE/DELETE statement execution
>> CommandLine -n labstat -x tcpip{myip=192.168.10.209} -ti 0
>> syb:\asany\labstat.db -n labstat Expanded command line used to start
>> the
>> server
>> CompressedBTrees ON Compressed B-Tree indexes are supported
>> Compression OFF Compression enabled
>> Conversion_error On Controls datatype conversion errors
>> Date_format YYYY-MM-DD Controls format for DATE values
>> Date_order YMD Controls order of date components
>> DBFileFragments 0 Database file fragments
>> DefaultCollation 850LATIN1 Default Collation
>> Divide_by_zero_error On Controls divide-by-zero errors
>> Encryption None Encryption type
>> Extended_join_syntax On Controls errors when using duplicate
>> correlation names in joins
>> File syb:\asany\labstat.db Database file
>> FileSize 77423 File size in pages
>> FileVersion 41 Database file version number
>> FreePageBitMaps ON Free database pages managed via bitmaps
>> FreePages 116 Number of free pages in dbspace
>> HistogramHashFix OFF Fixed histogram hash implementation
>> Histograms ON Optimizer statistics maintained as histograms
>> IndexStatistics OFF Index Statistics
>> IsJavaAvailable NO Java available
>> JavaObjectsEnabled NO Java objects enabled
>> IsNetworkServer YES Is Network Server
>> IsRuntimeServer NO Is Runtime Server
>> Language us_english
>> LicenseCount 300 Number of licensed seats
>> LicensedCompany Labstat Int Inc Name of the licensed company
>> LicensedUser Manuel Agotici Name of the licensed user
>> LicenseType networked seat (per-seat) License type
>> LivenessTimeout 120 Client liveness timeout default
>> LogFileFragments 0 Log file fragments
>> MachineName S1 Name of the machine
>> MainHeapBytes 2052272 Main heap bytes in cache
>> Max_plans_cached 20 Maximum number of cached execution plans
>> for a
>> connection
>> MaxCacheSize 153924 Maximum cache size in kilobytes
>> MinCacheSize 153924 Minimum cache size in kilobytes
>> MultiByteCharSet OFF Multi Byte Character Set ( on/off )
>> Name labstat
>> NamedConstraints OFF Named Constraints
>> NumProcessorsAvail 1 Number of processors on server
>> NumProcessorsMax 0 Maximum number of processors used
>> PageSize 2048 Database page size
>> PeakCacheSize 153924 Peak cache size in kilobytes
>> Platform Netware Operating system platform
>> PlatformVer NetWare 6.0, Support Pack 5 Operating system
>> platform
>> version
>> Precision 40 Maximum number of digits in decimal arithmetic
>> Prefetch On Controls prefetching of rows
>> ProcedureProfiling OFF Procedure profiling
>> ProcessorArchitecture X86 Processor architecture
>> ReadOnly OFF Database read-only mode
>> RequestLogging NONE Request logging
>> Row_counts Off Controls whether row counts are estimates or
>> exact
>> Scale 20 Minimum number of digits after decimal point
>> SeparateCheckpointLog ON Checkpoint log maintained at end of
>> system dbspace
>> SeparateForeignKeys ON Primary and foreign key indexes are
>> stored
>> separately
>> StartTime 2008-12-15 11:34:14.399 Server start time
>> String_rtruncation Off Controls truncation errors on INSERT or
>> UPDATE
>> StringHistogramsFix OFF Fixed string histograms implementation
>> TableBitMaps ON Table bit maps supported
>> TempDir . Temporary directory
>> TempFileName .\asat0000.tmp Database temporary file name
>> Threads 20 Server thread count
>> Time_format HH:NN:SS.SSS Controls format for TIME values
>> Timestamp_format YYYY-MM-DD HH:NN:SS.SSS Controls format for
>> TIMESTAMP values
>> Update_statistics On Controls collection of statistics during
>> query execution
>> Userid DBA User ID
>> VariableHashSize ON Hash length may be specified for BTree
>> indexes
>>
>>
>>
>>
>>
>>
>> "Chris Keating [Sybase iAnywhere]" <keating_no@spam_sybase.com> wrote in
>> message news:494a6d37@forums-1-dub...
>>> Can you get a query plan for this?
>>>
>>> To generate a Graphical Plan with Statistics
>>>
>>> 1. From DBISQL, go to Tools | Options and set the "Plan" option to
>>> Graphical Plan with Statistics
>>> 2. Press Shift-F5 to get the plan
>>> 3. For ASA 8 and ASA9, Select File | Save As... and
>>> Set the file type to XML
>>> 4. For SA 10, Select File | Save Plan...
>>> 5. provide a file name
>>> 6. Click Ok
>>>
>>> You can view a saved graphical plan by
>>>
>>> 1. For ASA 8 and ASA9, from DBISQL, select File | Open and
>>> Set the file type to XML
>>> 2. For SA 10, select File | Open Plan...
>>> 3. Select the Graphical plan file
>>>
>>> magotici wrote:
>>>> Hi David,
>>>> I have the querry that's slow now but was'nt in the previous version:
>>>>
>>>> SELECT "project"."project",
>>>> "project"."client_due_date",
>>>> "project"."brands_assignment",
>>>> "project"."tot_obs",
>>>> "project"."number_block",
>>>> "project"."number_test_brands",
>>>> "project"."control",
>>>> "project"."product_arrival",
>>>> "project"."due_date_2",
>>>> "project_objectives"."year",
>>>> "project_objectives"."month",
>>>> "project_objectives"."percent",
>>>> "project"."type",
>>>> "project_types"."description",
>>>> "project_types"."colour_bkg",
>>>> "project_types"."colour_text",
>>>> (select count(schedule_id) from p_info where p_info.project =
>>>> project.project) as tot_units,
>>>> (select count(repeat_date) from p_info where p_info.project =
>>>> project.project) as tot_repeats,
>>>> (select count(*) from p_info where p_info.project =
>>>> project.project and month(repeat_date) > 0 and status = 100) as
>>>> repeats_done,
>>>> (select sum(percent) from project_objectives where
>>>> project_objectives.project = project.project ) as total_objectives,
>>>> "project"."project_closed",
>>>> "project"."description"
>>>> FROM {oj "project" LEFT OUTER JOIN "project_objectives" ON
>>>> "project"."project" = "project_objectives"."project"},
>>>> "project_types"
>>>> WHERE ("project"."project_closed" is null OR
>>>> :pn_closed = 1 and "project"."project_closed" >= :pd_start and
>>>> "project"."project_closed" <= :pd_end) AND
>>>> ("project"."product_arrival" is not null OR
>>>> :pn_pending = 1) AND
>>>> ( "project"."type" = "project_types"."type" ) and
>>>> (("year" = year(:pd_start) AND
>>>> "month" >= month(:pd_start) OR
>>>> "year" > year(:pd_start)) AND
>>>> ("year" = year(:pd_end) AND
>>>> "month" <= month(:pd_end) OR
>>>> "year" < year(:pd_end)) OR
>>>> "percent" is null OR
>>>> "percent" = 0) AND
>>>> "project"."special" = 'N'
>>>>
>>>> Thanks,
>>>> magotici
>>>>
>>>>
>>>>
>>>> "David Fishburn" <fishburn_spam_off@ianywhere.com> wrote in message
>>>> news:Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106...
>>>>> "forums.sybase.com" <magotici@labstat.com> wrote in
>>>>> news:49482a21$1@forums-1-dub of ianywhere.public.general:
>>>>>
>>>>> fsc> After I updated to ASA 9.0.2 Netware EBF 3508, the DB got slower.
>>>>> Is
>>>>> fsc> there any known issue?
>>>>> fsc> I did the upgrade because of the problem with the log file from
>>>>> backup
>>>>> fsc> image.
>>>>>
>>>>> For these types of issues, it is generally not the ENTIRE database, it
>>>>> is usually certain queries.
>>>>>
>>>>> What have you done to figure out _which_ queries are slower?
>>>>> Don't know what your app does, but it is usually a portion of a page
>>>>> in your app is much slower than before. This usually points to an
>>>>> individual query.
>>>>>
>>>>> --
>>>>> David Fishburn
>>>>> Certified ASA Developer Version 8
>>>>> iAnywhere Solutions - Sybase
>>>>> Professional Services
>>>>> Please only post to the newsgroup
>>>>> Please ALWAYS include version and MORE importantly BUILD number with
>>>>> EACH post (dbeng11 -v).
>>>>>
>>>>> EBFs and Maintenance Releases
>>>>> http://downloads.sybase.com/
>>>>>
>>>>> Developer Community / Whitepapers
>>>>> http://www.ianywhere.com/developer
>>>>>
>>>>> Case-Express - to report bugs
>>>>> http://case-express.sybase.com
>>>>>
>>>>> CodeXchange - Free samples
>>>>> http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList
>>>>>
>>>>> Documentation
>>>>> http://dcx.sybase.com/
>>>>>
>>


Glenn Paulley [Sybase iAnywhere] Posted on 2008-12-18 18:55:08.0Z
From: "Glenn Paulley [Sybase iAnywhere]" <paulley@ianywhere.com>
Reply-To: paulley@ianywhere.com
Organization: Sybase iAnywhere
User-Agent: Thunderbird 2.0.0.18 (Windows/20081105)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: SQL Anywhere 9.0.2 for Netware slow
References: <49482a21$1@forums-1-dub> <Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106> <494a61fc$1@forums-1-dub> <494a6d37@forums-1-dub> <494a7aaf@forums-1-dub>
In-Reply-To: <494a7aaf@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <494a9c8c@forums-1-dub>
Date: 18 Dec 2008 10:55:08 -0800
X-Trace: forums-1-dub 1229626508 10.22.241.152 (18 Dec 2008 10:55:08 -0800)
X-Original-Trace: 18 Dec 2008 10:55:08 -0800, vip152.sybase.com
Lines: 400
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7280
Article PK: 5496

This is incomplete - can you please post the entire .xml plan as an
attachment?

Thanks

Glenn

magotici wrote:
> teHi,
> Here is what I've got:
>
> SELECT
>
> SELECT "project"."project",
> "project"."client_due_date",
> "project"."brands_assignment",
> "project"."tot_obs",
> "project"."number_block",
> "project"."number_test_brands",
> "project"."control",
> "project"."product_arrival",
> "project"."due_date_2",
> "project_objectives"."year",
> "project_objectives"."month",
> "project_objectives"."percent",
> "project"."type",
> "project_types"."description",
> "project_types"."colour_bkg",
> "project_types"."colour_text",
> (select count(schedule_id) from p_info where p_info.project =
> project.project) as tot_units,
> (select count(repeat_date) from p_info where p_info.project =
> project.project) as tot_repeats,
> (select count(*) from p_info where p_info.project = project.project
> and month(repeat_date) > 0 and status = 100) as repeats_done,
> (select sum(percent) from project_objectives where
> project_objectives.project = project.project ) as total_objectives,
> "project"."project_closed",
> "project"."description"
> FROM "project" LEFT OUTER JOIN "project_objectives"
> ON "project"."project" = "project_objectives"."project",
> "project_types"
> WHERE ("project"."project_closed" is null OR
> 0 = 1 and "project"."project_closed" >= '20080901' and
> "project"."project_closed" <= '20090101') AND
> ("project"."product_arrival" is not null OR
> 0 = 1) AND
> ( "project"."type" = "project_types"."type" ) and
> (("year" = year('20080901') AND
> "month" >= month('20080901') OR
> "year" > year('20080901')) AND
> ("year" = year('20090101') AND
> "month" <= month('20090101') OR
> "year" < year('20090101')) OR
> "percent" is null OR
> "percent" = 0) AND
> "project"."special" = 'N'
> Node Statistics
> Estimates Actual Description
> Invocations - 1 Number of times the result was computed
> RowsReturned 70.062 111 Number of rows returned
> PercentTotalCost 99.862 99.894 Run time as a percent of total
> query time
> RunTime 7.9344 13.229 Time to compute the results
> CPUTime 7.9344 - Time required by CPU
> DiskReadTime 0 - Time to perform reads from disk
> DiskWriteTime 0 - Time to perform writes to disk
> CacheHits - 3.6929e+06 Cache Hits
> CacheRead - 3.6929e+06 Cache reads
> CacheReadTable - 3.6929e+06 Cache table reads
> DiskRead 0 0 Disk reads
> DiskWrite 0 0 Disk writes
> IndLookup - 1 Number of index lookups
> ExprCacheStarts - 4 Number of times the expression cache was
> started
> ExprCacheLookups - 428 Number of lookups done in the expression
> cache
> ExprCacheHits - 192 Number of hits in the expression cache
> ExprCacheInserts - 236 Number of values inserted into the
> expression cache
> RelocatableHeapPages - 19 Relocatable heap pages
>
>
> Subtree Statistics
> Estimates Actual Description
> Invocations - 1 Number of times the result was computed
> RowsReturned 70.062 111 Number of rows returned
> PercentTotalCost 100 100 Run time as a percent of total query
> time
> RunTime 7.9453 13.243 Time to compute the results
> CPUTime 7.9453 - Time required by CPU
> DiskReadTime 0 - Time to perform reads from disk
> DiskWriteTime 0 - Time to perform writes to disk
> CacheHits - 3.6951e+06 Cache Hits
> CacheRead - 3.6951e+06 Cache reads
> CacheReadTable - 3.695e+06 Cache table reads
> CacheReadIndLeaf - 188 Cache index leaf reads
> DiskRead 0 0 Disk reads
> DiskWrite 0 0 Disk writes
> IndLookup - 113 Number of index lookups
> ExprCacheStarts - 4 Number of times the expression cache was
> started
> ExprCacheLookups - 428 Number of lookups done in the expression
> cache
> ExprCacheHits - 192 Number of hits in the expression cache
> ExprCacheInserts - 236 Number of values inserted into the
> expression cache
> HashRowsFiltered - 1439 Rate at which probe rows are rejected by
> bit-vector filters
> RelocatableHeapPages - 21 Relocatable heap pages
>
>
> Optimizer statistics
> Value Description
> Costed subplans 34 Number of different enumeration strategies
> considered by the optimizer
> Estimated cache pages 37110 Estimated cache pages available for
> this statement
> CurrentCacheSize 153924 Current cache size in kilobytes
> Isolation_level 0 Controls the locking isolation level
> Optimization_goal First-row Optimize queries for first row or all
> rows
> Optimization_level 9 Reserved
> Optimization_workload Mixed Controls whether optimizing for OLAP
> or mixed queries
> ProductVersion 9.0.2.3508 Product version
> User_estimates Override-magic Controls whether to respect user
> estimates
>
>
> Select list
> expr() varchar(20)
> expr() date
> expr() date
> expr() unsigned smallint
> expr() smallint
> expr() smallint
> expr() varchar(20)
> expr() date
> expr() date
> expr() unsigned smallint
> expr() tinyint
> expr() decimal(5,2)
> expr() char(3)
> project_types.description varchar(15)
> project_types.colour_bkg unsigned int
> project_types.colour_text unsigned int
> expr() int
> expr() int
> expr() int
> expr() numeric(13,2)
> expr() date
> expr() varchar(1000)
>
>
> Options
> Value Description
> Alias labstat Mounted database name
> Ansi_blanks Off Controls truncation errors
> Ansi_integer_overflow Off Controls whether integer overflow causes
> an error
> Ansi_update_constraints Cursors Controls the range of updates that
> are permitted
> Ansinull On Controls interpretation of NULL values
> BlankPadding OFF Blank padding
> BlobArenas ON Blob extension pages are stored separately from
> table pages
> Blocking On Controls response to locking conflicts
> Capabilities 4087FFDD Database capability bits
> CaseSensitive OFF Case sensitivity
> CharSet cp850 Character Set
> Checksum OFF Page checksum
> ClusteredIndexes OFF Clustered indexes
> Collation 850LATIN1 Collation name
> Collect_statistics_on_dml_updates On Controls collection of
> statistics during INSERT/UPDATE/DELETE statement execution
> CommandLine -n labstat -x tcpip{myip=192.168.10.209} -ti 0
> syb:\asany\labstat.db -n labstat Expanded command line used to start the
> server
> CompressedBTrees ON Compressed B-Tree indexes are supported
> Compression OFF Compression enabled
> Conversion_error On Controls datatype conversion errors
> Date_format YYYY-MM-DD Controls format for DATE values
> Date_order YMD Controls order of date components
> DBFileFragments 0 Database file fragments
> DefaultCollation 850LATIN1 Default Collation
> Divide_by_zero_error On Controls divide-by-zero errors
> Encryption None Encryption type
> Extended_join_syntax On Controls errors when using duplicate
> correlation names in joins
> File syb:\asany\labstat.db Database file
> FileSize 77423 File size in pages
> FileVersion 41 Database file version number
> FreePageBitMaps ON Free database pages managed via bitmaps
> FreePages 116 Number of free pages in dbspace
> HistogramHashFix OFF Fixed histogram hash implementation
> Histograms ON Optimizer statistics maintained as histograms
> IndexStatistics OFF Index Statistics
> IsJavaAvailable NO Java available
> JavaObjectsEnabled NO Java objects enabled
> IsNetworkServer YES Is Network Server
> IsRuntimeServer NO Is Runtime Server
> Language us_english
> LicenseCount 300 Number of licensed seats
> LicensedCompany Labstat Int Inc Name of the licensed company
> LicensedUser Manuel Agotici Name of the licensed user
> LicenseType networked seat (per-seat) License type
> LivenessTimeout 120 Client liveness timeout default
> LogFileFragments 0 Log file fragments
> MachineName S1 Name of the machine
> MainHeapBytes 2052272 Main heap bytes in cache
> Max_plans_cached 20 Maximum number of cached execution plans for a
> connection
> MaxCacheSize 153924 Maximum cache size in kilobytes
> MinCacheSize 153924 Minimum cache size in kilobytes
> MultiByteCharSet OFF Multi Byte Character Set ( on/off )
> Name labstat
> NamedConstraints OFF Named Constraints
> NumProcessorsAvail 1 Number of processors on server
> NumProcessorsMax 0 Maximum number of processors used
> PageSize 2048 Database page size
> PeakCacheSize 153924 Peak cache size in kilobytes
> Platform Netware Operating system platform
> PlatformVer NetWare 6.0, Support Pack 5 Operating system platform
> version
> Precision 40 Maximum number of digits in decimal arithmetic
> Prefetch On Controls prefetching of rows
> ProcedureProfiling OFF Procedure profiling
> ProcessorArchitecture X86 Processor architecture
> ReadOnly OFF Database read-only mode
> RequestLogging NONE Request logging
> Row_counts Off Controls whether row counts are estimates or exact
> Scale 20 Minimum number of digits after decimal point
> SeparateCheckpointLog ON Checkpoint log maintained at end of
> system dbspace
> SeparateForeignKeys ON Primary and foreign key indexes are stored
> separately
> StartTime 2008-12-15 11:34:14.399 Server start time
> String_rtruncation Off Controls truncation errors on INSERT or
> UPDATE
> StringHistogramsFix OFF Fixed string histograms implementation
> TableBitMaps ON Table bit maps supported
> TempDir . Temporary directory
> TempFileName .\asat0000.tmp Database temporary file name
> Threads 20 Server thread count
> Time_format HH:NN:SS.SSS Controls format for TIME values
> Timestamp_format YYYY-MM-DD HH:NN:SS.SSS Controls format for
> TIMESTAMP values
> Update_statistics On Controls collection of statistics during
> query execution
> Userid DBA User ID
> VariableHashSize ON Hash length may be specified for BTree indexes
>
>
>
>
>
>
> "Chris Keating [Sybase iAnywhere]" <keating_no@spam_sybase.com> wrote in
> message news:494a6d37@forums-1-dub...
>> Can you get a query plan for this?
>>
>> To generate a Graphical Plan with Statistics
>>
>> 1. From DBISQL, go to Tools | Options and set the "Plan" option to
>> Graphical Plan with Statistics
>> 2. Press Shift-F5 to get the plan
>> 3. For ASA 8 and ASA9, Select File | Save As... and
>> Set the file type to XML
>> 4. For SA 10, Select File | Save Plan...
>> 5. provide a file name
>> 6. Click Ok
>>
>> You can view a saved graphical plan by
>>
>> 1. For ASA 8 and ASA9, from DBISQL, select File | Open and
>> Set the file type to XML
>> 2. For SA 10, select File | Open Plan...
>> 3. Select the Graphical plan file
>>
>> magotici wrote:
>>> Hi David,
>>> I have the querry that's slow now but was'nt in the previous version:
>>>
>>> SELECT "project"."project",
>>> "project"."client_due_date",
>>> "project"."brands_assignment",
>>> "project"."tot_obs",
>>> "project"."number_block",
>>> "project"."number_test_brands",
>>> "project"."control",
>>> "project"."product_arrival",
>>> "project"."due_date_2",
>>> "project_objectives"."year",
>>> "project_objectives"."month",
>>> "project_objectives"."percent",
>>> "project"."type",
>>> "project_types"."description",
>>> "project_types"."colour_bkg",
>>> "project_types"."colour_text",
>>> (select count(schedule_id) from p_info where p_info.project =
>>> project.project) as tot_units,
>>> (select count(repeat_date) from p_info where p_info.project =
>>> project.project) as tot_repeats,
>>> (select count(*) from p_info where p_info.project =
>>> project.project and month(repeat_date) > 0 and status = 100) as
>>> repeats_done,
>>> (select sum(percent) from project_objectives where
>>> project_objectives.project = project.project ) as total_objectives,
>>> "project"."project_closed",
>>> "project"."description"
>>> FROM {oj "project" LEFT OUTER JOIN "project_objectives" ON
>>> "project"."project" = "project_objectives"."project"},
>>> "project_types"
>>> WHERE ("project"."project_closed" is null OR
>>> :pn_closed = 1 and "project"."project_closed" >= :pd_start and
>>> "project"."project_closed" <= :pd_end) AND
>>> ("project"."product_arrival" is not null OR
>>> :pn_pending = 1) AND
>>> ( "project"."type" = "project_types"."type" ) and
>>> (("year" = year(:pd_start) AND
>>> "month" >= month(:pd_start) OR
>>> "year" > year(:pd_start)) AND
>>> ("year" = year(:pd_end) AND
>>> "month" <= month(:pd_end) OR
>>> "year" < year(:pd_end)) OR
>>> "percent" is null OR
>>> "percent" = 0) AND
>>> "project"."special" = 'N'
>>>
>>> Thanks,
>>> magotici
>>>
>>>
>>>
>>> "David Fishburn" <fishburn_spam_off@ianywhere.com> wrote in message
>>> news:Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106...
>>>> "forums.sybase.com" <magotici@labstat.com> wrote in
>>>> news:49482a21$1@forums-1-dub of ianywhere.public.general:
>>>>
>>>> fsc> After I updated to ASA 9.0.2 Netware EBF 3508, the DB got slower.
>>>> Is
>>>> fsc> there any known issue?
>>>> fsc> I did the upgrade because of the problem with the log file from
>>>> backup
>>>> fsc> image.
>>>>
>>>> For these types of issues, it is generally not the ENTIRE database, it
>>>> is usually certain queries.
>>>>
>>>> What have you done to figure out _which_ queries are slower?
>>>> Don't know what your app does, but it is usually a portion of a page
>>>> in your app is much slower than before. This usually points to an
>>>> individual query.
>>>>
>>>> --
>>>> David Fishburn
>>>> Certified ASA Developer Version 8
>>>> iAnywhere Solutions - Sybase
>>>> Professional Services
>>>> Please only post to the newsgroup
>>>> Please ALWAYS include version and MORE importantly BUILD number with
>>>> EACH post (dbeng11 -v).
>>>>
>>>> EBFs and Maintenance Releases
>>>> http://downloads.sybase.com/
>>>>
>>>> Developer Community / Whitepapers
>>>> http://www.ianywhere.com/developer
>>>>
>>>> Case-Express - to report bugs
>>>> http://case-express.sybase.com
>>>>
>>>> CodeXchange - Free samples
>>>> http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList
>>>>
>>>> Documentation
>>>> http://dcx.sybase.com/
>>>>
>

--
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/databasemanagement/sqlanywhere/technicalsupport


magotici Posted on 2008-12-18 19:22:54.0Z
From: "magotici" <magotici@labstat.com>
Newsgroups: ianywhere.public.general
References: <49482a21$1@forums-1-dub> <Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106> <494a61fc$1@forums-1-dub> <494a6d37@forums-1-dub> <494a7aaf@forums-1-dub> <494a9c8c@forums-1-dub>
Subject: Re: SQL Anywhere 9.0.2 for Netware slow
Lines: 2856
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.3790.3959
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4325
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <494aa30e@forums-1-dub>
Date: 18 Dec 2008 11:22:54 -0800
X-Trace: forums-1-dub 1229628174 10.22.241.152 (18 Dec 2008 11:22:54 -0800)
X-Original-Trace: 18 Dec 2008 11:22:54 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7281
Article PK: 5499

I created it again.

"Glenn Paulley [Sybase iAnywhere]" <paulley@ianywhere.com> wrote in message
news:494a9c8c@forums-1-dub...
> This is incomplete - can you please post the entire .xml plan as an
> attachment?
>
> Thanks
>
> Glenn
>
> magotici wrote:
>> teHi,
>> Here is what I've got:
>>
>> SELECT
>>
>> SELECT "project"."project",
>> "project"."client_due_date",
>> "project"."brands_assignment",
>> "project"."tot_obs",
>> "project"."number_block",
>> "project"."number_test_brands",
>> "project"."control",
>> "project"."product_arrival",
>> "project"."due_date_2",
>> "project_objectives"."year",
>> "project_objectives"."month",
>> "project_objectives"."percent",
>> "project"."type",
>> "project_types"."description",
>> "project_types"."colour_bkg",
>> "project_types"."colour_text",
>> (select count(schedule_id) from p_info where p_info.project =
>> project.project) as tot_units,
>> (select count(repeat_date) from p_info where p_info.project =
>> project.project) as tot_repeats,
>> (select count(*) from p_info where p_info.project =
>> project.project
>> and month(repeat_date) > 0 and status = 100) as repeats_done,
>> (select sum(percent) from project_objectives where
>> project_objectives.project = project.project ) as total_objectives,
>> "project"."project_closed",
>> "project"."description"
>> FROM "project" LEFT OUTER JOIN "project_objectives"
>> ON "project"."project" = "project_objectives"."project",
>> "project_types"
>> WHERE ("project"."project_closed" is null OR
>> 0 = 1 and "project"."project_closed" >= '20080901' and
>> "project"."project_closed" <= '20090101') AND
>> ("project"."product_arrival" is not null OR
>> 0 = 1) AND
>> ( "project"."type" = "project_types"."type" ) and
>> (("year" = year('20080901') AND
>> "month" >= month('20080901') OR
>> "year" > year('20080901')) AND
>> ("year" = year('20090101') AND
>> "month" <= month('20090101') OR
>> "year" < year('20090101')) OR
>> "percent" is null OR
>> "percent" = 0) AND
>> "project"."special" = 'N'
>> Node Statistics
>> Estimates Actual Description
>> Invocations - 1 Number of times the result was computed
>> RowsReturned 70.062 111 Number of rows returned
>> PercentTotalCost 99.862 99.894 Run time as a percent of total
>> query time
>> RunTime 7.9344 13.229 Time to compute the results
>> CPUTime 7.9344 - Time required by CPU
>> DiskReadTime 0 - Time to perform reads from disk
>> DiskWriteTime 0 - Time to perform writes to disk
>> CacheHits - 3.6929e+06 Cache Hits
>> CacheRead - 3.6929e+06 Cache reads
>> CacheReadTable - 3.6929e+06 Cache table reads
>> DiskRead 0 0 Disk reads
>> DiskWrite 0 0 Disk writes
>> IndLookup - 1 Number of index lookups
>> ExprCacheStarts - 4 Number of times the expression cache was
>> started
>> ExprCacheLookups - 428 Number of lookups done in the
>> expression
>> cache
>> ExprCacheHits - 192 Number of hits in the expression cache
>> ExprCacheInserts - 236 Number of values inserted into the
>> expression cache
>> RelocatableHeapPages - 19 Relocatable heap pages
>>
>>
>> Subtree Statistics
>> Estimates Actual Description
>> Invocations - 1 Number of times the result was computed
>> RowsReturned 70.062 111 Number of rows returned
>> PercentTotalCost 100 100 Run time as a percent of total query
>> time
>> RunTime 7.9453 13.243 Time to compute the results
>> CPUTime 7.9453 - Time required by CPU
>> DiskReadTime 0 - Time to perform reads from disk
>> DiskWriteTime 0 - Time to perform writes to disk
>> CacheHits - 3.6951e+06 Cache Hits
>> CacheRead - 3.6951e+06 Cache reads
>> CacheReadTable - 3.695e+06 Cache table reads
>> CacheReadIndLeaf - 188 Cache index leaf reads
>> DiskRead 0 0 Disk reads
>> DiskWrite 0 0 Disk writes
>> IndLookup - 113 Number of index lookups
>> ExprCacheStarts - 4 Number of times the expression cache was
>> started
>> ExprCacheLookups - 428 Number of lookups done in the
>> expression
>> cache
>> ExprCacheHits - 192 Number of hits in the expression cache
>> ExprCacheInserts - 236 Number of values inserted into the
>> expression cache
>> HashRowsFiltered - 1439 Rate at which probe rows are rejected
>> by
>> bit-vector filters
>> RelocatableHeapPages - 21 Relocatable heap pages
>>
>>
>> Optimizer statistics
>> Value Description
>> Costed subplans 34 Number of different enumeration strategies
>> considered by the optimizer
>> Estimated cache pages 37110 Estimated cache pages available for
>> this statement
>> CurrentCacheSize 153924 Current cache size in kilobytes
>> Isolation_level 0 Controls the locking isolation level
>> Optimization_goal First-row Optimize queries for first row or
>> all
>> rows
>> Optimization_level 9 Reserved
>> Optimization_workload Mixed Controls whether optimizing for
>> OLAP
>> or mixed queries
>> ProductVersion 9.0.2.3508 Product version
>> User_estimates Override-magic Controls whether to respect user
>> estimates
>>
>>
>> Select list
>> expr() varchar(20)
>> expr() date
>> expr() date
>> expr() unsigned smallint
>> expr() smallint
>> expr() smallint
>> expr() varchar(20)
>> expr() date
>> expr() date
>> expr() unsigned smallint
>> expr() tinyint
>> expr() decimal(5,2)
>> expr() char(3)
>> project_types.description varchar(15)
>> project_types.colour_bkg unsigned int
>> project_types.colour_text unsigned int
>> expr() int
>> expr() int
>> expr() int
>> expr() numeric(13,2)
>> expr() date
>> expr() varchar(1000)
>>
>>
>> Options
>> Value Description
>> Alias labstat Mounted database name
>> Ansi_blanks Off Controls truncation errors
>> Ansi_integer_overflow Off Controls whether integer overflow
>> causes
>> an error
>> Ansi_update_constraints Cursors Controls the range of updates
>> that
>> are permitted
>> Ansinull On Controls interpretation of NULL values
>> BlankPadding OFF Blank padding
>> BlobArenas ON Blob extension pages are stored separately from
>> table pages
>> Blocking On Controls response to locking conflicts
>> Capabilities 4087FFDD Database capability bits
>> CaseSensitive OFF Case sensitivity
>> CharSet cp850 Character Set
>> Checksum OFF Page checksum
>> ClusteredIndexes OFF Clustered indexes
>> Collation 850LATIN1 Collation name
>> Collect_statistics_on_dml_updates On Controls collection of
>> statistics during INSERT/UPDATE/DELETE statement execution
>> CommandLine -n labstat -x tcpip{myip=192.168.10.209} -ti 0
>> syb:\asany\labstat.db -n labstat Expanded command line used to start
>> the
>> server
>> CompressedBTrees ON Compressed B-Tree indexes are supported
>> Compression OFF Compression enabled
>> Conversion_error On Controls datatype conversion errors
>> Date_format YYYY-MM-DD Controls format for DATE values
>> Date_order YMD Controls order of date components
>> DBFileFragments 0 Database file fragments
>> DefaultCollation 850LATIN1 Default Collation
>> Divide_by_zero_error On Controls divide-by-zero errors
>> Encryption None Encryption type
>> Extended_join_syntax On Controls errors when using duplicate
>> correlation names in joins
>> File syb:\asany\labstat.db Database file
>> FileSize 77423 File size in pages
>> FileVersion 41 Database file version number
>> FreePageBitMaps ON Free database pages managed via bitmaps
>> FreePages 116 Number of free pages in dbspace
>> HistogramHashFix OFF Fixed histogram hash implementation
>> Histograms ON Optimizer statistics maintained as histograms
>> IndexStatistics OFF Index Statistics
>> IsJavaAvailable NO Java available
>> JavaObjectsEnabled NO Java objects enabled
>> IsNetworkServer YES Is Network Server
>> IsRuntimeServer NO Is Runtime Server
>> Language us_english
>> LicenseCount 300 Number of licensed seats
>> LicensedCompany Labstat Int Inc Name of the licensed company
>> LicensedUser Manuel Agotici Name of the licensed user
>> LicenseType networked seat (per-seat) License type
>> LivenessTimeout 120 Client liveness timeout default
>> LogFileFragments 0 Log file fragments
>> MachineName S1 Name of the machine
>> MainHeapBytes 2052272 Main heap bytes in cache
>> Max_plans_cached 20 Maximum number of cached execution plans
>> for a
>> connection
>> MaxCacheSize 153924 Maximum cache size in kilobytes
>> MinCacheSize 153924 Minimum cache size in kilobytes
>> MultiByteCharSet OFF Multi Byte Character Set ( on/off )
>> Name labstat
>> NamedConstraints OFF Named Constraints
>> NumProcessorsAvail 1 Number of processors on server
>> NumProcessorsMax 0 Maximum number of processors used
>> PageSize 2048 Database page size
>> PeakCacheSize 153924 Peak cache size in kilobytes
>> Platform Netware Operating system platform
>> PlatformVer NetWare 6.0, Support Pack 5 Operating system
>> platform
>> version
>> Precision 40 Maximum number of digits in decimal arithmetic
>> Prefetch On Controls prefetching of rows
>> ProcedureProfiling OFF Procedure profiling
>> ProcessorArchitecture X86 Processor architecture
>> ReadOnly OFF Database read-only mode
>> RequestLogging NONE Request logging
>> Row_counts Off Controls whether row counts are estimates or
>> exact
>> Scale 20 Minimum number of digits after decimal point
>> SeparateCheckpointLog ON Checkpoint log maintained at end of
>> system dbspace
>> SeparateForeignKeys ON Primary and foreign key indexes are
>> stored
>> separately
>> StartTime 2008-12-15 11:34:14.399 Server start time
>> String_rtruncation Off Controls truncation errors on INSERT or
>> UPDATE
>> StringHistogramsFix OFF Fixed string histograms implementation
>> TableBitMaps ON Table bit maps supported
>> TempDir . Temporary directory
>> TempFileName .\asat0000.tmp Database temporary file name
>> Threads 20 Server thread count
>> Time_format HH:NN:SS.SSS Controls format for TIME values
>> Timestamp_format YYYY-MM-DD HH:NN:SS.SSS Controls format for
>> TIMESTAMP values
>> Update_statistics On Controls collection of statistics during
>> query execution
>> Userid DBA User ID
>> VariableHashSize ON Hash length may be specified for BTree
>> indexes
>>
>>
>>
>>
>>
>>
>> "Chris Keating [Sybase iAnywhere]" <keating_no@spam_sybase.com> wrote in
>> message news:494a6d37@forums-1-dub...
>>> Can you get a query plan for this?
>>>
>>> To generate a Graphical Plan with Statistics
>>>
>>> 1. From DBISQL, go to Tools | Options and set the "Plan" option to
>>> Graphical Plan with Statistics
>>> 2. Press Shift-F5 to get the plan
>>> 3. For ASA 8 and ASA9, Select File | Save As... and
>>> Set the file type to XML
>>> 4. For SA 10, Select File | Save Plan...
>>> 5. provide a file name
>>> 6. Click Ok
>>>
>>> You can view a saved graphical plan by
>>>
>>> 1. For ASA 8 and ASA9, from DBISQL, select File | Open and
>>> Set the file type to XML
>>> 2. For SA 10, select File | Open Plan...
>>> 3. Select the Graphical plan file
>>>
>>> magotici wrote:
>>>> Hi David,
>>>> I have the querry that's slow now but was'nt in the previous version:
>>>>
>>>> SELECT "project"."project",
>>>> "project"."client_due_date",
>>>> "project"."brands_assignment",
>>>> "project"."tot_obs",
>>>> "project"."number_block",
>>>> "project"."number_test_brands",
>>>> "project"."control",
>>>> "project"."product_arrival",
>>>> "project"."due_date_2",
>>>> "project_objectives"."year",
>>>> "project_objectives"."month",
>>>> "project_objectives"."percent",
>>>> "project"."type",
>>>> "project_types"."description",
>>>> "project_types"."colour_bkg",
>>>> "project_types"."colour_text",
>>>> (select count(schedule_id) from p_info where p_info.project =
>>>> project.project) as tot_units,
>>>> (select count(repeat_date) from p_info where p_info.project =
>>>> project.project) as tot_repeats,
>>>> (select count(*) from p_info where p_info.project =
>>>> project.project and month(repeat_date) > 0 and status = 100) as
>>>> repeats_done,
>>>> (select sum(percent) from project_objectives where
>>>> project_objectives.project = project.project ) as total_objectives,
>>>> "project"."project_closed",
>>>> "project"."description"
>>>> FROM {oj "project" LEFT OUTER JOIN "project_objectives" ON
>>>> "project"."project" = "project_objectives"."project"},
>>>> "project_types"
>>>> WHERE ("project"."project_closed" is null OR
>>>> :pn_closed = 1 and "project"."project_closed" >= :pd_start and
>>>> "project"."project_closed" <= :pd_end) AND
>>>> ("project"."product_arrival" is not null OR
>>>> :pn_pending = 1) AND
>>>> ( "project"."type" = "project_types"."type" ) and
>>>> (("year" = year(:pd_start) AND
>>>> "month" >= month(:pd_start) OR
>>>> "year" > year(:pd_start)) AND
>>>> ("year" = year(:pd_end) AND
>>>> "month" <= month(:pd_end) OR
>>>> "year" < year(:pd_end)) OR
>>>> "percent" is null OR
>>>> "percent" = 0) AND
>>>> "project"."special" = 'N'
>>>>
>>>> Thanks,
>>>> magotici
>>>>
>>>>
>>>>
>>>> "David Fishburn" <fishburn_spam_off@ianywhere.com> wrote in message
>>>> news:Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106...
>>>>> "forums.sybase.com" <magotici@labstat.com> wrote in
>>>>> news:49482a21$1@forums-1-dub of ianywhere.public.general:
>>>>>
>>>>> fsc> After I updated to ASA 9.0.2 Netware EBF 3508, the DB got slower.
>>>>> Is
>>>>> fsc> there any known issue?
>>>>> fsc> I did the upgrade because of the problem with the log file from
>>>>> backup
>>>>> fsc> image.
>>>>>
>>>>> For these types of issues, it is generally not the ENTIRE database, it
>>>>> is usually certain queries.
>>>>>
>>>>> What have you done to figure out _which_ queries are slower?
>>>>> Don't know what your app does, but it is usually a portion of a page
>>>>> in your app is much slower than before. This usually points to an
>>>>> individual query.
>>>>>
>>>>> --
>>>>> David Fishburn
>>>>> Certified ASA Developer Version 8
>>>>> iAnywhere Solutions - Sybase
>>>>> Professional Services
>>>>> Please only post to the newsgroup
>>>>> Please ALWAYS include version and MORE importantly BUILD number with
>>>>> EACH post (dbeng11 -v).
>>>>>
>>>>> EBFs and Maintenance Releases
>>>>> http://downloads.sybase.com/
>>>>>
>>>>> Developer Community / Whitepapers
>>>>> http://www.ianywhere.com/developer
>>>>>
>>>>> Case-Express - to report bugs
>>>>> http://case-express.sybase.com
>>>>>
>>>>> CodeXchange - Free samples
>>>>> http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList
>>>>>
>>>>> Documentation
>>>>> http://dcx.sybase.com/
>>>>>
>>
>
>
> --
> 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/databasemanagement/sqlanywhere/technicalsupport


magotici Posted on 2008-12-27 15:04:37.0Z
From: "magotici" <magotici@labstat.com>
Newsgroups: ianywhere.public.general
References: <49482a21$1@forums-1-dub> <Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106> <494a61fc$1@forums-1-dub> <494a6d37@forums-1-dub> <494a7aaf@forums-1-dub> <494a9c8c@forums-1-dub>
Subject: Re: SQL Anywhere 9.0.2 for Netware slow
Lines: 413
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.3790.3959
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4325
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49564405@forums-1-dub>
Date: 27 Dec 2008 07:04:37 -0800
X-Trace: forums-1-dub 1230390277 10.22.241.152 (27 Dec 2008 07:04:37 -0800)
X-Original-Trace: 27 Dec 2008 07:04:37 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7296
Article PK: 5509

Hi,

Is the new plan complete?

magotici

"Glenn Paulley [Sybase iAnywhere]" <paulley@ianywhere.com> wrote in message
news:494a9c8c@forums-1-dub...
> This is incomplete - can you please post the entire .xml plan as an
> attachment?
>
> Thanks
>
> Glenn
>
> magotici wrote:
>> teHi,
>> Here is what I've got:
>>
>> SELECT
>>
>> SELECT "project"."project",
>> "project"."client_due_date",
>> "project"."brands_assignment",
>> "project"."tot_obs",
>> "project"."number_block",
>> "project"."number_test_brands",
>> "project"."control",
>> "project"."product_arrival",
>> "project"."due_date_2",
>> "project_objectives"."year",
>> "project_objectives"."month",
>> "project_objectives"."percent",
>> "project"."type",
>> "project_types"."description",
>> "project_types"."colour_bkg",
>> "project_types"."colour_text",
>> (select count(schedule_id) from p_info where p_info.project =
>> project.project) as tot_units,
>> (select count(repeat_date) from p_info where p_info.project =
>> project.project) as tot_repeats,
>> (select count(*) from p_info where p_info.project =
>> project.project and month(repeat_date) > 0 and status = 100) as
>> repeats_done,
>> (select sum(percent) from project_objectives where
>> project_objectives.project = project.project ) as total_objectives,
>> "project"."project_closed",
>> "project"."description"
>> FROM "project" LEFT OUTER JOIN "project_objectives"
>> ON "project"."project" = "project_objectives"."project",
>> "project_types"
>> WHERE ("project"."project_closed" is null OR
>> 0 = 1 and "project"."project_closed" >= '20080901' and
>> "project"."project_closed" <= '20090101') AND
>> ("project"."product_arrival" is not null OR
>> 0 = 1) AND
>> ( "project"."type" = "project_types"."type" ) and
>> (("year" = year('20080901') AND
>> "month" >= month('20080901') OR
>> "year" > year('20080901')) AND
>> ("year" = year('20090101') AND
>> "month" <= month('20090101') OR
>> "year" < year('20090101')) OR
>> "percent" is null OR
>> "percent" = 0) AND
>> "project"."special" = 'N'
>> Node Statistics
>> Estimates Actual Description
>> Invocations - 1 Number of times the result was computed
>> RowsReturned 70.062 111 Number of rows returned
>> PercentTotalCost 99.862 99.894 Run time as a percent of total
>> query time
>> RunTime 7.9344 13.229 Time to compute the results
>> CPUTime 7.9344 - Time required by CPU
>> DiskReadTime 0 - Time to perform reads from disk
>> DiskWriteTime 0 - Time to perform writes to disk
>> CacheHits - 3.6929e+06 Cache Hits
>> CacheRead - 3.6929e+06 Cache reads
>> CacheReadTable - 3.6929e+06 Cache table reads
>> DiskRead 0 0 Disk reads
>> DiskWrite 0 0 Disk writes
>> IndLookup - 1 Number of index lookups
>> ExprCacheStarts - 4 Number of times the expression cache was
>> started
>> ExprCacheLookups - 428 Number of lookups done in the
>> expression cache
>> ExprCacheHits - 192 Number of hits in the expression cache
>> ExprCacheInserts - 236 Number of values inserted into the
>> expression cache
>> RelocatableHeapPages - 19 Relocatable heap pages
>>
>>
>> Subtree Statistics
>> Estimates Actual Description
>> Invocations - 1 Number of times the result was computed
>> RowsReturned 70.062 111 Number of rows returned
>> PercentTotalCost 100 100 Run time as a percent of total query
>> time
>> RunTime 7.9453 13.243 Time to compute the results
>> CPUTime 7.9453 - Time required by CPU
>> DiskReadTime 0 - Time to perform reads from disk
>> DiskWriteTime 0 - Time to perform writes to disk
>> CacheHits - 3.6951e+06 Cache Hits
>> CacheRead - 3.6951e+06 Cache reads
>> CacheReadTable - 3.695e+06 Cache table reads
>> CacheReadIndLeaf - 188 Cache index leaf reads
>> DiskRead 0 0 Disk reads
>> DiskWrite 0 0 Disk writes
>> IndLookup - 113 Number of index lookups
>> ExprCacheStarts - 4 Number of times the expression cache was
>> started
>> ExprCacheLookups - 428 Number of lookups done in the
>> expression cache
>> ExprCacheHits - 192 Number of hits in the expression cache
>> ExprCacheInserts - 236 Number of values inserted into the
>> expression cache
>> HashRowsFiltered - 1439 Rate at which probe rows are rejected
>> by bit-vector filters
>> RelocatableHeapPages - 21 Relocatable heap pages
>>
>>
>> Optimizer statistics
>> Value Description
>> Costed subplans 34 Number of different enumeration strategies
>> considered by the optimizer
>> Estimated cache pages 37110 Estimated cache pages available for
>> this statement
>> CurrentCacheSize 153924 Current cache size in kilobytes
>> Isolation_level 0 Controls the locking isolation level
>> Optimization_goal First-row Optimize queries for first row or
>> all rows
>> Optimization_level 9 Reserved
>> Optimization_workload Mixed Controls whether optimizing for
>> OLAP or mixed queries
>> ProductVersion 9.0.2.3508 Product version
>> User_estimates Override-magic Controls whether to respect user
>> estimates
>>
>>
>> Select list
>> expr() varchar(20)
>> expr() date
>> expr() date
>> expr() unsigned smallint
>> expr() smallint
>> expr() smallint
>> expr() varchar(20)
>> expr() date
>> expr() date
>> expr() unsigned smallint
>> expr() tinyint
>> expr() decimal(5,2)
>> expr() char(3)
>> project_types.description varchar(15)
>> project_types.colour_bkg unsigned int
>> project_types.colour_text unsigned int
>> expr() int
>> expr() int
>> expr() int
>> expr() numeric(13,2)
>> expr() date
>> expr() varchar(1000)
>>
>>
>> Options
>> Value Description
>> Alias labstat Mounted database name
>> Ansi_blanks Off Controls truncation errors
>> Ansi_integer_overflow Off Controls whether integer overflow
>> causes an error
>> Ansi_update_constraints Cursors Controls the range of updates
>> that are permitted
>> Ansinull On Controls interpretation of NULL values
>> BlankPadding OFF Blank padding
>> BlobArenas ON Blob extension pages are stored separately from
>> table pages
>> Blocking On Controls response to locking conflicts
>> Capabilities 4087FFDD Database capability bits
>> CaseSensitive OFF Case sensitivity
>> CharSet cp850 Character Set
>> Checksum OFF Page checksum
>> ClusteredIndexes OFF Clustered indexes
>> Collation 850LATIN1 Collation name
>> Collect_statistics_on_dml_updates On Controls collection of
>> statistics during INSERT/UPDATE/DELETE statement execution
>> CommandLine -n labstat -x tcpip{myip=192.168.10.209} -ti 0
>> syb:\asany\labstat.db -n labstat Expanded command line used to start
>> the server
>> CompressedBTrees ON Compressed B-Tree indexes are supported
>> Compression OFF Compression enabled
>> Conversion_error On Controls datatype conversion errors
>> Date_format YYYY-MM-DD Controls format for DATE values
>> Date_order YMD Controls order of date components
>> DBFileFragments 0 Database file fragments
>> DefaultCollation 850LATIN1 Default Collation
>> Divide_by_zero_error On Controls divide-by-zero errors
>> Encryption None Encryption type
>> Extended_join_syntax On Controls errors when using duplicate
>> correlation names in joins
>> File syb:\asany\labstat.db Database file
>> FileSize 77423 File size in pages
>> FileVersion 41 Database file version number
>> FreePageBitMaps ON Free database pages managed via bitmaps
>> FreePages 116 Number of free pages in dbspace
>> HistogramHashFix OFF Fixed histogram hash implementation
>> Histograms ON Optimizer statistics maintained as histograms
>> IndexStatistics OFF Index Statistics
>> IsJavaAvailable NO Java available
>> JavaObjectsEnabled NO Java objects enabled
>> IsNetworkServer YES Is Network Server
>> IsRuntimeServer NO Is Runtime Server
>> Language us_english
>> LicenseCount 300 Number of licensed seats
>> LicensedCompany Labstat Int Inc Name of the licensed company
>> LicensedUser Manuel Agotici Name of the licensed user
>> LicenseType networked seat (per-seat) License type
>> LivenessTimeout 120 Client liveness timeout default
>> LogFileFragments 0 Log file fragments
>> MachineName S1 Name of the machine
>> MainHeapBytes 2052272 Main heap bytes in cache
>> Max_plans_cached 20 Maximum number of cached execution plans
>> for a connection
>> MaxCacheSize 153924 Maximum cache size in kilobytes
>> MinCacheSize 153924 Minimum cache size in kilobytes
>> MultiByteCharSet OFF Multi Byte Character Set ( on/off )
>> Name labstat
>> NamedConstraints OFF Named Constraints
>> NumProcessorsAvail 1 Number of processors on server
>> NumProcessorsMax 0 Maximum number of processors used
>> PageSize 2048 Database page size
>> PeakCacheSize 153924 Peak cache size in kilobytes
>> Platform Netware Operating system platform
>> PlatformVer NetWare 6.0, Support Pack 5 Operating system
>> platform version
>> Precision 40 Maximum number of digits in decimal arithmetic
>> Prefetch On Controls prefetching of rows
>> ProcedureProfiling OFF Procedure profiling
>> ProcessorArchitecture X86 Processor architecture
>> ReadOnly OFF Database read-only mode
>> RequestLogging NONE Request logging
>> Row_counts Off Controls whether row counts are estimates or
>> exact
>> Scale 20 Minimum number of digits after decimal point
>> SeparateCheckpointLog ON Checkpoint log maintained at end of
>> system dbspace
>> SeparateForeignKeys ON Primary and foreign key indexes are
>> stored separately
>> StartTime 2008-12-15 11:34:14.399 Server start time
>> String_rtruncation Off Controls truncation errors on INSERT or
>> UPDATE
>> StringHistogramsFix OFF Fixed string histograms implementation
>> TableBitMaps ON Table bit maps supported
>> TempDir . Temporary directory
>> TempFileName .\asat0000.tmp Database temporary file name
>> Threads 20 Server thread count
>> Time_format HH:NN:SS.SSS Controls format for TIME values
>> Timestamp_format YYYY-MM-DD HH:NN:SS.SSS Controls format for
>> TIMESTAMP values
>> Update_statistics On Controls collection of statistics during
>> query execution
>> Userid DBA User ID
>> VariableHashSize ON Hash length may be specified for BTree
>> indexes
>>
>>
>>
>>
>>
>>
>> "Chris Keating [Sybase iAnywhere]" <keating_no@spam_sybase.com> wrote in
>> message news:494a6d37@forums-1-dub...
>>> Can you get a query plan for this?
>>>
>>> To generate a Graphical Plan with Statistics
>>>
>>> 1. From DBISQL, go to Tools | Options and set the "Plan" option to
>>> Graphical Plan with Statistics
>>> 2. Press Shift-F5 to get the plan
>>> 3. For ASA 8 and ASA9, Select File | Save As... and
>>> Set the file type to XML
>>> 4. For SA 10, Select File | Save Plan...
>>> 5. provide a file name
>>> 6. Click Ok
>>>
>>> You can view a saved graphical plan by
>>>
>>> 1. For ASA 8 and ASA9, from DBISQL, select File | Open and
>>> Set the file type to XML
>>> 2. For SA 10, select File | Open Plan...
>>> 3. Select the Graphical plan file
>>>
>>> magotici wrote:
>>>> Hi David,
>>>> I have the querry that's slow now but was'nt in the previous version:
>>>>
>>>> SELECT "project"."project",
>>>> "project"."client_due_date",
>>>> "project"."brands_assignment",
>>>> "project"."tot_obs",
>>>> "project"."number_block",
>>>> "project"."number_test_brands",
>>>> "project"."control",
>>>> "project"."product_arrival",
>>>> "project"."due_date_2",
>>>> "project_objectives"."year",
>>>> "project_objectives"."month",
>>>> "project_objectives"."percent",
>>>> "project"."type",
>>>> "project_types"."description",
>>>> "project_types"."colour_bkg",
>>>> "project_types"."colour_text",
>>>> (select count(schedule_id) from p_info where p_info.project =
>>>> project.project) as tot_units,
>>>> (select count(repeat_date) from p_info where p_info.project =
>>>> project.project) as tot_repeats,
>>>> (select count(*) from p_info where p_info.project =
>>>> project.project and month(repeat_date) > 0 and status = 100) as
>>>> repeats_done,
>>>> (select sum(percent) from project_objectives where
>>>> project_objectives.project = project.project ) as total_objectives,
>>>> "project"."project_closed",
>>>> "project"."description"
>>>> FROM {oj "project" LEFT OUTER JOIN "project_objectives" ON
>>>> "project"."project" = "project_objectives"."project"},
>>>> "project_types"
>>>> WHERE ("project"."project_closed" is null OR
>>>> :pn_closed = 1 and "project"."project_closed" >= :pd_start and
>>>> "project"."project_closed" <= :pd_end) AND
>>>> ("project"."product_arrival" is not null OR
>>>> :pn_pending = 1) AND
>>>> ( "project"."type" = "project_types"."type" ) and
>>>> (("year" = year(:pd_start) AND
>>>> "month" >= month(:pd_start) OR
>>>> "year" > year(:pd_start)) AND
>>>> ("year" = year(:pd_end) AND
>>>> "month" <= month(:pd_end) OR
>>>> "year" < year(:pd_end)) OR
>>>> "percent" is null OR
>>>> "percent" = 0) AND
>>>> "project"."special" = 'N'
>>>>
>>>> Thanks,
>>>> magotici
>>>>
>>>>
>>>>
>>>> "David Fishburn" <fishburn_spam_off@ianywhere.com> wrote in message
>>>> news:Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106...
>>>>> "forums.sybase.com" <magotici@labstat.com> wrote in
>>>>> news:49482a21$1@forums-1-dub of ianywhere.public.general:
>>>>>
>>>>> fsc> After I updated to ASA 9.0.2 Netware EBF 3508, the DB got slower.
>>>>> Is
>>>>> fsc> there any known issue?
>>>>> fsc> I did the upgrade because of the problem with the log file from
>>>>> backup
>>>>> fsc> image.
>>>>>
>>>>> For these types of issues, it is generally not the ENTIRE database, it
>>>>> is usually certain queries.
>>>>>
>>>>> What have you done to figure out _which_ queries are slower?
>>>>> Don't know what your app does, but it is usually a portion of a page
>>>>> in your app is much slower than before. This usually points to an
>>>>> individual query.
>>>>>
>>>>> --
>>>>> David Fishburn
>>>>> Certified ASA Developer Version 8
>>>>> iAnywhere Solutions - Sybase
>>>>> Professional Services
>>>>> Please only post to the newsgroup
>>>>> Please ALWAYS include version and MORE importantly BUILD number with
>>>>> EACH post (dbeng11 -v).
>>>>>
>>>>> EBFs and Maintenance Releases
>>>>> http://downloads.sybase.com/
>>>>>
>>>>> Developer Community / Whitepapers
>>>>> http://www.ianywhere.com/developer
>>>>>
>>>>> Case-Express - to report bugs
>>>>> http://case-express.sybase.com
>>>>>
>>>>> CodeXchange - Free samples
>>>>> http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList
>>>>>
>>>>> Documentation
>>>>> http://dcx.sybase.com/
>>>>>
>>
>
>
> --
> 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/databasemanagement/sqlanywhere/technicalsupport


Glenn Paulley [Sybase iAnywhere] Posted on 2008-12-28 13:49:36.0Z
From: "Glenn Paulley [Sybase iAnywhere]" <paulley@ianywhere.com>
Reply-To: paulley@ianywhere.com
Organization: Sybase iAnywhere
User-Agent: Thunderbird 2.0.0.18 (Windows/20081105)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: SQL Anywhere 9.0.2 for Netware slow
References: <49482a21$1@forums-1-dub> <Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106> <494a61fc$1@forums-1-dub> <494a6d37@forums-1-dub> <494a7aaf@forums-1-dub> <494a9c8c@forums-1-dub> <49564405@forums-1-dub>
In-Reply-To: <49564405@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <495783f0@forums-1-dub>
Date: 28 Dec 2008 05:49:36 -0800
X-Trace: forums-1-dub 1230472176 10.22.241.152 (28 Dec 2008 05:49:36 -0800)
X-Original-Trace: 28 Dec 2008 05:49:36 -0800, vip152.sybase.com
Lines: 465
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7297
Article PK: 5511

The plan you've attached is fine.

I have a number of general comments about the query and its plan. Since
I don't have a (presumably fast) plan with an older version of 902 I
can't comment on what might have changed to cause this query to execute
more slowly.

However, here are some comments:

1) Why is your optimization_goal setting set to FIRST-ROW?

2) It looks like the computation of each subquery is the main problem
here, because computing the 111 rows returned by the main block only
takes 0.014 seconds (look at the Work node at the top of the plan tree
under "subtree statistics").

Each of the subselects in the SELECT list of the base query involve a
complete table scan, for three of them a scan of the p_info table which
is 14523 rows on 629 pages. Those scans will occur for each row in the
output (except for the effects of subquery caching - instead of being
scanned 111 times (each row returned by the query), each subquery is
executed only 69 times. Hence the server is scanning

629x3x63 + 40x63 = 118881 + 2520 = 121401 pages

for the subqueries. That will take some time, even if all of the pages
are in cache.

I cannot tell from a 9.x plan what indexes for the subqueries were
rejected - is it possible that some indexes are missing?

Glenn

magotici wrote:
> Hi,
>
> Is the new plan complete?
>
> magotici
>
> "Glenn Paulley [Sybase iAnywhere]" <paulley@ianywhere.com> wrote in message
> news:494a9c8c@forums-1-dub...
>> This is incomplete - can you please post the entire .xml plan as an
>> attachment?
>>
>> Thanks
>>
>> Glenn
>>
>> magotici wrote:
>>> teHi,
>>> Here is what I've got:
>>>
>>> SELECT
>>>
>>> SELECT "project"."project",
>>> "project"."client_due_date",
>>> "project"."brands_assignment",
>>> "project"."tot_obs",
>>> "project"."number_block",
>>> "project"."number_test_brands",
>>> "project"."control",
>>> "project"."product_arrival",
>>> "project"."due_date_2",
>>> "project_objectives"."year",
>>> "project_objectives"."month",
>>> "project_objectives"."percent",
>>> "project"."type",
>>> "project_types"."description",
>>> "project_types"."colour_bkg",
>>> "project_types"."colour_text",
>>> (select count(schedule_id) from p_info where p_info.project =
>>> project.project) as tot_units,
>>> (select count(repeat_date) from p_info where p_info.project =
>>> project.project) as tot_repeats,
>>> (select count(*) from p_info where p_info.project =
>>> project.project and month(repeat_date) > 0 and status = 100) as
>>> repeats_done,
>>> (select sum(percent) from project_objectives where
>>> project_objectives.project = project.project ) as total_objectives,
>>> "project"."project_closed",
>>> "project"."description"
>>> FROM "project" LEFT OUTER JOIN "project_objectives"
>>> ON "project"."project" = "project_objectives"."project",
>>> "project_types"
>>> WHERE ("project"."project_closed" is null OR
>>> 0 = 1 and "project"."project_closed" >= '20080901' and
>>> "project"."project_closed" <= '20090101') AND
>>> ("project"."product_arrival" is not null OR
>>> 0 = 1) AND
>>> ( "project"."type" = "project_types"."type" ) and
>>> (("year" = year('20080901') AND
>>> "month" >= month('20080901') OR
>>> "year" > year('20080901')) AND
>>> ("year" = year('20090101') AND
>>> "month" <= month('20090101') OR
>>> "year" < year('20090101')) OR
>>> "percent" is null OR
>>> "percent" = 0) AND
>>> "project"."special" = 'N'
>>> Node Statistics
>>> Estimates Actual Description
>>> Invocations - 1 Number of times the result was computed
>>> RowsReturned 70.062 111 Number of rows returned
>>> PercentTotalCost 99.862 99.894 Run time as a percent of total
>>> query time
>>> RunTime 7.9344 13.229 Time to compute the results
>>> CPUTime 7.9344 - Time required by CPU
>>> DiskReadTime 0 - Time to perform reads from disk
>>> DiskWriteTime 0 - Time to perform writes to disk
>>> CacheHits - 3.6929e+06 Cache Hits
>>> CacheRead - 3.6929e+06 Cache reads
>>> CacheReadTable - 3.6929e+06 Cache table reads
>>> DiskRead 0 0 Disk reads
>>> DiskWrite 0 0 Disk writes
>>> IndLookup - 1 Number of index lookups
>>> ExprCacheStarts - 4 Number of times the expression cache was
>>> started
>>> ExprCacheLookups - 428 Number of lookups done in the
>>> expression cache
>>> ExprCacheHits - 192 Number of hits in the expression cache
>>> ExprCacheInserts - 236 Number of values inserted into the
>>> expression cache
>>> RelocatableHeapPages - 19 Relocatable heap pages
>>>
>>>
>>> Subtree Statistics
>>> Estimates Actual Description
>>> Invocations - 1 Number of times the result was computed
>>> RowsReturned 70.062 111 Number of rows returned
>>> PercentTotalCost 100 100 Run time as a percent of total query
>>> time
>>> RunTime 7.9453 13.243 Time to compute the results
>>> CPUTime 7.9453 - Time required by CPU
>>> DiskReadTime 0 - Time to perform reads from disk
>>> DiskWriteTime 0 - Time to perform writes to disk
>>> CacheHits - 3.6951e+06 Cache Hits
>>> CacheRead - 3.6951e+06 Cache reads
>>> CacheReadTable - 3.695e+06 Cache table reads
>>> CacheReadIndLeaf - 188 Cache index leaf reads
>>> DiskRead 0 0 Disk reads
>>> DiskWrite 0 0 Disk writes
>>> IndLookup - 113 Number of index lookups
>>> ExprCacheStarts - 4 Number of times the expression cache was
>>> started
>>> ExprCacheLookups - 428 Number of lookups done in the
>>> expression cache
>>> ExprCacheHits - 192 Number of hits in the expression cache
>>> ExprCacheInserts - 236 Number of values inserted into the
>>> expression cache
>>> HashRowsFiltered - 1439 Rate at which probe rows are rejected
>>> by bit-vector filters
>>> RelocatableHeapPages - 21 Relocatable heap pages
>>>
>>>
>>> Optimizer statistics
>>> Value Description
>>> Costed subplans 34 Number of different enumeration strategies
>>> considered by the optimizer
>>> Estimated cache pages 37110 Estimated cache pages available for
>>> this statement
>>> CurrentCacheSize 153924 Current cache size in kilobytes
>>> Isolation_level 0 Controls the locking isolation level
>>> Optimization_goal First-row Optimize queries for first row or
>>> all rows
>>> Optimization_level 9 Reserved
>>> Optimization_workload Mixed Controls whether optimizing for
>>> OLAP or mixed queries
>>> ProductVersion 9.0.2.3508 Product version
>>> User_estimates Override-magic Controls whether to respect user
>>> estimates
>>>
>>>
>>> Select list
>>> expr() varchar(20)
>>> expr() date
>>> expr() date
>>> expr() unsigned smallint
>>> expr() smallint
>>> expr() smallint
>>> expr() varchar(20)
>>> expr() date
>>> expr() date
>>> expr() unsigned smallint
>>> expr() tinyint
>>> expr() decimal(5,2)
>>> expr() char(3)
>>> project_types.description varchar(15)
>>> project_types.colour_bkg unsigned int
>>> project_types.colour_text unsigned int
>>> expr() int
>>> expr() int
>>> expr() int
>>> expr() numeric(13,2)
>>> expr() date
>>> expr() varchar(1000)
>>>
>>>
>>> Options
>>> Value Description
>>> Alias labstat Mounted database name
>>> Ansi_blanks Off Controls truncation errors
>>> Ansi_integer_overflow Off Controls whether integer overflow
>>> causes an error
>>> Ansi_update_constraints Cursors Controls the range of updates
>>> that are permitted
>>> Ansinull On Controls interpretation of NULL values
>>> BlankPadding OFF Blank padding
>>> BlobArenas ON Blob extension pages are stored separately from
>>> table pages
>>> Blocking On Controls response to locking conflicts
>>> Capabilities 4087FFDD Database capability bits
>>> CaseSensitive OFF Case sensitivity
>>> CharSet cp850 Character Set
>>> Checksum OFF Page checksum
>>> ClusteredIndexes OFF Clustered indexes
>>> Collation 850LATIN1 Collation name
>>> Collect_statistics_on_dml_updates On Controls collection of
>>> statistics during INSERT/UPDATE/DELETE statement execution
>>> CommandLine -n labstat -x tcpip{myip=192.168.10.209} -ti 0
>>> syb:\asany\labstat.db -n labstat Expanded command line used to start
>>> the server
>>> CompressedBTrees ON Compressed B-Tree indexes are supported
>>> Compression OFF Compression enabled
>>> Conversion_error On Controls datatype conversion errors
>>> Date_format YYYY-MM-DD Controls format for DATE values
>>> Date_order YMD Controls order of date components
>>> DBFileFragments 0 Database file fragments
>>> DefaultCollation 850LATIN1 Default Collation
>>> Divide_by_zero_error On Controls divide-by-zero errors
>>> Encryption None Encryption type
>>> Extended_join_syntax On Controls errors when using duplicate
>>> correlation names in joins
>>> File syb:\asany\labstat.db Database file
>>> FileSize 77423 File size in pages
>>> FileVersion 41 Database file version number
>>> FreePageBitMaps ON Free database pages managed via bitmaps
>>> FreePages 116 Number of free pages in dbspace
>>> HistogramHashFix OFF Fixed histogram hash implementation
>>> Histograms ON Optimizer statistics maintained as histograms
>>> IndexStatistics OFF Index Statistics
>>> IsJavaAvailable NO Java available
>>> JavaObjectsEnabled NO Java objects enabled
>>> IsNetworkServer YES Is Network Server
>>> IsRuntimeServer NO Is Runtime Server
>>> Language us_english
>>> LicenseCount 300 Number of licensed seats
>>> LicensedCompany Labstat Int Inc Name of the licensed company
>>> LicensedUser Manuel Agotici Name of the licensed user
>>> LicenseType networked seat (per-seat) License type
>>> LivenessTimeout 120 Client liveness timeout default
>>> LogFileFragments 0 Log file fragments
>>> MachineName S1 Name of the machine
>>> MainHeapBytes 2052272 Main heap bytes in cache
>>> Max_plans_cached 20 Maximum number of cached execution plans
>>> for a connection
>>> MaxCacheSize 153924 Maximum cache size in kilobytes
>>> MinCacheSize 153924 Minimum cache size in kilobytes
>>> MultiByteCharSet OFF Multi Byte Character Set ( on/off )
>>> Name labstat
>>> NamedConstraints OFF Named Constraints
>>> NumProcessorsAvail 1 Number of processors on server
>>> NumProcessorsMax 0 Maximum number of processors used
>>> PageSize 2048 Database page size
>>> PeakCacheSize 153924 Peak cache size in kilobytes
>>> Platform Netware Operating system platform
>>> PlatformVer NetWare 6.0, Support Pack 5 Operating system
>>> platform version
>>> Precision 40 Maximum number of digits in decimal arithmetic
>>> Prefetch On Controls prefetching of rows
>>> ProcedureProfiling OFF Procedure profiling
>>> ProcessorArchitecture X86 Processor architecture
>>> ReadOnly OFF Database read-only mode
>>> RequestLogging NONE Request logging
>>> Row_counts Off Controls whether row counts are estimates or
>>> exact
>>> Scale 20 Minimum number of digits after decimal point
>>> SeparateCheckpointLog ON Checkpoint log maintained at end of
>>> system dbspace
>>> SeparateForeignKeys ON Primary and foreign key indexes are
>>> stored separately
>>> StartTime 2008-12-15 11:34:14.399 Server start time
>>> String_rtruncation Off Controls truncation errors on INSERT or
>>> UPDATE
>>> StringHistogramsFix OFF Fixed string histograms implementation
>>> TableBitMaps ON Table bit maps supported
>>> TempDir . Temporary directory
>>> TempFileName .\asat0000.tmp Database temporary file name
>>> Threads 20 Server thread count
>>> Time_format HH:NN:SS.SSS Controls format for TIME values
>>> Timestamp_format YYYY-MM-DD HH:NN:SS.SSS Controls format for
>>> TIMESTAMP values
>>> Update_statistics On Controls collection of statistics during
>>> query execution
>>> Userid DBA User ID
>>> VariableHashSize ON Hash length may be specified for BTree
>>> indexes
>>>
>>>
>>>
>>>
>>>
>>>
>>> "Chris Keating [Sybase iAnywhere]" <keating_no@spam_sybase.com> wrote in
>>> message news:494a6d37@forums-1-dub...
>>>> Can you get a query plan for this?
>>>>
>>>> To generate a Graphical Plan with Statistics
>>>>
>>>> 1. From DBISQL, go to Tools | Options and set the "Plan" option to
>>>> Graphical Plan with Statistics
>>>> 2. Press Shift-F5 to get the plan
>>>> 3. For ASA 8 and ASA9, Select File | Save As... and
>>>> Set the file type to XML
>>>> 4. For SA 10, Select File | Save Plan...
>>>> 5. provide a file name
>>>> 6. Click Ok
>>>>
>>>> You can view a saved graphical plan by
>>>>
>>>> 1. For ASA 8 and ASA9, from DBISQL, select File | Open and
>>>> Set the file type to XML
>>>> 2. For SA 10, select File | Open Plan...
>>>> 3. Select the Graphical plan file
>>>>
>>>> magotici wrote:
>>>>> Hi David,
>>>>> I have the querry that's slow now but was'nt in the previous version:
>>>>>
>>>>> SELECT "project"."project",
>>>>> "project"."client_due_date",
>>>>> "project"."brands_assignment",
>>>>> "project"."tot_obs",
>>>>> "project"."number_block",
>>>>> "project"."number_test_brands",
>>>>> "project"."control",
>>>>> "project"."product_arrival",
>>>>> "project"."due_date_2",
>>>>> "project_objectives"."year",
>>>>> "project_objectives"."month",
>>>>> "project_objectives"."percent",
>>>>> "project"."type",
>>>>> "project_types"."description",
>>>>> "project_types"."colour_bkg",
>>>>> "project_types"."colour_text",
>>>>> (select count(schedule_id) from p_info where p_info.project =
>>>>> project.project) as tot_units,
>>>>> (select count(repeat_date) from p_info where p_info.project =
>>>>> project.project) as tot_repeats,
>>>>> (select count(*) from p_info where p_info.project =
>>>>> project.project and month(repeat_date) > 0 and status = 100) as
>>>>> repeats_done,
>>>>> (select sum(percent) from project_objectives where
>>>>> project_objectives.project = project.project ) as total_objectives,
>>>>> "project"."project_closed",
>>>>> "project"."description"
>>>>> FROM {oj "project" LEFT OUTER JOIN "project_objectives" ON
>>>>> "project"."project" = "project_objectives"."project"},
>>>>> "project_types"
>>>>> WHERE ("project"."project_closed" is null OR
>>>>> :pn_closed = 1 and "project"."project_closed" >= :pd_start and
>>>>> "project"."project_closed" <= :pd_end) AND
>>>>> ("project"."product_arrival" is not null OR
>>>>> :pn_pending = 1) AND
>>>>> ( "project"."type" = "project_types"."type" ) and
>>>>> (("year" = year(:pd_start) AND
>>>>> "month" >= month(:pd_start) OR
>>>>> "year" > year(:pd_start)) AND
>>>>> ("year" = year(:pd_end) AND
>>>>> "month" <= month(:pd_end) OR
>>>>> "year" < year(:pd_end)) OR
>>>>> "percent" is null OR
>>>>> "percent" = 0) AND
>>>>> "project"."special" = 'N'
>>>>>
>>>>> Thanks,
>>>>> magotici
>>>>>
>>>>>
>>>>>
>>>>> "David Fishburn" <fishburn_spam_off@ianywhere.com> wrote in message
>>>>> news:Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106...
>>>>>> "forums.sybase.com" <magotici@labstat.com> wrote in
>>>>>> news:49482a21$1@forums-1-dub of ianywhere.public.general:
>>>>>>
>>>>>> fsc> After I updated to ASA 9.0.2 Netware EBF 3508, the DB got slower.
>>>>>> Is
>>>>>> fsc> there any known issue?
>>>>>> fsc> I did the upgrade because of the problem with the log file from
>>>>>> backup
>>>>>> fsc> image.
>>>>>>
>>>>>> For these types of issues, it is generally not the ENTIRE database, it
>>>>>> is usually certain queries.
>>>>>>
>>>>>> What have you done to figure out _which_ queries are slower?
>>>>>> Don't know what your app does, but it is usually a portion of a page
>>>>>> in your app is much slower than before. This usually points to an
>>>>>> individual query.
>>>>>>
>>>>>> --
>>>>>> David Fishburn
>>>>>> Certified ASA Developer Version 8
>>>>>> iAnywhere Solutions - Sybase
>>>>>> Professional Services
>>>>>> Please only post to the newsgroup
>>>>>> Please ALWAYS include version and MORE importantly BUILD number with
>>>>>> EACH post (dbeng11 -v).
>>>>>>
>>>>>> EBFs and Maintenance Releases
>>>>>> http://downloads.sybase.com/
>>>>>>
>>>>>> Developer Community / Whitepapers
>>>>>> http://www.ianywhere.com/developer
>>>>>>
>>>>>> Case-Express - to report bugs
>>>>>> http://case-express.sybase.com
>>>>>>
>>>>>> CodeXchange - Free samples
>>>>>> http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList
>>>>>>
>>>>>> Documentation
>>>>>> http://dcx.sybase.com/
>>>>>>
>>
>> --
>> 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/databasemanagement/sqlanywhere/technicalsupport
>
>

--
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/databasemanagement/sqlanywhere/technicalsupport


magotici Posted on 2009-01-06 18:13:16.0Z
From: "magotici" <magotici@labstat.com>
Newsgroups: ianywhere.public.general
References: <49482a21$1@forums-1-dub> <Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106> <494a61fc$1@forums-1-dub> <494a6d37@forums-1-dub> <494a7aaf@forums-1-dub> <494a9c8c@forums-1-dub> <49564405@forums-1-dub> <495783f0@forums-1-dub>
Subject: Re: SQL Anywhere 9.0.2 for Netware slow
Lines: 477
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.3790.3959
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4325
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49639f3c@forums-1-dub>
Date: 6 Jan 2009 10:13:16 -0800
X-Trace: forums-1-dub 1231265596 10.22.241.152 (6 Jan 2009 10:13:16 -0800)
X-Original-Trace: 6 Jan 2009 10:13:16 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7306
Article PK: 5517

Happy New Year, Glenn,

And thanks for the advices,

Manuel Agotici

"Glenn Paulley [Sybase iAnywhere]" <paulley@ianywhere.com> wrote in message
news:495783f0@forums-1-dub...
> The plan you've attached is fine.
>
> I have a number of general comments about the query and its plan. Since I
> don't have a (presumably fast) plan with an older version of 902 I can't
> comment on what might have changed to cause this query to execute more
> slowly.
>
> However, here are some comments:
>
> 1) Why is your optimization_goal setting set to FIRST-ROW?
>
> 2) It looks like the computation of each subquery is the main problem
> here, because computing the 111 rows returned by the main block only takes
> 0.014 seconds (look at the Work node at the top of the plan tree under
> "subtree statistics").
>
> Each of the subselects in the SELECT list of the base query involve a
> complete table scan, for three of them a scan of the p_info table which is
> 14523 rows on 629 pages. Those scans will occur for each row in the output
> (except for the effects of subquery caching - instead of being scanned 111
> times (each row returned by the query), each subquery is executed only 69
> times. Hence the server is scanning
>
> 629x3x63 + 40x63 = 118881 + 2520 = 121401 pages
>
> for the subqueries. That will take some time, even if all of the pages are
> in cache.
>
> I cannot tell from a 9.x plan what indexes for the subqueries were
> rejected - is it possible that some indexes are missing?
>
> Glenn
>
> magotici wrote:
>> Hi,
>>
>> Is the new plan complete?
>>
>> magotici
>>
>> "Glenn Paulley [Sybase iAnywhere]" <paulley@ianywhere.com> wrote in
>> message news:494a9c8c@forums-1-dub...
>>> This is incomplete - can you please post the entire .xml plan as an
>>> attachment?
>>>
>>> Thanks
>>>
>>> Glenn
>>>
>>> magotici wrote:
>>>> teHi,
>>>> Here is what I've got:
>>>>
>>>> SELECT
>>>>
>>>> SELECT "project"."project",
>>>> "project"."client_due_date",
>>>> "project"."brands_assignment",
>>>> "project"."tot_obs",
>>>> "project"."number_block",
>>>> "project"."number_test_brands",
>>>> "project"."control",
>>>> "project"."product_arrival",
>>>> "project"."due_date_2",
>>>> "project_objectives"."year",
>>>> "project_objectives"."month",
>>>> "project_objectives"."percent",
>>>> "project"."type",
>>>> "project_types"."description",
>>>> "project_types"."colour_bkg",
>>>> "project_types"."colour_text",
>>>> (select count(schedule_id) from p_info where p_info.project =
>>>> project.project) as tot_units,
>>>> (select count(repeat_date) from p_info where p_info.project =
>>>> project.project) as tot_repeats,
>>>> (select count(*) from p_info where p_info.project =
>>>> project.project and month(repeat_date) > 0 and status = 100) as
>>>> repeats_done,
>>>> (select sum(percent) from project_objectives where
>>>> project_objectives.project = project.project ) as total_objectives,
>>>> "project"."project_closed",
>>>> "project"."description"
>>>> FROM "project" LEFT OUTER JOIN "project_objectives"
>>>> ON "project"."project" = "project_objectives"."project",
>>>> "project_types"
>>>> WHERE ("project"."project_closed" is null OR
>>>> 0 = 1 and "project"."project_closed" >= '20080901' and
>>>> "project"."project_closed" <= '20090101') AND
>>>> ("project"."product_arrival" is not null OR
>>>> 0 = 1) AND
>>>> ( "project"."type" = "project_types"."type" ) and
>>>> (("year" = year('20080901') AND
>>>> "month" >= month('20080901') OR
>>>> "year" > year('20080901')) AND
>>>> ("year" = year('20090101') AND
>>>> "month" <= month('20090101') OR
>>>> "year" < year('20090101')) OR
>>>> "percent" is null OR
>>>> "percent" = 0) AND
>>>> "project"."special" = 'N'
>>>> Node Statistics
>>>> Estimates Actual Description
>>>> Invocations - 1 Number of times the result was computed
>>>> RowsReturned 70.062 111 Number of rows returned
>>>> PercentTotalCost 99.862 99.894 Run time as a percent of
>>>> total query time
>>>> RunTime 7.9344 13.229 Time to compute the results
>>>> CPUTime 7.9344 - Time required by CPU
>>>> DiskReadTime 0 - Time to perform reads from disk
>>>> DiskWriteTime 0 - Time to perform writes to disk
>>>> CacheHits - 3.6929e+06 Cache Hits
>>>> CacheRead - 3.6929e+06 Cache reads
>>>> CacheReadTable - 3.6929e+06 Cache table reads
>>>> DiskRead 0 0 Disk reads
>>>> DiskWrite 0 0 Disk writes
>>>> IndLookup - 1 Number of index lookups
>>>> ExprCacheStarts - 4 Number of times the expression cache
>>>> was started
>>>> ExprCacheLookups - 428 Number of lookups done in the
>>>> expression cache
>>>> ExprCacheHits - 192 Number of hits in the expression cache
>>>> ExprCacheInserts - 236 Number of values inserted into the
>>>> expression cache
>>>> RelocatableHeapPages - 19 Relocatable heap pages
>>>>
>>>>
>>>> Subtree Statistics
>>>> Estimates Actual Description
>>>> Invocations - 1 Number of times the result was computed
>>>> RowsReturned 70.062 111 Number of rows returned
>>>> PercentTotalCost 100 100 Run time as a percent of total
>>>> query time
>>>> RunTime 7.9453 13.243 Time to compute the results
>>>> CPUTime 7.9453 - Time required by CPU
>>>> DiskReadTime 0 - Time to perform reads from disk
>>>> DiskWriteTime 0 - Time to perform writes to disk
>>>> CacheHits - 3.6951e+06 Cache Hits
>>>> CacheRead - 3.6951e+06 Cache reads
>>>> CacheReadTable - 3.695e+06 Cache table reads
>>>> CacheReadIndLeaf - 188 Cache index leaf reads
>>>> DiskRead 0 0 Disk reads
>>>> DiskWrite 0 0 Disk writes
>>>> IndLookup - 113 Number of index lookups
>>>> ExprCacheStarts - 4 Number of times the expression cache
>>>> was started
>>>> ExprCacheLookups - 428 Number of lookups done in the
>>>> expression cache
>>>> ExprCacheHits - 192 Number of hits in the expression cache
>>>> ExprCacheInserts - 236 Number of values inserted into the
>>>> expression cache
>>>> HashRowsFiltered - 1439 Rate at which probe rows are
>>>> rejected by bit-vector filters
>>>> RelocatableHeapPages - 21 Relocatable heap pages
>>>>
>>>>
>>>> Optimizer statistics
>>>> Value Description
>>>> Costed subplans 34 Number of different enumeration strategies
>>>> considered by the optimizer
>>>> Estimated cache pages 37110 Estimated cache pages available
>>>> for this statement
>>>> CurrentCacheSize 153924 Current cache size in kilobytes
>>>> Isolation_level 0 Controls the locking isolation level
>>>> Optimization_goal First-row Optimize queries for first row or
>>>> all rows
>>>> Optimization_level 9 Reserved
>>>> Optimization_workload Mixed Controls whether optimizing for
>>>> OLAP or mixed queries
>>>> ProductVersion 9.0.2.3508 Product version
>>>> User_estimates Override-magic Controls whether to respect
>>>> user estimates
>>>>
>>>>
>>>> Select list
>>>> expr() varchar(20)
>>>> expr() date
>>>> expr() date
>>>> expr() unsigned smallint
>>>> expr() smallint
>>>> expr() smallint
>>>> expr() varchar(20)
>>>> expr() date
>>>> expr() date
>>>> expr() unsigned smallint
>>>> expr() tinyint
>>>> expr() decimal(5,2)
>>>> expr() char(3)
>>>> project_types.description varchar(15)
>>>> project_types.colour_bkg unsigned int
>>>> project_types.colour_text unsigned int
>>>> expr() int
>>>> expr() int
>>>> expr() int
>>>> expr() numeric(13,2)
>>>> expr() date
>>>> expr() varchar(1000)
>>>>
>>>>
>>>> Options
>>>> Value Description
>>>> Alias labstat Mounted database name
>>>> Ansi_blanks Off Controls truncation errors
>>>> Ansi_integer_overflow Off Controls whether integer overflow
>>>> causes an error
>>>> Ansi_update_constraints Cursors Controls the range of updates
>>>> that are permitted
>>>> Ansinull On Controls interpretation of NULL values
>>>> BlankPadding OFF Blank padding
>>>> BlobArenas ON Blob extension pages are stored separately from
>>>> table pages
>>>> Blocking On Controls response to locking conflicts
>>>> Capabilities 4087FFDD Database capability bits
>>>> CaseSensitive OFF Case sensitivity
>>>> CharSet cp850 Character Set
>>>> Checksum OFF Page checksum
>>>> ClusteredIndexes OFF Clustered indexes
>>>> Collation 850LATIN1 Collation name
>>>> Collect_statistics_on_dml_updates On Controls collection of
>>>> statistics during INSERT/UPDATE/DELETE statement execution
>>>> CommandLine -n labstat -x tcpip{myip=192.168.10.209} -ti 0
>>>> syb:\asany\labstat.db -n labstat Expanded command line used to start
>>>> the server
>>>> CompressedBTrees ON Compressed B-Tree indexes are supported
>>>> Compression OFF Compression enabled
>>>> Conversion_error On Controls datatype conversion errors
>>>> Date_format YYYY-MM-DD Controls format for DATE values
>>>> Date_order YMD Controls order of date components
>>>> DBFileFragments 0 Database file fragments
>>>> DefaultCollation 850LATIN1 Default Collation
>>>> Divide_by_zero_error On Controls divide-by-zero errors
>>>> Encryption None Encryption type
>>>> Extended_join_syntax On Controls errors when using duplicate
>>>> correlation names in joins
>>>> File syb:\asany\labstat.db Database file
>>>> FileSize 77423 File size in pages
>>>> FileVersion 41 Database file version number
>>>> FreePageBitMaps ON Free database pages managed via bitmaps
>>>> FreePages 116 Number of free pages in dbspace
>>>> HistogramHashFix OFF Fixed histogram hash implementation
>>>> Histograms ON Optimizer statistics maintained as histograms
>>>> IndexStatistics OFF Index Statistics
>>>> IsJavaAvailable NO Java available
>>>> JavaObjectsEnabled NO Java objects enabled
>>>> IsNetworkServer YES Is Network Server
>>>> IsRuntimeServer NO Is Runtime Server
>>>> Language us_english
>>>> LicenseCount 300 Number of licensed seats
>>>> LicensedCompany Labstat Int Inc Name of the licensed company
>>>> LicensedUser Manuel Agotici Name of the licensed user
>>>> LicenseType networked seat (per-seat) License type
>>>> LivenessTimeout 120 Client liveness timeout default
>>>> LogFileFragments 0 Log file fragments
>>>> MachineName S1 Name of the machine
>>>> MainHeapBytes 2052272 Main heap bytes in cache
>>>> Max_plans_cached 20 Maximum number of cached execution plans
>>>> for a connection
>>>> MaxCacheSize 153924 Maximum cache size in kilobytes
>>>> MinCacheSize 153924 Minimum cache size in kilobytes
>>>> MultiByteCharSet OFF Multi Byte Character Set ( on/off )
>>>> Name labstat
>>>> NamedConstraints OFF Named Constraints
>>>> NumProcessorsAvail 1 Number of processors on server
>>>> NumProcessorsMax 0 Maximum number of processors used
>>>> PageSize 2048 Database page size
>>>> PeakCacheSize 153924 Peak cache size in kilobytes
>>>> Platform Netware Operating system platform
>>>> PlatformVer NetWare 6.0, Support Pack 5 Operating system
>>>> platform version
>>>> Precision 40 Maximum number of digits in decimal arithmetic
>>>> Prefetch On Controls prefetching of rows
>>>> ProcedureProfiling OFF Procedure profiling
>>>> ProcessorArchitecture X86 Processor architecture
>>>> ReadOnly OFF Database read-only mode
>>>> RequestLogging NONE Request logging
>>>> Row_counts Off Controls whether row counts are estimates or
>>>> exact
>>>> Scale 20 Minimum number of digits after decimal point
>>>> SeparateCheckpointLog ON Checkpoint log maintained at end of
>>>> system dbspace
>>>> SeparateForeignKeys ON Primary and foreign key indexes are
>>>> stored separately
>>>> StartTime 2008-12-15 11:34:14.399 Server start time
>>>> String_rtruncation Off Controls truncation errors on INSERT
>>>> or UPDATE
>>>> StringHistogramsFix OFF Fixed string histograms
>>>> implementation
>>>> TableBitMaps ON Table bit maps supported
>>>> TempDir . Temporary directory
>>>> TempFileName .\asat0000.tmp Database temporary file name
>>>> Threads 20 Server thread count
>>>> Time_format HH:NN:SS.SSS Controls format for TIME values
>>>> Timestamp_format YYYY-MM-DD HH:NN:SS.SSS Controls format for
>>>> TIMESTAMP values
>>>> Update_statistics On Controls collection of statistics during
>>>> query execution
>>>> Userid DBA User ID
>>>> VariableHashSize ON Hash length may be specified for BTree
>>>> indexes
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> "Chris Keating [Sybase iAnywhere]" <keating_no@spam_sybase.com> wrote
>>>> in message news:494a6d37@forums-1-dub...
>>>>> Can you get a query plan for this?
>>>>>
>>>>> To generate a Graphical Plan with Statistics
>>>>>
>>>>> 1. From DBISQL, go to Tools | Options and set the "Plan" option to
>>>>> Graphical Plan with Statistics
>>>>> 2. Press Shift-F5 to get the plan
>>>>> 3. For ASA 8 and ASA9, Select File | Save As... and
>>>>> Set the file type to XML
>>>>> 4. For SA 10, Select File | Save Plan...
>>>>> 5. provide a file name
>>>>> 6. Click Ok
>>>>>
>>>>> You can view a saved graphical plan by
>>>>>
>>>>> 1. For ASA 8 and ASA9, from DBISQL, select File | Open and
>>>>> Set the file type to XML
>>>>> 2. For SA 10, select File | Open Plan...
>>>>> 3. Select the Graphical plan file
>>>>>
>>>>> magotici wrote:
>>>>>> Hi David,
>>>>>> I have the querry that's slow now but was'nt in the previous version:
>>>>>>
>>>>>> SELECT "project"."project",
>>>>>> "project"."client_due_date",
>>>>>> "project"."brands_assignment",
>>>>>> "project"."tot_obs",
>>>>>> "project"."number_block",
>>>>>> "project"."number_test_brands",
>>>>>> "project"."control",
>>>>>> "project"."product_arrival",
>>>>>> "project"."due_date_2",
>>>>>> "project_objectives"."year",
>>>>>> "project_objectives"."month",
>>>>>> "project_objectives"."percent",
>>>>>> "project"."type",
>>>>>> "project_types"."description",
>>>>>> "project_types"."colour_bkg",
>>>>>> "project_types"."colour_text",
>>>>>> (select count(schedule_id) from p_info where p_info.project
>>>>>> = project.project) as tot_units,
>>>>>> (select count(repeat_date) from p_info where p_info.project
>>>>>> = project.project) as tot_repeats,
>>>>>> (select count(*) from p_info where p_info.project =
>>>>>> project.project and month(repeat_date) > 0 and status = 100) as
>>>>>> repeats_done,
>>>>>> (select sum(percent) from project_objectives where
>>>>>> project_objectives.project = project.project ) as total_objectives,
>>>>>> "project"."project_closed",
>>>>>> "project"."description"
>>>>>> FROM {oj "project" LEFT OUTER JOIN "project_objectives" ON
>>>>>> "project"."project" = "project_objectives"."project"},
>>>>>> "project_types"
>>>>>> WHERE ("project"."project_closed" is null OR
>>>>>> :pn_closed = 1 and "project"."project_closed" >= :pd_start
>>>>>> and "project"."project_closed" <= :pd_end) AND
>>>>>> ("project"."product_arrival" is not null OR
>>>>>> :pn_pending = 1) AND
>>>>>> ( "project"."type" = "project_types"."type" ) and
>>>>>> (("year" = year(:pd_start) AND
>>>>>> "month" >= month(:pd_start) OR
>>>>>> "year" > year(:pd_start)) AND
>>>>>> ("year" = year(:pd_end) AND
>>>>>> "month" <= month(:pd_end) OR
>>>>>> "year" < year(:pd_end)) OR
>>>>>> "percent" is null OR
>>>>>> "percent" = 0) AND
>>>>>> "project"."special" = 'N'
>>>>>>
>>>>>> Thanks,
>>>>>> magotici
>>>>>>
>>>>>>
>>>>>>
>>>>>> "David Fishburn" <fishburn_spam_off@ianywhere.com> wrote in message
>>>>>> news:Xns9B776F43B8F8Afishburnsybasecom@10.22.241.106...
>>>>>>> "forums.sybase.com" <magotici@labstat.com> wrote in
>>>>>>> news:49482a21$1@forums-1-dub of ianywhere.public.general:
>>>>>>>
>>>>>>> fsc> After I updated to ASA 9.0.2 Netware EBF 3508, the DB got
>>>>>>> slower. Is
>>>>>>> fsc> there any known issue?
>>>>>>> fsc> I did the upgrade because of the problem with the log file from
>>>>>>> backup
>>>>>>> fsc> image.
>>>>>>>
>>>>>>> For these types of issues, it is generally not the ENTIRE database,
>>>>>>> it
>>>>>>> is usually certain queries.
>>>>>>>
>>>>>>> What have you done to figure out _which_ queries are slower?
>>>>>>> Don't know what your app does, but it is usually a portion of a page
>>>>>>> in your app is much slower than before. This usually points to an
>>>>>>> individual query.
>>>>>>>
>>>>>>> --
>>>>>>> David Fishburn
>>>>>>> Certified ASA Developer Version 8
>>>>>>> iAnywhere Solutions - Sybase
>>>>>>> Professional Services
>>>>>>> Please only post to the newsgroup
>>>>>>> Please ALWAYS include version and MORE importantly BUILD number with
>>>>>>> EACH post (dbeng11 -v).
>>>>>>>
>>>>>>> EBFs and Maintenance Releases
>>>>>>> http://downloads.sybase.com/
>>>>>>>
>>>>>>> Developer Community / Whitepapers
>>>>>>> http://www.ianywhere.com/developer
>>>>>>>
>>>>>>> Case-Express - to report bugs
>>>>>>> http://case-express.sybase.com
>>>>>>>
>>>>>>> CodeXchange - Free samples
>>>>>>> http://ianywhere.codexchange.sybase.com/servlets/ProjectDocumentList
>>>>>>>
>>>>>>> Documentation
>>>>>>> http://dcx.sybase.com/
>>>>>>>
>>>
>>> --
>>> 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/databasemanagement/sqlanywhere/technicalsupport
>>
>>
>
>
> --
> 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/databasemanagement/sqlanywhere/technicalsupport