Commits

tuck  committed 350afc3

bugfix: table route average

  • Participants
  • Parent commits 37e7199
  • Branches 0.2

Comments (0)

Files changed (2)

File pyramid_perfstat/perf_serializer.py

                                                                sql_avg_duration real,
                                                                sql_nb_queries integer,
                                                                date_measure datetime )""")
+
+        c.execute("""create index if not exists idx_url on pyramid_perf (url)""")
+        c.execute("""create index if not exists idx_route on pyramid_perf (route_name)""")
+        c.execute("""create index if not exists idx_view on pyramid_perf (view_name)""")
+
         if from_scratch :
             c.execute("""drop table if exists pyramid_agg_route_perf""")
             
         c.execute("""create table if not exists pyramid_agg_route_perf ( id integer PRIMARY KEY,
                                                                id_measure integer,
-                                                               url text,
                                                                route_name text,
                                                                view_name text,
                                                                measure real,
                                                                sql_nb_queries integer,
                                                                fetch_count integer )""")
 
+        c.execute("""create index if not exists idx_url on pyramid_agg_route_perf (url)""")
+        c.execute("""create index if not exists idx_route on pyramid_agg_route_perf (route_name)""")
+        c.execute("""create index if not exists idx_view on pyramid_agg_route_perf (view_name)""")
+
         if from_scratch :
             c.execute("""drop table if exists pyramid_agg_view_perf""")
             
                                                                sql_avg_duration real,
                                                                sql_nb_queries integer,
                                                                fetch_count integer )""")
+        c.execute("""create index if not exists idx_view on pyramid_agg_view_perf (view_name)""")
+
         self.conn.commit()
         self.end_connection()
 
         :param id_view: an view recorded id
         """
         c = self.conn.cursor()
-        c.execute("""select pr.id, pv.id, pr.measure, pr.sql_avg_duration, pr.sql_nb_queries, pr.url,
+        c.execute("""select pr.id, pv.id, pr.measure, pr.sql_avg_duration, pr.sql_nb_queries,
                             pr.route_name, pr.view_name, pr.fetch_count
                      from pyramid_agg_route_perf pr
                      join pyramid_agg_view_perf pv on pv.id=%d and pr.view_name=pv.view_name
-                     where pr.id_measure=%d order by pr.measure desc, pr.fetch_count desc, pr.url desc"""%(id_view,id_measure))
+                     where pr.id_measure=%d order by pr.measure desc, pr.fetch_count desc"""%(id_view,id_measure))
         rows = c.fetchall()
         c.close()
         return rows 
         :param id_measure: the session id
         """
         c = self.conn.cursor()
-        c.execute("""select id, measure, sql_avg_duration, sql_nb_queries, view_name, fetch_count from pyramid_agg_view_perf 
-                     where id_measure=%d order by measure desc, fetch_count desc, view_name desc"""%id_measure)
+        c.execute("""select id, measure, sql_avg_duration, sql_nb_queries, view_name, fetch_count 
+                     from pyramid_agg_view_perf 
+                     where id_measure=%d
+                     order by measure desc, fetch_count desc, view_name desc"""%id_measure)
         rows = c.fetchall()
         c.close()
         return rows 
         """
         c = self.conn.cursor()
         c.execute("""select measure, sql_avg_duration, sql_nb_queries, fetch_count from pyramid_agg_route_perf 
-                    where id_measure=%d and url='%s'"""%(self.session_perf_id, self.matched_url))
+                    where id_measure=%d and route_name='%s'"""%(self.session_perf_id, self.matched_route_name))
         rows = c.fetchall()
         measure, sql_avg_duration, sql_nb_queries, fetch_count = None, None, None, None
         if rows :
         c = self.conn.cursor()
         if insert :
             c.execute("""insert into pyramid_agg_route_perf values 
-                     (null, ?, ?, ?, ?, ?, ?, ?, 1)""", (self.session_perf_id,
-                                                  self.matched_url,
-                                                  self.matched_route_name,
-                                                  self.view_name,
-                                                  resume_perf,
-                                                  sql_resume_perf,
-                                                  sql_nb_queries))
+                     (null, ?, ?, ?, ?, ?, ?, 1)""", (self.session_perf_id,
+                                                      self.matched_route_name,
+                                                      self.view_name,
+                                                      resume_perf,
+                                                      sql_resume_perf,
+                                                      sql_nb_queries))
         else :
             c.execute("""update pyramid_agg_route_perf
              set measure=%f, sql_avg_duration=%f, sql_nb_queries=%d, fetch_count=fetch_count+1 
