Slow sql queries

[karen]

I apologize if this is a repeat. I am wondering what the solution is to all the questions about the SQL queries being too long. I did not get a definitive answer after perusing most of these. I have run p3 Performance Profiler plugin which indicates that the Events Calendar is taking over 2.9 seconds to load. What is the answer? Shall I disable the widget and only show the Events page? Shall I show the DAILY list only instead of the monthly list? It is really bogging down my site. My site is running on a VPS.
thanks

[George]

Hey @karen,

I’m sorry to hear that you weren’t satisfied with the response you received in another thread here on this topic. Could you link directly to that thread, and any other SQL query-related ones that you’re referring to here? I’ll take a closer look and see what’s going on here.

Your questions about having the widget loaded, or specific views, definitely have merit but the biggest thing I’m curious about here is how many events do you have on your site?

That can inform a lot of things here – let us know!

Thank you,
George

[karen]

Referral question

Slow page load time due to SQL query

and yeah.. there are over 29,000 entries .. half of which I did not do. many are recurring but this one is interesting.
I have one entry which has created 16,882 events for one recurring event until November 5, 2015. How can that be?
How do I clean it up? it overloads the server when I try to delete it.

[karen]

I still have 16,000 entries that I need to delete. I have no idea where they came from. I cannot delete 20 at a time and if I try to do more I get the dreaded “GURU meditation” . Can you help me delete these errant entries?

[karen]

This reply is private.

[George]

Hey Karen,

Unfortunately, even if you delete 16,000 events, from a starting place of more than 29,000 events this still leaves you with more than 13,000 events.

I do not have a bit of SQL code or something to share with you at this time that would fix this or induce some significant performance improvement. I’m sorry to disappoint! We are working hard to improve the ability of The Events Calendar and its add-ons to support large sets of events like this, but the inherent limitations of The Events Calendar and your server leave very few options here. 🙁

I’d love for there to be much better outcome in this thread than in the one you linked to above, but many of the things Barry pointed out are still true and performance problems in codebases are very hard to change, in general.

Let me know if you have any further thoughts on this matter or if there’s anything else I can help with.

Thank you,
George

[karen]

George, While that may be true, I still need to get rid of the 16,000 events. I am having to do that 20 entries at a time which will take me over 13 hours of constant work if I do it with the GUI. Either you can provide me with a query that I can run or a name of a consultant who is willing to run this query to save me over 14 hours of work. thanks.

[George]

Hey Karen,

Sure thing, apologies for not realizing the depth of that issue for you! There are a few solutions here:

1. In the admin list view for your events, click the “Screen options” tab in the upper-right of the admin page. An option for how many events should show on the page will appear – increase this option as high as you server will allow (it should at least be 100 or 200, though it might take a few moments to load!). This way you can work with much more than 20 events at a time.

2. If that still is not enough of an advantage, you could use a plugin to do this. There are many bulk-deletion plugins out there. We do not endorse any of them, and if you use a third-party plugin it is at your own risk, we do not officially support any plugin of this type. This plugin is the most highly-rated of this type, I have not used it but it has many great reviews → https://wordpress.org/plugins/bulk-delete/. Just an example.

3. If neither option #1 nor #2 are appealing here, you can indeed hire someone to come up with a custom solution. We have a list of well-recommended developers here that you can check out, and can contact at any time to see if they can help with your issues here → https://theeventscalendar.com/knowledgebase/find-a-customizer/

Cheers,
George

[karen]

thanks George.
#1 overloads my server.
I will try #2 and let you know.
#3 will be costly and the question still remains.. how did this happen?
there is a vulnerability somewhere.
k.

[George]

Hey Karen,

By “Vulnerability”, do you mean that you found a security vulnerability specifically somewhere in the code? If so, please let us know in more detail so we can patch it immediately.

Otherwise, to address item #3 here, what things really come down to is that you have 29,000 entries.

You admit in #1 that your server crashes when even trying to load 100 or 200 events, so the problem may be with your site’s infrastructure more so than The Events Calendar itself.

[karen]

HI George.
Sorry, by Vulnerablity, I meant that Somehow the program was able to insert 16,000 duplicate events. I consider that a bug.

2. I meant no such thing. I meant that SQL overloads when trying to delete more than 20 events at a time, presumably because of the Query that it is running is too long. I have php memory configured to 256m.
thanks

[George]

The duplication error is a tricky thing because we’re not able to reliably reproduce that behavior. However, we are constantly ironing out bugs and if you stay tuned to plugin updates you may indeed see something address this in a Changelog in an upcoming update.

As for the SQL overloading, this is indeed what I’m referring to, and is again something that I cannot reproduce. I need to be clear that there’s no snark intended in my messages here! It’s just a bit of an impassable barrier with some of these things because we cannot recreate the problems directly.

Your frustration is justified and I’m very sorry about all the inconvenience this stuff has caused you. We are working hard on performance improvements, but admit that there are some things left to be desired with The Events Calendar’s ability to handle large amounts of events.

I think the combination of The Events Calendar’s mediocre ability to handle many thousands of events is combining with some genuine server / web-hosting inefficiencies on your site that are creating all of your problems here.

Thank you,
George

[karen]

Here is what the host site says: It would appear I am stuck in the middle.

In regards to the 503 error you’re seeing are caused by a plugin you’re using; this plugin is making incredibly inefficient database queries and those queries in conjuction with the sheer volume of Events you’re trying to make is causing the execution time for this particular action to skyrocket. Even if we do increase execution, it’s unlikely that the query would complete in any reasonable amount of time. We recommend that you delete them in small batches until the number is significantly smaller. I was able to delete 20 at a time in my testing.

[George]

Hey Karen,

Thanks for the update. I’ve responded to your email as well on this topic, and can echo the same sentiments here that I did there: there is indeed a combination of problems. It is not just that our queries are “incredibly” inefficient – they are somewhat inefficient, yes, but regardless of our query logic the sheer number of events you have and your web host’s performance are limiting factors.

We are working hard to improve our queries regardless and, as we coordinated over email, are very appreciative of both your patience here and your willingness to share your Database data to improve our ability to test things.

Stay tuned to things over email – we can keep our conversation there since we might be discussing data from your site and sharing database dumps and/or pre-release versions of code for you to test. In other words, things that are best handled privately over email and not in the forums here.

So, with that in mind, I will close up this thread for now. Thank you for your patience and diligence here, I look forward to continuing the conversation over email.

Cheers,
George

[Author]

Posts