How to speed up of output json object format to feed into the cfhttp

[henryc62208508]

The following codes take more then 12 hours to run for only more than 300,000 records in the table. How do I speed up the process? Is there another way to generate the json object and feed into the cfhttp in a faster manner? Thanks.

----------------------------------------------------

      <cfquery name="getData" datasource="#request.dsn#">

          SELECT     District, Route, DateTime, Site, TicketNumber, Tons, CommodityCode, GeneralCommodity, DateCreated

                FROM         tSolidResourcesTonnages

      </cfquery>

      <cfif getData.recordcount gt 0>

          <cfloop query="getData">

                    <cfset variables.objJson = variables.objJson & "{'district': '#HTMLEditFormat(getData.District)#'," &

                 "'route': '#HTMLEditFormat(getData.Route)#'," &

                 "'datetime': '#HTMLEditFormat(getData.Datetime)#'," &

                 "'site': '#HTMLEditFormat(getData.Site)#', " &

                 "'ticketnumber': '#HTMLEditFormat(getData.TicketNumber)#'," &

                 "'tons': #HTMLEditFormat(getData.Tons)#," &

                 "'commoditycode': '#HTMLEditFormat(getData.CommodityCode)#'," &

                 "'generalcommodity': '#HTMLEditFormat(getData.GeneralCommodity)#'" &                

                "},">

        </cfloop>

        <cfset variables.objJson = #mid(variables.objJson,1,len(variables.objJson)-1)# & "]">       

        <!---Note: method: put is to replace, post is to append--->

        <cfhttp url="#request.SolidResourcesTonnages_ApiUrl#" method="put" result="uploadResponse" timeout="30000" proxyServer="#request.proxyServer#" proxyport="#request.proxyPort#">

          <cfhttpparam type="header" name="Authorization" value="Basic #ToBase64(request.myCredential)#" />

          <cfhttpparam type="header" name="X-App-Token" value="#request.appToken#">

          <cfhttpparam type="header" name="Content-Type" value="application/json; charset=utf-8">

          <cfhttpparam type="body" value="#variables.objJson#">

        </cfhttp>

      </cfif>

--------------------------------------------------------------------------------

[WolfShade · Accepted solution]

I don't know what is taking 12 hours to run - creating the JSON object, or processing it once it's passed to the webservice.

But instead of manually looping through a query to build the JSON, I believe that you can directly convert the query into a JSON object using SerializeJSON().  Depending upon what options are selected, the webservice (assuming it's ColdFusion) can directly deserialize the JSON object back into a query object.

HTH,

^_^

[henryc62208508]

I can use SerializeJSON(getData, "struct") to output into json object, but ColdFusion does not preserve the key casing, it always uppercase the key. Is there a way to preserve the key case? Thanks.

[WolfShade]

It depends upon what version of CF you're running.

CF11 (and presumably CF2016) "Added Application.cfc setting this.serialization.preserveCaseForStructKey which defaults to true, also added useCustomSerializer argument."

CF10 and earlier will automatically push keys to upper case.

HTH,

^_^

[henryc62208508]

Thanks. I just need to use the Replace function to find and replace all the keys with the proper case. You're right. It took only a few minutes to generate the json object instead of couple hours to generate the json in the manual custom format.

[WolfShade]

Glad you got it to speed up the process!  And thank you for marking my answer correct.  I do appreciate it.

V/r,

^_^