-                     where id_measure=%d and url='%s'"""%(resume_perf,
+                     where id_measure=%d and route_name='%s'"""%(resume_perf,
                                                    sql_resume_perf,
                                                    sql_nb_queries,
                                                    self.session_perf_id,
-                                                   self.matched_url))
+                                                   self.matched_route_name))
         c.close()
 
     def update_mean_view(self, request_duration_time, sql_sigma_duration, sql_nb_queries):
         :param sql_nb_queries:
         """
         last_record_resume, last_sql_avg_duration, last_sql_nb_queries, last_cpt = self.get_last_view_mean_perf()
-        new_record_resume = self.cumulative_upkeep(last_record_resume, last_cpt, request_duration_time)
+        new_avg_resume = self.cumulative_upkeep(last_record_resume, last_cpt, request_duration_time)
         new_sql_sigma_duration = 0.0
         new_sql_nb_queries = 0
         
             new_sql_nb_queries = int(self.cumulative_upkeep(last_sql_nb_queries, last_cpt, sql_nb_queries))
         
         if last_record_resume is None :
-            self.update_mean_view_perf_value(new_record_resume, new_sql_sigma_duration, new_sql_nb_queries, insert=True)
+            self.update_mean_view_perf_value(new_avg_resume, new_sql_sigma_duration, new_sql_nb_queries, insert=True)
         else :
-            self.update_mean_view_perf_value(new_record_resume, new_sql_sigma_duration, new_sql_nb_queries)
+            self.update_mean_view_perf_value(new_avg_resume, new_sql_sigma_duration, new_sql_nb_queries)
         
 
-    def update_mean_view_perf_value(self, resume_perf, sql_resume_perf, sql_nb_queries, insert=False):
+    def update_mean_view_perf_value(self, request_time_avg, sql_resume_perf, sql_nb_queries, insert=False):
         """
+           update every recorded params linked to the current view
+           :request_time_avg:
+           :sql_resume_perf:
+           :sql_nb_queries:
+           :insert: boolean
         """
         c = self.conn.cursor()
         if insert :
             c.execute("""insert into pyramid_agg_view_perf values 
                      (null, ?, ?, ?, ?, ?, 1)""", (self.session_perf_id,
                                                    self.view_name,
-                                                   resume_perf,
+                                                   request_time_avg,
                                                    sql_resume_perf,
                                                    sql_nb_queries))
         else :
             c.execute("""update pyramid_agg_view_perf
              set measure=%f, sql_avg_duration=%f, sql_nb_queries=%d, fetch_count=fetch_count+1 
-                     where id_measure=%d and view_name='%s'"""%(resume_perf,
-                                                   sql_resume_perf,
-                                                   sql_nb_queries,
-                                                   self.session_perf_id,
-                                                   self.view_name))         
+                     where id_measure=%d and view_name='%s'"""%(request_time_avg,
+                                                                sql_resume_perf,
+                                                                sql_nb_queries,
+                                                                self.session_perf_id,
+                                                                self.view_name))
         c.close()
     
     def cumulative_upkeep(self, last_resume, last_cpt, perf):
     def get_queries_information(self):
         """
         if sql alchemy is know. we can return sql query sigma time execution
-        return sql_sigma_duration, sql_nb_queries
+        :param return :
+              sql_sigma_duration : sum duration of every sql request executed during the request
+              sql_nb_queries : the number of queries executed in the request
         """
         sql_sigma_duration, sql_nb_queries = None, None
         
         if self.request is not None and has_sqla and hasattr(self.request,'pfstb_duration_queries') :
               duration_queries_liste = self.request.pfstb_duration_queries
