The Notes/History tab under the Account will really slow down a user if they open an Account that has thousands of records in the History table. So I watched the SQL that runs when a user does that and the SQL seems silly to me. Is there a way to change the SQL that runs?

The first thing it does is goes to the History table and gets all the HistoryId's for this AccountId that are not a type of "DBCHANGE" or they are "Personal" to the user.

SELECT A1.HISTORYID FROM HISTORY A1 INNER JOIN PICKLIST A2 ON (A1.TYPE=A2.ID)  WHERE ((A1.TYPE<>262162) OR (A1.USERID='ADMIN')) AND (A1.ACCOUNTID = :1)    AND (A1.TYPE <> 262156) 

Then it takes all these ID's and throws then into an IN-Clause for the next SQL statement:

SELECT A1.HISTORYID, A2.TEXT A2_TEXT, A1.COMPLETEDDATE, A1.USERNAME, A1.USERID, A1.CONTACTNAME, A1.RESULT, A1.DESCRIPTION FROM HISTORY A1 INNER JOIN PICKLIST A2 ON (A1.TYPE=A2.ID)  WHERE A1.HISTORYID IN ('H6UJ9A002JPB','H6UJ9A001XAO','H6UJ9A001F2G' ,'H6UJ9A001CYT' ,'H6UJ9A001C4U','H6UJ9A001ARE' ,'H6UJ9A0019Q4' ,'H6UJ9A0018HD','H6UJ9A000C7Y','H6UJ9A000B5U', 'H6UJ9A0009YI','H6UJ9A0009A3' ,'H6UJ9A0007W0','H6UJ9A0007HZ','H6UJ9A0007AA', 'H6UJ9A0006Y4','H6UJ9A000637','H6UJ9A0005S0','H6UJ9A0005C2','H6UJ9A0002X4','H6UJ9A00022Q')

That is just an Account with 20 History records. I can't even imagine what it looks like when there are 10,000 History records.

Is there a way to just get all the data in the first SQL and not even do the second SQL? It maks no sense why it even makes the second SQL when they have everything they need in the first one. They just need to return more columns. They're already getting the same records.

Can you change anything in Architect?  Can you make a DB view so it won't try to compile a second SQL that is a half-million characters long?

My System:

Oracle database 11.2.0.3.0

SLX 8.0

Windows 2003 Server

One of our Accounts has 26,000 History records. We have 100 Accounts with over 1,000. There are almost 500,000 records in the History table. 

No one wants to delete or "archive" any of the History.

When we upgraded our database to a new server, we got the time from 7 minutes on that one large account to 40 seconds, but 40 seconds is still a long time to sit on the phone and wait for your SLX to unlock.

The database indexes are all working correctly. But it takes forever when you throw the SQL with thousands of history ID's in the IN-Clause. 

Hi Aaron

Actually, what you are seeing is Virtual Server Side Cursors (VSSC) in operation. If you had picked an account with 10000 history rows you would see the exact same SQL - albeit with only 100 (maximum) ID's in the IN clause. SLX does this so that it can step through 100 rows at a time, and not - as you think - an IN clause with 10,000 ID's !

As the user passes the 100th row - SLX re-executes the query for the next 100 rows. This leads to pagination of the effective 10,000 rows. If you search VSSC in client help it will explain this. The main view grids do this as well (most queries do if it can identify the primary key effectively). In the client, if you look toward the group selector on right hand side you will see an icon, if you hover above it you will see whether the group is in virtual server side [fast] or client side [slow] mode.

Now, as for your other issue (where it takes forever to load the grid) - can you tell me if you are using v8 Notes-History or whether you have released the old version of the plugin. Earlier versions of SLX suffered terrible issues with this grid control whereby the grid would execute several times - without a BindID (i.e. get all records for all). You can fix this by adding a condition to the blank grid (in Architect) and set a condition to "{PrimaryID} does contain data" where {PrimaryID} is the grids primary ID e.g. if you are modifying the account history view, use AccountID as the condition. ContactID for Contact History view etc etc.

You can also do this to speed things up as well:

UPDATE HISTORY SET ACCOUNTID = NULL WHERE ACCOUNTID = ''

UPDATE HISTORY SET CONTACTID = NULL WHERE CONTACTID = ''

UPDATE HISTORY SET OPPORTUNITYID = NULL WHERE OPPORTUNITYID = ''

As, again, an old bug didn't distinguish from Null/Blank.

Hi Aaron,

