Learn More
Resend Email
4
zarathustra
4y

How do you diagnose speed issues?

I've been lumped with looking after a legacy app.

It connects to our ERP system to handle raising invoices etc. And in June is developed slowness, which we sort of fixed by making one section load later (the SQL was a horrible view in a view in a view).

We then upgraded our ERP software, and the SQL issues are resolved (had to upgrade SQL server etc at the same time) and now the legacy app is running really slowly.

I know that it is when it loops through a data set to set column values etc.

A particular project has 1900 time transactions and takes upto 2 minutes to load.

This part of the program hasn't been changed in over a year, and has only started running slowly since the upgrade.

Are there any good way I can investigate and diagnose exactly why it has suddenly started running slowly?

question
Favorite
Ranter
Comments
  • 0
    4y
    Don't really have much experience in this field, but have you tried to EXPLAIN queries?

    We once had an issue where we were lazy and queried SELECT * instead of SELECT field1, field2 in our client. But it was still fast. However, IIRC, the vendor added some binary fields in a later update, which made the whole thing brutally slow, of course.
  • 0
    4y
    Well do you take it multiple times a week?
  • 0
    4y
    Check your indexes, as previously suggested use EXPLAIN, profile the consumer to see which queries are causing it and take a look at tools like newrelic, they have some analysis and stuff in it that can be pretty useful.
  • 0
    4y
    Developed an invoicing tool once and there was a missing index on the state of the invoice which was queried a lot.
    Got the query time down from 10 seconds to about 20 milliseconds.

    The amount of data was the cause. It worked fine for 2 years and suddenly it became incredibly slow.
  • 0
    4y
    Thanks all. I'm pretty confident the SQL is ok, one project that takes nearly 2 minutes to load 1900 rows of time through the application, but the query in SQL completes in less than a second. But I will take another look at the indexes etc.

    Unfortunately we don't have anything like New relic etc.
  • 0
    4y
    How are you calling into the SQL? Are you holding the result set cached? Are you pulling the entire datasets just to grab a subset after (e.g. pagination in code but not in SQL)? Are you properly closing your connections? Had a similar problem years ago and it turned out we were not caching the data and kept calling full result sets and paginating in code. This would not have been a problem if we were caching the data, but we weren't. We were querying the entire data set each time we changed pages.
  • 0
    4y
    I only read the first sentence, but you keep the bus over 50 mph or it explodes. That’s how Keanu handled his speed issue anyways
  • 0
    4y
    Thanks all, did further investigations, indexes etc were all good, but I'd decided to do some comparisons against a previous version of the program running against the old dB server.

    It looks like upgrading SQL fixed some other issues with slow running queries, so now we are left with the fact that the way the program is written isn't very efficient
devRant © 2021 Hexical Labs LLC
Privacy Policy  |  Terms of Service
Add Comment