1

I've got approximately 1000 rows (4 fields) in a MySQL database. I know for a fact that the data in the database is not going to change very often (they are GPS coordinates). Is it better for me to call this information from the database every time the appropriate script is loaded, or would it be better for me to "hard code" the data into the script, and when I do make a change to the database, simply update the hard coded data too?

I'm wondering if this improves performance, but part of me thinks this may not be best practice.

Thanks

Lars
  • 7,908
  • 11
  • 52
  • 70
  • How much flexibility are you looking for? Is it likely that more GPS coordinates will be added later or some removed? – Simon Forsberg Jan 31 '13 at 00:26
  • It's possible that changes and additions will occur, perhaps once or twice a month. But as I have the data in a database locally, I could easily update the relevant script with new data and upload a new version. – Lars Jan 31 '13 at 00:28
  • Have you measured how long time the SQL query takes? How often is it executed? (i.e. how often is the script used?) – Simon Forsberg Jan 31 '13 at 00:47
  • If I run the query through phpMyAdmin it takes 0.0007 seconds, though this is all local at the minute so I guess that would be longer on the live server. I'm expecting the script to run somewhere around 5000 times a day when it finally does go live. – Lars Jan 31 '13 at 01:03
  • ...a similar query I've got on a live server takes ~ 0.0063 seconds. – Lars Jan 31 '13 at 01:13

2 Answers2

2

Hard coding coordinates into a script is not a good idea.
I would read the 1000 coordinates at start into an array, either from SQL DB or from a File.

But do that reading only once at start up, and not at each caluclation step.

AlexWien
  • 28,470
  • 6
  • 53
  • 83
  • I don't think you have answered the question, the question is about hardcoding the values or using the database. How would you read the 1000 coordinates into an array without querying the SQL? – Simon Forsberg Jan 31 '13 at 18:21
  • Updated: read the cooridnates from a file, or read it from sql once, not for each calculation query the positions again. – AlexWien Jan 31 '13 at 18:30
  • Yes, that might be a solution. If you improve your answer then I might upvote it. – Simon Forsberg Jan 31 '13 at 18:33
  • Updated (addional chardacter to fill) – AlexWien Jan 31 '13 at 18:34
  • Again you mention SQL in your answer, and no mention of a file. Also, @Lars never indicated that he actually queries the SQL more than once per script execution. – Simon Forsberg Jan 31 '13 at 18:50
1

Given the fact that changes might occur once or twice per month, and the fact that 0.0063 seconds isn't very much (at least not from my point of view, if it would be a matter of life or death or very important Wall Street stock data that would be another matter), my recommendation is that you use the SQL. Of course, as long as you perform the query only once per script execution.

Indeed, it could improve performance with some milliseconds if you hard-code the data into your script. But ask yourself the question: How much extra work is needed to maintain the hard-corded data? If you really want to be sure, then make a version of the script where you hard-code the data and execute the script 1000 times and measure the time difference. (However, just making this test would probably take more time than it would save...)

If your script is run 5000 times per day and each time the SQL takes an extra 0.01 seconds compared to having hard-coded values, that's a sum of 50 seconds per day in total for your users. However, for each user they will most likely not notice any difference.

Simon Forsberg
  • 13,086
  • 10
  • 64
  • 108