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

New Here ,
Apr 12, 2017 Apr 12, 2017

Copy link to clipboard

Copied

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>

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

Views

767

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct Answer

LEGEND , Apr 12, 2017 Apr 12, 2017
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,^_^

Likes

Translate

Translate
LEGEND ,
Apr 12, 2017 Apr 12, 2017

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 12, 2017 Apr 12, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 12, 2017 Apr 12, 2017

Copy link to clipboard

Copied

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,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Apr 13, 2017 Apr 13, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Apr 13, 2017 Apr 13, 2017

Copy link to clipboard

Copied

LATEST

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

V/r,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines