|
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 |
|
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. Unfortunately the version of SQL Anywhere is version 7.0! And they tell me there's no intentions to upgrade.
(26 Mar '12, 13:27)
pjr
Replies hidden
Breck, would the case of indices being automatically created when foreign keys are created be apart of SQL Anywhere 7.0 by chance?
(26 Mar '12, 13:28)
pjr
Replies hidden
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".
(26 Mar '12, 14:02)
Volker Barth
2
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).
(26 Mar '12, 14:09)
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' )
(26 Mar '12, 14:12)
Breck Carter
1
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:
(27 Mar '12, 10:09)
Volker Barth
Good catch... FWIW Foxhound displays both server and database file versions; here's an example of a v5 db file on a v7 server:
(27 Mar '12, 11:57)
Breck Carter
|
|
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. Martin, I'm guessing the "tools - index consultant" is a feature of dbisql greater than the version 7.0.4.3472 that I have!?! :-)
(26 Mar '12, 13:37)
pjr
Replies hidden
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...
(26 Mar '12, 14:21)
Volker Barth
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' )
(26 Mar '12, 16:13)
Chris Keating
|
|
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!!! Can you provide the CREATE TABLE statements for the tables, and the row counts?
(27 Mar '12, 08:09)
Breck Carter
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?
(27 Mar '12, 08:27)
Volker Barth
Replies hidden
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
(27 Mar '12, 08:34)
pjr
Replies hidden
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
(27 Mar '12, 08:40)
pjr
Volker, isn't the use of '*=' a left outer join? Again, SQL Anywhere not my expertise, so correct me if I'm wrong. Thanks
(27 Mar '12, 08:47)
pjr
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)
(27 Mar '12, 08:49)
Breck Carter
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...
(27 Mar '12, 09:30)
Volker Barth
awesome. I'll take that into consideration. Thanks
(27 Mar '12, 09:39)
pjr
|
