Let us consider an example. If you have two boxes say BoxA and BoxB, and there are few balls of different colors, let’s say 5 balls (red, blue, green, yellow, and white) in BoxA, and you want to move one ball, say red ball from BoxA to BoxB. What will be the steps? If you can make a flow chart of it then its good, but if you find it difficult to make a flow chart of it then you need to do some hard work to become a programmer.
I asked this to someone and guess how he solved it. Here is his solution. Take out all balls from BoxA, then pick the red ball, put it in BoxB, and then put all remaining balls back in BoxA.
Fine! it worked. But why would you take out all balls if you can only take out red ball and move it to next box?
Let’s move this to programming side. In the above mentioned scenario we will have two database tables, boxes, and balls. boxes will have boxid as PK, name, and other details. Keep it simple so we don’t get lost in other details. balls table has ballid as PK, name, boxid as FK, and other details. boxes table has two records with 1 and BoxA as its boxid and name. balls table will have 5 records with id between 1 to 5 and red, blue, green, yellow, and white as their names, and all balls will have 1 in boxid field which is FK to boxes table.
Now if we adopt the first solution then it will run following queries (these are pseudo queries not actual SQL queries):
– select all balls where boxid=1 (so we can have a list of all balls before deleting them from database)
– delete from balls where boxid=1
– insert into balls values ballid=1, name=red ball, boxid=2
– insert into balls values
(ballid=2, name=blue ball, boxid=1)
(ballid=3, name=green ball, boxid=1)
(ballid=4, name=yellow ball, boxid=1)
(ballid=5, name=white ball, boxid=1)
Or even worse if we use multiple insert queries. It will run from 4 to 7 queries.
Now let’s see what happens if we just perform operation on the red ball and don’t touch other balls.
– update balls set boxid=2 where ballid=1; /* ball with id 1 is the red ball */
Wow, just one query and we’re done! We saved 3 to 6 queries. Imagine this scenario for a high traffic website or any other application, we can improve the performance with a big difference by just implementing correct logic to solve a problem.