SQL for mashups - optimalisation

[ZbigniewK]

I have project where I need to take a lot of labels for mashup from SQL.

First, there are configuration mashups to create questions and their config.

Then, there is mashup where customer chooses their setting, and radio buttons with questions depending on his are presented.
I use for this radio buttons, and connect labels with reading text from SQL querry. Unfortunatelly, it means ca hundred SQL calls to read every data for every label. It takes time, >20 secs. 
And I'm looking for something more optimised, where I would call ONE SQL querry to read data, then split data from it to every label. Thought about storing whole SQL querry in some infotable inside properties of Thing, then services could read this data - but if two users will be working simultaneously, it can happen there will happen that infotable will be set with data for user A, then for user B, then both will use data of user B - so user A will have wrong datas.

[PaiChung · Accepted solution]

Not 100% what your scenario is, The fastest way would be to load all necessary info in memory, but you would have to be aware of the impact on total memory usage in TWX.

But potentially you could per user logging in and accessing a mashup, load all that data into an in memory infotable.

It could be done directly associated with the User (UserExtensions) but it used to have a bug, that I'm not sure if it has been resolved.

If you were to use a single infotable you will likely see some performance impact if you load around 250K rows into it into memory.

Is each call to MS SQL taking 20 seconds? That seems rather long to begin with.

[ZbigniewK]

Do not know User Extension, but meantime I found Session Infos and it sounds like solution.
When it goes about memory load... it was something like ... 200 labels? So short text, maybe 10-20 chars longest. Hope it would be not a big issue for system.