Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    71

    Red face Unanswered: inefficient SQL syntax?

    I am assured by a developer that the following SQL statement has been
    lovingly crafted to be as efficient as possible and that the
    performance issues we see when this runs MUST be due to the outdated
    (his words) database technology that is Informix. Can you guys take a
    look and let me know what you think of its structure, from purely a
    SQL best practice/theoretical standpoint?

    INSERT INTO fileoddity SELECT f.file_key, l.oddity_no,
    CASE WHEN c.data_type = 1 THEN l.char_data ELSE NULL END,
    CASE WHEN c.data_type = 2 THEN l.int_data ELSE NULL END,
    CASE WHEN c.data_type = 3 THEN l.dec_data ELSE NULL END,
    CASE WHEN c.data_type = 4 THEN l.data_data ELSE NULL END
    FROM batchload s, files f, loadoddity l, clntoddity c
    WHERE s.membernum = f.membernum AND s.oddity_link
    IN (SELECT capt_oddity_lnk FROM tmp_in_threshold)
    AND s.clnt_id = f.clnt_id
    AND s.oddity_link = l.oddity_link
    AND l.oddity_no = c.oddity_no
    AND c.oddity_level = 'F'
    AND c.entity_id = 141
    AND f.file_key > 12596584;

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780

  3. #3
    Join Date
    Apr 2003
    Location
    Phoenix, AZ
    Posts
    177
    So, what does the Optimizer explanation tell you? It's joining several tables (Indexes?) and using the "IN" which tends to promote bad optimizer choices.

    Statistics up to date?

    Based upon what SET EXPLAIN tells you, you may need to add some optimizer directives to force a given path.
    Fred Prose

Posting Permissions

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