Hallo,

we have performance problems with an
esql/c program.

The select via isql works fine and is
fast but takes some minutes in the esql/c
program.

I think it's possible to activate
"set showplan on" via "exec sql".

But how do I get the text of the
execution plan?

Rega

> The select via isql works fine and is
> fast but takes some minutes in the esql/c
> program.

Make sure you're running *identical* sql in isql and esql/c.
esql/c might be using different datatype in WHERE clause than indexed
column(s) that causes tablescan.

i.d.

I'm not sure if it's possible to print the "showplan"
as an ESQL program runs. If not, there are other
things you can do, such as running
sp_showplan <spid>, NULL, NULL, NULL
as the program executes. Unfortunately, this only
shows the execution plan for INSERT, UPDATE,
DELETE and plain SELECT, not ESQL cursors, where
sp_showplan only shows the cursor name.

If the ESQL program executes SQL with slower
performance than isql, check the common causes for
this:
- Add "FOR READ ONLY" in every non-updateable cursor.
Otherwise, ASE will asume it is a cursor for update
and choose a unique index, even if there is
a more performant non-unique index
- Check datatype mismatch. IIRC, differences in the
length of char variables are handled OK by ESQL,
but this is not the case with numbers; mismatchs
between int and float, for instance, may prevent
ASE from choosing an index.
- In very rare cases, cursors behave differently
than plain selects. If in doubt, check if isql
shows the same showplan for a SELECT and its
corresponding OPEN cursor.
For instance, cursors cannot use the "OR
strategy" (not the same as the "special OR
strategy", which cursors can use)
- The optimizer uses different statistics if the
value of a column is known or not. If in doubt,
check if isql shows the same showplan for both;
for instance,
WHERE COL1 = 7
versus
WHERE COL1 = @local_variable

Regards,
Mariano Corral

"J. Bajdala" <baj@sisis.de> wrote:
> we have performance problems with an
> esql/c program.
>
> The select via isql works fine and is
> fast but takes some minutes in the esql/c
> program.
>
> I think it's possible to activate
> "set showplan on" via "exec sql".
>
> But how do I get the text of the
> execution plan?

Mariano Corral wrote:

> I'm not sure if it's possible to print the "showplan"
> as an ESQL program runs. If not, there are other
> things you can do, such as running
> sp_showplan <spid>, NULL, NULL, NULL

Thanks for the hints.

I checked them (especially the cursor and
variable hint) but the execution plan was
ok.

We then tested our program at a big customer
database - the performance was fine there.

Since this program shall run only once
(at different customers db's of course)
we hope this was a special problem at our
database and hope the best for our customers :-)

Regards

Jürgen