SQL Anywhere database - Dead spots in a dataset?

Author

Dead spots in a dataset?

Steven J. Serenska

2005-05-30, 8:23 pm

Hello everyone:

I have a fairly complex query that uses 4 INNER JOINs and 20 LEFT JOINs.
I am using SQLA 9.0.2.3044.

The query uses a WHERE CLAUSE to display 10-record slices of a dataset
in a dbGrid. When the user reaches the 10th record and presses either
PgDn, the Down arrow, or uses the mouse wheel, the WHERE CLAUSE is
updated and the next 10 records are SELECTed and displayed in the grid.
This technique allows the user to browse a 30,000 record dataset on a
desktop application and have the performance resemble a desktop DB such
as MS-Access.

My problem is that one of my datasets reliably returns query results in
..01-.03 seconds with one key exception. When the queries are run
against one particular spot in the DB, performance slows to a miserable
crawl (e.g., 10-20 seconds). Occasionally, ISQL even hangs up when
running certain records in the query. As I say, this occurs for only
about 20 or so records in the dataset. All other records respond almost
instantaneously.

I am not a SQL nor SQL Anywhere expert and I labeled this email's
subject line above for humorous purposes. Can anyone suggest which
tools/statistics/areas of SQLA I should begin looking at to discover why
performance suffers in this way?

Thanks.

Steven J. Serenska

P.S. I can post the SQL if you think it would help, but it's fairly
lengthy and highly specific to my application.

Paul Horan[TeamSybase]

2005-05-30, 8:23 pm

"Steven J. Serenska" < sjs@RemoveTheseWords
Banksurance.com> wrote in message news:429b91b1$1@foru
ms-1-dub...
> Hello everyone:
>
> I have a fairly complex query that uses 4 INNER JOINs and 20 LEFT JOINs. I am using SQLA 9.0.2.3044.
>
<snip>
>
> I am not a SQL nor SQL Anywhere expert and I labeled this email's subject line above for humorous purposes. Can
> anyone suggest which tools/statistics/areas of SQLA I should begin looking at to discover why performance suffers in
> this way?
>
> Thanks.
>
> Steven J. Serenska
>
> P.S. I can post the SQL if you think it would help, but it's fairly lengthy and highly specific to my application.

Before you start your application, run the following two statements in ISQL:
call sa_server_option( 'request_level_loggi
ng', 'SQL');
call sa_server_option( 'request_level_log_f
ile', '<drive\path\filename.txt>');

Now run the application, and note where you are (what the retrieval arguments are) when it gets "slow".

call sa_server_option( 'request_level_loggi
ng', 'None');
call sa_server_option( 'request_level_log_f
ile', '');

In that .txt file, you'll see all the SQL that the server was asked to process. What you can do at this point, is find
the "slow" queries, cut them to the clipboard, paste them back into ISQL, and select Get Plan. It will show you exactly
what access plan it chose to process the query. Compare the slow plan to the fast plan, and see if that doesn't
illuminate the problem. You might also take the Index Consultant for a spin - it can recommend additional indexes that
might help.

You may need to run CREATE STATISTICS on some (or all) of the tables involved in the query.

--
Paul Horan[TeamSybase]

Sponsored Links