Issue Information
-
#002848
-
2 - Fair
-
Fixed
Issue Confirmations
-
Yes (1)No (0)
0
Originally posted by theultramage
http://www.eathena.w...er&showbug=2848
I have noticed that several places use the following pattern:
1. prepare "INSERT INTO table ( ... ) VALUES (?,?,?)"
2. bind params
3. execute statement
4. if more stuff to be inserted, goto 2
If the number of entries is big, this will cause the server to spam the SQL database with a lot of tiny requests, which is inefficient and introduces processing lag. This is especially bad when the mapserver does it (mapreg code).
The traditional way would be to craft a long VALUES list by escaping the params, sprinf-ing them into a string and appending it to the rest of the query.
I received a suggestion that this same approach is possible with prepared statements - just generate VALUES (?,?,?),(?,?,?),(?,?,?), ... and bind all the params before executing.
This removes some parsing and communication overhead. Also, both of these steps can be done in parallel, since Sql_BindParam only records the param in an in-memory structure and doesn't require the entire query to be prepared first. Plus it's always better than the alternative query spam approach.
This post has been edited by theultramage: Mar 11 2009, 03:07 AM
http://www.eathena.w...er&showbug=2848
I have noticed that several places use the following pattern:
1. prepare "INSERT INTO table ( ... ) VALUES (?,?,?)"
2. bind params
3. execute statement
4. if more stuff to be inserted, goto 2
If the number of entries is big, this will cause the server to spam the SQL database with a lot of tiny requests, which is inefficient and introduces processing lag. This is especially bad when the mapserver does it (mapreg code).
The traditional way would be to craft a long VALUES list by escaping the params, sprinf-ing them into a string and appending it to the rest of the query.
I received a suggestion that this same approach is possible with prepared statements - just generate VALUES (?,?,?),(?,?,?),(?,?,?), ... and bind all the params before executing.
This removes some parsing and communication overhead. Also, both of these steps can be done in parallel, since Sql_BindParam only records the param in an in-memory structure and doesn't require the entire query to be prepared first. Plus it's always better than the alternative query spam approach.
This post has been edited by theultramage: Mar 11 2009, 03:07 AM
Originally posted by Epoque
I don't see anywhere in the source where this issue might occur. It's likely anything to be affected by this has been fixed.
I don't see anywhere in the source where this issue might occur. It's likely anything to be affected by this has been fixed.
Originally posted by Brian
Here is one example:
That for() loop does an insert query for every global_reg_value variable the player has.
Here is one example:
I was going through char/inter.c and noticed this reg function
http://pastebin.com/raw.php?i=A72NNkyt
That for() loop does an insert query for every global_reg_value variable the player has.
Originally posted by MarkZD
As Brian showed it was not fixed, so may any re-open this issue?
Maybe, there're another places where it happens, it should be looked carefully.
As Brian showed it was not fixed, so may any re-open this issue?
Maybe, there're another places where it happens, it should be looked carefully.
Edited by MarkZD, 19 March 2012 - 11:25 AM.
Originally posted by Epoque
That particular for loop should now be fixed in [rev='15729'].
That particular for loop should now be fixed in [rev='15729'].
Originally posted by Brian
awesome possum! Thanks Epoque <3
awesome possum! Thanks Epoque <3
