6 replies to this topic

[chrizmo]

Hi! I have been searching around for sometime about this, but have yet to find what I'm looking for.

I have been making a web-based christmas calender to be used by my co-workers. The user will type in his/her username, and the calender will get her name. The name is in a table with about 350 enteries. The calender administrator will get a list of everyone who has the right answer.

Now I'm wondering what the best way to get the name is. Should I do one small query for every right anser in the list, or make an array of the whole table with real name and match the two. I want to make the site use as little resources as possible.

[David Knight]

What do you mean by answer? answer to what?

[rc69]

I'd query the table for those who have the right answer, then store the corresponding name in an array that will later be sent to the administrator.

One bit of basic SQL optimization is that you want it to return as few things as possible. A list of people with just the right answer is naturally smaller than a list of everybody. And just returning the name field is smaller than returning all fields (i.e. '*').

Of course, if your answer isn't unique (i.e. a sentance answer), you'll have a few more problems. If it is a simple, yes, no, or numerical value, you'll have no problem.

Edited by rc69, 29 November 2007 - 11:21 PM.

[chrizmo]

rc69, on Nov 30 2007, 05:20 AM, said:

I'd query the table for those who have the right answer, then store the corresponding name in an array that will later be sent to the administrator.

One bit of basic SQL optimization is that you want it to return as few things as possible. A list of people with just the right answer is naturally smaller than a list of everybody. And just returning the name field is smaller than returning all fields (i.e. '*').

Of course, if your answer isn't unique (i.e. a sentance answer), you'll have a few more problems. If it is a simple, yes, no, or numerical value, you'll have no problem.

Great, because that's the way I did it. I was scared that doing 50 tiny queries would be worse than fetching the whole table of 300 rows in a array. When you think about it, it's kind of obvious. Thank you!

[rc69]

Actually, 50 tiny queries has potential to be worse. Generally speaking, it is also a good idea to fetch what you need from the database in one shot if possible. Even if one shot means 300 rows... Depending on how you handle everything will determine which choice is better.

In my opinion, i can see a 300 row fetch slowing the site down if you have high traffic on the page running the query. But if it is for individual administration use, it won't matter.

[Wildhoney]

Although in development terms if 10 small queries is quicker than 1 slow query then you should definitely go for the former. You should try and bring everything back, but when you start adding sub-queries to your queries, it can all get a bit complex and rather slow and thus breaking the queries up all together would be more beneficial in terms of lucidity and speed.

[rc69]

That just proves the point that no one optimization works in all cases