+              # sum duration of every sql request executed during the request
               sql_sigma_duration = sum(duration_queries_liste)
+              # the number of queries executed in the request
               sql_nb_queries = len(duration_queries_liste)
 
         return sql_sigma_duration, sql_nb_queries
     def insert_data(self, request_duration_time):
         """
         insert all recorded data in db
-        
         :param request_duration_time: the request time duration
         """
         
              c = self.conn.cursor()
              c.execute("""insert into pyramid_perf values 
                           (null, ?, ?, ?, ?, ?, ?, ?, ?)""", (self.session_perf_id,
-                                                        self.matched_url,
-                                                        self.matched_route_name,
-                                                        self.view_name,
-                                                        request_duration_time,
-                                                        sql_sigma_duration,
-                                                        sql_nb_queries,
-                                                        datetime.datetime.now(),))
+                                                             self.matched_url,
+                                                             self.matched_route_name,
+                                                             self.view_name,
+                                                             request_duration_time,
+                                                             sql_sigma_duration,
+                                                             sql_nb_queries,
+                                                             datetime.datetime.now(),))
      
              # update specific view table
              self.update_mean_view(request_duration_time, sql_sigma_duration, sql_nb_queries)

File pyramid_perfstat/templates/reporting.mako

 	</tr>
 </%def>
 
-<%def name="publish_agg_routes_row(id, id_view, request_avg_time, sql_avg_time, sql_nb_queries, url, route_name, view_name, cpt)">
+<%def name="publish_agg_routes_row(id, id_view, request_avg_time, sql_avg_time, sql_nb_queries, route_name, view_name, cpt)">
 	<tr>
 		<td>${id}</td>
 		<td>${get_color_perf(request_avg_time)}</td>
 		<td>-</td>
             % endif
         <td>${sql_nb_queries}</td>
-		<td>${url}</td>
 		<td>
 				  <a href="${request.route_url("pyramid_perfstat.reporting.url_detail", id_session=id_session, id_view=id_view, id_route=id)}">${route_name}</a>
 				</td>
 }
 
 #perf_views_reporting_table  tbody td:nth-child(7),
-#perf_routes_reporting_table tbody td:nth-child(9) {
+#perf_routes_reporting_table tbody td:nth-child(8) {
 	width: 5%;
 	min-width: 50px;
 }
 		
 	</head>
 	<body>
-		<select onchange="javascript: goTo('${request.route_url(route_name='pyramid_perfstat.reporting.session_detail',id_session='')}'+$(this).val());">
+		<select onchange="javascript:goTo('${request.route_url(route_name='pyramid_perfstat.reporting.session_detail',id_session='')}'+$(this).val());">
 			% for id, dt_label, nb_rows in lst_ids_measures_date :
 				<option value="${id}" ${"selected='selected'" if id==id_session else ''}>SESSION-${id}: ${dt_label} nb of records : ${nb_rows}</option>
 			% endfor
 					<th>category</th>
 					<th><b>sql avg</b><br/>&#160;time&#160;(ms)</th>
 					<th>sql queries count</th>
-					<th>requested url</th>
 					<th>matched route name</th>
 					<th>matched view name</th>
 					<th>url count</th>
 				</tr>
 			</thead>
 			<tbody>
-				% for (id, id_view, request_avg_time, sql_avg_time, sql_nb_queries, url, route_name, view_name, cpt) in lst_agg_routes_measures :
-					${publish_agg_routes_row(id, id_view, request_avg_time, sql_avg_time, sql_nb_queries, url, route_name, view_name, cpt)}
+				% for (id, id_view, request_avg_time, sql_avg_time, sql_nb_queries, route_name, view_name, cpt) in lst_agg_routes_measures :
+					${publish_agg_routes_row(id, id_view, request_avg_time, sql_avg_time, sql_nb_queries, route_name, view_name, cpt)}
 				% endfor
 			</tbody>
 		</table>
 		function goTo(page)
 		{
 			window.location.assign(page);
-		}	
+		}
+
 		$(document).ready(function() { 
 			$("#perf_views_reporting_table").tablesorter({}); 
 			
 			% endif
 		});
 	</script>
-	
 </html>