nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

SQL to matrix

ron_horne

Hi all,

Is it possible to extract data from a database directly into a matrix without importing it first to a data table?

Thanks!

Accepted answers

msharp

Well, yes and no. You can import it directly into JSL as a matrix using:

matrix = Execute SQL(dsn, sql_string, table_name, invisible) << Get as matrix;

However, the execute SQL statement still creates a datatable. If "private" was an option then it would be possible since JMP automatically clears private datatables from your computers memory if they aren't stored somewhere. Alas, invisible is the best they give us.

You can use the below script to close the invisible table. In all practicality it'll be just as fast since executing the SQL statement will always be the bottle neck.

matrix = (dt = Execute SQL(dsn, sql_string, table_name, invisible)) << Get as matrix;

close(dt);

All comments

msharp

Well, yes and no. You can import it directly into JSL as a matrix using:

matrix = Execute SQL(dsn, sql_string, table_name, invisible) << Get as matrix;

You can use the below script to close the invisible table. In all practicality it'll be just as fast since executing the SQL statement will always be the bottle neck.

matrix = (dt = Execute SQL(dsn, sql_string, table_name, invisible)) << Get as matrix;

close(dt);

ron_horne

thanks msharp,

this makes it clearer to me that i didn't miss anything.

The thing is that the data source is big and i only need to get a list of ID's (one column). After that I extract the data (many columns) for each ID and analyze it separately. opening the whole data set is just not feasible.

ron

msharp

Sounds like you could optimize your SQL statement.

Something like:

With IDtemp as (Select IDcol from IDtable Where IDcol = 'FilterCriteria')

Select * From IDtable

Inner Join IDtemp

This will first create a temporary IDtemp table that SQL uses to Join with the larger IDtable. The inner join greatly reduces the size of the tables SQL has to work with. Of course, there may be simpler ways to make it faster. Just all depends on how the data is stored and how large the tables are.

I would focus on optimizing your SQL statements over optimizing how JMP interacts and pulls the data.

ron_horne

Thanks msharp,

Perhaps I should have shared my script before.

Any recommendation are welcome.

Usermatrix = (dt100 = Open Database(

"DBQ=D:\MY DOCUMENTS;DefaultDir=D:\MY DOCUMENTS;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;FILEDSN=D:\My Documents\SQLLog Data.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;",

// the next line will extract only the UserId from the dataset to reduce memory usage.

"SELECT UserId FROM dataset.csv",

"dt10 - Userid list",

invisible

// private // this option does not work!!!

)) << get as matrix;

Close( dt100, NoSave );

// identify the users and their ID number

Userlist = Associative Array( Usermatrix );

UniqueId = userlist << get keys;

// this is just a statement in a string

sqlstr = "SELECT * FROM dataset.csv where id = ^i^";

// loop through the dataset and extract individual data one at a time.

For ( i=1 , i<= N Items( uniqueId ) , i++,

// extract a personal log for each user using the statement.

dt10i = Open Database(

Eval Insert( sqlstr ),

"Log Data User " || Char( i ),

);

// preform the analysis ...//

close (dt10i, no save);

);