Fork me on GitHub
#sql
<
2019-09-16
>
byrongibby18:09:59

I am not sure whether to ask this in #beginners or here. I am new to SQL and I am trying to figure out why this code is so slow, it is meant to check whether a record (describing the metadata of a dataset) exists and if it doesn't then it should insert it into the table. Below follows the HugSQL code and then the Clojure code:

-- :name jcmod-dataset-exists?
-- :command :query 
-- :result :one
-- :doc Query whether a dataset exists by repo id
select count(*) > 0
as record_exists
from jcmod_datasets
where repo_id = :repo_id;

-- :name drop-jcmod-datasets-table
-- :command :execute
-- :result :raw
-- :doc Drop jcMod datasets table
drop table if exists jcmod_datasets
and
(defn add-jcmod-dataset [record]
  (jdbc/with-db-transaction [tx db]
    (if (not (:record_exists (jcmoddatasets/jcmod-dataset-exists? tx {:repo_id (:repo_id record)})))
      (jcmoddatasets/insert-jcmod-dataset tx record))))

byrongibby18:09:09

Any help would be appreciated.

byrongibby18:09:14

Sorry that should have been:

-- :name insert-jcmod-dataset
-- :command :execute 
-- :result :affected
-- :doc Insert a single jcMod dataset record
insert into jcmod_datasets (repo_id, object_type, parent_directory, name)
values (:repo_id, :object_type, :parent_directory, :name)

byrongibby18:09:34

And not:

-- :name drop-jcmod-datasets-table
-- :command :execute
-- :result :raw
-- :doc Drop jcMod datasets table
drop table if exists jcmod_datasets

byrongibby18:09:43

(Sorry for spam...)

seancorfield19:09:56

That first query could be very slow if you have a large table and no index on repo_id.

byrongibby19:09:04

repo_id is a primary key and the table is less than a thousand records. If the way I have written it is how someone who knows what they're doing would write it (more or less), then I'll learn to live with the speed.

seancorfield20:09:38

@byrongibby You might try select count(*) as total from ... and see if that is faster (and then check (pos? (:total (jcmoddatasets/... )))).

seancorfield20:09:13

Best advice is to try out these queries directly in the SQL command line tool and check whether it's really the queries that are slow vs something in your code...

byrongibby20:09:49

Thanks for the guidance, much appreciated as always. 🙂