Page 1 of 1
Paging lookup table records?
Posted: Mon May 14, 2018 9:35 am
by slaven
Is there a way to get paged results from lookup tables?
Some lookup tables contain thousands of records and we're concerned we may run into timeout issues when fetching large amount of data in one call.
I couldn't find anything regarding paging in SDK documentation and LookupQuerySpec doesn't seem to support it.
Re: Paging lookup table records?
Posted: Tue May 15, 2018 11:52 am
by BobRichards
You should be able to use the Skip() and Take() LINQ extensions. Below, we retrieve 50 items at a time.
Code: Select all
// Send SelectServer object here. Needed for services.
public PagedLUTableFetch(SelectServer ss)
{
int pageSize = 50;
int pageNum = 0;
// Put below in loop until number items retrieved is zero.
var t = FetchPage(ss, pageSize, pageNum++);
t = FetchPage(ss, pageSize, pageNum++);
t = FetchPage(ss, pageSize, pageNum++);
t = FetchPage(ss, pageSize, pageNum++);
t = FetchPage(ss, pageSize, pageNum++);
t = FetchPage(ss, pageSize, pageNum++);
}
// Get pageSize items at a time. The pageNum specifies which page (0..n).
public IEnumerable<ILookupRow> FetchPage(SelectServer ss, int pageSize, int pageNum)
{
// Get the ILookups service.
ILookups lookups = ss.GetService<ILookups>();
// Create a LookupQuerySpec and specify the table name.
LookupQuerySpec spec = new LookupQuerySpec();
spec.Table = "Attorney";
// Return page worth of data.
return lookups.QueryTable(spec).Rows.Skip(pageSize * pageNum).Take(pageSize);
}
Re: Paging lookup table records?
Posted: Fri May 18, 2018 11:17 am
by slaven
Thanks for your input Bob.
I did try that, but it looks like intermediate materialization happens right after calling lookups.QueryTable(spec) - whole collection will be fetched and stored in memory before Skip() and Take() do any effect.
Skip() and Take() will basically only slice number of rows from memory. Which means that whole collection will be fetched every single loop (for each page) from server.
Is my assumption correct?
Re: Paging lookup table records?
Posted: Fri May 18, 2018 1:57 pm
by BobRichards
You are correct. The skip/take extensions are not overridden in this area as they are in IOrderStore.NewOrderSearch(). Lookup Tables are not designed for bulk export. If you need to get at them in this way, use SQL to access them directly.
In short, look in [dbo].[LookupView] for the table name and get that ID. ColumnsXml holds definitions for the table columns. Then look up the table entries in the table named "[dbo].[lkupvw_<ID>]".
Sorry for the run-around and good luck!
Re: Paging lookup table records?
Posted: Wed May 23, 2018 6:37 am
by slaven
Thank you Bob, that was helpful!
Re: Paging lookup table records?
Posted: Tue Jun 05, 2018 9:54 am
by toddsou
Hi-
Check out LookupQuerySpec's MaxRows property.
Regards-
Re: Paging lookup table records?
Posted: Tue Jun 05, 2018 10:44 am
by BobRichards
The LookupQuerySpec can be used to fine tune the query string sent to a lookup table or to specify the number of rows desired for the query (with MaxRows). It is translated to a "TOP n" qualifier in a SQL expression. LookupQuerySpec is described in the SDK topic How-To / Lookup Tables / Query a Table.
However, you would need to create multiple Filter statements to break the table into bite sized pieces that fit the timeout window. Using the MaxRows parameter would mean that you might truncate data unless you are careful. You would still have the same problem if you used SQL directly but you have direct access to the table to interrogate the number of rows (with an aggregate count) and all that other useful SQL stuff that you don't have by issuing row queries only.
That being said, MaxRows fits many types of problems and it is a good feature to have in your back pocket. (Thanks toddsou!)