1. First execute the following statement in SQL Advantage
Set SHOWPLAN on
Go
See if execution is used to the index.
For example: SELECT issuing.book_name,
Issuing.author,
Issuing.publisher,
Issuing.publish_date,
ISSUING.ISBN,
Issuing.branch,
Issuing.price,
Issuing.biblio_no,
Issuing.biblio_code,
Issuing.data_no,
Issuing.summary,
0,
Issuing.order_status,
Issuing.check_status,
Issuing.money_type
From Mark.issuing issuing
WHERE biblio_code= ' ZD200400002 '
return value:
QUERY PLAN for STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
From TABLE
Mark.issuing
Issuing
Nested iteration.
Table Scan. This shows that the index is not used
Forward Scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer replacement strategy for data pages.
Parse and Compile time 0.
SQL Server CPU time:0 Ms.
Table:issuing Scan count 1, logical reads: (regular=2915 apf=0 total=2915), physical reads: (regular=0 apf=0 total=0), AP F IOs used=0
Total writes for this command:0
Execution Time 1.
SQL Server CPU time:100 Ms. SQL Server elapsed time:63 Ms.
(629 rows affected)
The above results indicate that the search point is not an index entry, which affects the retrieval speed.
If you add an index, the return value should be as follows.
QUERY PLAN for STATEMENT 1 (at line 1).
STEP 1
The type of query is SELECT.
From TABLE
Mark.issuing
Issuing
Nested iteration.
Index:idx_biblio_code
Forward Scan.
Positioning by key.
Keys are:
Biblio_code ASC
Using I/O Size 2 kbytes for index leaf pages.
With LRU Buffer replacement strategy for index leaf pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer replacement strategy for data pages.
(629 rows affected)
With this index, the retrieval speed will be increased.
2. Similar commands have the following
SET STATISTICS IO on
Go
Set STATISTICS TIME on
Go
Check execution time.
Set NOEXEC on
Go
This means that only the query plan is executed and not really queried.
How to check the usage of indexes when the Sybase database is retrieved
