Need faster query for multiple left join | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Need faster query for multiple left join

I need to call the same table multiple of times and just wondering if there’s any faster way to do it.
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, AS regdate, a1.time AS regtime, AS playdate, a2.time AS playtime, AS stopdate, a3.time AS stoptime
FROM person p INNER JOIN activity a1 ON =
LEFT JOIN ( SELECT * FROM activity WHERE desc = ‘timein’ ) a2 ON =
LEFT JOIN ( SELECT * FROM activity WHERE desc = ‘timeout’ ) a3 ON =
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.

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.

Have you tried using CTE (Common Table Expression) if it is SQL Server 2005.. They may be of bit help here.
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 AS regdate, …., ( SELECT date FROM activity WHERE 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.

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.

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

Can you summarized the output result set so we will get an idea what output you require?

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.

Hi, I think you could resolve it as:
SELECT, AS regdate, a1.time AS regtime, AS playdate, a2.time AS playtime, AS stopdate, a3.time AS stoptime
FROM person p INNER JOIN activity a1 ON =
LEFT JOIN activity a2 ON ( = and desc = 'timein')
LEFT JOIN activity a3 ON ( = and desc = 'timeout')
WHERE a1.desc = 'registration'

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

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |