Skip to main content
R
rockhiker
Inspiring
November 20, 2012
Answered

page load takes too long

  • November 20, 2012
  • 2 replies
  • 2764 views

hi i run the the storedprocedure in SQL  management studio. It executes in less than a second. In my page i query the storeprocedure 5 times.  It takes about 15 seconds for the page to load.  Is there away to speed this up???  Thank you!!

I am a beginnner working on being an intermediate.  The  stored procedure is listed here. I use the same code for each region that i am querying for.

<cfstoredproc procedure="dbo.StatewideFatalCollisionsByRegion" datasource="cfpMeasure">

                        <cfprocresult name="fatals">

                      </cfstoredproc>

                              cfquery name="fatals" dbtype="query">

      Select collisionYear, total as fatal

      FROM fatals

      where region='NW'

     

     

        </cfquery>

I have six regions and I have this code repeated on the page for each region. I am using the result set for charts.

<cfchart font="arial"

      fontbold="yes"

      xaxistitle="Year"

      yaxistitle="## of Fatal"

      title="NW Region Fatal"

      scalefrom="0"

      scaleto="60"

      format="jpg"

      fontsize="14"

      chartwidth="270"

      chartheight="275"

      showborder="yes"

      showlegend="no">

                          <cfchartseries

      type="line"

      seriescolor="##000099"

      query="fatals"

      itemcolumn="collisionYear"

      valuecolumn="fatal">

                        </cfchart>

This topic has been closed for replies.
Correct answer Dan_Bracuk

I ran the sp once and tried to query it six times. I am not sure if this is what you were talking about. See code.  Thank you- jim

cfstoredproc procedure="dbo.StatewideFatalCollisionsByRegion" datasource="cfpMeasure">

                        <cfprocresult name="fatals">

                      </cfstoredproc>

                            

cfquery name="fatals" dbtype="query">

      Select collisionYear, total as fatal

      FROM fatals

      where region='NW'

     

            </cfquery>

<cfquery name="fatals" dbtype="query">

      Select collisionYear, total as fatal

      FROM fatals

      where region='PS'

     

              </cfquery>

I did this query for all the regions but it did not work. I ran the same chart code for all regions. One block is listed below.

<cfflush interval=100>

             <cfchart font="arial"

      fontbold="yes"

      xaxistitle="Year"

      yaxistitle="## of Fatal"

      title="PS Region Fata"

      scalefrom="0"

      scaleto="60"

      format="jpg"

      fontsize="14"

      chartwidth="270"

      chartheight="275"

      showborder="yes"

      showlegend="no">

                          <cfchartseries

      type="line"

     seriescolor="##993333"

      query="fatals"

      itemcolumn="collisionYear"

      valuecolumn="fatal">

                        </cfchart


Your mistake is that your Q of Q name is the same as your procresult name.  That basically overwrites the variable name fatal.

2 replies

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
November 20, 2012

There is a neat way to find out the amount of time taken by the charts. Just comment out all the chart code. Then use getTickCount to time the rest, something like this

<cfset startClock = getTickCount()>

<cfstoredproc procedure="dbo.StatewideFatalCollisionsByRegion" datasource="cfpMeasure">

<cfprocresult name="fatals">

</cfstoredproc>

<cfquery name="fatals" dbtype="query">

Select collisionYear, total as fatal

FROM fatals

where region='NW'

</cfquery>

<cfquery name="fatals" dbtype="query">

Select collisionYear, total as fatal

FROM fatals

where region='SE'

</cfquery>

...

etc., etc.

<cfset stopClock = GetTickCount()>

Time taken by queries: <cfoutput>#(stopClock-startClock)/1000# seconds</cfoutput>

R
rockhikerAuthor
Inspiring
November 20, 2012

That is a useful piece of code: Each of my queries run at 0.109 seconds. So it is the chart rendering that slows everything down?

Thank you - jim

R
rockhikerAuthor
Inspiring
November 20, 2012

some of these queries are taking more than 6 seconds to run. On page with a query for each of 6 charts - it takes a while. Any ideas on how improve peformance?

Thank you - jim

D
Dan_Bracuk
Inspiring
November 20, 2012

The time it takes to load the page is probably due to the fact that the browser has to render 6 charts.  If that's your requirement, a cfflush tag might help.

Miguel-F
Miguel-F
Inspiring
November 20, 2012

If you can enable ColdFusion debugging it will show you how long different portions of the page are taking to execute.  It will also show you how long the queries are taking.  Like Dan said, most likely it is not the queries taking the time but the page rendering.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices