在 DBI 包中使用系結變數時，我面臨性能問題。我最初的用例是使用 Postgres 資料庫，但為了可重復性，下面我使用記憶體中的 SQLite，它具有完全相同的問題 - 當我通過 id 從某個表中選擇資料時（在 Postgres 中，列被索引）引數化版本運行多次選擇行數比在IN陳述句中粘貼 ID 的 SQL 更長：
library(DBI)
library(tictoc)
sample.data <- data.frame(
id = 1:100000,
value = rnorm(100000)
)
sqlite <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(
sqlite, "sample_data",
sample.data,
overwrite = T
)
tic("Load by bind")
ids <- 50000:50100
res <- dbSendQuery(sqlite, "SELECT * FROM sample_data WHERE id = $1")
dbBind(res, list(ids))
result <- dbFetch(res)
dbClearResult(res)
toc()
# Load by bind: 0.81 sec elapsed
tic("Load by paste")
ids <- 50000:50100
result2 <- dbGetQuery(sqlite, paste0("SELECT * FROM sample_data WHERE id IN (", paste(ids, collapse = ","), ")"))
toc()
# Load by paste: 0.04 sec elapsed
似乎我應該有一些明顯的錯誤，因為準備好的查詢應該更快（我確實在同一個 Postgres 示例中使用 Python/SQLAlchemy 看到了它）。
uj5u.com熱心網友回復：
你的第一個查詢... id = $1被執行了 101 次；您的第二個查詢... id in (..)執行一次。如果您在 DBMS 端進行審計（此處不進行演示），那么您將看到 101 個單獨的查詢。
在前面，一個常見的錯誤是簡化修改陳述句以使用IN (?)子句，
dbGetQuery(pgcon, "SELECT * FROM sample_data WHERE id in (?)", params = list(ids))
但這也執行了 101 次查詢，感覺與result1.
要將引數系結與更有效的IN (..)子句一起使用，您需要提供那么多問號（或美元數字）。
bench::mark(
result1 = dbGetQuery(sqlite, "SELECT * FROM sample_data WHERE id = $1", params = list(ids)),
result2 = dbGetQuery(sqlite, paste0("SELECT * FROM sample_data WHERE id IN (", idcommas, ")")),
result3 = dbGetQuery(sqlite, paste0("SELECT * FROM sample_data WHERE id IN (", qmarks, ")"),
params = as.list(ids)),
min_iterations = 50
)
# # A tibble: 3 x 13
# expression min median `itr/sec` mem_alloc `gc/sec` n_itr n_gc total_time result memory time gc
# <bch:expr> <bch:tm> <bch:tm> <dbl> <bch:byt> <dbl> <int> <dbl> <bch:tm> <list> <list> <list> <list>
# 1 result1 280.97ms 347.4ms 2.86 20.6KB 0 50 0 17.5s <df[,2] [101 x 2]> <Rprofmem[,3] [14 x 3]> <bch:tm [50]> <tibble [50 x 3]>
# 2 result2 7.31ms 8.21ms 115. 15.6KB 0 58 0 502.2ms <df[,2] [101 x 2]> <Rprofmem[,3] [12 x 3]> <bch:tm [58]> <tibble [58 x 3]>
# 3 result3 7.57ms 8.93ms 113. 28.4KB 0 57 0 506ms <df[,2] [101 x 2]> <Rprofmem[,3] [28 x 3]> <bch:tm [57]> <tibble [57 x 3]>
如果您好奇，它在 postgres 實體上執行相同（明顯更快）（盡管我將您更改$1為?：sqlite 接受兩者，odbc/postgres 僅支持 qmarks）：
pgcon <- dbConnect(odbc::odbc(), ...) # local docker postgres instance
bench::mark(...)
# # A tibble: 3 x 13
# expression min median `itr/sec` mem_alloc `gc/sec` n_itr n_gc total_time result memory time gc
# <bch:expr> <bch:tm> <bch:tm> <dbl> <bch:byt> <dbl> <int> <dbl> <bch:tm> <list> <list> <list> <list>
# 1 result1 967.4ms 1.05s 0.933 20.6KB 0 50 0 53.57s <df[,2] [101 x 2]> <Rprofmem[,3] [14 x 3]> <bch:tm [50]> <tibble [50 x 3]>
# 2 result2 57ms 67.7ms 14.4 18.1KB 0 50 0 3.47s <df[,2] [101 x 2]> <Rprofmem[,3] [13 x 3]> <bch:tm [50]> <tibble [50 x 3]>
# 3 result3 56.9ms 65.17ms 14.3 21.4KB 0 50 0 3.5s <df[,2] [101 x 2]> <Rprofmem[,3] [15 x 3]> <bch:tm [50]> <tibble [50 x 3]>
我還在 odbc/sql-server 上進行了測驗，結果非常相似。
result2并且result3通常在所有三個 DBMS 上都非常接近，實際上在不同的采樣中，前者比后者快，因此我將它們的性能比較稱為清洗。那么，使用系結的動機是什么？在許多情況下，這主要是學術討論：大多數時候，不使用它（而是使用您的paste(ids, collapse=",")方法）并沒有做錯任何事。
然而：
無意中的“sql 注入”。從技術上講，SQL 注入必須是惡意的才能被標記為這樣，但我非正式地將 SQL 查詢中的“哎呀”時刻歸因于資料嵌入了引號，并將其粘貼到靜態查詢字串中，我破壞了參考。對我來說幸運的是，它所做的只是破壞了查詢的決議，我沒有洗掉今年的學生記錄。
一個常見的錯誤是嘗試使用
sQuote轉義嵌入引號。長話短說：不，SQL 的做法不同。許多 SQL 用戶不知道要轉義嵌入的單引號，必須將其加倍：sQuote("he's Irish") # [1] "'he's Irish'" # WRONG DBI::dbQuoteString(sqlite, "he's Irish") # <SQL> 'he''s Irish' # RIGHT for both sqlite and pgcon查詢優化。大多數（全部？我不確定）DBMS 會進行某種形式的查詢優化，試圖利用索引和/或類似措施。為了擅長它，這種優化對查詢進行一次，然后快取。但是，即使您更改查詢的一個字母，也可能會導致快取未命中（我不是說“總是”，因為我還沒有審核快取代碼……但我認為前提很清楚）。這意味著，從更改查詢
select * from mytable where a=1到... a=2并沒有得到快取命中，并進行了優化（再次）。對比，與
select * from mytable where a=?同引數結合，并從快取中受益。請注意，如果您的串列
ids更改長度，那么查詢很可能會被重新優化（從 更改id in (?,?)為id in (?,?,?)）；如果這真的是快取未命中，我不知道，同樣沒有審核 DBMSes 代碼。
順便說一句：您提到的“準備好的陳述句”與此查詢優化非常一致，但是您遇到的性能損失更多是關于運行相同查詢 101 次，而不是與快取命中/未命中有關的任何事情。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/390449.html
標籤：r PostgreSQL的 sqlite 分贝
上一篇：如何在條形圖中查看類別的名稱？