Prequery or Prefilter ordering?

[pastvne]

I've been working to resolve a performance issue. My trials and tribulations are detailed here:
http://www.fabrikar.com/forums/index.php?threads/help-with-performance-issue.38095/

I have placed credentials for my site in my profile and I've setup a test site that can be tortured at will.

The last post in the thread above contains my current thinking and questions.

I'm very appreciative of any attention this can be given. I'm hoping to come away with a better idea of how prequery and prefilter ordering and possibly reducing joins could help resolve my performance issue which is becoming quite serious and I'm hopeful has a pretty simple solution.

Regards,

Bruce Decker
Colorado

 

[cheesegrits]

Looking at it now.

-- hugh

 

[pastvne]

Awesome. Thanks!!! I do have skype if you need to speak with me that way. I need to start it up however.

 

[pastvne]

Hugh:
There's something wrong with my hosting co LiquidWeb right now. All sites, including ones not running J! or F! are extremely slow to respond. My phpinfo() page is slow which tells me its nothing you are doing and it's not F! or J!. I'm contacting LiquidWeb now. Figures this would happen right now arghhh!
-BD

 

[pastvne]

Update: Liquid Web has a router that's going sideways. They are looking into it now. I'll keep you posted and sorry for the inconvenience.
-BD

 

[pastvne]

Hi Hugh:
They think they have the router issue fixed. It may not have affected you but it was affecting me from my location. Anyway, you should be free to access the site as you can. Let me know if you experience any issues and sorry again for the inconvenience.

 

[cheesegrits]

I seem to be having problems generating problems, if you see what I mean. I've taken an example getData() query for that documents_my list, ie the main query we do to build the list selection with, by enabling Fabrik debug and copying and pasting it from the debug output, and then running / explaining it in phpMyAdmin.

I don't know whether it's just because my query gets cached, but even if I switch user ID in it (which should mean I'm running a new uncached query in MySQL), I'm only seeing query times of tenths of a second.

I'll need to get with you on Skype, and we can put our heads together, you can show me problematic queries and I can see if there's a way round them.

Definitely one issue could be the dependent subquery in that last pre-filter.

One thing that is puzzling me is the "explain" for the main primary key usage ..."Using index; Using temporary; Using filesort" ... hmm some more playing aorund, that looks like a result of the GROUP BY ...

This week is going to be a problem ... I have neck fusion surgery on Tuesday. I just odn't know what my availability is going to be like.

-- hugh

 

[pastvne]

Hi Hugh:
Thanks for looking at this. I too was having a heck of a time with cached results. I'm around today if you are working and would like to discuss. Let me know because I don't leave Skype on normally. In the mean time, I'll try to document a procedure to re-create the slow-query result. If we don't connect before your surgery, let me now wish you a smooth operation and a speedy recovery.

 

[cheesegrits]

I'm online now if you want to grab me, 'cheesegrits'. Although I'm still on my first cup of coffee. I had my annual Day of the Dead party last night, so I'm moving slooooow ....

-- hugh

 

[cheesegrits]

Saw your call, can't do voice calls atm, I sent you back a conctact request as an IM.

-- hugh

 

[pastvne]

Hi Hugh:
Glad to see you're back and that the surgery was successful. I'd like to resume our conversation when you are able. If you recall, in our last chat, we observed that queries were actually snappy but something was going 'sideways' with the list rendering. We were seeing several 10's in Joomla debugger. You looked at code and thought you had an idea that would 'cut out a lot of time'.

When you're healed and ready to rock, let me know and we'll connect.

Again, great to hear that you are rebounding!

-BD

 

[cheesegrits]

I had some setbacks with my recovery, wound up needing to have a cervical epidural and fairly massive oral steroid doses to resolve some "severely irritated" nerve roots in the C7/C8 region, which were causing intense post-op pain, which I just couldn't work through.

I'm back at work now, and starting to work through the backlog. Catch me on Skype when you see me, so I can remind myself of where we are at with your issues.

-- hugh

 

[pastvne]

Hi Hugh
I was worried about that and decided to wait for a bit before pressing. So happy to hear you are recovering well. I'm on the road back tomorrow afternoon and will try you then or fri on sky thanks again,

Bd

 

[cheesegrits]

OK - thank you SO much for your patience and understanding. This round of surgery was supposed to have a much faster and easier recovery than my lumbar surgery earlier in the year, literally "back at work within a few days", but it seems I'm one of the handful of folk who encounter some very severe post-op pain which can take months to subside. Fortunately it is now at least under control, and I'm now able to work and sleep, after a couple of weeks of hell.

I should be around online for most of the rest of the week, although as usual my hours may be a little unpredictable, and tending towards the nocturnal.

-- hugh