On 2/07/2009 11:00 AM, yaconsult wrote: > > Most of the queries I've done so far have been pretty straightforward > and it's worked very well. But, now I need to do one that's taking > too long. There's probably a better way than the one I'm using. > > The problem is that I need to produce information for the timeslots > that are missing as well as those that are there. > > For example, I need to be able to evaluate the effect of different > load balancers, so I want to compare the numbers of transactions on > all the ports. I need to do so on a second by second basis so I can > see the most detail and not an average. > > I thought one way do do this would be with a self-join because > there are so many transactions that I'm pretty sure that all seconds > will be present in the table. I also considered creating a second > table derived from the first whenever it's updated that would have a > unix epoch entry for each second within the log file.
Consider filling in zeroes for missing timeslots in the script that's processing the results of a simple only-non-missing-timeslots query: Assuming the results come out in time order: I dunno about perl, but in Python I'd read the results in a generator which maintained a one-line history and yielded missing or non-missing slot data as appropriate. You can get the same effect without generators, it's just a bit awkward/ugly. If results not in order, either USE ORDER BY or for a daily report prepopulate an array with 24*60*60 slots containing zero, fill in with your query results, process the array. HTH, John _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
