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.

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.

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.

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.

This does two things, makes your query faster and also since you are pulling it as a matrix Usermatrix will now = UniqueID cutting several lines of code. This removes the need to store potentially very large lists and matrix's that aren't used (Userlist/UserMatrix). Just but sure to change nitems to nrows in the for loop.

A couple take aways from this, you will save time from connecting to the database everytime (which should save alot of time). You don't need to eval insert() the sqlstr, not a big deal but one less step. Lastly, and pretty important, is that opening dt10i invisibly will save a lot of memory space helping your code run faster. Especially if you manipulate the data table at all in the "perform analysis" portion. I see you end up closing it later on anyways. To make a script run faster it's important to remember that any data table that you plan to close should be opened in either invisible or private mode.

Be sure to have a Close Database Connection(dbconnection); at the end of your code.