|Professional Services||Second Signal||Presentations||Andrew's Blog||Support|
View indexes are the real culprit in performance on so many servers. One reason they're too big, is the use of the wrong @Function. Here's the result of a simple test I tossed together. I did this for my presentation on performance at AdminCamp a week ago. I thought I'd share it here.
New database with two forms, both the same other than the form name. Form one has two documents created. Form two has two documents created. What's the difference then? The two documents using form two has have 30,000 response documents on them. The documents created from form one have no responses.
Next, I created four views, with selection formulas as follows:
View #1 -- SELECT form = "TEST1" | @AllDescendants
View #2 -- SELECT form = "TEST1" | @IsResponseDoc
View #3 -- SELECT form = "TEST2" | @AllDescendants
View #4 -- SELECT form = "TEST2" | @IsResponseDoc
In the database itself, views #1 & #2 look identical. Views number 3 and 4 also look identical to each other.
What about the difference in view sizes?
View #2 is 153 Times the Size of #1
and has the EXACT same content
This happens because the @IsResponseDoc formula selects ALL response documents in the database, including those not responses to the actual parents in the view. Those response documents don't display, because their parent isn't in the view -- so they're orphaned -- but they're still in the index. By using @AllDescendants, only responses where the parent is in the view are actually found.
Now imagine the savings if instead of 30,000 documents we were talking about 300,000 documents.
Please wait while your document is saved.
Manage Views. The two largest views had exactly this problem, and one was
reduced to a fifth of the size when fixed, the other to about a third of the
size. Not as great as 1/153 of the size, but a substantial and measurable
improvement with no loss of function. Outstanding!
Now, I just have to go run Synopsis on every other large db I have. Sigh!