0

I have some code that I am trying to make more efficient.

I am running a query and then looping through those results and running additional queries to drill down into my data even further. The code takes long to process and I'm sure that it is not as efficient as it could be and I believe that doing a query of queries would be more efficient, but I'm am not sure how to exactly implement that.

Here is my current code

My first query:

<!--- Get equipment Query --->    
<cfquery name="get_equipment" datasource="#datasource#">
    select *
    from equipment_maintenance
    where machine_type != 'unifi_site' AND machine_type != 'firewall' AND machine_type != 'dvr' AND machine_type != 'pbx' AND active = 'yes'
    ORDER by #querySortType#
</cfquery>

Then I output my query and run additional queries:

<cfoutput query="get_equipment">
    <!--- Get In-Progress Maintenance History --->
    <cfquery name="get_in_progress_history" datasource="#datasource#">
        select *
        from service_ticket
        where equipment_id=#id#
    </cfquery>

    OUTPUT SOME DATA

    <!--- Update due date in the database for this machine --->  
    <cfquery name="dueDate#id#" datasource="#datasource#">
        update equipment_maintenance
        set maintenance_due_date = #dueDate#
        where id = #id#
    </cfquery>

    OUTPUT SOME DATA

    <cfquery name="get_history" datasource="#datasource#">
        select *
        from equipment_service_history
        where equipment_id = #id#
    </cfquery> 
    <cfquery name="get_history_ticket_detail" datasource="#datasource#">
        select *
        from closed_tickets
        where equipment_id = #id#
        order by ticket_id DESC
    </cfquery>

    OUTPUT SOME DATA

    <cfloop query="get_history_ticket_detail">
        OUTPUT SOME DATA
    </cfloop>
</cfoutput>

There is a lot of HTML code in the middle for outputing my data, but that is the basic structure of my Coldfusion code.

I'm assuming that my multiple queries within my CFOUTPUT tag are what's causing the performance issues correct? How can that be fixed using QoQ?

-Brian

rrk
  • 15,677
  • 4
  • 29
  • 45
Brian Fleishman
  • 1,237
  • 3
  • 21
  • 43
  • 5
    This is more of an SQL situation. You can join the tables and get all the information needed for the loop in a single query, that should be enough. – rrk May 09 '18 at 14:20
  • 2
    You and RRK are both correct. As a general rule, bombarding your database with new connections inside a loop is bad practice, and most of the time can be remedied with one query that makes smart use of joins. Depending on the data you are querying, you may also want to make use of query caching. – TRose May 09 '18 at 14:29
  • 1
    In addition to performance, all those query objects consume RAM, which is a finite resource. – Dan Bracuk May 09 '18 at 14:35
  • At the very lease you should be using ``. Get rid of all the `SELECT *` s too. `` ing inside of a `` makes me nervous – James A Mohler May 09 '18 at 14:46
  • So I should join all those queries into a single query? What if there are duplicate column names between the different tables? – Brian Fleishman May 09 '18 at 14:46
  • If you have duplicates, `select column AS newName` – TRose May 09 '18 at 14:47
  • @JamesAMohler - Yeah, during my testing I use select * and don't paramaterize, jsut to speed things up. – Brian Fleishman May 09 '18 at 14:47
  • 1
    It might speed up development, but it will slow down performance. – James A Mohler May 09 '18 at 14:48
  • So I would make one big query joining all the tables together based on #get_equipment.id# record? But I am looping through my output so that I can show all the data for a given #get_equipment.id# record, so how would I loop through that data if I join everything into one query? – Brian Fleishman May 09 '18 at 15:10
  • The group attribute of the cfoutput tag will help you display your data. – Dan Bracuk May 09 '18 at 19:17
  • How many rows are you working with in your initial query? I'm a fan of letting the SQL server do its job by running a `JOIN`ed query to get as much data as you can in as few passes as possible. You should be able to get all you need in one pass, but if you're dealing with a lot of records and a lot of columns (without `SELECT *`), then you may have to limit how much you can return at one time. It would also help to see a basic structure of how you're trying to build out your results, ie `OUTPUT SOME DATA`. – Shawn May 09 '18 at 21:11
  • And on that note, what version of CF and what flavor/version of SQL? – Shawn May 09 '18 at 21:16

1 Answers1

1

Here is an easy one to do. Change the update statement to

<cfquery datasource="#datasource#">
update equipment_maintenance
set maintenance_due_date = #dueDate#
where machine_type != 'unifi_site' AND machine_type != 'firewall' AND machine_type != 'dvr' AND machine_type != 'pbx' AND active = 'yes'
</cfquery>

There is no need to give it a name because it is not returning any data

James A Mohler
  • 11,060
  • 15
  • 46
  • 72