This may seem like a stupid question but, here goes. I have dropdown list in an XDBGrid which lists part numbers and descriptions. The list is linked to a DBISAMTable which has just two persistent fields. The question is "does the table only pull the two persistent fields from the server or does it pull the complete records?"

The reason I ask is that the customer has 1000's of parts and they say that the lookup is very slow. A local copy of the table is not an option because another user may have added parts since the last lookup. I am considering using a DBISAMQuery to populate the dropdown list, which is probably what I should have done in the first place, but I don't know if it will make a difference.

Mike
(V4.26 working in C/S mode with Delphi 7)

Mike


Wait for Tim's official answer but from my memory of posts about this before it should only pull the fields that are to be displayed. My guess would be something to do with XBDGrid which is not one I've ever used.

Roy Lambert [Team Elevate]

Mike,

<< This may seem like a stupid question but, here goes. I have dropdown list
in an XDBGrid which lists part numbers and descriptions. The list is linked
to a DBISAMTable which has just two persistent fields. The question is "does
the table only pull the two persistent fields from the server or does it
pull the complete records?" >>

It pulls complete records, but that's not the source of your lookup
slowdown.  More than likely, the slowdown is due to the lookup not using an
index.  Lookups always perform case-insensitive, partial-length searches, so
if you don't have a case-insensitive index on the field being searched, then
you will see performance issues.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>It pulls complete records,

I though in c/s mode it only pulled the fields that were required even for tables so that if eg there's a memo field it wouldn't be pulled because it wasn't displayed in the grid.

Roy Lambert

Roy,

<< I though in c/s mode it only pulled the fields that were required even
for tables so that if eg there's a memo field it wouldn't be pulled because
it wasn't displayed in the grid. >>

BLOBs are different - they are always loaded on-demand.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thanks Roy & Tim,

Tim, your answer suggests that you thought it was a lookup field that I was referring to. Sorry, that's not what I meant. XDBGrid allows a dropdown list in a cell to be populated with one, or more, fields from a datasource so that the user can look up an item rather than remembering the item number.

The customer is complaining that the dropdown is slow to display and because, a) the customer has 1000's of items and, b) the part number table has 52 fields per record, I thought that might be a traffic/bandwidth issue.

I imagine that if I did a query to only return the two fields I need, it would be a lot more efficient?

Mike

Mike,

Yes - switching to (live) query would result in less data being brought down (2 vs 52 fields).

As Tim mentioned also make sure you use indexed fields in the lookup as it likely has an impact as well.

Finally, have you looked into remotereadsize (http://www.elevatesoft.com/manual?action=viewprop&id=dbisam4&product=d&version=7&comp=TDBISAMDataSet&prop=RemoteReadSize) as it might help as well

Raul

Mike


If you have the source for XDBGrid have a look at how the dropdown list is populated. As I discovered with TMS AdvStringGrid I use with PageMode=False, and others have found with DevEx if you populate a list from a table/query it can take a lot of time.

If XDBGrid is looping through a table/query and writing eatch item into a TList then, with thousands of records, its going to be slow.

I know you say a local table is out of the question but how much faster is it if the table is local? Is it enough that that could be the solution? If so I have a suggestion.

Roy Lambert

Raul,

<< Yes - switching to (live) query would result in less data being brought
down (2 vs 52 fields). >>

Actually, that's backwards - a canned query result set (non-live) will only
contain 2 fields, but live queries always contain the complete records.

--
Tim Young
Elevate Software
www.elevatesoft.com

Mike,

<< Tim, your answer suggests that you thought it was a lookup field that I
was referring to. Sorry, that's not what I meant. XDBGrid allows a dropdown
list in a cell to be populated with one, or more, fields from a datasource
so that the user can look up an item rather than remembering the item
number. >>

I suspect that the problem is what Roy said - the grid is looping through
the lookup table record-by-record, and that is going to be very slow.  If
you set the RemoteReadSize property of the lookup dataset to something like
100 or 200, that should speed up such a loop:

http://www.elevatesoft.com/manual?action=viewprop&id=dbisam4&product=d&version=7&comp=TDBISAMDataSet&prop=RemoteReadSize

<< I imagine that if I did a query to only return the two fields I need, it
would be a lot more efficient? >>

Not really, the problem is most likely the way that the lookup is being
populated.

--
Tim Young
Elevate Software
www.elevatesoft.com