search

nnjeim / world

A Laravel package which provides a list of the countries, states, cities, currencies, timezones and languages.
MIT License
773 stars 108 forks source link

Database query (db:seed) took too long #54

Closed gholamrezaeipt closed 1 year ago

gholamrezaeipt commented 1 year ago

Describe the bug I installed and configured the package as described in the documentation, and everything seems to be working properly. However, I've noticed that the queries are taking a long time to execute. For example, the seed operation takes 25,087,113 ms, which is much too high.

Upon checking the MySQL process while the seed operation is running, I found that the package is creating cities for each country using single insert queries instead of bulk queries. This means that the package is running approximately 150,000 insert queries, which corresponds to the number of rows in the city table.

To improve performance, I suggest modifying the package to use bulk insert queries instead of single insert queries when creating cities. This should significantly reduce the number of queries executed and improve the overall performance of the package.

To Reproduce I describe the problem above.

Desktop (please complete the following information):

nnjeim commented 1 year ago

@gholamrezaeipt thank you for your input, would you care to contribute by testing your suggestion and potentially submit a PR?

gholamrezaeipt commented 1 year ago

@nnjeim Yes, i need to review all codes and check them then submit PR. this may take a time.

mrmmg commented 1 year ago

@nnjeim Hi First, I should say that the @gholamrezaeipt is my GitHub account in the company where I work for them. So, from now I'm working on the bulk query issue. i'm changing a lot of in your codes and everything is dirty right now but working!

the first result of dirty codes that i fixed is

Seeding end

Nnjeim\World\Actions\SeedAction ............................................................................................... 229,569.94 ms DONE

Compare to the first try (first message on this issue) it's 109X faster with fewer queries!

you can close this issue, I'm still working on this and send a PR when the changes are ready!

nnjeim commented 1 year ago

@nnjeim Hi First, I should say that the @gholamrezaeipt is my GitHub account in the company where I work for them. So, from now I'm working on the bulk query issue. i'm changing a lot of in your codes and everything is dirty right now but working!

the first result of dirty codes that i fixed is

Seeding end

Nnjeim\World\Actions\SeedAction ............................................................................................... 229,569.94 ms DONE

Compare to the first try (first message on this issue) it's 109X faster with fewer queries!

you can close this issue, I'm still working on this and send a PR when the changes are ready!

Thanks a lot please merge your PR.