Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Copper Elite Contributor
Posts: 47
Registered: ‎04-28-2009

Account Summary tab very slow

Dear all,

 

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).

 

To reproduce:

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

 

Best regards,

Lukas

Bronze Super Contributor
Posts: 113
Registered: ‎07-27-2009

Re: Account Summary tab very slow

Hello Lukas,

 

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.

 

Regards

Regards
Rainer Raebiger
Silver Contributor
Posts: 835
Registered: ‎03-24-2009

Re: Account Summary tab very slow

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....

RJ Samp
Highlighted
Bronze Super Contributor
Posts: 113
Registered: ‎07-27-2009

Re: Account Summary tab very slow

Hi RJ,

thanks for that hint.
Regards
Rainer Raebiger
Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: Account Summary tab very slow

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).

 

--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html