Fork me on GitHub
#sql
<
2019-07-07
>
carkh00:07:06

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

carkh00:07:08

clojure is nice and all, but it's allocation happy, in many small steps

carkh00:07:53

also test and measure what java alone can do

seancorfield00:07:13

@thiru0130 I would ask: why are you doing that? Why do you want 13 million rows in memory?

seancorfield00:07:52

(and, yes, that's going to take a good long while to load)

seancorfield00:07:24

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...

thiru01:07:40

Thanks for the suggestions guys

thiru01:07:21

@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).

thiru01:07:53

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

theeternalpulse04:07:13

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

carkh04:07:48

@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

thiru16:07:03

Thanks I'll see what more can be done on the DB side and talk to the dba

thiru16:07:48

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

carkh16:07:04

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

carkh16:07:14

maybe some index option could help

carkh16:07:13

FTS is better in every way though

carkh04:07:01

you'll get better bang for your memory bucks

seancorfield05:07:40

@theeternalpulse The default is that you don't need anything except the top-level next.jdbc namespace.

seancorfield05:07:45

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).

seancorfield05:07:01

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).

theeternalpulse05:07:31

ah, I guess I'm just having a problem with my join statement

seancorfield05:07:52

Based on what you've said, I'd expect this to be something close to what you need

(into #{} (map :col) (plan ds ["select ..." ...]))

theeternalpulse05:07:02

(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)))

seancorfield05:07:51

You could just use :name instead of key-selector.

theeternalpulse05:07:23

I eventually want multiple keys, I'm just trying a bare minimum statement

theeternalpulse05:07:35

it's saying that nothing is returned, but the statement works fine in the psql command line

seancorfield05:07:59

You have two strings in there. You need to call str to make one string.

seancorfield05:07:54

(def query [(str "select api_key.name from api_key join response"
                 "on api_key.id = response.api_id")])

seancorfield05:07:23

I'm a bit surprised you didn't get an error about trying to set parameters that aren't needed.

seancorfield05:07:58

Oh, you'll need a space in one of those strings

seancorfield05:07:08

Otherwise you'll get responseon

theeternalpulse05:07:16

totally missed that, I thougt I saw it without the string in th edocs

theeternalpulse05:07:57

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})

theeternalpulse05:07:13

but I see those are for params

theeternalpulse05:07:20

I'll probably take the docs advice and look into honey-sql if it gets a bit more involved