Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2008
    Posts
    4

    Unanswered: SQL Anywhere - join performance

    Hi There

    I'm using SQL Anywhere 9.xx

    3 Tables
    Contacts
    Address
    Look_up

    My query reads

    SELECT
    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_descripti on 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_desc ription AS contact_work_location_address_country,
    replace (replace (contact_work_location_address.client_address_line s, 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_subur b 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_count ry = contact_work_location_address_country.look_up_id_p f
    ORDER BY contact.client_id_pf, contact_first_name, contact_surname

    My problem is, for every join to the client_address table (and there are 4) my performance drops by 20%-30%.

    (client_address_id_pf is the primary key (int)).

    I have run the Sybase Central Index Consultant and it doesn't recommend the addition of any indexes.

    Can anyone suggest a way I can speed this? Please.

  2. #2
    Join Date
    Oct 2008
    Posts
    4
    Pretty please?

  3. #3
    Join Date
    Aug 2008
    Posts
    12
    wow... I could be wrong...lord knows thats what my wife always says

    but I believe you are joining the ENTIRE client_address table 4 seperate times

    this means...you are scanning that entire table 4 times!

    there has got to be a more efficient way to write this

    what exactly are you trying to do?

  4. #4
    Join Date
    Oct 2008
    Posts
    4
    No you're not wrong. Regardless of what your wife says.:-)

    Simple Client database. Each Client can have mulitple contacts. Each contact can have up to 4 addresses; 1. Mail, 2. Physical Location, 3. Work Location and 4. Billing. These addresses may be the same for all contacts within the one Client or quite different. And each contact address may be the same or again different.

  5. #5
    Join Date
    Feb 2009
    Posts
    1

    Join

    Cambia los Join por Where y de forma que coincida co los indices.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •