CODEWITHSUNDEEP
mysqlcoldfusionquery-timeout
blarg
blarg

Reputation: 3893

How to improve ColdFusion MySQL query time?

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>

Upvotes: 3

Views: 264

Answers (4)

Meherzad
Meherzad

Reputation: 8563

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

Upvotes: 5

Dan Bracuk
Dan Bracuk

Reputation: 20804

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.

Upvotes: 1

Kickstart
Kickstart

Reputation: 21533

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

Upvotes: 2

Pannan Mp
Pannan Mp

Reputation: 77

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

Upvotes: 0

Related Questions