This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
i don't know the first thing about jdbc.next but ... it's probably creating a map per record, that's with redundant information and maybe processing per row. You might want to use your own data structure, something more packed, batching allocations, and maybe provide your own map protocols implementations over that
@thiru0130 I would ask: why are you doing that? Why do you want 13 million rows in memory?
(and, yes, that's going to take a good long while to load)
If you use reducible-query
(or plan
in next.jdbc
), you can build data structures that avoid converting the ResultSet
data to hash maps as an intermediate step -- but you're still going to be dealing with a massive data structure in memory...
@seancorfield Yes that is a good question. Maybe it's not a good idea. I want to keep these records in memory on the backend in order to provide fast auto-complete suggestions to the client. It's a website.. so as a user types in characters in a search field I want sub-second responses of names (of videos).
I tried hitting the database (SQL Server) but it seems too slow. Maybe the table isn't optimzed well but all the three columns (unique integer id, video name and created date) are indexed with FTS
with next.jdbc are the rs functions meant to be used in conjunction with plan? I'm attempting to do a join and dump those results into a hash-set
@thiru0130 i doubt you can do better than a database in the full test search department in any reasonable amount of dev time, either your indexes are wrong, or your queries...also if you want the whole data set in memory, configure your database to have enough memory to cache the whole index in it
I know I mentioned FTS but actually for the purposes of auto-complete I would only need to determine if the string the user entered starts with any clip names. So I suppose FTS wouldn't help in that case
i don't know about mssql specifically, but prefix search should be covered by your index and be real fast even with a very large index
@theeternalpulse The default is that you don't need anything except the top-level next.jdbc
namespace.
In particular, with plan
, if you reduce
or whatever, the functions you use can avoid creating a row altogether if they just request simple named fields (based on the label for each column).
If you use execute!
or execute-one!
, you'll get fully-realized result sets back, built with rs/as-maps
by default. But you can use any other builders, including the ones in next.jdbc.optional
(which omit columns that have a null
value).
ah, I guess I'm just having a problem with my join statement
Based on what you've said, I'd expect this to be something close to what you need
(into #{} (map :col) (plan ds ["select ..." ...]))
(defn key-selector [row]
(select-keys row [:api_key/name]))
(def query ["select api_key.name from api_key join response"
"on api_key.id = response.api_id"])
(->> (jdbc/plan ds query)
(into #{} (map key-selector)))
You could just use :name
instead of key-selector
.
I eventually want multiple keys, I'm just trying a bare minimum statement
it's saying that nothing is returned, but the statement works fine in the psql command line
You have two strings in there. You need to call str
to make one string.
(def query [(str "select api_key.name from api_key join response"
"on api_key.id = response.api_id")])
I'm a bit surprised you didn't get an error about trying to set parameters that aren't needed.
Oh, you'll need a space in one of those strings
ok that worked
Otherwise you'll get responseon
totally missed that, I thougt I saw it without the string in th edocs
I was looking at the wrong ones I guess, I saw this
(jdbc/execute-one! ds ["INSERT INTO address (name,email) VALUES (?,?)"
"A.Person" ""] {:return-keys true})
but I see those are for params
I'll probably take the docs advice and look into honey-sql if it gets a bit more involved