Why does sql query works faster with many records in odoo?
Because there is a lot going on in write not just a query executing. For example:

Checking Model access right.
Checking record rules access right if there is, and ggis the most heavy step. 
Computing others values that depends on this update if there is. 
Posting mail.thread messages.
Using api.one in some method this way odoo will repeat the same steps for each record and execute the query N times (very very bad thing to do) 

Keep in mind that using plain SQL will not trigger computing values or security checking so don't use it or use it carefully.
I had a problem when I needed to update a lot of records using write example: self.sudo().write({'field': True})
In this case, took me like 10-15 minutes to do it. However, when I tried with a sql query it took me a few seconds. 
My doubts are, Why does that happen?, why is it better to use one or the other? or in which cases should I use one or the other?.