How to get counts in milliseconds ?
Hello,
I have 50 crores of data with 500 data points (columns), I have to server counts to 1000 subscribers at a time, what I have to do to get counts in milliseconds?
Thanks in advance
0
Best Answer
-
I have 1 crore data in my vertica db::
user_testing=> select count(*) from likes_test; count ---------- 10000001 (1 row) Time: First fetch (1 row): 282.163 ms. All rows formatted: 282.187 ms
The data contains like this
[
{"id":1,
"likes":[401,218,134,941,565,457,864,346,774,921,766,432,815,63,822,202,679,409,457,743,902,199,717,232,172,958,179,808,271,418,145,949,806,399,68,935,423,287,31,365,68,875,347,786,386,792,620,903,136,67,539,196,866,419,390,612,591,881,68,575,226,291,354,380,889,105,412,511,411,861,61,802,388,168,655,296,43,541,824,756,649,2,204,396,231,499,42,773,918,19,229,716,675,762,939,352,35,949,445,787,215,524,998,596,267,444,690,52,762]}
]When i query on likes it takes 29 seconds
user_testing=> select count(id) from likes_test where mapcontainsvalue(likes,'2')='t' or mapcontainsvalue(likes,'3'); count --------- 1967925 (1 row) Time: First fetch (1 row): 29112.269 ms. All rows formatted: 29112.297 ms
Then I decreased interval of AnalyzeRowCount service.
user_testing=> ALTER DATABASE db_testing_0 SET AnalyzeRowCountInterval=0; ALTER DATABASE Time: First fetch (0 rows): 209.445 ms. All rows formatted: 209.456 ms
Again I tried same query it takes 26 seconds
user_testing=> select count(id) from likes_test where mapcontainsvalue(likes,'2')='t' or mapcontainsvalue(likes,'3'); count --------- 1967925 (1 row) Time: First fetch (1 row): 26893.240 ms. All rows formatted: 26893.264 ms
Then I created projection on it but result is same
user_testing=> select count(id) from likes_test_p where mapcontainsvalue(likes,'2')='t' or mapcontainsvalue(likes,'4'); count --------- 1968654 (1 row) Time: First fetch (1 row): 27286.438 ms. All rows formatted: 27286.462 ms user_testing=>
What I have to do to get counts in less time, provide solution
0
Answers
@Jim_Knicely I have to serve** counts to 1000 subscribers at a time
Hi,
What do you mean that you have to provide counts? Do you mean a table row count? Getting a row count is normally pretty quick in Vertica. But maybe you can speed things up a bit with a Live Aggregate Projection?
But our table getting updated continuously, in this case, How can I get counts?
Hi @ashu123!
Try to decrease interval of
AnalyzeRowCount
service.Docs