create account

STEEMSQL - Update 10 - New features and performances by arcange

View this thread on: hive.blogpeakd.comecency.com
· @arcange · (edited)
$105.62
STEEMSQL - Update 10 - New features and performances
<center>https://i.imgsafe.org/77e5fc4a12.png</center>

### <center>Enjoy new features and performance improvement for your queries.</center>
<br>
SteemSQL is a public MS-SQL database with all the blockchain data in it.

##### Previous posts:
* [Introduction post](https://steemit.com/steemit/@arcange/steemsql-com-a-public-sql-server-database-with-all-steemit-blockchain-data)
* [SteemSQL  - Update 1](https://steemit.com/steemit/@arcange/steemsql-a-public-sql-database-with-all-blockchain-data-update-1)
* [SteemSQL  - Update 2 - Language  detection](https://steemit.com/steemit/@arcange/steemsql-a-public-sql-database-with-all-blockchain-data-new-language-detection)
* [SteemSQL - Update 3](https://steemit.com/steemit/@arcange/steemsql-a-public-sql-database-with-all-blockchain-data-update3)
* [SteemSQL  - Update 4](https://steemit.com/steemsql/@arcange/steemsql-a-public-sql-database-with-all-blockchain-data-update-2)
* [SteemSQL  - Update 5](https://steemit.com/steemsql/@arcange/steemsql-a-public-sql-database-with-all-blockchain-data-update-5)
* [SteemSQL  - Update 6](https://steemit.com/steemsql/@arcange/steemsql-a-public-sql-database-with-all-blockchain-data-update-6)
* [SteemSQL  - Update 7](https://steemit.com/steemsql/@arcange/steemsql-a-public-sql-database-with-all-blockchain-data-update-7)
* [SteemSQL  - Update 8](https://steemit.com/steemsql/@arcange/steemsql-a-public-sql-database-with-all-blockchain-data-update-8)
* [SteemSQL  - Update 9](https://steemit.com/steemsql/@arcange/steemsql-update-9-performances-improvement)

# What’s new?

Many people use SteemSQL to get information about followers or posts that have been resteemed.

The problem is that these operations are not associated with a dedicated transaction, but are stored in the blockchain using `custom_json` transactions. In the SteemSQL database, You will find these transaction in the the `TxCustoms` table.

Storing the details of a follow or reblog operation in a JSON string is pretty annoying because this requires deserializing the content of the JSON string before you can analyze its contents. 
Even if SQL server has JSON functions to perform such operation, queries are quite slow as the server has to process every row before being able to filter data. Unfortunately, it is not possible to create an index on values contained in the JSON string.

Searching for followers or resteemed posts is all but efficient, especially when you know that the `TxCustoms` table contains almost 14 million records.

But that's not all, the format of the JSON string for 'follow' operations has changed over time. This has to be taken into account when parsing the data and writing an efficient query quickly becomes cumbersome.

Long story made short, it is really a mess when you want to analyze followers or reblogs!

Fortunately, SteemSQL comes to the rescue with new features.

### The Followers and the Reblogs views

Two new views have been created that will allow faster and easier analysis of followers and resteemed posts

#### 1. The Followers view

This is a very simple view, which contains only 2 columns:

* `follower` : the name of the follower
* `following` : the name of the followed user

The Followers view is not a transaction view, meaning it does not expose historical data and does not allow to know when a user start or stop to follow another account.

Knowing how many followers I have is now as simple as issuing a query like
```
SELECT COUNT(*) FROM Followers (NOLOCK) WHERE following = 'arcange'
```

#### 2. The Reblogs view

This view is also very simple and contains the following columns:

* `account` : the user who resteemed a post
* `author` : the author of the resteemed post
* `permlink` : the permlink of resteemed post
* `timestamp` : when the post has been resteemed

Knowing how many of my posts have been resteemed is now as simple as issuing a query like

```
SELECT COUNT(DISTINCT permlink) FROM Reblogs (NOLOCK) WHERE author = 'arcange'
```

### Future performance improvements

As more and more are using SteemSQL, I plan to continue my work on improving SteemSQL performances. 

However, I will first take some well deserved vacation. 
In the meantime, do not hesitate to send me your suggestions or comments. contact me in the dedicated SteemSQL channel on [steemit.chat](https://steemit.chat/channel/steemsql) or via Telegram (@VIM_Arcange)

Thanks for reading.

---

##### <center>All payout from this post will be dedicated to keep SteemSQL running. </center>

##### <center>Thanks for your support.</center>

---
<center>[![](http://i.cubeupload.com/HbDjmH.png)](http://steemitboard.com/@arcange)</center>
###### <center>_footer created with **[steemitboard](steemitboard.com)** - click any award to see my board of honor_</center>

### <center>Support me and my work as a witness by voting for me  [here!](https://steemit.com/~witnesses)</center>
<center>http://i.cubeupload.com/dlkwkk.png</center>
You Like this post, do not forget to **upvote** or **[follow me](https://steemit.com/@arcange)** or **resteem**
πŸ‘  , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , and 44 others
properties (23)
authorarcange
permlinksteemsql-update-10
categorysteemsql
json_metadata{"tags":["steemsql","steemit","database","sql","programming"],"image":["https://i.imgsafe.org/77e5fc4a12.png","http://i.cubeupload.com/HbDjmH.png","http://i.cubeupload.com/dlkwkk.png"],"links":["https://steemit.com/steemit/@arcange/steemsql-com-a-public-sql-server-database-with-all-steemit-blockchain-data","https://steemit.com/steemit/@arcange/steemsql-a-public-sql-database-with-all-blockchain-data-update-1","https://steemit.com/steemit/@arcange/steemsql-a-public-sql-database-with-all-blockchain-data-new-language-detection","https://steemit.com/steemit/@arcange/steemsql-a-public-sql-database-with-all-blockchain-data-update3","https://steemit.com/steemsql/@arcange/steemsql-a-public-sql-database-with-all-blockchain-data-update-2","https://steemit.com/steemsql/@arcange/steemsql-a-public-sql-database-with-all-blockchain-data-update-5","https://steemit.com/steemsql/@arcange/steemsql-a-public-sql-database-with-all-blockchain-data-update-6","https://steemit.com/steemsql/@arcange/steemsql-a-public-sql-database-with-all-blockchain-data-update-7","https://steemit.com/steemsql/@arcange/steemsql-a-public-sql-database-with-all-blockchain-data-update-8","https://steemit.com/steemsql/@arcange/steemsql-update-9-performances-improvement","https://steemit.chat/channel/steemsql","http://steemitboard.com/@arcange","steemitboard.com","https://steemit.com/~witnesses","https://steemit.com/@arcange"],"app":"steemit/0.1","format":"markdown"}
created2017-08-07 18:07:33
last_update2017-08-07 18:08:27
depth0
children18
last_payout2017-08-14 18:07:33
cashout_time1969-12-31 23:59:59
total_payout_value82.948 HBD
curator_payout_value22.672 HBD
pending_payout_value0.000 HBD
promoted1.000 HBD
body_length5,050
author_reputation1,146,633,668,945,473
root_title"STEEMSQL - Update 10 - New features and performances"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,083,645
net_rshares28,519,425,808,414
author_curate_reward""
vote details (108)
@amanjain2k ·
$0.05
thats nice work.
πŸ‘  , ,
properties (23)
authoramanjain2k
permlinkre-arcange-steemsql-update-10-20170807t194614127z
categorysteemsql
json_metadata{"tags":["steemsql"],"app":"steemit/0.1"}
created2017-08-07 19:46:15
last_update2017-08-07 19:46:15
depth1
children0
last_payout2017-08-14 19:46:15
cashout_time1969-12-31 23:59:59
total_payout_value0.048 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length16
author_reputation3,335,258,446,767
root_title"STEEMSQL - Update 10 - New features and performances"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,092,180
net_rshares13,193,731,286
author_curate_reward""
vote details (3)
@evolved08gsr ·
Do you have the logic you used to generate the follow/following logic? I was about to start working on that code and am curious if you did the same method I was going to use or if you used another method.

Thanks!
πŸ‘  
properties (23)
authorevolved08gsr
permlinkre-arcange-steemsql-update-10-20170807t181200940z
categorysteemsql
json_metadata{"tags":["steemsql"],"app":"steemit/0.1"}
created2017-08-07 18:12:00
last_update2017-08-07 18:12:00
depth1
children0
last_payout2017-08-14 18:12:00
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length213
author_reputation4,411,583,250,238
root_title"STEEMSQL - Update 10 - New features and performances"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,084,020
net_rshares0
author_curate_reward""
vote details (1)
@farukcom ·
you are correct about some people use SteemSQL to get information about followers or posts that have been resteemed. as we know There is a  problem  these operations are not associated with a dedicated transaction . Congratulation & 
 ![thank-you-.jpg](https://steemitimages.com/DQmZPwSR3PAs2roRszbfr4oceGDjWUhyrLjwKRF3wQqirf3/thank-you-.jpg)
πŸ‘  
properties (23)
authorfarukcom
permlinkre-arcange-steemsql-update-10-20171108t130144512z
categorysteemsql
json_metadata{"tags":["steemsql"],"image":["https://steemitimages.com/DQmZPwSR3PAs2roRszbfr4oceGDjWUhyrLjwKRF3wQqirf3/thank-you-.jpg"],"app":"steemit/0.1"}
created2017-11-08 13:01:48
last_update2017-11-08 13:01:48
depth1
children0
last_payout2017-11-15 13:01:48
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length342
author_reputation32,897,128,085,525
root_title"STEEMSQL - Update 10 - New features and performances"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id19,779,486
net_rshares0
author_curate_reward""
vote details (1)
@fauzanlexa ·
Mantap that lagenyan
πŸ‘  
properties (23)
authorfauzanlexa
permlinkre-arcange-steemsql-update-10-20180609t173803126z
categorysteemsql
json_metadata{"tags":["steemsql"],"app":"steemit/0.1"}
created2018-06-09 17:38:06
last_update2018-06-09 17:38:06
depth1
children0
last_payout2018-06-16 17:38:06
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length20
author_reputation277,045,670
root_title"STEEMSQL - Update 10 - New features and performances"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id60,024,208
net_rshares64,020,625
author_curate_reward""
vote details (1)
@gomeravibz ·
Hi there @arcange, tell me something is it me or have you shifted your choice of colour for the steemit reward plaques from bllue to green ??
properties (22)
authorgomeravibz
permlinkre-arcange-steemsql-update-10-20171103t233536261z
categorysteemsql
json_metadata{"tags":["steemsql"],"users":["arcange"],"app":"steemit/0.1"}
created2017-11-03 23:35:36
last_update2017-11-03 23:35:36
depth1
children0
last_payout2017-11-10 23:35:36
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length141
author_reputation53,218,725,520,811
root_title"STEEMSQL - Update 10 - New features and performances"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id19,391,072
net_rshares0
@greenacrehome ·
As a SteemSQL user, thank you again for this and keeping with updating us on it, too!
πŸ‘  
properties (23)
authorgreenacrehome
permlinkre-arcange-steemsql-update-10-20170807t183331916z
categorysteemsql
json_metadata{"tags":["steemsql"],"app":"steemit/0.1"}
created2017-08-07 18:33:30
last_update2017-08-07 18:33:30
depth1
children0
last_payout2017-08-14 18:33:30
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length85
author_reputation5,550,811,725,482
root_title"STEEMSQL - Update 10 - New features and performances"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,085,876
net_rshares0
author_curate_reward""
vote details (1)
@horlly ·
A nice and cool post ,a good writer of a good articles that cool more of these from you ,...you can also comment ,upvote and follow my posts @horlly..
πŸ‘  , ,
properties (23)
authorhorlly
permlinkre-arcange-steemsql-update-10-20171220t162335656z
categorysteemsql
json_metadata{"tags":["steemsql"],"users":["horlly"],"app":"steemit/0.1"}
created2017-12-20 16:24:15
last_update2017-12-20 16:24:15
depth1
children0
last_payout2017-12-27 16:24:15
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length150
author_reputation1,105,467,232
root_title"STEEMSQL - Update 10 - New features and performances"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id24,375,479
net_rshares145,063,781
author_curate_reward""
vote details (3)
@hottipsbd ·
nice post     I follow you, please follow me. I'll upvote to all of your posts and hope you will
properties (22)
authorhottipsbd
permlinkre-arcange-steemsql-update-10-20170813t091920814z
categorysteemsql
json_metadata{"tags":["steemsql"],"app":"steemit/0.1"}
created2017-08-13 09:19:24
last_update2017-08-13 09:19:24
depth1
children0
last_payout2017-08-20 09:19:24
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length96
author_reputation2,829,054,498
root_title"STEEMSQL - Update 10 - New features and performances"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,672,310
net_rshares0
@liudasbutkus ·
Is there a PHPmyadmin GUI available?
πŸ‘  
properties (23)
authorliudasbutkus
permlinkre-arcange-steemsql-update-10-20170807t183648499z
categorysteemsql
json_metadata{"tags":["steemsql"],"app":"steemit/0.1"}
created2017-08-07 18:36:54
last_update2017-08-07 18:36:54
depth1
children0
last_payout2017-08-14 18:36:54
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length36
author_reputation1,093,333,067,528
root_title"STEEMSQL - Update 10 - New features and performances"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,086,171
net_rshares0
author_curate_reward""
vote details (1)
@muhajirnyakcut ·
Bereh postingan lage Nyoe
I like
properties (22)
authormuhajirnyakcut
permlinkre-arcange-steemsql-update-10-20170807t185226530z
categorysteemsql
json_metadata{"tags":["steemsql"],"app":"steemit/0.1"}
created2017-08-07 18:52:36
last_update2017-08-07 18:52:36
depth1
children0
last_payout2017-08-14 18:52:36
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length32
author_reputation193,360,099,051
root_title"STEEMSQL - Update 10 - New features and performances"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,087,520
net_rshares0
@paulag ·
brill, thank you.  I have been able to work with the json using powerbi but this make things a lot simpler for me now
properties (22)
authorpaulag
permlinkre-arcange-steemsql-update-10-20170807t212628033z
categorysteemsql
json_metadata{"tags":["steemsql"],"app":"steemit/0.1"}
created2017-08-07 21:26:27
last_update2017-08-07 21:26:27
depth1
children0
last_payout2017-08-14 21:26:27
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length117
author_reputation274,264,287,951,003
root_title"STEEMSQL - Update 10 - New features and performances"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,102,916
net_rshares0
@rindi118 ·
Great post, thanks for sharing, regards
πŸ‘  
properties (23)
authorrindi118
permlinkre-arcange-steemsql-update-10-20170807t194535288z
categorysteemsql
json_metadata{"tags":["steemsql"],"app":"steemit/0.1"}
created2017-08-07 19:45:42
last_update2017-08-07 19:45:42
depth1
children0
last_payout2017-08-14 19:45:42
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length39
author_reputation393,894,096,212
root_title"STEEMSQL - Update 10 - New features and performances"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,092,127
net_rshares0
author_curate_reward""
vote details (1)
@teamhumble ·
cool, you make some bad ass stuff! :)
πŸ‘  ,
properties (23)
authorteamhumble
permlinkre-arcange-steemsql-update-10-20170807t181752303z
categorysteemsql
json_metadata{"tags":["steemsql"],"app":"steemit/0.1"}
created2017-08-07 18:17:51
last_update2017-08-07 18:17:51
depth1
children1
last_payout2017-08-14 18:17:51
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length37
author_reputation315,232,864,758,316
root_title"STEEMSQL - Update 10 - New features and performances"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,084,515
net_rshares0
author_curate_reward""
vote details (2)
@arcange ·
Thanks
πŸ‘  ,
properties (23)
authorarcange
permlinkre-teamhumble-re-arcange-steemsql-update-10-20170807t211530896z
categorysteemsql
json_metadata{"tags":["steemsql"],"app":"steemit/0.1"}
created2017-08-07 21:15:33
last_update2017-08-07 21:15:33
depth2
children0
last_payout2017-08-14 21:15:33
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length6
author_reputation1,146,633,668,945,473
root_title"STEEMSQL - Update 10 - New features and performances"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,102,092
net_rshares0
author_curate_reward""
vote details (2)
@techybear · (edited)
$0.06
Thanks for this Database. 
Great work! Resteemed and voted.
πŸ‘  , , ,
properties (23)
authortechybear
permlinkre-arcange-steemsql-update-10-20170807t183753324z
categorysteemsql
json_metadata{"tags":["steemsql"],"app":"steemit/0.1"}
created2017-08-07 18:37:54
last_update2017-08-07 20:19:54
depth1
children1
last_payout2017-08-14 18:37:54
cashout_time1969-12-31 23:59:59
total_payout_value0.050 HBD
curator_payout_value0.014 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length59
author_reputation12,922,993,911
root_title"STEEMSQL - Update 10 - New features and performances"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,086,259
net_rshares17,703,243,010
author_curate_reward""
vote details (4)
@arcange ·
Thank you  ; )
πŸ‘  
properties (23)
authorarcange
permlinkre-techybear-re-arcange-steemsql-update-10-20170807t211453202z
categorysteemsql
json_metadata{"tags":["steemsql"],"app":"steemit/0.1"}
created2017-08-07 21:14:54
last_update2017-08-07 21:14:54
depth2
children0
last_payout2017-08-14 21:14:54
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length14
author_reputation1,146,633,668,945,473
root_title"STEEMSQL - Update 10 - New features and performances"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,102,045
net_rshares0
author_curate_reward""
vote details (1)
@yogi ·
$0.25
Cool! I'm learning SQL just to be able to make some stats :)  Needless to say I'm very happy with your SteemSQL.
πŸ‘  ,
properties (23)
authoryogi
permlinkre-arcange-steemsql-update-10-20170807t191252738z
categorysteemsql
json_metadata{"tags":["steemsql"],"app":"steemit/0.1"}
created2017-08-07 19:12:51
last_update2017-08-07 19:12:51
depth1
children1
last_payout2017-08-14 19:12:51
cashout_time1969-12-31 23:59:59
total_payout_value0.184 HBD
curator_payout_value0.061 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length112
author_reputation24,631,816,177,191
root_title"STEEMSQL - Update 10 - New features and performances"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id11,089,341
net_rshares66,839,736,146
author_curate_reward""
vote details (2)
@horlly ·
What a nice,cool and interesting post,i will like to see more of this you can also follow ,comment and upvote @horlly
πŸ‘  ,
properties (23)
authorhorlly
permlinkre-yogi-re-arcange-steemsql-update-10-20171220t162053629z
categorysteemsql
json_metadata{"tags":["steemsql"],"users":["horlly"],"app":"steemit/0.1"}
created2017-12-20 16:21:03
last_update2017-12-20 16:21:03
depth2
children0
last_payout2017-12-27 16:21:03
cashout_time1969-12-31 23:59:59
total_payout_value0.000 HBD
curator_payout_value0.000 HBD
pending_payout_value0.000 HBD
promoted0.000 HBD
body_length117
author_reputation1,105,467,232
root_title"STEEMSQL - Update 10 - New features and performances"
beneficiaries[]
max_accepted_payout1,000,000.000 HBD
percent_hbd10,000
post_id24,375,030
net_rshares156,668,884
author_curate_reward""
vote details (2)