TSQL / PHP Too Slow - Rewrite

Question

I have a table called mainevents, and a table called subevents. For each mainevent there are 3 subevents that I want, which match subname='$sub_name' and eventid. When there are a lot of mainevents, the script is too slow. Dragging slow, 10 seconds to load. When I disable subevents loop, the script loads instantly. I think there might be a shorter/faster/easier way of writing the following. Maybe all in one query. I'm not completely sure.

$a=sqlsrv_query($conn, "SELECT
    eventid,status,name, CONVERT(varchar(100),date,107) AS dt 
    FROM dbo.mainevents WHERE 
    ( date >= '$start_date' AND date <= '$stop_date' ) AND disabled='0'
    ORDER BY category asc");
while($e=sqlsrv_fetch_array($a)){
    $b=sqlsrv_query($conn, "SELECT 
        subid, subname FROM dbo.subevents WHERE
        eventid='$e[eventid]' AND subname='$sub_name' ORDER BY subname");
    while($s=sqlsrv_fetch_array($b)){
        //do stuff
    }
}

score 1 · Answer 1 · answered Mar 16 '13 at 10:35

1

Why not join the two tables in the first query?

Where there are no sub events though you will need to put in an outer join or you will not get the main event.

Let the SQL server's optimiser decide what needs looping.

The SQL would be something like this:

SELECT m.eventid, m.status, m.name, CONVERT(varchar(100),date,107) AS m.dt, s.subid, s.subname 
FROM dbo.mainevents m, dbo.subevents s
WHERE m.eventId *= s.eventId
AND ( m.date >= '$start_date' AND m.date <= '$stop_date' ) 
AND m.disabled='0'
ORDER BY m.category asc, s.subname asc

I think that is the correct syntax for an outer join in T-SQL, but it is a long while since I did any T-SQL work! :)

answered Mar 16 '13 at 10:35

Chris Cooper

4,982
1
17
27

You should use the explicit `LEFT JOIN` (or `LEFT OUTER JOIN`, although the `OUTER` is not required) syntax. – alzaimar Mar 16 '13 at 10:40
Why? There's a perfectly useable shorthand, and personally I have always found the LEFT/RIGHT OUTER JOIN syntax to be overly wordy and confusing to newbies. I would suggest using the `(+)` syntax in an Oracle example for the same reasons. – Chris Cooper Mar 16 '13 at 10:42
It is recommended in the book 'Inside SQL-Server (Soukup, Delaney)'. You should separate joining conditions from filter conditions (`WHERE` clause). Joining tables like that can also lead to errors when joining more than two tables as you don't have control in which order they should be joined. And finally, it is deprecated (in MS SQL-Server). See this article for more: http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins – alzaimar Mar 16 '13 at 10:52
Use `LEFT JOIN dbo.subevents s ON s.eventId = m.eventId` – Bart Mar 16 '13 at 11:24
Used LEFT JOIN, had to re-write my script a bit to display events correctly but increased my loadtime to normal. – user1695981 Mar 16 '13 at 12:44

TSQL / PHP Too Slow - Rewrite

1 Answers1