Vision client won't load template repeater but the designer will

I have a project that loads work order templates into a template repeater limited to max of 100. The repeater will load in the designer but when I open the same window in the client it will not. It is also worth noting that the same repeater will load on some computers in the client while not at all on others, like mine.

The template has 15 parameters to fill in the location, status, colors, etc. I supply the template params via a single query from our database shown below.
On the computers that work it takes a few seconds to load but does load. Mine can be left for 30 mins without loading anything.

Originally I thought it was the query performance causing loading issues but that doesn't make sense given the difference of client vs designer on my computer. I then thought it was a rendering issue and the computer being fast enough but again client/designer difference throw that out.

I'm at a loss for why this is happening. Any ideas?

param query

SELECT 
  * 
FROM 
  (
    SELECT 
      r.id as "id", 
      e.Name as "Requester", 
      title as "Title", 
      detail as "Detail", 
      `timestamp` as "Submitted", 
      a.tag as "Area", 
      a.id as "aId", 
      a.color as "AreaColor", 
      r.priority as "PriorityId", 
      p.color as "PriorityColor", 
      p.tag as "Priority", 
      r.top as "top", 
      r.images as "images", 
      CONCAT(
        "R", 
        LPAD(r.id, 4, "0")
      ) as "rId", 
      r.clockNumber as "ClockNumber" 
    From 
      Requests r 
      JOIN ShopOrderData.Employees e ON r.clockNumber = e.ClockID 
      JOIN Area a ON a.id = r.area 
      JOIN Priority p ON p.id = r.priority
  ) as request 
  JOIN (
    SELECT 
      rh.requestId, 
      s.id as "StatusId", 
      s.status as "Status", 
      s.color as "StatusColor", 
      rh.id as rhId 
    From 
      RequestHistory rh 
      JOIN Status s ON s.id = rh.statusId 
    WHERE 
      rh.id IN (
        SELECT 
          MAX(id) 
        From 
          RequestHistory 
        Group By 
          requestId
      )
  ) AS history ON request.id = history.requestId
  LEFT JOIN (
    SELECT 
      eqc.reqid, 
      eqc.equipment as "Equipment" 
    FROM 
      Equipment_conn eqc
  ) equip ON equip.reqid = request.id 
  LEFT JOIN (
    SELECT 
      eq.equipment, 
      eq.description as "EquipmentDesc" 
    FROM 
      Equipment eq
  ) equipd ON equipd.equipment = SUBSTRING_INDEX(
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(equip.equipment, '"', 2), 
      '"', 
      -1
    ), 
    ',', 
    -1
  ) 
WHERE 
  (
    SELECT 
    IF("{searchString}" = "ALL",
    	TRUE,
        ClockNumber = CAST('{searchString}' AS SIGNED) 
        OR Requester LIKE '%{searchString}%' 
        OR Title LIKE '%{searchString}%' 
        OR CONCAT(Requester, Title) LIKE '%{searchString}%' 
        OR id = :partToRequest 
        OR CONCAT(
          "R", 
          LPAD(id, 4, "0")
        ) LIKE '%{searchString}%'
        OR equip.equipment LIKE '%{searchString}%' 
        OR equipd.EquipmentDesc LIKE '%{searchString}%'
	  )
  )
  AND history.StatusId in {statusFilter} 
  AND IF(
    :AreaFilter > 0, 
    (aId = :AreaFilter), 
    True
  ) 
  AND IF(
    :hideCompleted, 
    (Status != 'COMPLETE'), 
    True
  )
ORDER BY 
  request.id DESC
LIMIT 100

Any errors in the client console? Help -> Diagnostics, or Ctrl + Shift + F7.

None that show up when navigating to the window.

Are you 100% sure it's not query performance? This part right here is going to be bad for performance and unindexable. Have you run this query through the query explainer or got any metrics on average time it takes? As far as I can tell it will need to do full scans (the worst for perfomance) on each of these columns for this where clause.

Well, you could do stuff like prove the templates are loading by decorating them with property change scripts that log/print, and verify those in the console. Or try a bisecting approach - duplicate the problematic template repeater onto a test window, see if it works there; try restricting the query to return less rows or otherwise filtering to see if you can hit a critical threshold; try displaying a single instance of the template with hardcoded or semi-hardcoded data on a template window, etc.

I'm not 100% sure, but I did have my suspicions about that segment. Although when I look in the designer I can view the dataset returned in the vision property editor but the repeater still loads. Worth taking a look at though. Thanks.

Ok good idea thanks for the pointers.

If you are using MySQL, run this query in workbench then click on Query->Explain Current statement and post results. Also, note how long it takes. I wonder if in designer the results are cached at all on the window.

I used phpMyAdmin to get this but it should be the same as the workbench.

If the search query = '%%' then it runs with time 0.45 s.
and explainging results in this

This is always going to evaluate to true. Put something in there and see how long it takes. Use a string someone would realistically use in your application.

I tried "Hunter" and got 0.3 s

1 Like

Alright, good enough for me I would not consider the query any further then.

Well If I move the repeater to its own window it loads normally, so there are some strange interactions going on in my original window that is causing the problem.

I was binding to a tag in my window, once I made a dummy listener for it in the Client Events suggested above it fixed the loading issues of the repeater .

1 Like