Page 1 of 1

What is least hard for the server?

Posted: Sat Sep 27, 2008 10:03 am
by seigan
Hi all,
I wonder if some one has any information on with of folowing is easiest for the server?


1) SQL selects VS loops
To take out one big chuck of data from the mySQL database then format it (in one or many arrays for example) using loops. All data in the chunck will be used i one way or the other.
OR
Take out several small chuncks of data from the mySQL database and then push it in the right place of the array using a minimum of loops?


2) Using a SELECT and a UPDATE sql query to add a value in to a speparated string. Using a regular SELECT and a explod in php when using the data
OR
use a INSERT sql query in a relation table (containing 2 int colums) that saves the value instead and use a SELECT INNERJOIN when using the data?


3) Use one big table with many rows
OR
useing many small tables each with lesser rows but a totalt of the same or more then the big table?


4) Using many tables with a sufix in a database (ex: eng_text, fr_text, de_text)
OR
Using diffrent databases on the same server?


Grateful for any tips!

//Seigan

Re: What is least hard for the server?

Posted: Sat Sep 27, 2008 2:13 pm
by califdon
The issue is not what creates a greater or lesser load on the server, but whether your database is structured properly. The answers to at least 3 of your 4 questions are textbook answers about designing relational databases. Depending on the details of each data model, there is a correct answer and several incorrect answers. I recommend that you study relational database design, including normalization.

Re: What is least hard for the server?

Posted: Sat Sep 27, 2008 6:41 pm
by seigan
califdon wrote:The issue is not what creates a greater or lesser load on the server, but whether your database is structured properly. The answers to at least 3 of your 4 questions are textbook answers about designing relational databases. Depending on the details of each data model, there is a correct answer and several incorrect answers. I recommend that you study relational database design, including normalization.
Can you please give some example on the diffrents data models do's and don'ts?

2) I know that accordnig to "First Normal Form" you should never stor a list inside a column because it is hard to manipulate. Sure I get that is hard to manipulate but if I still use it will it take a longer time to load then if I would use a realtion table?

Re: What is least hard for the server?

Posted: Sat Sep 27, 2008 7:09 pm
by califdon
seigan wrote:
califdon wrote:The issue is not what creates a greater or lesser load on the server, but whether your database is structured properly. The answers to at least 3 of your 4 questions are textbook answers about designing relational databases. Depending on the details of each data model, there is a correct answer and several incorrect answers. I recommend that you study relational database design, including normalization.
Can you please give some example on the diffrents data models do's and don'ts?
Not in a forum. I taught database courses in college for years and have written several tutorials (one example is on an Access forum: http://forums.aspfree.com/microsoft-acc ... 08217.html). It's just impossible to supply a quick list of "do's and don'ts". You have to study how relational databases work. It's based on strict mathematics that were developed around 1970 by Dr. E. F. Codd at IBM. As to data modeling, every database is a model of some part of the real world. A database designer's first task is to construct the data model for the database, before considering other issues, such as presentation, optimization, etc. So of course I can't give you examples of data models that would be meaningful to you, because there are literally millions of them.

Re: What is least hard for the server?

Posted: Sat Sep 27, 2008 7:56 pm
by seigan
califdon wrote: So of course I can't give you examples of data models that would be meaningful to you, because there are literally millions of them.
I understand that.

But if we look at nr 4 of my question. If you have a small database structure (page, img, text, text_page_rel) but with alot of conent. Now you whant to extend this site with another language. What would be the lesser loading time do you think: To create a relation table witch also include the language key (according to norm 1-4) but by that adding twice more rows to the table? Or creating a table with a sufix (ex eng_text) and by that holding down the nuber of rows in the table? Or create a whole new database to hold down the total content in the data base? I'm only interested in the diffrence in performance...

Some say the lesser tables the better performance. Some say the lesser sql querys the better performance. Some say the smaller data transfer between php and the database the better performace - even if it means alot more sql calls from php. What is true and what is <span style='color:blue' title='I&#39;m naughty, are you naughty?'>smurf</span> do you think?

Re: What is least hard for the server?

Posted: Sat Sep 27, 2008 8:21 pm
by califdon
seigan wrote:Some say the lesser tables the better performance. Some say the lesser sql querys the better performance. Some say the smaller data transfer between php and the database the better performace - even if it means alot more sql calls from php. What is true and what is <span style='color:blue'>smurf</span> do you think?
I see that I didn't make my point, before. The database structure doesn't depend on performance issues. It is entirely dictated by the relations of the data. Inexperienced developers often think they are gaining something by playing with the schema, but that can lead to bad design, which IMHO is never warranted. For one thing, unless you are dealing with many millions of records, there will be no measurable difference. If you are dealing with that much data, the proper solution will lie in such advanced techniques as segmentation, partitioning, indexing methods, and choosing the best database engine for the task, not in just making arbitrary changes to the schema.

I am not an expert in terms of extremely large, high usage databases, but I am acquainted with several people who are. I don't think any of them would be willing to state a general principle that would answer your questions. It would depend on a whole host of factors, among them: the size of the tables, the number of simultaneous users, the database engine used (MyISAM will be different than INNODB), your indexing scheme, what kind of queries you will run, the version of the server, the specs of the server, etc. These guys make a living analyzing large databases and providing real answers based on a specific case, and often after running performance tests. I wouldn't begin to try to give a general answer.

Re: What is least hard for the server?

Posted: Sun Sep 28, 2008 4:12 am
by seigan
califdon wrote:I wouldn't begin to try to give a general answer.
General answer or not you still give me some answer that is very good - thank you! :)

