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.
Paging lookup table records?
Moderator: Phil Barton
-
- Posts: 1377
- Joined: Wed Jan 15, 2014 3:50 pm
- Location: Raleigh, NC
- Contact:
Re: Paging lookup table records?
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);
}
Bob Richards, Senior Software Developer, SoftPro
Re: Paging lookup table records?
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?
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?
-
- Posts: 1377
- Joined: Wed Jan 15, 2014 3:50 pm
- Location: Raleigh, NC
- Contact:
Re: Paging lookup table records?
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!
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!
Bob Richards, Senior Software Developer, SoftPro
Re: Paging lookup table records?
Thank you Bob, that was helpful!
Re: Paging lookup table records?
Hi-
Check out LookupQuerySpec's MaxRows property.
Regards-
Check out LookupQuerySpec's MaxRows property.
Regards-
-
- Posts: 1377
- Joined: Wed Jan 15, 2014 3:50 pm
- Location: Raleigh, NC
- Contact:
Re: Paging lookup table records?
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!)
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!)
Bob Richards, Senior Software Developer, SoftPro