Paging lookup table records?

Discussions concerning general integration topics.

Moderator: Phil Barton

Post Reply
slaven
Posts: 7
Joined: Fri May 11, 2018 5:17 am

Paging lookup table records?

Post 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.
BobRichards
Posts: 1376
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: Paging lookup table records?

Post 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);
}
Bob Richards, Senior Software Developer, SoftPro
slaven
Posts: 7
Joined: Fri May 11, 2018 5:17 am

Re: Paging lookup table records?

Post 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?
BobRichards
Posts: 1376
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: Paging lookup table records?

Post 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!
Bob Richards, Senior Software Developer, SoftPro
slaven
Posts: 7
Joined: Fri May 11, 2018 5:17 am

Re: Paging lookup table records?

Post by slaven »

Thank you Bob, that was helpful!
toddsou
Posts: 75
Joined: Wed Jul 25, 2012 9:39 am

Re: Paging lookup table records?

Post by toddsou »

Hi-

Check out LookupQuerySpec's MaxRows property.

Regards-
BobRichards
Posts: 1376
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: Paging lookup table records?

Post 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!)
Bob Richards, Senior Software Developer, SoftPro
Post Reply