0

I'm currently writing a stored procedure in MySql, but soon noticed a few shortcomings. First of all I'm using cursor on a large set of data (~50 million rows) and do calculations on those. However, the performance for cursor is not good. Furthermore, I faced some limitations on the calculation because there are no array/list in MySql. I know that I should separate the logic in application layer instead of database, but at this point I need to try to perform calculations in database layer. So my question is, is there other SQL-like languages that are optimized for algorithms and calculations?

Improve this question

2 Answers 2

Reset to default
0

SQL is best when you work within a SELECT statement. You should try to express your calculations in a query. Depending on what you are doing with the arrays, you might be able to re-cast the operation to assign rank with joins, instead. Perhaps SQL can do the job. If you can do this, it should definitely be done in the databse!

Improve this answer
1
  • 1
    As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Dec 20, 2022 at 18:09
0

APL has operators for everything. Example: Matrix inversion. No, it is not SQL-like.

Some non-MySQL SQLs have and "array" datatype.

SELECT SUM(foo) FROM t

will be a lot faster than using a cursor to walk through t one row at a time using a "cursor". Whenever I see someone using a "cursor", I ask them whether they are long-time programmer who is new to SQL. SQL loves to act on lots of rows at once. See if you can change your calculations to work that way.

(If you want help in SQL, please provide the calculations.)

More

data point (mz, intensity) ... that is closest

Is the a 2-dimensional problem? Like finding the nearest coffee shop to where you are? Of so, then I list 5 approaches (in increasing speed) for Find Nearest The first approach is equivalent to checking all 50M 'data points'. The second is more like 10K to 100K. The other three are more like 5-50 rows being checked.

dot product formula

Perhaps it is not quite like the geographic problem; can you show me the dop product?

Improve this answer
3
  • Thanks for your answer. Basically I have a given data point (mz, intensity) and a set of data points in the database to search against (~50 m). I wan to find the data point that is closest to the given data point. To do so I need to loop through the data points table to get each one to compare with the given data point using dot product formula. I have proper index on my table so even if I filter out some data points to search against it is still very slow. So I should avoid looping through a large table using cursor?
    – Tony
    Dec 20, 2022 at 20:59
  • @Tony "So I should avoid looping through a large table using cursor?" - You should avoid looping and cursors in SQL always. There's usually a much more efficient relational approach one can use instead. If you provide example code of what you're doing exactly (including whatever the "dot product formula" is) then we can probably suggest how to solve your problem with SQL efficiently.
    – J.D.
    Dec 21, 2022 at 0:54
  • I added a link to a blog that might help.
    – Rick James
    Dec 21, 2022 at 5:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.