Optimize query and python loop code

[wz18207]

https://github.com/DragonMinded/bemaniutils/blob/trunk/bemani/data/mysql/music.py#L734-L734

This part seems to have repeated queries, and the efficiency is not very high. Is there amyway to speed up and save the request time.

https://github.com/DragonMinded/bemaniutils/blob/trunk/bemani/data/mysql/music.py#L804-L804

By the way, it seems a waste of time to fill the query results directly into the list in the form of a for loop and cursor.fetchall(). I dont know how to fix it to improve its efficiency.

Thanks

[DragonMinded]

Those for loops could definitely be rewritten as list comprehensions for sure, it would save a lot of time appending to the list. I'm not sure what you mean by repeated queries however?

[DragonMinded]

Ah, I definitely see one optimization with JOINs instead of inner SQL statements. I can noodle on things for a little bit.

[DragonMinded]

64aeeaf drops the unnecessary temporary list creation and calls to fetchall() in favor of comprehensions. I don't imagine it will affect much but it is technically faster and could be noticeable with networks that have a lot of scores or users.

[wz18207]

return [
--
  (
  UserID(result["userid"]),
  Score(
  result["scorekey"],
  result["songid"],
  result["chart"],
  result["points"],
  result["timestamp"],
  result["update"],
  result["lid"],
  result["plays"],
   self.deserialize(result["data"]),
  ),
   )
   for result in cursor
   ]

I tried to take down like this in get_all_records. However when i test in 1 millions data generate in sql. This part would take 7s in response. I mean is there anyway to improve the speed when faced the large data circumstances. list comprehensions may slow the time as the data over 10k. en im not sure about that. thanks.

edit: is there any possible to add pandas.

    results = cursor
    df = pd.DataFrame(results, columns=["userid", "scorekey", "songid", "chart", "points", "timestamp", "update", "lid", "plays", "data"])
    df["userid"] = df["userid"].apply(UserID)
    df[["scorekey", "songid", "chart", "points", "timestamp", "update", "lid", "plays"]] = df[["scorekey", "songid", "chart", "points", "timestamp", "update", "lid", "plays"]].apply(lambda x: Score(*x), axis=1)
    df["data"] = df["data"].apply(self.deserialize)
    return df.to_records(index=False)

[DragonMinded]

No, that's borderline incomprehensible for the same result.

[DragonMinded]

I'm not sure your test cases are particularly valid. How can you have a million rows when this is looking up records and you will have at most the number of songs * charts for a particular game?

[wz18207]

I test it in gitadora. Each song has 12 charts and highvoltage has almost 3000 songs. I tried to get all records from db. Im afraid if the data would get slow(long time requests) when combine all the scores in loop lists(12*4000=48000). Anyway it may get slow when i try to calculate each songs clear rates(I'm assuming a total of 1 million data, which is probably the most). also send rival information in gfdm its really slow(need to load all rival profile from db). i think thereis a way to optimize the request speed on it however i dont know how do that based on my knowledged.

[wz18207]

Well ehh what about create the top scores and the music clear rate tables. Ehh i think this would save the times when fetch whole scores from the sql.(i mean saving request time in the frontend/some games backend.) And when doing update_score, these two tables update it recently.