indexing foreign key columns

[Former Member]

folks,

Been asked to look into some SQL Anywhere queries that have been performing poorly. Unfortunately, I have no experience with SQL Anywhere, more experience with Oracle.

My question is, in SQL Anywhere, is it good practice to index foreign key columns if they don't reference such low cardinality columns as status and types?

From what I see on some of the queries that I'm looking at, in cases there are several tables joined but the foreign keys are not indexed.

pjr

[Breck_Carter]

Unlike other DBMS software, SQL Anywhere automatically creates indexes on foreign keys, and the query optimizer uses those indexes when joining table IF (and it is a big IF) the indexes will make the query go faster.

Please tell us what version of SQL Anywhere you are using.

[Former Member]

Unfortunately the version of SQL Anywhere is version 7.0! And they tell me there's no intentions to upgrade.

[Former Member]

Breck, would the case of indices being automatically created when foreign keys are created be apart of SQL Anywhere 7.0 by chance?

[VolkerBarth]

AFAIK, this has even been true for v5.5:

SQL Anywhere's referential integrity checks rely on these indices to check efficiently whether each child record has a fitting "parent row".

[Breck_Carter]

Versions 5 and 6 created funky combined indexes for primary and foreign keys, but with versions 7 and later the situation was greatly improved by creating a separate index for each primary key, foreign key and UNIQUE constraint. If you have multiple columns in your primary keys, there MAY be an advantage in creating separate secondary indexes depending on your needs (different column ordering, different column sorting, etc).

[Breck_Carter]

Version 7 has the PLAN function; post the output for your poor-performing queries: SELECT PLAN( 'select * from department where dept_id > 100' )

[VolkerBarth]

One might note that this improvement will only apply in case the database has been created with v7 or has unloaded from an earlier version and has been rebuilt with v7. Running a v7 setup will not necessarily mean that the database is v7, too... (Just for the record.)

From the v7 "What's New" section:

In previous releases, primary and foreign keys have had a single index automatically associated with them, which describes all primary key values and all the related foreign key entries. In some situations, this architecture lead to poor performance. The new index organization separates these indexes, which leads to improved performance in some situations.

For more information on key indexes, see Using keys to improve query performance.

Your database must be unloaded and reloaded to take advantage of variable hash size indexes, and separate key indexes. Running the Upgrade [dbupgrad] utility is not sufficient.

[Breck_Carter]

Good catch... FWIW Foxhound displays both server and database file versions; here's an example of a v5 db file on a v7 server:

[Former Member]

Here is one of the queries and the associated plan ...

select c.clientid, c.lastname, c.firstname, pr.name1, p.patientid, 
       p.statusid, p.name, r.description, pch.staffid, pch.datein, 
       pch.dateout, (dateformat(pch.datein, 'hh:mm')) as time_in,
      (dateformat(pch.dateout, 'hh:mm')) as time_out,
      (durationdisplay(pch.datein, pch.dateout)) AS time_duration,
       pch.secondary_reason_id as census_reason_id, pch.secondary_reason_txt as      census_reason_txt,
       s.secondary_reason_txt as reason_txt
from   client c,  
       owner o, 
       patient p, 
       pcensush pch, 
       practice pr, 
       reason r, 
       SECONDARY_REASON s
where  p.patientid = pch.patientid
    AND  o.clientid = c.clientid  
    AND  o.patientid = p.patientid
    AND  c.clientid = pch.clientid 
    AND  pr.primary_prac = 1
    AND  pch.reasonid *= r.reasonid
    and  pch.secondary_reason_id *= s.secondary_reason_id

here are the table counts ... 
client - 202297 rows 
owner - 327874 rows 
patient - 323112 rows 
pcensush - 820652 rows 
practice - 6 rows (I lied previously when I thought there was just 1) 
reason - 50 rows 
secondary_reason - 0 rows

estimate 41479 i/o operations (best of 158 plans considered)

scan owner as o sequentially
   estimate getting here 327874 times   
scan pcensush as pch using foreign key fk_pcensush_patient
   for rows where patientid equals owner.patientid
   estimate getting here 996683 times conditions reduce this to 857     
scan secondary_reason as s using primary key
   for rows where secondary_reason_id equals pcensush.secondary_reason_id
   estimate getting here 0 times conditions reduce this to 857       
scan patient as p using primary key
   for rows where patientid equals pcensush.patientid
   estimate getting here 857 times         
scan client as c using primary key
   for rows where clientid equals pcensush.clientid
   estimate getting here 857 times           
scan reason as r using primary key
   for rows where reasonid equals pcensush.reasonid
   estimate getting here 857 times             
Scan PRACTICE AS pr sequentially
   Estimate getting here 5142 times 

Sorry for the formatting! Of note, there is reference to a PRACTICE table with no joins to any other tables. At present there is only one record in that table.

Your help is very much appreciated!!!

[Breck_Carter]

Can you provide the CREATE TABLE statements for the tables, and the row counts?

[VolkerBarth]

Would it make any difference if you rewrite the FROM/WHERE clauses with INNER / OUTER joins instead of WHERE conditions and T-SQL star joins?

[Former Member]

Breck, I have QweryBuilder v7.2 Express and ISQL v7 but see no easy way to see the table definitions. Is there a tool that could be of more use to me? Thanks

[Former Member]

here are the table counts ... client - 202297 rows owner - 327874 rows patient - 323112 rows pcensush - 820652 rows practice - 6 rows (I lied previously when I thought there was just 1) reason - 50 rows secondary_reason - 0 rows

[Former Member]

Volker, isn't the use of '*=' a left outer join? Again, SQL Anywhere not my expertise, so correct me if I'm wrong. Thanks

[Breck_Carter]

I think there is a way in Sybase Central to capture or export schema, but maybe not in V7.

The dbunload.exe utility with the -n option will dump the schema for the entire database into a reload.sql file from which you can copy and paste.

Foxhound has a "Display Schema" facility that includes row counts, optimized for copy and paste... designed for just this kind of forensic investigation http://www.risingroad.com/foxhound/#schema_at_a_glance

(Note: Disk space numbers are not displayed for V7 databases)

[VolkerBarth]

Yes, that's a T-SQL left outer join. Me suggestion was just to try to rewrite this with ANSI JOIN syntax (i.e. like "...FROM client c inner join owner o on c.clientid = o.clientid inner join ...") - it should stay semnatically identical but could help SQL Anywhere to make better use of FK relationships...

But note, that's a mere suggestion, I do not claim that it will have ANY effect...

[Former Member]

awesome. I'll take that into consideration. Thanks

[MCMartin]

In Sybase Central you can try the Application Profiling, it will provide you after analyzing the workload with index recommendations if the indexes would improve the workload. Or in dbisql you can use the tools - index consultant for a similar recommendation.

[Former Member]

Martin, I'm guessing the "tools - index consultant" is a feature of dbisql greater than the version 7.0.4.3472 that I have!?! 🙂

[VolkerBarth]

Yes, the "Index consultant" was introduced in v9.

However, in case you can get a v9 database engine and according Sybase Central version, this engine still can run a v7 database...

[chris_keating]

Performance diagnostic tools are limited in ASA7. Perhaps you can post the query plans for the problem queries for comment. You can get plans in dbisql or using the PLAN(<stmt_str>) function. Here is an example for using PLAN:

   SELECT PLAN( 'select * from department where dept_id > 100' )