// API callback
related_results_labels_thumbs({"version":"1.0","encoding":"UTF-8","feed":{"xmlns":"http://www.w3.org/2005/Atom","xmlns$openSearch":"http://a9.com/-/spec/opensearchrss/1.0/","xmlns$blogger":"http://schemas.google.com/blogger/2008","xmlns$georss":"http://www.georss.org/georss","xmlns$gd":"http://schemas.google.com/g/2005","xmlns$thr":"http://purl.org/syndication/thread/1.0","id":{"$t":"tag:blogger.com,1999:blog-7905389674158671872"},"updated":{"$t":"2025-09-18T07:02:56.138+08:00"},"category":[{"term":"Programming"},{"term":"Culture"},{"term":"Testing"},{"term":"scrum"},{"term":"Agile"},{"term":"performance tuning"},{"term":"Book"},{"term":"Code Quality"},{"term":"Qt"},{"term":"Event Storming"},{"term":"database"},{"term":"file system"},{"term":"virtual machine"}],"title":{"type":"text","$t":"VIVOTEK Digest"},"subtitle":{"type":"html","$t":"Journey of Software Development"},"link":[{"rel":"http://schemas.google.com/g/2005#feed","type":"application/atom+xml","href":"https:\/\/blog.rd.vivotek.com\/feeds\/posts\/default"},{"rel":"self","type":"application/atom+xml","href":"https:\/\/www.blogger.com\/feeds\/7905389674158671872\/posts\/default\/-\/database?alt=json-in-script\u0026max-results=8"},{"rel":"alternate","type":"text/html","href":"https:\/\/blog.rd.vivotek.com\/search\/label\/database"},{"rel":"hub","href":"http://pubsubhubbub.appspot.com/"}],"author":[{"name":{"$t":"diro"},"uri":{"$t":"http:\/\/www.blogger.com\/profile\/13292247339970268280"},"email":{"$t":"noreply@blogger.com"},"gd$image":{"rel":"http://schemas.google.com/g/2005#thumbnail","width":"16","height":"16","src":"https:\/\/img1.blogblog.com\/img\/b16-rounded.gif"}}],"generator":{"version":"7.00","uri":"http://www.blogger.com","$t":"Blogger"},"openSearch$totalResults":{"$t":"1"},"openSearch$startIndex":{"$t":"1"},"openSearch$itemsPerPage":{"$t":"8"},"entry":[{"id":{"$t":"tag:blogger.com,1999:blog-7905389674158671872.post-6912934947286812828"},"published":{"$t":"2019-04-22T11:15:00.000+08:00"},"updated":{"$t":"2019-04-29T17:09:57.493+08:00"},"category":[{"scheme":"http://www.blogger.com/atom/ns#","term":"database"},{"scheme":"http://www.blogger.com/atom/ns#","term":"performance tuning"}],"title":{"type":"text","$t":"DataMagnet Performance tuning"},"content":{"type":"html","$t":"\u003Cdiv style=\"color: #172b4d; font-size: 14px; padding: 0px;\"\u003E\n\u003Cdiv class=\"separator\" style=\"clear: both; text-align: center;\"\u003E\n\u003Ca href=\"https:\/\/blogger.googleusercontent.com\/img\/b\/R29vZ2xl\/AVvXsEjvDzp5wDbmPKzAsyd-CnzKKE7PQBE7ueCGckq62QjZeR2TFVdaiwIAq6MV2OPhzvi-iGzvl2mpXauVcVzqC5nfp5BQNJuMfnA0c5XCJ8ZWIg42kO2KMsj4VT7PcSG1XrPWarrZk2a6ZUfB\/s1600\/car-1574029_1920.jpg\" imageanchor=\"1\" style=\"background-color: #eeeeee; margin-left: 1em; margin-right: 1em;\"\u003E\u003Cimg border=\"0\" data-original-height=\"900\" data-original-width=\"1600\" height=\"225\" src=\"https:\/\/blogger.googleusercontent.com\/img\/b\/R29vZ2xl\/AVvXsEjvDzp5wDbmPKzAsyd-CnzKKE7PQBE7ueCGckq62QjZeR2TFVdaiwIAq6MV2OPhzvi-iGzvl2mpXauVcVzqC5nfp5BQNJuMfnA0c5XCJ8ZWIg42kO2KMsj4VT7PcSG1XrPWarrZk2a6ZUfB\/s400\/car-1574029_1920.jpg\" width=\"400\" \/\u003E\u003C\/a\u003E\u003C\/div\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E\u003Cbr \/\u003E\u003C\/span\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E\u003Cbr \/\u003E\u003C\/span\u003E\n\u003Cbr \/\u003E\n\u003Cblockquote class=\"tr_bq\"\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003EDataMagnet 是讓客戶能夠透過 HTTP POST 傳送 JSON data 並儲存各種資訊到我們的錄影軟體 VAST2, 再透過 VAST2 來搜尋內容的功能\u0026nbsp;\u003C\/span\u003E\u003C\/blockquote\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E客戶用我們的 data magnet 功能, 一天下來會有好幾萬筆的資料，\u003C\/span\u003E\u003Cspan style=\"background-color: #eeeeee;\"\u003E而且內容是夾帶著 image 的 data (base64 format)\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cdiv class=\"separator\" style=\"clear: both; text-align: center;\"\u003E\n\u003Ca href=\"https:\/\/blogger.googleusercontent.com\/img\/b\/R29vZ2xl\/AVvXsEjLSyzXBrFy_2-t9v5XHph9Rx1PxukG6x46QDNhnPOLm-SR6ENqhvQiywPAyvkTu_hNlqLaK0YV9Kq3KOKSaEUXMcN_B9IJIbYYr3FJuTQRWh3ksF3h1ia-s7IDuBD3WMb3EVIPYmPhVIWg\/s1600\/1.png\" imageanchor=\"1\" style=\"clear: left; float: left; margin-bottom: 1em; margin-right: 1em;\"\u003E\u003Cimg border=\"0\" data-original-height=\"173\" data-original-width=\"1269\" src=\"https:\/\/blogger.googleusercontent.com\/img\/b\/R29vZ2xl\/AVvXsEjLSyzXBrFy_2-t9v5XHph9Rx1PxukG6x46QDNhnPOLm-SR6ENqhvQiywPAyvkTu_hNlqLaK0YV9Kq3KOKSaEUXMcN_B9IJIbYYr3FJuTQRWh3ksF3h1ia-s7IDuBD3WMb3EVIPYmPhVIWg\/s1600\/1.png\" \/\u003E\u003C\/a\u003E\u003C\/div\u003E\n\u003Cspan class=\"confluence-embedded-file-wrapper confluence-embedded-manual-size\" style=\"background-color: #eeeeee; display: inline-block; max-width: none; position: relative;\"\u003E\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E而如果這樣的 data 有幾百萬 or 幾千萬筆的資料在 db 裏頭，\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E雖然我們在 UI 有限制結果的筆數 (50 results\/page)，也不會針對 snapshot column 去搜尋\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E但若搜尋條件的範圍一大，例如： 1s 一筆資料，那麼半年範圍就會有高達一千五百萬筆資料，造成\u003C\/span\u003E\u003Cspan style=\"background-color: #eeeeee;\"\u003E搜尋的速度很慢。\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E而且這樣的 database size 也是很驚人的，以上面的欄位來看 (include image data: 128px*128px) ，15 million 筆數的資料就高達 60G 左右的大小。\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E\u003Cbr \/\u003E\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E所以中間我們就找了幾種作法:\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E1. Fast text search:\u0026nbsp;\u003Ca class=\"external-link\" href=\"https:\/\/blog.kapeli.com\/sqlite-fts-contains-and-suffix-matches\" rel=\"nofollow\" style=\"color: #0052cc; text-decoration-line: none;\"\u003Ehttps:\/\/blog.kapeli.com\/sqlite-fts-contains-and-suffix-matches\u003C\/a\u003E\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E這做法有幾個流程要改:\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E首先 Create Table 的方式不一樣了\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E還有 SELECT 找 string 的方式也不一樣 (match)\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E也因為我們要 search 的文字是要 match prefix, middle, postfix\u0026nbsp;\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E原本 fts 只有 prefix mapping, 而上面的 link 跟我們說可以透過 uncompress string 的方式來做到搜尋中間的字串, 但這有幾個缺點:\u003C\/span\u003E\u003C\/div\u003E\n\u003Col style=\"color: #172b4d; font-size: 14px; margin: 10px 0px 0px;\"\u003E\n\u003Cli\u003E\u003Cspan style=\"background-color: #eeeeee;\"\u003E每個 column 個都要多一個 column 來儲存 uncompress 的 string (除了不用搜尋的 snapshot \u0026amp; timestamp column), 但這會增加整個 database 的 size\u003C\/span\u003E\u003C\/li\u003E\n\u003Cli\u003E\u003Cspan style=\"background-color: #eeeeee;\"\u003Eor 要增加 compress, uncompress function 在 Create Table 時加入\u0026nbsp;\u003Ca class=\"external-link\" href=\"https:\/\/www.sqlite.org\/fts3.html#the_compress_and_uncompress_options\" rel=\"nofollow\" style=\"color: #0052cc; text-decoration-line: none;\"\u003Ehttps:\/\/www.sqlite.org\/fts3.html#the_compress_and_uncompress_options\u003C\/a\u003E\u0026nbsp;, 但這要直接使用 SQLite 的 API function, 對我們使用 Qt 包一層的 API 不好實作\u003C\/span\u003E\u003C\/li\u003E\n\u003C\/ol\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cdiv class=\"separator\" style=\"clear: both; text-align: center;\"\u003E\n\u003Ca href=\"https:\/\/blogger.googleusercontent.com\/img\/b\/R29vZ2xl\/AVvXsEi32GRpas3DR0AZyxuGJfjrDuMEhfx0803xIP7FSgtumVeRs6nj9bHFkjvKkgaLb5VmS8rpZsYxxojP-RJmAeSVxbynuGPu6dAo1Ni5ZRFH-teetLPAyjqiIFHPzzdfIc5muyKBmjlCYIkP\/s1600\/2.png\" imageanchor=\"1\" style=\"clear: left; float: left; margin-bottom: 1em; margin-right: 1em;\"\u003E\u003Cimg border=\"0\" data-original-height=\"197\" data-original-width=\"1347\" src=\"https:\/\/blogger.googleusercontent.com\/img\/b\/R29vZ2xl\/AVvXsEi32GRpas3DR0AZyxuGJfjrDuMEhfx0803xIP7FSgtumVeRs6nj9bHFkjvKkgaLb5VmS8rpZsYxxojP-RJmAeSVxbynuGPu6dAo1Ni5ZRFH-teetLPAyjqiIFHPzzdfIc5muyKBmjlCYIkP\/s1600\/2.png\" \/\u003E\u003C\/a\u003E\u003C\/div\u003E\n\u003Cspan class=\"confluence-embedded-file-wrapper confluence-embedded-manual-size\" style=\"background-color: #eeeeee; display: inline-block; max-width: none; position: relative;\"\u003E\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E重要的是, 我們改了一版用 fts 的 table 來搜尋, 並沒有特別的好....\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E\u003Cbr \/\u003E\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E2. 後來回頭想原本的瓶頸在哪, 拆解一下傳進去的內容...\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E看到 snapshot 的 column 其實佔了 db 很大的容量\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E若少了 snapshot 這欄位, 原本的 search 數度就會很快 (5~6 個月的搜尋範圍, 搜尋某個字串, 1s 左右)\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E所以我們就把 snapshot 的 context 想辦法不要放進去 db 裡面\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cspan class=\"confluence-embedded-file-wrapper confluence-embedded-manual-size\" style=\"background-color: #eeeeee; display: inline-block; max-width: none; position: relative;\"\u003E\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cdiv class=\"separator\" style=\"clear: both; text-align: center;\"\u003E\n\u003Ca href=\"https:\/\/blogger.googleusercontent.com\/img\/b\/R29vZ2xl\/AVvXsEiahTfDg_aAM_sYIyclAi70oxGtAOUqynLajMgO_ZnOTYyPSVLgALkHu07NBqVaJnvQCEfnsMs_E-BIWUhvGTlDltVmmPr-uBlpyCqAeyc5YDfUOGctk5AEzuIi0EWjYjk2UkRoXBCeLHHq\/s1600\/3.png\" imageanchor=\"1\" style=\"clear: left; float: left; margin-bottom: 1em; margin-right: 1em;\"\u003E\u003Cimg border=\"0\" data-original-height=\"215\" data-original-width=\"1072\" src=\"https:\/\/blogger.googleusercontent.com\/img\/b\/R29vZ2xl\/AVvXsEiahTfDg_aAM_sYIyclAi70oxGtAOUqynLajMgO_ZnOTYyPSVLgALkHu07NBqVaJnvQCEfnsMs_E-BIWUhvGTlDltVmmPr-uBlpyCqAeyc5YDfUOGctk5AEzuIi0EWjYjk2UkRoXBCeLHHq\/s1600\/3.png\" \/\u003E\u003C\/a\u003E\u003C\/div\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E改成存 snapshot 的路徑, 而將 user 傳過得來的 snapshot data 存成 binary 的路徑\u003C\/span\u003E\u003C\/div\u003E\n\u003Cdiv style=\"color: #172b4d; font-size: 14px; margin-top: 10px; padding: 0px;\"\u003E\n\u003Cspan style=\"background-color: #eeeeee;\"\u003E這樣的好處就是搜尋速度變快好幾倍。\u003C\/span\u003E\u003C\/div\u003E\n"},"link":[{"rel":"replies","type":"application/atom+xml","href":"https:\/\/blog.rd.vivotek.com\/feeds\/6912934947286812828\/comments\/default","title":"Post Comments"},{"rel":"replies","type":"text/html","href":"https:\/\/blog.rd.vivotek.com\/2019\/04\/datamagnet-performance-tuning.html#comment-form","title":"0 Comments"},{"rel":"edit","type":"application/atom+xml","href":"https:\/\/www.blogger.com\/feeds\/7905389674158671872\/posts\/default\/6912934947286812828"},{"rel":"self","type":"application/atom+xml","href":"https:\/\/www.blogger.com\/feeds\/7905389674158671872\/posts\/default\/6912934947286812828"},{"rel":"alternate","type":"text/html","href":"https:\/\/blog.rd.vivotek.com\/2019\/04\/datamagnet-performance-tuning.html","title":"DataMagnet Performance tuning"}],"author":[{"name":{"$t":"Jack"},"uri":{"$t":"http:\/\/www.blogger.com\/profile\/07395665566315722323"},"email":{"$t":"noreply@blogger.com"},"gd$image":{"rel":"http://schemas.google.com/g/2005#thumbnail","width":"16","height":"16","src":"https:\/\/img1.blogblog.com\/img\/b16-rounded.gif"}}],"media$thumbnail":{"xmlns$media":"http://search.yahoo.com/mrss/","url":"https:\/\/blogger.googleusercontent.com\/img\/b\/R29vZ2xl\/AVvXsEjvDzp5wDbmPKzAsyd-CnzKKE7PQBE7ueCGckq62QjZeR2TFVdaiwIAq6MV2OPhzvi-iGzvl2mpXauVcVzqC5nfp5BQNJuMfnA0c5XCJ8ZWIg42kO2KMsj4VT7PcSG1XrPWarrZk2a6ZUfB\/s72-c\/car-1574029_1920.jpg","height":"72","width":"72"},"thr$total":{"$t":"0"}}]}});