如何在SQLite(w / Python)中优化多个嵌套SELECT?
我正在构建一个CGI脚本,用于轮询SQLite数据库并构建统计表.下面描述源数据库表,以及相关代码的块.一切正常(功能上),但CGI本身非常慢,因为我有多个嵌套的SELECT COUNT(id)调用.我认为我最好的优化是向SO社区提问,因为我与谷歌的时间相对无果而终.
桌子: CREATE TABLE messages ( id TEXT PRIMARY KEY ON CONFLICT REPLACE,date TEXT,hour INTEGER,sender TEXT,size INTEGER,origin TEXT,destination TEXT,relay TEXT,day TEXT); (是的,我知道这个表没有标准化,但它填充了邮件日志中的摘录……我很高兴得到提取和填充工作,更不用说规范化了.我不认为表结构有在这一点上与我的问题有很多关系,但我可能是错的.) 样本行: 476793200A7|Jan 29 06:04:47|6|admin@mydomain.com|4656|web02.mydomain.pvt|user@example.com|mail01.mydomain.pvt|Jan 29 而且,构建我的表的Python代码: #!/usr/bin/python print 'Content-type: text/htmlnn' from datetime import date import re p = re.compile('(w+) (d+)') d_month = {'Jan':1,'Feb':2,'Mar':3,'Apr':4,'May':5,'Jun':6,'Jul':7,'Aug':8,'Sep':9,'Oct':10,'Nov':11,'Dec':12} l_wkday = ['Mo','Tu','We','Th','Fr','Sa','Su'] days = [] curs.execute('SELECT DISTINCT(day) FROM messages ORDER BY day') for day in curs.fetchall(): m = p.match(day[0]).group(1) m = d_month[m] d = p.match(day[0]).group(2) days.append([day[0],"%s (%s)" % (day[0],l_wkday[date.weekday(date(2010,int(m),int(d)))])]) curs.execute('SELECT DISTINCT(sender) FROM messages') senders = curs.fetchall() for sender in senders: curs.execute('SELECT COUNT(id) FROM messages WHERE sender=%s',(sender[0])) print ' <div id="'+sender[0]+'">' print ' <h1>Stats for Sender: '+sender[0]+'</h1>' print ' <table><caption>Total messages in database: %d</caption>' % curs.fetchone()[0] print ' <tr><td> </td><th colspan=24>Hour of Day</th></tr>' print ' <tr><td class="left">Day</td><th>%s</th></tr>' % '</th><th>'.join(map(str,range(24))) for day in days: print ' <tr><td>%s</td>' % day[1] for hour in range(24): sql = 'SELECT COUNT(id) FROM messages WHERE sender="%s" AND day="%s" AND hour="%s"' % (sender[0],day[0],str(hour)) curs.execute(sql) d = curs.fetchone()[0] print ' <td>%s</td>' % (d>0 and str(d) or '') print ' </tr>' print ' </table></div>' print ' </body>n</html>n' 我不确定是否有任何方法可以组合一些查询,或者从不同角度处理它以提取数据.我还想过构建一个带有计数的第二个表,并在原始表更新时更新它.我今天已经盯着这个太长时间了,所以我明天再打开它,希望能得到专家的一些见解;) 编辑:使用下面提供的GROUP BY答案,我能够在一个查询中获取数据库所需的数据.我切换到Perl,因为Python的嵌套dict支持对于我需要处理它的方式(以特定方式构建一组HTML表)的方式不能很好地工作.以下是修订后的代码片段: my %data; my $rows = $db->selectall_arrayref("SELECT COUNT(id),sender,day,hour FROM messages GROUP BY sender,hour ORDER BY sender,hour"); for my $row (@$rows) { my ($ct,$se,$dy,$hr) = @$row; $data{$se}{$dy}{$hr} = $ct; } for my $se (keys %data) { print "Sender: $sen"; for my $dy (keys %{$data{$se}}) { print "Day: ",time2str('%a',str2time("$dy 2010"))," $dyn"; for my $hr (keys %{$data{$se}{$dy}}) { print "Hour: $hr = ".$data{$se}{$dy}{$hr}."n"; } } print "n"; } 曾经在大约28.024秒执行的现在需要0.415秒! 首先,您可以使用group by子句:select count(*),sender from messages group by sender; 通过这种方式,您可以为所有发件人执行一个查询,而不是查询每个发件人.另一种可能性是: select count(*),hour from messages group by sender,hour order by sender,hour; 我没有测试它,但至少现在你知道group by子句的存在.这应该减少查询的数量,我认为这是提高性能的第一个重要步骤. 第二,根据搜索列创建索引,在您的情况下,发件人,日和小时. 如果这还不够,请使用分析工具查找花费最多时间的位置.你还应该考虑使用fetchmany而不是fetchall来保持低内存消耗.请记住,因为sqlite模块是用C编码的,所以尽可能使用它. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |