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 » Mon May 14, 2018 9:35 am

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: 1006
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: Paging lookup table records?

Post by BobRichards » Tue May 15, 2018 11:52 am

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, Software Developer, SoftPro

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

Re: Paging lookup table records?

Post by slaven » Fri May 18, 2018 11:17 am

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: 1006
Joined: Wed Jan 15, 2014 3:50 pm
Location: Raleigh, NC
Contact:

Re: Paging lookup table records?

Post by BobRichards » Fri May 18, 2018 1:57 pm

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, Software Developer, SoftPro

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

Re: Paging lookup table records?

Post by slaven » Wed May 23, 2018 6:37 am

Thank you Bob, that was helpful!

toddsou
Posts: 51
Joined: Wed Jul 25, 2012 9:39 am

Re: Paging lookup table records?

Post by toddsou » Tue Jun 05, 2018 9:54 am

Hi-

Check out LookupQuerySpec's MaxRows property.

Regards-

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

Re: Paging lookup table records?

Post by BobRichards » Tue Jun 05, 2018 10:44 am

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, Software Developer, SoftPro

Post Reply