How to improve ColdFusion MySQL query time?

Question

I have 15 queries that generate data for a table on a page dynamically for the purpose of reports. Each query takes between 250 and 900ms which means a page loading time of 4 to 13 seconds depending on server load. The loading time is causing some users to the think the page is not going to load at all.

I was wondering if there was some way I could streamline the queries to give a more acceptable loading time. Here is one of the queries:

             <cfquery datasource="MeetingDB"  name="One">
              SELECT COUNT( meetingID ) AS countatron
              FROM case_meeting
              WHERE meetingID
              IN (

              SELECT DISTINCT a.meetingID
              FROM case_meeting a
              INNER JOIN meeting b ON a.meetingID = b.meetingID
              WHERE b.categoryID =  '1' 
              AND SUBSTRING( meetingCode, 5, 2 ) 
              BETWEEN 12 
              AND 22 
              AND SUBSTRING( meetingCode, 7, 2 ) 
              BETWEEN 01 
              AND 12 
              AND SUBSTRING( meetingCode, 9, 2 ) 
              BETWEEN 01 
              AND 31
              )
              AND caseID
              IN (
              '1',  '2',  '3', '28',  '29',  '30', '39', '40', '45'
              )
              GROUP BY meetingID
              HAVING COUNT( caseID ) > 0 AND COUNT( caseID ) < 2
              </cfquery>

              <td><cfoutput> #One.recordcount# </cfoutput></td>

Meherzad · Accepted Answer · 2013-02-25T17:25:18.480

5

Try this query

SELECT COUNT( a.meetingID ) AS countatron
          FROM case_meeting a, case_meeting b
          WHERE a.meetingID = b.meetingID 
          AND b.categoryID =  '1' 
          AND SUBSTRING( b.meetingCode, 5, 2 ) 
          BETWEEN 12 
          AND 22 
          AND SUBSTRING( b.meetingCode, 7, 2 ) 
          BETWEEN 01 
          AND 12 
          AND SUBSTRING( b.meetingCode, 9, 2 ) 
          BETWEEN 01 
          AND 31
          AND b.caseID
          IN (
          '1',  '2',  '3', '28',  '29',  '30', '39', '40', '45'
          )
          GROUP BY a.meetingID
          HAVING COUNT( a.caseID ) = 1

edited Feb 25 '13 at 17:25

answered Feb 25 '13 at 09:39

Meherzad

8,433
1
30
40

Hi, it's throwing up errors. There is only one table called case_meeting – blarg Feb 25 '13 at 09:45
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AND b.caseID IN ('1', '2', '3', '28', '29', '30', '3' at line 14 – blarg Feb 25 '13 at 09:50
Excellent! This now loads in 46ms! Thank you. – blarg Feb 25 '13 at 09:58
Why is your query so much quicker? – blarg Feb 25 '13 at 10:02
1

The only change which I have made is removing the subquery from you original query and create a self join. Avoid using subquery as much as possible only use it as last option. The subquery in your query was taking so much time for executing. http://stackoverflow.com/questions/2577174/join-vs-subquery – Meherzad Feb 25 '13 at 10:07
Meherzad, you should put that explanation in the answer itself. – Peter Boughton Feb 25 '13 at 14:22
Also, why do you have `HAVING COUNT( a.caseID ) > 0 AND COUNT( a.caseID ) < 2`? Can't you just make that `HAVING COUNT(a.caseID) = 1`? – Shawn Feb 25 '13 at 17:23
Shawn, this query counts users that have only 1 meeting. I want to list the count of users that have 1,2,3,4 or 5 meetings. However, the query would read a user who has two meetings as also having one meeting causing confusion of statistics. Specifying explicitly like this fixes that. – blarg Feb 25 '13 at 23:28

Kickstart · Answer 2 · 2013-02-25T09:57:29.003

Might be worth trying to do joins on subselects rather than using IN.

Something like this:-

SELECT COUNT( case_meeting.meetingID ) AS countatron
FROM case_meeting
INNER JOIN (
SELECT DISTINCT a.meetingID
FROM case_meeting a
INNER JOIN meeting b ON a.meetingID = b.meetingID
WHERE b.categoryID =  '1' 
AND SUBSTRING( meetingCode, 5, 2 ) BETWEEN 12 AND 22 
AND SUBSTRING( meetingCode, 7, 2 ) BETWEEN 01 AND 12 
AND SUBSTRING( meetingCode, 9, 2 ) BETWEEN 01 AND 31
) Sub1
ON case_meeting.meetingID = Sub1.meetingID
INNER JOIN (
SELECT meetingID, COUNT( caseID ) AS MeetingCaseCount
FROM case_meeting
WHERE  caseID IN ('1',  '2',  '3', '28',  '29',  '30', '39', '40', '45')
GROUP BY meetingID
) Sub2
ON case_meeting.meetingID = Sub2.meetingID
WHERE  Sub2.MeetingCaseCount > 0 AND Sub2.MeetingCaseCount < 2
GROUP BY case_meeting.meetingID

Hi the 'meetingID' mentioned twice in this query are ambiguous. — blarg, Feb 25 '13 at 09:49

score 1 · Answer 3 · answered Feb 25 '13 at 13:10

If you have a lot of queries that don't depend on each other, then take a look at cfthread. This will allow you to run the queries concurrently.

Make sure you test it thoroughly. I've had one experience where the use of cfthread had adverse effects on a database server.

It's still worth a shot though.

score 0 · Answer 4 · answered Feb 25 '13 at 09:39

0

i suppose creating a procedure in the MySql and invoking it with arguments from my sql is most appropriate. you could also create views with dynamic param

answered Feb 25 '13 at 09:39

Pannan Mp

77
10

How to improve ColdFusion MySQL query time?

4 Answers4