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.
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.
answered 26 Mar '12, 10:36
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.
answered 26 Mar '12, 11:12
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.
Your help is very much appreciated!!!