07-29-09, 04:18 #1Registered User
- Join Date
- Jul 2009
SQL process, do sql load all the data in their memory before querying?
hi i have an argument with my boss, he said that the process of sql is that it loads all its resources/data to the memory before the query kicks??which i contracdict as it only loads the queried statement
so for example i have an application that will let people upload images to the database,then the images would be stored to a table that have ac olumn for the actual image as image_full, then the 2nd column is the thumb picture the smaller version of the original image called image_thumb.
he wants me to separate the two column(image_full & image_thumb) to two different tables and i'll just reference them to as foreign. as this will improve the speed and efficiency of the database when it would be use by many people.
as what he have said because these columns would still be loaded to the process/memory even the i have this only query ex. (select image_thumb from table) thus it will also load image_full, which i think do not.
Can any one explain me , is it really appropriate to separate the two columns to different table?are there any articles that would explain this?thanks and im using POSTGRES
07-29-09, 11:46 #2vaguely human
- Join Date
- Jun 2007
I would of thought that storing the images in the user record just increases the amount of storage required for each record. The database cache is a fixed size in most RDBMS so having images in records just reduces the number of records that can be stored in the cache i.e. it's slower and user reporting will be much slower.
Storing images in the database is usually slower than storing them in the file system. If you just store the file name in the user record and then get the html to point to that image file. I suppose you could also store the user images as a standard file named after the user id i.e. 12345.gif which would mean you wouldn't even need to pull this data at all.
You do need to be more careful about cleaning up old images that are no longer required when you store images in a file. If you want information more pertinent to POSTGRES then you should post in the POSTGRES forum.
07-29-09, 16:35 #3:-)
Originally Posted by jigen7
- Join Date
- Jun 2003
- Toronto, Canada
As for the server memory usage, in recent versions of PostgreSQL long data are stored separately from the other data types (see TOAST in the Postgres documentation) and will not be accessed on disk at all, unless referenced in the query, so you should be OK from that point of view as well.
Originally Posted by mike_bike_kite---
"It does not work" is not a valid problem statement.