Where is this SQL statement bottlenecking?

Question

I am trying to write a SQL (Sybase) query to fetch movie theatre information out of a database. When I run the query below, the query just keeps running. Eventually, after 5 minutes or so, I just abort it. There's no bad/illegal SQL, it's just slow!

SELECT
    TM.type_prefix +
        right('00000000' + convert(varchar, TM.showing_id), 8) +
        right('0000' + convert(varchar, TM.theatre_id), 4) as data_id
FROM
    theatre_times..theatre_mappings TM,
    theatres..region_codes RC,
    title_summaries..movie_summary MS
WHERE
    TM.scheme_id = 512 AND
    RC.region_code_id = MS.region_code_id

Don't worry about the table names or logic used in the query, I'm just wondering if there's anything obvious that I'm doing wrong or inefficiently. Here are the counts for all three tables:

theatre_mappings = 2,094,163
region_codes = 11,140,348
movie_summary = 6,437,782

Thanks in advance!

You have this... `tm CROSS JOIN (rc INNER JOIN ms ON RC.region_code_id = MS.region_code_id)` Is that really correct? Or is there a field that should be used to join `tm` to `rc`, or `tm` to `ms`? Also, note, if you use explicit `JOIN` syntax, it becomes much harder to make these mistakes. *(If, indeed, it **is** a mistake.)* — MatBailie, Nov 20 '12 at 12:22

score 0 · Answer 1 · answered Nov 20 '12 at 12:19

You're creating a cartesian product of TM entries to (RC/MS) entries. Do you want to be?

I think Sybase supports ANSI 92 SQL syntax, so your SQL is equivalent to

FROM
    theatre_times..theatre_mappings TM,

    theatres..region_codes RC
          inner join title_summaries..movie_summary MS
          on RC.region_code_id = MS.region_code_id
WHERE
    TM.scheme_id = 512

score 0 · Accepted Answer · answered Nov 20 '12 at 12:20

Its producing a Cartesian because you're not joining the theatre_mappings table to the other two.. Without seeing your schema, I can't say for sure, but I suspect you need something like:

SELECT
    TM.type_prefix +
        right('00000000' + convert(varchar, TM.showing_id), 8) +
        right('0000' + convert(varchar, TM.theatre_id), 4) as data_id
FROM
    theatre_times..theatre_mappings TM,
    theatres..region_codes RC,
    title_summaries..movie_summary MS
WHERE
    TM.scheme_id = 512 AND
    TM.region_code_id = RC.region_code_id  -- Extra join
    RC.region_code_id = MS.region_code_id

@orionTurtle - I ***strongly*** recommend you use explicit `JOIN` sysntax to avoid these types of problems. `ANSI-92` has had 20 years of adoption, I think that's long enough... — MatBailie, Nov 20 '12 at 12:42

Where is this SQL statement bottlenecking?

2 Answers2