SQL Anywhere database - Checking if SQL1 performs better than SQL2

Author

Checking if SQL1 performs better than SQL2

2005-05-13, 7:23 am

We're using ASA 9.0.2.3044

One of our developers has 'optimized' some our more complex SQLs
by turning them inside out.

I'd like to make sure, they actually perform better than before.

When using 'Get plan ' in ISQL, I'm unsure which parameter to
look at, when I want to compare the two statements with 'all else
being equal'.

I mean, if the server is idle when running SQL1 and happens to be
very busy or has flushed some cached data when running SQL2, the
run-times, cache-hits, disk access etc. is bound be different even
though the SQLs should perform identically.

So which parameter do I compare, if I want to know if an SQL indeed
has been optimized, or the bottleneck has just been shifted around
and perhaps is actually performing worse.

regards/Peter Simonsen

anil k goel

2005-05-13, 9:23 am

Some things to do:

1. Run the statements using 'fetchtst' instead of using DBISQL to keep the
overhead of measurements to a minimum. There are a number of things you can
control with fetchtst. While it is a useful exercise to use DBISQL to
analyze plans for the alternate queries, it is not an optimal tool in
determining their relative speeds.
2. If you expect your queries to be running concurrently, look into trantest
which can simulate multiple connections.
3. Compare the two queries both with no data in cache (cold) and with data
already in cache (warm). You could give different importance to cold and
warm speeds depending upon what actually happens when these queries are in
production.
4. Use 'sa_flush_cache' to obtain repeatable results -- calling this
procedure makes the server remove all buffered pages from its cache thereby
allowing you to obtain 'cold cache' results without having to restart the
server.

--
-anil
Research and Development, Query Processing
iAnywhere Solutions Engineering

-------------------------------------------------------------------------
** Whitepapers, TechDocs, bug fixes are all available through the **
** iAnywhere Developer Community at http://www.ianywhere.com/developer **
-------------------------------------------------------------------------

<Peter Simonsen> wrote in message news:428481c1$1@foru
ms-2-dub...
> We're using ASA 9.0.2.3044
>
> One of our developers has 'optimized' some our more complex SQLs
> by turning them inside out.
>
> I'd like to make sure, they actually perform better than before.
>
> When using 'Get plan ' in ISQL, I'm unsure which parameter to
> look at, when I want to compare the two statements with 'all else
> being equal'.
>
> I mean, if the server is idle when running SQL1 and happens to be
> very busy or has flushed some cached data when running SQL2, the
> run-times, cache-hits, disk access etc. is bound be different even
> though the SQLs should perform identically.
>
> So which parameter do I compare, if I want to know if an SQL indeed
> has been optimized, or the bottleneck has just been shifted around
> and perhaps is actually performing worse.
>
> regards/Peter Simonsen
>
>

Breck Carter [TeamSybase]

2005-05-13, 9:23 am

On 13 May 2005 05:57:40 -0700, "anil k goel"
<firstname.no-junk.lastname@spam.ianywhere.com> wrote:

>Some things to do:
>
>1. Run the statements using 'fetchtst' instead of using DBISQL to keep the
>overhead of measurements to a minimum. There are a number of things you can
>control with fetchtst. While it is a useful exercise to use DBISQL to
>analyze plans for the alternate queries, it is not an optimal tool in
>determining their relative speeds.
>2. If you expect your queries to be running concurrently, look into trantest
>which can simulate multiple connections.

With all due respect to the author(s), I have never been able to
figure out how to use either fetchtst or trantest to do anything
useful. I'm sure they work wonderfully, but not for me (:

Breck

>3. Compare the two queries both with no data in cache (cold) and with data
>already in cache (warm). You could give different importance to cold and
>warm speeds depending upon what actually happens when these queries are in
>production.
>4. Use 'sa_flush_cache' to obtain repeatable results -- calling this
>procedure makes the server remove all buffered pages from its cache thereby
>allowing you to obtain 'cold cache' results without having to restart the
>server.

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

anil k goel

2005-05-13, 11:23 am

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:3pb981hsah33sod
bsn5pd4v2kd3l5aosqi@
4ax.com...
> On 13 May 2005 05:57:40 -0700, "anil k goel"
> <firstname.no-junk.lastname@spam.ianywhere.com> wrote:
>
>
> With all due respect to the author(s), I have never been able to
> figure out how to use either fetchtst or trantest to do anything
> useful. I'm sure they work wonderfully, but not for me (:

I'll admit that trantest takes some getting used to (when I used trantest
for the first time, it took me a while to get all the nuances and I think it
can be improved substantially), but fetchtst? .

Just putting the text of a query in a file and invoking fetchtst on the file
is as simple as it gets and gives you the bare bones time for executing that
query. I am curious to know, Breck, about your experiences with fetchtst.

-anil

2005-05-17, 7:23 am

Thanks, I'll try that.

Regards/Peter Simonsen

"anil k goel" <firstname.no-junk.lastname@spam.ianywhere.com> wrote in
message news:4284a440@forums
-2-dub...
> Some things to do:
>
> 1. Run the statements using 'fetchtst' instead of using DBISQL to keep the
> overhead of measurements to a minimum. There are a number of things you
can
> control with fetchtst. While it is a useful exercise to use DBISQL to
> analyze plans for the alternate queries, it is not an optimal tool in
> determining their relative speeds.
> 2. If you expect your queries to be running concurrently, look into
trantest
> which can simulate multiple connections.
> 3. Compare the two queries both with no data in cache (cold) and with data
> already in cache (warm). You could give different importance to cold and
> warm speeds depending upon what actually happens when these queries are in
> production.
> 4. Use 'sa_flush_cache' to obtain repeatable results -- calling this
> procedure makes the server remove all buffered pages from its cache
thereby
> allowing you to obtain 'cold cache' results without having to restart the
> server.
>
> --
> -anil
> Research and Development, Query Processing
> iAnywhere Solutions Engineering
>
> -------------------------------------------------------------------------
> ** Whitepapers, TechDocs, bug fixes are all available through the **
> ** iAnywhere Developer Community at http://www.ianywhere.com/developer **
> -------------------------------------------------------------------------
>
> <Peter Simonsen> wrote in message news:428481c1$1@foru
ms-2-dub...
>
>

Sponsored Links