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!

Answer 1

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