coldfusion - CFChart labels disappear -
i'm running following query , after running qoq secondconn , not getting desired output in cfchart.
<!--- qoq firstconn ---> <!--- master query ---> <cfquery datasource = "xx.xx.x.xx" name="master1"> select str_to_date(date_format(timedetail,'%m-%d-%y'),'%m-%d-%y') firstconn , count(timedetail) firstoccurances , events mydatabase events = "first" group firstconn ; </cfquery> <!--- detail query ---> <!--- <cfdump var = "#master#"> ---> <cfquery dbtype="query" name="detail1"> select * master1 firstconn >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_varchar"> , firstconn < <cfqueryparam value="#dateadd('d', 1,form.enddate)#" cfsqltype="cf_sql_varchar">; </cfquery> <!--- qoq secondconn ---> <!--- master query ---> <cfquery datasource = "xx.xx.x.xx" name="master2"> select str_to_date(date_format(timedetail,'%m-%d-%y'),'%m-%d-%y') secondconn , count(timedetail) secondoccurances , events mydatabase events = "second" group secondconn ; </cfquery> <!--- detail query ---> <!--- <cfdump var = "#master#"> ---> <cfquery dbtype="query" name="detail2"> select * master2 secondconn >= <cfqueryparam value="#form.startdate#" cfsqltype="cf_sql_varchar"> , secondconn < <cfqueryparam value="#dateadd('d', 1,form.enddate)#" cfsqltype="cf_sql_varchar">; </cfquery> <cfchart format="flash" chartwidth="1000" chartheight="500" scalefrom="0" scaleto="50000" xaxistitle="dates" yaxistitle="number of connections"> <cfchartseries query="detail1" type="line" itemcolumn="firstconn" valuecolumn="firstoccurances" > <cfchartseries query="detail2" type="line" itemcolumn="secondconn" valuecolumn="secondoccurances" > </cfchartseries> </cfchart> on x axis, chart displaying correct startdate stops displaying dates after middle point somewhere , rest of line chart displayed without dates mentioned on x-axis. reason? dates displaying when ran 1 query, qoq firstconn.
attached shows output 1 query , qoq firstconn
both , showing undesirable output.
another point noticed when run query secondconn, see output without dates mentioned on x-axis. th reason? have attached 
below reference.there no change in query.
the cfchart code i'm using follows:
<cfchart format="flash" chartwidth="1000" chartheight="500" scalefrom="0" scaleto="50000" xaxistitle="date" yaxistitle="number of connections" showlegend = "yes" showmarkers = "yes" sortxaxis= "yes" tipstyle="mousedown" > <cfchartseries query="detail2" type="line" itemcolumn="secondconn " valuecolumn="secondoccurances" > </cfchartseries> </cfchart> the cfdump of second query follows:
query resultset query secondconn secondoccurances events 1 {ts '2013-06-24 00:00:00'} 556 second 2 {ts '2013-06-25 00:00:00'} 2710 second 3 {ts '2013-06-26 00:00:00'} 2854 second 4 {ts '2013-06-27 00:00:00'} 6348 second 5 {ts '2013-06-28 00:00:00'} 4285 second 6 {ts '2013-06-29 00:00:00'} 2843 second 7 {ts '2013-06-30 00:00:00'} 875 second 8 {ts '2013-07-01 00:00:00'} 4033 second 9 {ts '2013-07-02 00:00:00'} 3211 second 10 {ts '2013-07-03 00:00:00'} 2882 second 11 {ts '2013-07-04 00:00:00'} 978 second 12 {ts '2013-07-05 00:00:00'} 1727 second 13 {ts '2013-07-06 00:00:00'} 811 second 14 {ts '2013-07-07 00:00:00'} 522 second 15 {ts '2013-07-08 00:00:00'} 2556 second 16 {ts '2013-07-09 00:00:00'} 1160 second 17 {ts '2013-07-10 00:00:00'} 8580 second 18 {ts '2013-07-11 00:00:00'} 2630 second 19 {ts '2013-07-16 00:00:00'} 12 second please let me know if can answer more questions.
without seeing dump of query data, guess charting engine may have decided there many values reasonably display on x-axis. (i tested code , works fine cf9). try smaller date range , see if date labels reappear. if so, may need customize chart settings better fit. here of pertinent style settings.
ishideoverlapped- hide/show labels overlapskiplabels- adjust display everynlabels instead of allorientation- label orientation (horizontal or vertical)
test code
<cfset detail1 = querynew("")> <cfset queryaddcolumn(detail1, "firstconn", "date", listtoarray("2013-07-31,2013-08-15,2013-08-17"))> <cfset queryaddcolumn(detail1, "firstoccurances", listtoarray("3,5,6"))> <cfset detail2 = querynew("")> <cfset queryaddcolumn(detail2, "secondconn", "date", listtoarray("2013-08-10,2013-08-18,2013-08-20"))> <cfset queryaddcolumn(detail2, "secondoccurances", listtoarray("4,10,8"))> <cfchart format="flash" xaxistitle="dates" yaxistitle="number of connections"> <cfchartseries query="detail1" type="line" itemcolumn="firstconn" valuecolumn="firstoccurances" /> <cfchartseries query="detail2" type="line" itemcolumn="secondconn" valuecolumn="secondoccurances" /> </cfchart> qoq faster , that's why i'm using it.
honestly, not approach handling slow running queries. best place optimize db query in database. take time examine query plan , find bottleneck, add appropriate indexes, etcetera. using qoq's incur lot of network o/h pulling lot of data discarded. not mention, memory required build new resultset. lot of wasted resources , not scale well.
(that said, believe have opened separate thread regarding query speed. rather mixing questions again, leave conversation other thread.)
Comments
Post a Comment