SQL Server Performance

Need faster query for multiple left join

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by bleach77, Jan 12, 2009.

  1. bleach77 New Member

    Hi,
    I need to call the same table multiple of times and just wondering if there's any faster way to do it.
    Note:
    1. I don't have the authority to change the table.

    2. The example below is something that I just made up but should be enough to recreate my problem.
    Table Person
    id name
    ----------------
    1 John
    2 Mark
    3 Peter
    4 Zack
    *******************************
    Table activity
    id date time desc
    -----------------------------------------------------
    1 1223 0900 registration
    1 1223 1530 lunch
    2 1212 1000 registration
    1 1225 1425 play
    2 1214 0900 classes
    3 1002 0926 registration
    1 1421 1631 stop
    4 1121 1906 registration
    2 1221 1100 play
    4 1231 1345 play
    4 1441 1205 stop
    2 1321 1720 stop
    3 1116 2200 play
    3 1126 2200 stop
    *******************************************
    Current SQL statement:
    SELECT p.name, a1.date AS regdate, a1.time AS regtime, a2.date AS playdate, a2.time AS playtime, a3.date AS stopdate, a3.time AS stoptime
    FROM person p INNER JOIN activity a1 ON p.id = a1.id
    LEFT JOIN ( SELECT * FROM activity WHERE desc = 'timein' ) a2 ON a1.id = a2.id
    LEFT JOIN ( SELECT * FROM activity WHERE desc = 'timeout' ) a3 ON a1.id = a3.id
    WHERE a1.desc = 'registration'

    **********************************
    Basically, this query is just to get the date and time of when did they register, they play and then stop.
    **********************************
    This query run very slow so if any of you guys have a better way on how to make this query, please do help me.


  2. Adriaan New Member

    Does the table have indexes on id and desc, or perhaps a PK or UNIQUE constraint? If not, then even if you cannot alter a table definition, perhaps you can still create an index.
  3. gurucb New Member

    Have you tried using CTE (Common Table Expression) if it is SQL Server 2005.. They may be of bit help here.
  4. bleach77 New Member

    Well, like I said, I don't have any authority to change anything in the database as the database is created and being used by another program. I just want to read the data for my convenience.
    But I end up putting only asterisk in the SELECT statement and I split the query into 2. Now I have a not so slow execution time for both (still a convenience time frame).


    Some interesting thing that I found out.
    1. The execution time is a lot faster if I put an asterisk(*) in SELECT statements rather than the field name.
    2. Execute 2 separated query is much faster than having one query that summarize it all.
    3. I did try execute a query something like this,
    ( SELECT a1.date AS regdate, ...., ( SELECT date FROM activity WHERE id = a1.id AND desc = 'timein'),.... FROM person p, activity a1.................
    and surprisingly it is a lot faster compare to join statement for extracting one field from the a2 table, but the performance deteriorate when I add another SELECT statement for a second field..
    Anyway, Thanks guy for helping out.
  5. Adriaan New Member

    It would probably help more if you specified the exact columns you need within the derived tables. Then you can safely use an * on the outer query.
  6. bleach77 New Member

    I did try that but the time is much faster if I put * on both.
  7. RedDevils New Member

    Can you summarized the output result set so we will get an idea what output you require?
  8. Adriaan New Member

    Another option would be to create a temp table into which you can copy the relevant data from the source table, add any relevant indexes on the temp table, and then query the temp table.
    If the only relevant index would be a primary key, you can probably also use a table variable instead of a temp table.
  9. Marco Salazar New Member

    Hi, I think you could resolve it as:

    Code:
    SELECT p.name, a1.date AS regdate, a1.time AS regtime, a2.date AS playdate, a2.time AS playtime, a3.date AS stopdate, a3.time AS stoptime
    FROM person p INNER JOIN activity a1 ON p.id = a1.id
    LEFT JOIN activity a2 ON (a1.id = a2.id and desc = 'timein')
    LEFT JOIN activity a3 ON (a1.id = a3.id and desc = 'timeout')
    WHERE a1.desc = 'registration'
  10. Luis Martin Moderator

    Marco,
    Welcome to the forums:!
    Thanks for your help.
    Kindly check thread date. This one is 3 years old.:)

Share This Page