Faster Loading Advice needed [ Edited ]

[RJSamp]

7.5.4

Acount 1:M grid with 600 records is taking 1+ minutes to load.....

this is on a Quick Form data grid and indexed fields used for retrieval.

The payload itself is in the 200k range.....the 1:M table is very wide......

We may Archive YEARS worth of data to reduce the row count......or only retrieve the last year's worth of data.....both of these method's probably would speed things up.....

also may have an initial load of say 10 rows......and an ALL Button for the user's to click if they truly need 2001 data.

Also thinking of removing the portal page smart parts for NOTES and HISTORY (leaving the new Notes\History js form).

What is the fastest way to load this grid?

HQL and specify as few columns as possible?

sData and have a very narrow SQL View?

go to a custom ascx form and use a Data Reader with very few columns?

Inquiring minds want to know.....

Thanks!

[rchavez]

Do you need to load all 600 records at once?

Are you using Paging on your Grid?

Also, you stated that your Many table is very wide, do you need to have all columns available?

If not, how about modifying the Entity (assuming that this is entity based) so that the Entity only contains those Columns you need 

SLX Typical behavior for Groups is to load top 150, not sure what the default would be for Paging, but you may want to experiment with it.

Set it up with Paging and See how it load and how it behaves

[RJSamp]

Where;s Stuart Carnie when you need him......

Pagination, yes, there are page numbers running across the bottom of the Tab window for the grid in question.....so the 'retrieve' is paginated. Not as good as VSS (hence the shout out to Stu), but supposedly 'faster' than retrieving all 600 records.

We will probably limit the retrieval size on initial load....the question  remains what is the fastest way to load the data be it the last year of records top 50 in the last year, etc.

i think we're paginating 10 ir 20 rows, I'll play with it.

As far as cutting down the size of an Entity? Not going to happen to this 10 year old table. Raul, you wouldn't ask me to cut the Account Table Entity in half would you? It's an equivalent request.

[jameswsutton]

When we have to display a large amount of data like this we've been using the ExtJS grid control (the older libraries are already deployed with SalesLogix).  It load asynchronously and supports sorting,filtering and dyanmic loading as the user scrolls.  We've achieved amazing performance this way (kind of like the list view in 7.5.4)  The trick is that you'll probably need to set up either a custom function under the SLXData handler or a web pagemethod on your page to respond to the aync calls.  Once you get the details buttoned down the payoff is enormous.

James Sutton

[RJSamp]

ExtJS, you mean like the Notes\History js stuff?

[rchavez]

---

RJSamp wrote:

As far as cutting down the size of an Entity? Not going to happen to this 10 year old table. Raul, you wouldn't ask me to cut the Account Table Entity in half would you? It's an equivalent request.

---

RJ:

  I wasn't referring to your actual Table structure, but rather your Entity. You could have 1000 fields on your Table, but then only define the 20 fields you need for the Web Client on your Entity.

  That said, I do agree that you could do a lot with ExtJS. I myself have used it extensively and sometimes just to replace dialogs (since you avoid the Postbacks to open the dialogs and handle everything client side on Javascript making async calls).

  That said, maybe you need to go back to square one and figure out what is actually causing you to look for a Faster load.

  For instance, have you looked at the SLX Profiler and see how long it takes to Execute the Statement and to retrieve the Data?

  If it is taking a while to execute, then you need to address the Performance on the DB Side.

  Now, if the DB execution is optimal, how about the processing of the data on the server side? SLX adds some overhead with the processing of the data to paint the grid (specially with the Delete function, but that should be minimal).

  And off course, how about the rendering of the data client side?

  To avoid some of the overhead, certainly going the ExtJS route is a good option, but it will require you to do some additional work on setting up the Grid and making sure you get all functionality you require it to have (e.g. Edit, Delete, Add, Refresh, etc).

[RJSamp]

Good stuff Raul!

There are like 200 fields, but they have gone completely to the web and the Add Edit screen (called from the grid) uses nearly all of the fields.

The GetRows() call in the SLX Profiler is spiking out the window.....(not the Execute column, the GetRows column). Normally we see GetRows in the .05.....007 ranges.....this is at 10,20,120!

I thought this was the parsing of the entity properties for the 200 fields * 500 rows.....

The indexes have been adjusted and this helped a scoootch......we are having a hardware review this morning.

Right now we are looking at restricting the rows to the grid.....last year.....only open items, etc. 

50 rows returned is quite acceptable.

[mlaspina]

If you don't have any code snippet properties displayed on the grid, turn the Smart Selection for the DataSource on.  This will limit the fields coming back to only those on the grid. 

If that does not give you the performance you want, I would reccomend converting to an HQL datasource for the grid.  I've pretty much gone that route on all of my new grids as it seems to perform much better than the GetBy datasources, and you have direct control over the columns you get back.

[RJSamp]

Thanks Mike, HQL it is!

[jameswsutton]

Yep.