When i execute a select statement with a group by. I get a worktable
created. When i run sysmon on the system during this period I only see
APL Clustered Table activity in the Transaction Profile section. This
seams to indicate ASE is creating the temp table, then adding a CI and
then inserting. Is this correct? (The documentation I have seen tends
to indicate that it should always use an APL heap table for
worktables.) If so, is there a way to force ASE to insert into a heap
instead? To save of index maintenance and page splits. I'm using 12.5.

output
Inserts

APL Heap Table 0.0 0.0 0 0.0 %

APL Clustered Table 529.5 10590.0 10590 100.0 %

Data Only Lock Table 0.0 0.0 0 0.0 %

Hi Jim,

Run a showplan for your query ... perhaps the optimizer is
"REFORMATTING" one (or more) of the tables included in your query ...
when a "REFORMAT" is done a Clustered work table is created, at least
that has been my experience ... and this almost always has a drastic
negative effect on performance ... just recently I had a developer
refer a poorly performing query to me ... I found that is was being
REFORMATTED because the join variables were of different type
(numeric/int) ...

Let me know if this helps!

Sara ...

jim.wright (AT) bigfoot (DOT) com (James Wright) wrote in message news:<1cb7c826.0407300304.6a78b03d (AT) posting (DOT) google.com>...

Hi Jim,

Try running a "showplan" on your query ... perhaps one or more of the
tables is being "REFORMATTED" by the optimizer ... when a REFORMAT
occurs a Clustered APL worktable is created (at least that has been my
experience) and this usually has a big negative impact on performance
if the table has any significant size ... recently a developer
referred a poorly performing query to me ... I found that one large
table was being REFORMATTED because the join variables were of
different datatypes (numeric/integer).

Hope this helps!!

Sara ...

jim.wright (AT) bigfoot (DOT) com (James Wright) wrote in message news:<1cb7c826.0407300304.6a78b03d (AT) posting (DOT) google.com>...

Hi Sara,

Thanks for the response. The underlying query isn't the problem. I'm
looking at the overall performance of our server and noticed this
behaviour of inserting into a table with a CI already created. Why
does the server create the index before an insert and incur the
maintenance hit? The documentation appears to say that it should
insert into a heap for worktables, which, on the face of it, would be
the sensible option. I was wondering if there are config setting to
make this happen.

Cheers,

Jim