Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kruger53
Starting Member
2 Posts |
 Posted - 2008-10-27 : 19:54:29
|
| Hi ThereI'm using SQL Anywhere 9.xx3 TablesContactsAddressLook_upContacts can link to Address for up to 4 different addresses. (mail_address, work_address, bill_address, physical_address). Look-up holds the country description and the contact_title_descriptionMy query readsSELECT contact.contact_date_of_birth, contact.contact_direct_phone, contact.contact_email_address, contact.contact_fax, contact.contact_first_contact, contact.contact_first_name, contact.contact_home_phone, contact.contact_job_title, contact.contact_last_contact, contact.contact_surname, contact.contact_mobile_phone, contact_bill_address_country.look_up_description AS contact_bill_address_country, replace (replace (contact_bill_address.client_address_lines, char(10), ', '), char(13), '' ) AS contact_bill_address_lines_flat, contact_bill_address.client_address_lines AS contact_bill_address_lines_wrapped, contact_bill_address.client_address_name AS contact_bill_address_name, contact_bill_address.client_address_post_code AS contact_bill_address_post_code, contact_bill_address.client_address_suburb AS contact_bill_address_suburb, contact_mail_address_country.look_up_description AS contact_mail_address_country, replace (replace (contact_mail_address.client_address_lines, char(10), ', '), char(13), '' ) AS contact_mail_address_lines_flat, contact_mail_address.client_address_lines AS contact_mail_address_lines_wrapped, contact_mail_address.client_address_name AS contact_mail_address_name, contact_mail_address.client_address_post_code AS contact_mail_address_post_code, contact_mail_address.client_address_suburb AS contact_mail_address_suburb, contact_physical_address_country.look_up_description AS contact_physical_address_country, replace (replace (contact_physical_address.client_address_lines, char(10), ', '), char(13), '' ) AS contact_physical_address_lines_flat, contact_physical_address.client_address_lines AS contact_physical_address_lines_wrapped, contact_physical_address.client_address_name AS contact_physical_address_name, contact_physical_address.client_address_post_code AS contact_physical_address_post_code, contact_physical_address.client_address_suburb AS contact_physical_address_suburb, contact_title.look_up_description AS contact_title, contact_work_location_address_country.look_up_description AS contact_work_location_address_country, replace (replace (contact_work_location_address.client_address_lines, char(10), ', '), char(13), '' ) AS contact_work_location_address_lines_flat, contact_work_location_address.client_address_lines AS contact_work_location_address_lines_wrapped, contact_work_location_address.client_address_name as contact_work_location_address_name, contact_work_location_address.client_address_post_code as contact_work_location_address_post_code, contact_work_location_address.client_address_suburb AS contact_work_location_address_suburb FROM contact LEFT JOIN look_up as contact_title ON contact.contact_title = contact_title.look_up_id_pf LEFT JOIN client_address as contact_mail_address ON contact.contact_mail_address = contact_mail_address.client_address_id_pf LEFT JOIN look_up as contact_mail_address_country ON contact_mail_address.client_address_country = contact_mail_address_country.look_up_id_pf LEFT JOIN client_address as contact_physical_address ON contact.contact_physical_address = contact_physical_address.client_address_id_pf LEFT JOIN look_up as contact_physical_address_country ON contact_physical_address.client_address_country = contact_physical_address_country.look_up_id_pf LEFT JOIN client_address as contact_bill_address ON contact.contact_bill_address = contact_bill_address.client_address_id_pf LEFT JOIN look_up as contact_bill_address_country ON contact_bill_address.client_address_country = contact_bill_address_country.look_up_id_pf LEFT JOIN client_address as contact_work_location_address ON contact.contact_work_location = contact_work_location_address.client_address_id_pf LEFT JOIN look_up as contact_work_location_address_country ON contact_work_location_address.client_address_country = contact_work_location_address_country.look_up_id_pf ORDER BY contact.client_id_pf, contact_first_name, contact_surname My problem is, for every join to the client_address table my performance drops by 20%-25%Can anyone suggest a way I can speed this? Please. |
|
|
dexter.knudson
Constraint Violating Yak Guru
260 Posts |
Posted - 2008-10-27 : 21:16:27
|
| What is the type & length of client_address_id_pf & what indexes/key do you have on this table? |
 |
|
|
Kruger53
Starting Member
2 Posts |
Posted - 2008-10-28 : 01:50:37
|
| Thanks for the reply. All of my tables have as their primary key the table name followed by _id_pf and all are integers. I've used the Index Consultant in Sybase's Interactive SQL and in doesn't recommend any changes. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-10-28 : 06:10:38
|
| 1. when we asked about the indices....we were looking for the "columns" that were covered by tehe indices...not the index names.2. This is a MS-SQL Server site (see the FAQ's)....you may get better sybase support on www.dbforums.com (but our general advice can be useful at times)3. Best tack is to ensure the columns in the join conditions are indexed. Start out with a small query with no indices, add an index and check performance, add a join, check performance, add an index, check performance. GOOD coding is about a series of repeated steps..."change, measure, adjust" |
|
|
|
|
|
|
|
|
Join Performance