In SalesLogix version 7, I usually resolved the issue by removing the 'Sortable' property of the datagrid. You may need to comment out ".Sort" in the scripts too. This is the solution I can give on top of my head.

Ta

Jinsan

That works, but isn't best solution as, by doing so, users can't then sort the grid using the grid headers !

Hi Mike,

Funny enough, even though we have unchekced the Sortable property of the datagrid, the sorting still works. That's from my exprience. I am not sure if v8 is different to v7.

Jinsan

Ah yes, but if you look at the code behind the .Sortable = True - and that's set after the load ! So, yes, this would work !

Thanks for the lesson on VSSC, Mike. 

I do have the v8 notes/history form. I added the "accountid does contain data" and made sure all the null and blank records were straight. I didn't see any speed improvement -- probably because I have the new version.

I switched off the Sorting and now it is super-fast. So thanks Jin San Son. 

Never turn off Sorting....it actually means load all records.....when you do multi selects across a page and sorting is turned off you get very terrible resorts....

BTW Never means NEVER and your mileage will not vary.

I've covered how to speed up grids many times up here.....

1. turn on SLX Profiler and see how many times the grid is re-refreshed. It only needs one!

2. Here are Five refreshes that I frequently see on many OOTB and Custom grids alike, you only need ONE.

a) use the BINDID property

b) use the Link table property in the SQL property grid builder dialog....i.e. :BINDID
(yes, a and b are used in most OOTB grids, that's TWO roundtrip queries to the data base! you only need ONE.)

c) set the gridname.SQL.Text property with a SQL statement.

d) gridname.Refresh

(yes I see a lot of developers use ALL 4 refreshes on custom grids, that's 4 round trips!).

e) Set gridname.recordset = objRS of data.

My preferred method?

1. get rid of the BINDID and Link to Table properties. Use an invisible editbox databound to the entitiy.ID field (Account:AccountID for example).

2. AXFormChange event set a global variable to the invisible editbox: gstrAccountID = Trim(txtLinkID.Text & "")

3. Build up the SQL you want to use including AccountID = '" & gstrAccountID  & "' "

4. Obtain an ADO recordset using the SQL and an ADO connection.

5.Set gridname.recordset = objRS

There you go: 1 roundtrip to the database, 1 refresh.

If you REALLY want it fast......use a NATIVE connection to the database, not the SLX OLE DB Provider.....we use Sysdba and whatever the password is in a global script.... when you use the SLX OLE DB Provider as a User (not Admin) you'd think a LEFT OUTER JOIN to SecCode and SecRights would be a fast way to include\exclude records in team ownership environment with field level security.....actually SLX and SQL team up to do a begin and end TRANS on EACH record.....10,000 records fetched, 10,000 transactions..... and this may not be on a Field Level Secure or Team Ownership SECCODEID controlled table.

At a big project I was involved in we routinely displayed, sorted, inserted, removed....5000-15000 records attached to each of 4-10 Opportunity Product Records.....we found out early on that 500-1000 rows KILLED SLX Grids.....with the above techniques and a Native connection we could easily load 8,000+ records in a few seconds.

OH, the good news is that grid caption header click sorts are now done IN the ADO Recordset\memory. Much faster than the old 7.22 or earlier method of going to the server for a sorted query and then going through the whole SLX Provider routine again.....

2,000 grid rows retrieved in an hour??? most of our users went to sleep.....or lunch....

You meant never turn ON sorting right ?

Another tip - use the SLXProfiler to provide a query hint. Use a MERGE JOIN into anything withseccode/ecrights/userinfo if you use a lot of teams ! You'd be amazed at the performance gain you get from just that !

Quote: Originally posted by Mike Spragg You meant never turn ON sorting right ?

Sortable is turned ON 99%+ in my grids. If you have enhanced the grid to properly handle multi-select deletes or grab a list of KeyFieldID's and populate the next form\selection list then turning OFF Sortable is a disaster. Especially when you multi-select on different grid pages. The techniques I outlined above will return thousands of rows of data to an SLX datagrid that SLX OOTB techniques would choke on. Run SLX Profiler and see how many times the data is fetched.....

Sortable means load all records and Turn ON grid header click sorting. This header caption sorting used to be a roundtrip to the server (7.2.x) and was very slooow....now sorting is handling as it should have been, as an ADO RecordSet sort in local memory.

Another reason to set sortable to true.....if you have grid footers, they won't add/count/sum up if sortable = false.....ouch!