12-02-2010 08:23 AM
We are optimizing a database for our customer in speed. The customer has a history table
with the size of 1.5 GB.
Issue: The Account Summary tab is very slow (even in our internal database).
Use an SLX-DB (may be your own company) where there is a lot of history.
Choose the account with the most history (your company) click on the summary tab and wait.
With 7.5.3 we wait over 30 seconds and it is the same issue as with earlier SLX versions.
Is there any way to improve this speed? May be with indexes or other tricks?
Thank you in advance for your answer and help
12-02-2010 10:02 AM
i think it's a question of the general performance of the SQL-Server. I did a test in our Testenviroment.
virtualized SLX Server
- Windows 2003 R2, with 4 GB RAM, 1 Gigabit Network
virtualized SLX Client on the same Host
- Windows XP SP3, with 3 GB RAM, 1 Gigabit Network
SLX-DB about 15 GB
History-Table about 5 GB
Account with most entries has about 380.000 entries (SELECT ACCOUNTID, COUNT(ACCOUNTID) AS ACCOUNTCOUNT FROM sysdba.HISTORY GROUP BY ACCOUNTID ORDER BY ACCOUNTCOUNT DESC)
Opening History-Tab takes about 12 Seconds. Not that fast but it's ok.
Very interesting how data is collected.
First Script looks for valid Historydata:
SELECT A1.HISTORYID FROM sysdba.HISTORY A1 INNER JOIN sysdba.PICKLIST A2 ON (A1.TYPE=A2.ID)
WHERE ((A1.TYPE<>262162) OR (A1.USERID='ADMIN')) AND (A1.ACCOUNTID = 'A6UJ9A000DTE') AND (A1.TYPE <> 262156) Order By A1.COMPLETEDDATE Desc,A1.HISTORYID
--> in this case abount 20.000 lines HISTORYIDs
Second Script lokks for the first 100 lines
SELECT A1.HISTORYID, A2.TEXT A2_TEXT, A1.COMPLETEDDATE, A1.USERNAME, A1.USERID, A1.CONTACTNAME, A1.RESULT, A1.DESCRIPTION FROM sysdba.HISTORY A1 INNER JOIN sysdba.PICKLIST A2 ON (A1.TYPE=A2.ID)
WHERE A1.HISTORYID IN ('H6UJ9A02119G','H6UJ9A02119F', ---> cut out left 98 HISTORYIDs)
Order By A1.COMPLETEDDATE Desc ,A1.HISTORYID
So. Good question what takes so long .... IMO it's not requesting the data from the SQL-Server.
Maybe some information to start looking.
12-13-2010 10:16 AM
All tests need to be done as a USER, never Admin. Retrieving 'secure' rows is very slow for SLX as each record is its OWN transaction from the OLE DB Provider into SQL Server. You read that correctly: each record retrieved is its own seperate transaction in SQL. OUCH. Even if the owner is Everyone and the Account is viewable by the user and all fields are at least readonly for the user (Field Level Security) and the Everyone Profile it still takes an amazingly long time to go through 20,000....200,000.....2,000,000+ records. Much longer than a simple bunch of inner joins to SECRIGHTS and SECCODE kinds of stuff.
We've had to go to the SLX Native connection for MANY grid builder's, summary (sums, counts), List View builder's, et al....
12-16-2010 05:47 AM
There are many things that make the history (as well as other) table slow. Here's a few::
A - Over time, the history table tends to end up with a lot of foreign keys that are invalid. You need to NULL out all the invalid keys or point them to real records.
B - Missing indices on foreign key fields. EVERY foreign key field in every table should be tagged w/an index.
C - Not re-indexing the db on a regular basis. For large highly active db's, this should be at least once a day (after hours of course).