Re: DB Question, speed is the answer!

Replies are listed 'Best First'.
Re^2: DB Question, speed is the answer! by Anonymous Monk on Jan 14, 2005 at 15:08 UTC
Well, I could use place holders, don't know exactly how many rows, but it should be a lot of rows, could be thousands.	[reply]
Re^3: DB Question, speed is the answer! by dragonchild (Archbishop) on Jan 14, 2005 at 15:10 UTC
Ok ... So, you have, say, 5000 rows returned. First off, you really think someone wants to read 5000 rows on a webpage? Secondly, you're going to have to do whatever it is you do within the loop 5000 times. And, since you're constructing strings within that loop, those strings are going to be taking more and more memory. The less memory you have free, the more you have to swap. Once you start swapping memory to disk, you are in a world of hurt. I'd try limiting the number of rows you return to no more than 100 and seeing if it helps. Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply]
Re^4: DB Question, speed is the answer! by Anonymous Monk on Jan 14, 2005 at 15:17 UTC
Yes, I am looking into a big table in this DB, but the data returned is small, around 10 rows, if that was what you were asking before.	[reply]
Re^3: DB Question, speed is the answer! by gellyfish (Monsignor) on Jan 14, 2005 at 15:12 UTC
Have you tried the same query in, say, MS Access (or some other client that can use ODBC) using the same ODBC connection? I think you will find that it will take an equally long time to return all the data. /J\	[reply]
Re^2: DB Question, speed is the answer! by Anonymous Monk on Jan 14, 2005 at 15:24 UTC
Considering the SQL in the code fragment doesn't use anything variable, what use will placeholders have? `print "Hello, world\n";` [download] Oh - use variables. Good gods, man!	[reply] [d/l]
Re^3: DB Question, speed is the answer! by dragonchild (Archbishop) on Jan 14, 2005 at 15:32 UTC
The use of placeholders should not be evaluated on a case-by-case basis, but instead on an overall basis. Understanding where placeholders can safely be avoided requires the same level of knowledge as knowing where 'strict' can be safely avoided. It is telling that nearly every Perl developer who has been lead on at least one project starts every file with `use strict;`. The same goes for placeholders. It is a good habit. The query isn't using anything variable ... today. If you use placeholders, you have future-proofed your query. By not using placeholders, you have introduced a place where bugs can occur and you have to be more vigilant. I don't know about you, but if I can avoid having to be vigilant because a certain class of bugs simply cannot occur, I'm going to. As for your example, if the snippet was part of a larger application, I would definitely say in a code review to use variables. There is no reason not to future-proof it. Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply] [d/l]
Re^4: DB Question, speed is the answer! by thor (Priest) on Jan 15, 2005 at 01:31 UTC
I agree with using placeholders when there are variables in the query, but saying "sure, it's static today...but that doesn't mean it always will be" is like saying "Sure, your print statement doesn't need formatted output today, but someday it might. Therefore, always use printf." Planning for "someday" is an exercise in futility; code for what you need today and worry about tomorrow when it gets here. thor `Feel the white light, the light within Be your own disciple, fan the sparks of will For all of us waiting, your kingdom will come`	[reply]