14
$\begingroup$

Considering a DatabaseExample included with Mathematica:

Needs["DatabaseLink`"]
conn = OpenSQLConnection["publisher"];
SQLSelect[conn, "ROYSCHED", "ShowColumnHeadings" -> True]//Short

{{TITLE_ID,LORANGE,HIRANGE,ROYALTY},{BS1011,0,5000,0.1},<<49>>,{PY2008,0,50000,0.1}}

Now with the introduction of v10 and functions such as Association and Dataset, we can convert this data to a Dataset:

sqltodataset[fields_, data_] /; Equal @@ Join[{Length@fields}, Length /@ data] :=
 Dataset[Association[Thread[fields -> #]] & /@ data]


data = SQLSelect[conn, "ROYSCHED", "ShowColumnHeadings" -> True];
ds = sqltodataset[First@data, Rest@data];

Querying should now be much faster:

Do[SQLSelect[conn, "ROYSCHED", {"ROYALTY"}, SQLColumn["TITLE_ID"] == "BS1001"], {100}] // Timing
Do[Normal[Select[ds, #"TITLE_ID" == "BS1001" &][[All, "ROYALTY"]]], {100}] // Timing

And it seems to be. Although not quite a fast as I expected:

{0.171601,Null}

{0.093601, Null}

But at the expense of RAM:

ByteCount[ds]

35644

Is there a real upside to converting to a Dataset, or should I just stick to SQL to query? And is there a more direct way to convert straight to a Dataset?

Improve this question
$\endgroup$
6
  • $\begingroup$ Just a note: you can use AssociationThread in place of Association[Thread[...]]. $\endgroup$
    – RunnyKine
    Jul 29, 2014 at 15:12
  • $\begingroup$ @RunnyKine Thanks... Soooo many new functions to learn. $\endgroup$
    – kale
    Jul 29, 2014 at 15:14
  • $\begingroup$ I know, makes using v10 so much fun :) $\endgroup$
    – RunnyKine
    Jul 29, 2014 at 15:15
  • 1
    $\begingroup$ The benefit would be more pronounced with a remote database: pulling a large block of data and then manipulating it locally will be faster than numerous more specific queries. You can of course do that without a dataset; I think the reason to use a dataset in some cases would be to preserve the fields/keys so you can work in a more expressive syntax similar to what is available in a database. $\endgroup$
    – mfvonh
    Jul 29, 2014 at 17:14
  • 1
    $\begingroup$ It has been discussed in chat several times, around the date of the release, that Dataset currently is optimized to work with hierarchical data, rather than tabular. There are plans to add SQL-like backend(s) to Dataset, which will enable efficient manipulation of tabular data, but those will likely not be based on Associations in the same manner as Dataset is (namely, one Association per row in a db). $\endgroup$
    – Leonid Shifrin
    Jul 29, 2014 at 17:26

1 Answer 1

Reset to default
1
$\begingroup$

Depending on your requirements... I've gone radical. My practice is: - if I generate the data use associations and datasets save as .mx files or CloudObjects. - if data in SQL, use the simplest SQL possible to load the data then transform it to associations or datasets and save to .mx file or CloudObjects.

I'm a SQL free guy now and happier for it.

This allows much more flexibility when wrangling data and joining disparate data sources for computation.

Improve this answer
$\endgroup$
1
  • $\begingroup$ I tend to do this too, but it does requires that your data or some reasonable fraction of it can be held in memory. Often that is not the case, at which point some SQL connection will still be required (although this can always just be to load chunks of data as needed). $\endgroup$
    – b3m2a1
    Apr 3, 2018 at 7:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.