Hi, I have a task to update a few million records every 15 min this records have different value and less then a 100 tousends are new my process a bit slow and I have no Idea what to do, any suggestion may help thanks by the way I am using last version of phalcon 3.4.2, php 7.2 and SQL server
HOW slow? Updating a complex schema may take a while even if you update only a few hundred rows.
What type of SQL? MSSQL, MySQL, PGSQL?
What do you use to issue the query? Raw SQL? Phalcon ORM builder?
What is the nature of the update? Is it just an UPDATE foo='bar' WHERE baz='bax' or something more complex?
In any case, this will be an SQL optimization problem, since the only way Phalcon can detriment performance is if you use it's ORM.
- To much slow
- I must use MSSQL
- I use phalcon ORM Builder
- the nature of update is UPDATE foo='bar' where baz='bax' and I have millions of this records
- should I use a single query of update with a loop or there is a way to send a multiple row update to MSSQL with RAW SQL?
AND if I use CLI instead of CGI will have a difference?? Or I could use Ptheards?
Even if you rewrite it to use raw SQL instead of Phalcon ORM, the performance difference is minimal (a few more method calls on PHP side).
Your bottleneck will be the MSSQL server, you should search for a solution there.
CLI/CGI will yield no difference in performance. Pthreads would only help if your SQL server can accept the throughput, but even if it could, refactoring PHP to use threads is a pain in the arse.
I would poke around the SQL forums, looking at topics like this:
Seems like you rather have a conception challenge/problem, and not a programming one. You state that you must carry this task every 15 minutes... seems like a cron task.
So DB is your primary concern here, not middle layer, no matter what you use.
I searched for answer and and got to know about the MSSQL server OLTP, it seems it is the answer to my problem I am going to test it with PHP and PHALCON for result, in a simple test of inserting 100,000 rows with simple MSSQL I should wiat minutes to task be complete but with OLTP it been done less then 10 seconds. but for using OLTP, I should use stored procedure in MSSQL, after testing it I let you know about the result
thanks for advice so far, any other suggestion may help here
建议你使用原生 SQL 的语句来进行修改处理
例如 $this->db->execute("UPDATE table");
建议你使用原生 SQL 的语句来进行修改处理
例如 $this->db->execute("UPDATE table");
keep your note in mind to use row sql but please type english, I used google translate to understand you
thanks everyone, we I change the MSSQL from diskbase to OLTP and use json fill for bulk update and insert with help of sqlsrv without phalcon orm, it recude the time process 5 times