I've got a report that has a fairly lengthy query, because in the report it displays the last record for a variety of document types in a separate table, which I've used views to whittle down queries.
If I turn the report's portable syntax SQL definition into native MySQL syntax and execute the query, it takes about 3-4 seconds, a long time in computer time but really reasonable considering all the joins. Note that it uses arguments in the WHERE clause and it uses the A5 portable SQL syntax "FIRST 1" (read: MySQL "LIMIT 1", MS SQL "TOP 1"). The latter is a significant piece because of the way the views had to be designed--they each return multiple records for the given individual but are ordered by date descending, so the LIMIT 1 is used to bring back just the top record of each view, i.e. the most recent.
I may be giving too much detail here, but the point is that the query works in a few seconds' time. However, this exact query with the columns in the select turned into a COUNT(*) hangs up MySQL, in a significant, I-have-to-manually-kill-the-thread-because-no-other-users'-queries-will-then-execute kind of way. I found this out because Alpha apparently inserts COUNT(*) queries prior to the SELECT query you actually designed, presumably for a good under-the-hood reason. But it's unacceptable in this scenario.
Does anyone know how to get around it?
If I turn the report's portable syntax SQL definition into native MySQL syntax and execute the query, it takes about 3-4 seconds, a long time in computer time but really reasonable considering all the joins. Note that it uses arguments in the WHERE clause and it uses the A5 portable SQL syntax "FIRST 1" (read: MySQL "LIMIT 1", MS SQL "TOP 1"). The latter is a significant piece because of the way the views had to be designed--they each return multiple records for the given individual but are ordered by date descending, so the LIMIT 1 is used to bring back just the top record of each view, i.e. the most recent.
I may be giving too much detail here, but the point is that the query works in a few seconds' time. However, this exact query with the columns in the select turned into a COUNT(*) hangs up MySQL, in a significant, I-have-to-manually-kill-the-thread-because-no-other-users'-queries-will-then-execute kind of way. I found this out because Alpha apparently inserts COUNT(*) queries prior to the SELECT query you actually designed, presumably for a good under-the-hood reason. But it's unacceptable in this scenario.
Does anyone know how to get around it?
Comment