hmm... I'm currently working on a website that they estimate will have 600k visitors a month and of them maybe 300 k unic. 300 k unic visitors will each leave 12 row with data in a table... It is around 3.6 milion rows coming in the table each month. After 6 month it will be 21.6 milion rows in the table. Aproxemaitly 415 user will simultanius visit the site each hour (around 7 each minute). My question is - do you think I have to consider advanced techniques as segmentation, partitioning, indexing methods?

But if I understand you right your answer only fokus on the database design but what about my folowing questions?:
1) SQL selects VS loops:
To take out one big chuck of data from the mySQL database then format it (in one or many arrays for example) using loops. All data in the chunck will be used i one way or the other or take out several small chuncks of data from the mySQL database and then push it in the right place of the array using a minimum of loops?
Some say the lesser sql querys the better performance. Some say the smaller data transfer between php and the database the better performace - even if it means alot more sql calls from php.
Do you think that the number of calls, sql querys, to the database server from PHP has a huge impact on the performance? Or is it the amount of data that is tranferd that is the important factor? Or doesn't this have ANY significant impact on the performance at all?

In shorth: Many small calls Vs. One big call.

Thanks for the input
/Seigan

Re: What is least hard for the server?

Posted: Sun Sep 28, 2008 1:57 pm
by califdon
As with most choices, there are trade-offs. More calls to the database require more cpu activity; more data collected at one time requires more memory usage. Which is your bigger problem? Probably the only way to know is to make measurements of how much cpu activity and memory you are currently using. It sounds like your application wouldn't really require a whole lot of data to be retrieved at a time, anyway, so my first inclination would be to stop worrying about such issues and just write the application in a logical manner. Then test it and measure the performance. If it's within reason, you're done. If it should prove to have performance issues, then would be the time to explore "tuning" possibilities. If the performance is really bad, you would need to consider employing a database scaling expert (like my friends) to advise you. At least that's the approach that I would recommend. Good luck.

Re: What is least hard for the server?

Posted: Mon Sep 29, 2008 5:59 am
by seigan
Thank you for the information.

It feels kind off bad to use 4 or 5 calls when I can get all infromation in 1. But as you said, there are trade offs. I need to format tha output in a spacial way and this is a cros road. 4-5 small calls will make it easyer for me to put the right output together but as I said it feels kind of bad. One call feels better but it makes it a little harder for me, and require alot more looping, to create the right output. Both solutions are logical in my book but I wonder what path that is the best...

Re: What is least hard for the server?

Posted: Mon Sep 29, 2008 6:28 am
by onion2k
seigan wrote:4) Using many tables with a sufix in a database (ex: eng_text, fr_text, de_text)
OR
Using diffrent databases on the same server?
Definitely not using different databases. Connecting to a database is one of the slowest things you can do. If your data is found a single application (eg one website) then it should definitely be in different tables in a single database.