this thread brought to light several issues.
NOTE _ I'm not blaming Aware/Vlad for anything. I'm not criticizing the product. Not in any way. I'm describing what I see going on because it will help me (and others hopefully) design Aware BOs to work more efficiently once I know whats going on behind the scenes. The Aware "concept" and the fact you don't need to be a professional programmer to write these apps is still true. You don't really need to know whats going on behind the scenes, which is why some ideas in the next post "Food for Thought" might help you avoid unexpected results.
1) I had previously posted about higher CPU time when a process ran - AND - you had a server Output window open. Under v8+, it was possible to open a grid and then observe the TomCat Output still scrolling for many seconds after the grid had completed on the client PC. Esp. when you have conditional formatting/buttons on each row in that returned grid... each grid row result may "expand" into 3,5 or more actual lines in the Tomcat window. And if the user does a next page, or a sort, then there's even more rows that still have to be displayed in the Tomcat window.
I believe this is one reason why CHRIS29 had discovered those Output windows need to be closed. A Habit I've tried to get in myself, but still can bite me as it did yesterday when I had them open and got a User issue with a grid "hanging" because it had already reported a Out of Memory issue.
The Tomcat window is the biggest Offender I think and you may be able to leave open the Server Output.
2) This process also got me looking more indepth at MS SQL SERVER Profiler Traces... to see why [it appeared] one grid was responsible for causing this OOM issue.
WHAT I DISCOVERED (that others may already know) is when you have a BO with 50 fields, for example, and you make a grid call to show 25 records:
a) a SELECT * from BO is issued, returning ALL fields, regardless of what you are displaying in the Grid
b) a SELECT * FROM PS_BO is issued for each reference in that row, regardless of whether you display linked fields in the Grid
EXAMPLE: Lead system. Each Lead has a ps_EnteredBy, ps_AssignedTo, ps_ClosedBy, ps_Branch, ps_Tenant
Just displaying the first 25 Leads causes:
SELECT TOP 25 Lead.*
SELECT * FROM REGULARUSER for EnteredBy
SELECT * FROM REGULARUSER for AssignedTo
SELECT * FROM BRANCH for 1 branch
SELECT * FROM TENANT for 1 Tenant
Clicking NEXT PAGE reissues similar statements, except the TOP xxx grows. For the 3rd page of 25 rows shown, the statement is:
SELECT TOP 75 Lead.*
Internally, Vlad knows he only is interested in the 51-75 rows, but the underlying database still returns more and more rows as the user pages through a grid.
THE FULL MONTY and the SOLUTION
3) taking all this into account, I started reviewing what were unused fields and references in my App.
This particular app was started using the CRM as a framework.
the BO Group COMMUNICATION contains 4 BOs (email out, email in, sms, ???). We were only using Email in this list. But Queries, procedures, etc. had been built on "Communication", so you can't easily remove tables from the group and still Publish because you get a rash of Integrity issues. This is why this hadn't been done in the past. So I bite the bullet and dig in to start cleaning up this BO.
This wasn't a big deal, but it came to light when examining the SQL Trace and seeing a over complicated query (for what I needed) for showing Emails - since the Query was built on Communication (not just OutgoingEmail) it had a join of all tables in the group.
OK, here's the solution, promise
4) I started looking at DOCDATA fields in the App. Esp. in the lead table. There WAS one - from the CRM - we were not using it, but I deleted it anyway.
THEN, I realized a PHOTO was in RegularUser.
We were not using it either but it WAS in the UserEdit form and some users had had uploaded photos.
I ran a SQL Query using DATALENGTH([Photo_DOCDATA]) and found most photos were around 100k, but THE PHOTO of the user where we saw ALL THE SLOWNESS AND OutOfMemory issues was...
:oops: 12 Megabytes :oops:
Yes, take a picture of yourself at a portrait studio, have them give you a CD cause the image is so big, and use THAT DAMN HUGE IMAGE as your tiny little picture in the CRM - Makes sense to me !!!! :roll:
And, reference back to # 2 above, she was the sales rep assigned to all those leads.
So for every read of every grid line, her "Select * from LoggedInRegularUser" dataset included a 12Meg image that wasn't even needed.
A quick
Update [R3].[dbo].[REGULARUSER]
set Photo_DOCDATA=NULL, Photo_DOCTYPE=Null
wiped all that crap out and performance IMMEDIATELY returned to normal.
