sqlanywhere performance improvement

Hi,

1. Does anyone know how I can rebuild indexes in a sqlanywhere database.
2. can anyone tell me how to see how the optimizer searches for the best
path, if he uses his indexes properly, ...
3. Is there a possibility to view all sql-statements that are executed on
the database, with there execution time?

Thanks in advance !


Xavier



0
Xavier
2/20/2000 6:07:56 PM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

11 Replies
384 Views

Similar Articles

[PageSpeed] 37

Xavier Keters wrote:
> 
> Hi,
> 
> 1. Does anyone know how I can rebuild indexes in a sqlanywhere database.

IF EXISTS(SELECT name FROM sysindexes WHERE name='MyIndexName')
   DROP INDEX MyTableName.MyIndexName
GO
CREATE INDEX MyIndexName ON MyTableName (ColName1 ASC, ColName2 ASC)
GO

> 2. can anyone tell me how to see how the optimizer searches for the best
> path, if he uses his indexes properly, ...

Big subject. Try searching in the archives (www.deja.com) for past
discussions and links to white papers.

> 3. Is there a possibility to view all sql-statements that are executed on
> the database, with there execution time?

I don't think so...

Kathleen
0
Kathleen
2/20/2000 6:52:40 PM
>1. Does anyone know how I can rebuild indexes in a sqlanywhere database.
see Kathleen's answer.
>2. can anyone tell me how to see how the optimizer searches for the best
>path, if he uses his indexes properly, ...
Kathleen is correct, you can find a white paper on this topic at www.sybase.com.  If you simply want
to know what the optimizer choose, you can execute the query in ISQL and look at the Statistics
window.  Or you can use the PLAN() function. 

>3. Is there a possibility to view all sql-statements that are executed on
>the database, with there execution time?
Powerbuilder can produce  such a trace. 


Leo Tohill - Team Sybase
>> Please post in newsgroup, not via email <<
0
leotohill
2/20/2000 9:25:01 PM
I think you might be able to at least create the index create statements
without too much work if you use Sybase Central.  Use the Unload Database
utility and generate just the SQL for the structure.  Then edit the
resulting script to remove everything except for the CREATE INDEX commands.
If you have a good text editor, you can make corresponding DROP statements
easily.

--

Michael F. Nicewarner [TeamSybase]
mailto:mike.nicewarner@ibpinc.com
http://www.datamodel.org


Xavier Keters <xavier.keters@advalvas.be> wrote in message
news:AmJz228e$GA.332@forums.sybase.com...
> Hi,
>
> 1. Does anyone know how I can rebuild indexes in a sqlanywhere database.
> 2. can anyone tell me how to see how the optimizer searches for the best
> path, if he uses his indexes properly, ...
> 3. Is there a possibility to view all sql-statements that are executed on
> the database, with there execution time?
>
> Thanks in advance !
>
>
> Xavier
>
>
>


0
Michael
2/21/2000 4:31:13 PM
Hi Kathleen,
I have to use
select Iname from SysIndexes where iName = 'MyIndexName' and
         tName = 'MyTableName'
Since SQLA 5.5.04 does not enforce unique index names for the entire database I
have to check the table name also so I do not attempt to drop a non existent
index.

Gavin Godfrey


Kathleen Beaumont wrote:

> Xavier Keters wrote:
> >
> > Hi,
> >
> > 1. Does anyone know how I can rebuild indexes in a sqlanywhere database.
>
> IF EXISTS(SELECT name FROM sysindexes WHERE name='MyIndexName')
>    DROP INDEX MyTableName.MyIndexName
> GO
> CREATE INDEX MyIndexName ON MyTableName (ColName1 ASC, ColName2 ASC)
> GO
>
> > 2. can anyone tell me how to see how the optimizer searches for the best
> > path, if he uses his indexes properly, ...
>
> Big subject. Try searching in the archives (www.deja.com) for past
> discussions and links to white papers.
>
> > 3. Is there a possibility to view all sql-statements that are executed on
> > the database, with there execution time?
>
> I don't think so...
>
> Kathleen

0
Gavin
2/22/2000 12:34:44 AM
Gavin Godfrey wrote:

> Since SQLA 5.5.04 does not enforce unique index names for the entire database I
> have to check the table name also so I do not attempt to drop a non existent
> index.

I didn't know that. Thanks for the information, Gavin.

Kathleen
0
Kathleen
2/22/2000 4:15:27 PM
You're welcome. I have 3 indexes called 'Name' on 3 tables. One day I may rename them
to something clearer, eg, CustName and so forth. The only thing I have noticed is
that sometimes SQL Central will show the comments for one of the 'Name' indexes when
you are looking at another 'Name' index.

Gavin Godfrey

Kathleen Beaumont wrote:

> Gavin Godfrey wrote:
>
> > Since SQLA 5.5.04 does not enforce unique index names for the entire database I
> > have to check the table name also so I do not attempt to drop a non existent
> > index.
>
> I didn't know that. Thanks for the information, Gavin.
>
> Kathleen

0
Gavin
2/23/2000 1:36:40 AM
That is possible since the COMMENTS command applies the comment to 
and index name.  The remarks are stored on the same row with the rest of 
the index information.  So you're right that having the same name will 
cause this problem.  But other than that I don't see there being a 
problem with using an index name more than once.  As long as the names 
are predictable and you can reference the table to indicate which index 
is the target you'll be OK.
-- 
Jim Egan [TeamSybase]
Houston, TX

