c# - Redis - Hits count tracking and querying in given datetime range -

i have many different items , want keep track of number of hits each item , query hit count each item in given datetime range, down every second.

so started storing hits in sorted set, 1 sorted set each second (unix epoch time) example :

zincrby itemcount:1346742000 item1 1     zincrby itemcount:1346742000 item2 1 zincrby itemcount:1346742001 item1 1 zincrby itemcount:1346742005 item9 1 

now aggregate hit count each item in given date range :

1. given start datetime , end datetime:    calculate range of epochs fall under range.  2. generate key names each sorted set using epoch values example:    itemcount:1346742001, itemcount:1346742002, itemcount:1346742003  3. use union store aggregate values different sorted sets      zuinionstore _item_count keys....  4. final results out:     zrange _item_count 0, -1 withscores 

so kinda works, run problem when have big date range 1 month, number of key names calculated step 1 & 2 run millions (86400 epoch values per day). such large number of keys, zuinionstore command fails - socket gets broken. plus takes while loop through , generate many keys.

how can design in redis in more efficient way , still keep tracking granularity way down seconds , not minutes or days.

yeah, should avoid big unions of sorted sets. nice trick can do, assuming know maximum hits item can per second.

1. sorted set per item timestamps both scores , values.
2. but scores incremented 1/(max_predicted_hits_per_second), if not first client write them. way number after decimal dot hits/max_predicted_hits_per second, can still range queries.

so let's max_predicted_hits_per_second 1000. (python example):

#1. make sure 1 client adds actual timestamp,  #by doing setnx temporary key)  = int(time.time()) rc = redis.setnx('item_ts:%s' % itemid, now)   #just count part val = float(1)/1000 if rc: #we first incement second    val +=    redis.expire('item_ts:%s' % itemid, 10) #we won't need anymore soon, assuming clients have same clock  #2 increment count redis.zincrby('item_counts:%s' % itemid, now, amount = val)  

and querying range like:

counts = redis.zrangebyscore('item_counts:%s' % itemid, mintime, maxtime + 0.999, withscores=true)  total = 0 value, score in counts:     count = (score - int(value))*1000     total += count