Dynamic SQL load if filtered down to one record in a table

[#1]

Is it possible to initiate a SQL query that would return results back to QV once a table has been filtered down to a single remaining record. I have a SQL data table that has many records in it and one of the table attributes is an nvarchar(max) with very large text articles in it. If I try to load all this in the load script, it takes a very long time (if it even completes) and there isn't a need to have this data readily available for all records.

Ideally, I would load all the key fields from the SQL data table and some of the value fields excluding the large text column. In QV, if I filtered down those results in a table / screen object to only have one remaining record (based on the key fields), I would want to be able to fetch the text article column based on the selected QV items from that table.

Any ideas / suggestions / workarounds that people can think of?

Thanks,

Pete

[#2]

Depends on how dynamic the data is you want to interrogate, i.e. how often does it update and how fresh do you need it?

You could stage an incremental load e.g.

ETL App runs at 1am, pulling all the historic data from sql into a qvd

Main app runs during the day, loading the historic qvd (approx 100 times faster thn a sql query if qv displays "qvd optimized") and only pulls from sql the data for that day, both datasets get concatenated by qv to look like one seemless dataset in memory

This is a strategy I use for a table of metrics approx 30+ million rows by 40 fields and allows me to load the entire dataload in under 20 minutes

Other than that, you would be messing around with using a macro to output a sql query to a txt file and altering your script to run that txt file as an "include" file whilst forcing an edx reload (i'm assuming your ususing publisher and qvws)

[#3]

Recently I had to deal with Nvarchar.. converting to_char(nvarchar column) helped improving the runtime quite a lot.

Thanks,

Boopathi

[gandalfgray]

I haven't used it myself but maybe using the Info Select/Info Load syntax is something in this case?