Sybase Developers Network
http://sdn.sybase.com/sdn/mec/mec_home.stm
0
eganjp
2/23/2000 2:31:20 AM
>> 3. Is there a possibility to view all sql-statements that are executed on
>> the database, with there execution time?

> I don't think so...

Yes, you can see this information.
In 6.0.3 (at least this version can do it) you can:

call sa_server_option ( 'request_level_debugging', 'on' );

As long as you run with the -o switch on the engine, it will capture
everything that goes to the server/engine.  This file can get quite large.

This has been refined in 7.0
call sa_server_option ( 'request_level_debugging', 'ALL or SQL or NONE' );
call sa_server_option ( 'request_level_log_file', 'specify the file name' );

Now you can capture the SQL sent to the database.  The ALL option is what
6.x does.

Check the Help file on the above stored procedure call.  This might have
been added all the way back to 6.0.0



--
David Fishburn
Sybase
Please only post to the newsgroup

Sybase Developers Network
http://sdn.sybase.com/sdn/mec/mec_home.stm

Xavier Keters <xavier.keters@advalvas.be> wrote in message
news:AmJz228e$GA.332@forums.sybase.com...
> Hi,
>
> 1. Does anyone know how I can rebuild indexes in a sqlanywhere database.
> 2. can anyone tell me how to see how the optimizer searches for the best
> path, if he uses his indexes properly, ...
> 3. Is there a possibility to view all sql-statements that are executed on
> the database, with there execution time?
>
> Thanks in advance !
>
>
> Xavier
>
>
>


0
David
2/23/2000 2:33:14 PM
>Yes, you can see this information.
>In 6.0.3 (at least this version can do it) you can:

>call sa_server_option ( 'request_level_debugging', 'on' );
Unfortunately, this  option doesn't provide execution time, which Xavier wanted. 

Now that would be a _really nice_ enhancement.  Just a timestamp on each message line would do. 


Leo Tohill - Team Sybase
>> Please post in newsgroup, not via email <<
0
leotohill
2/23/2000 3:49:10 PM
> Unfortunately, this  option doesn't provide execution time, which Xavier
wanted.

I would have to disagree with you on that Leo.

> Now that would be a _really nice_ enhancement.  Just a timestamp on each
message line would do.

Look at the output FILE, not the engine window and you will see line is
already timestamped.

In this case you can see the cursor_open and each fetch as it came in.

This is output from 6.0.3


I. 02/23 14:31:27. ** REQUEST conn: 12541672 CURSOR_OPEN
Stmt=65544
I. 02/23 14:31:27. ** DONE    conn: 12541672 CURSOR_OPEN
Crsr=65545
I. 02/23 14:31:27. ** REQUEST conn: 12541672 ROW_DESCRIPTOR
Crsr=65545
I. 02/23 14:31:27. ** DONE    conn: 12541672 ROW_DESCRIPTOR
I. 02/23 14:31:27. ** REQUEST conn: 12541672 CURSOR_FETCH
Crsr=65545, Count=1, Offset=1 (absolute)
I. 02/23 14:31:27. ** DONE    conn: 12541672 CURSOR_FETCH
I. 02/23 14:31:27. ** REQUEST conn: 12541672 CURSOR_FETCH
Crsr=65545, Count=1, Offset=2 (absolute)
I. 02/23 14:31:27. ** DONE    conn: 12541672 CURSOR_FETCH
I. 02/23 14:31:27. ** REQUEST conn: 12541672 CURSOR_FETCH
Crsr=65545, Count=1, Offset=3 (absolute)
I. 02/23 14:31:27. ** DONE    conn: 12541672 CURSOR_FETCH
I. 02/23 14:31:27. ** REQUEST conn: 12541672 CURSOR_FETCH
Crsr=65545, Count=1, Offset=4 (absolute)
I. 02/23 14:31:27. ** DONE    conn: 12541672 CURSOR_FETCH
I. 02/23 14:31:27. ** REQUEST conn: 12541672 CURSOR_FETCH
Crsr=65545, Count=1, Offset=5 (absolute)
I. 02/23 14:31:28. ** DONE    conn: 12541672 CURSOR_FETCH
I. 02/23 14:31:28. ** REQUEST conn: 12541672 CURSOR_FETCH
Crsr=65545, Count=1, Offset=6 (absolute)


--
David Fishburn
Sybase
Please only post to the newsgroup

Sybase Developers Network
http://sdn.sybase.com/sdn/mec/mec_home.stm

Leo Tohill <leotohill@csi.com> wrote in message
news:38b400ec.413143@199.93.177.77...
> >Yes, you can see this information.
> >In 6.0.3 (at least this version can do it) you can:
>
> >call sa_server_option ( 'request_level_debugging', 'on' );
> Unfortunately, this  option doesn't provide execution time, which Xavier
wanted.
>
> Now that would be a _really nice_ enhancement.  Just a timestamp on each
message line would do.
>
>
> Leo Tohill - Team Sybase
> >> Please post in newsgroup, not via email <<


0
David
2/23/2000 7:33:22 PM
Thanks folks !



0
Xavier
2/26/2000 6:23:51 PM
Reply: