Question of DB resource hit

I see, thanks for that clarification. So, I guess the only difference will be in the eye of the user where it displays the data in manageable 10 rows compared to the 1000 rows. No performance improvement on the server side.

Guess this is where I would implement query caching.....thanks!

You can actually verify what Cobol said by examining the query plan
http://dev.mysql.com/doc/refman/5.0/en/explain.html

.. and in addition, there's the question of whether you're actually using all of the data once you retrieve if from the db.  Pulling 1000 rows across the network, then discarding all but 10, is less efficient. But simply because it's a waste of resources.  

Our posts crossed.

> So, I guess the only difference will be in the eye of the user
> No performance improvement on the server side.

Not strictly true. The db query plan is only one side of it. There's also the cost of constructing those results in server memory (ie query object).  Obviously 1000 records requires more resources than only 10.  But since it's only a 1000 rows, performance concerns are probably a bit premature.  

Hi _agx_, nice to see you again :)

Maybe I got my question wrong....or maybe it just came across wrong. Sorry.

What I meant by 'display' the recent 10 was a condition I placed inside the query itself. In the example below the query itself without the last condition would pull 1000 lines or more.

(Query with limit)
Select *
FROM tObjects
Where ...
ORDER by dListDateTime DESC limit 10

(Query without limit)
Select *
FROM tObjects
Where ...

Would there by a difference in DB resource use?

Thanks!

Hi again :)

Right. What Cobol said is true about the db. But since I know you're using a web app, that's just one side of the picture. The results from the db are converted into a query object by the CF server. So obviously more records == more resources.  So obviously 1000 records will take more to store than a 10 record query.  So if you don't need all 1000 records, there's no point retrieving them in the 1st place.  It's like buying 5 gallons of milk from the store, when you only need 1. The money spent on the other 4 just goes to waste.  

In short: if you're just asking about the db side of things, Cobol's right. There's little difference.  

When you said "server performance", that implied something broader ie The overall communication between CF and db from start to finish, not just the db query :)  But again, in terms of the db side only, there's not too much difference.

_Agx_"  So if you don't need all 1000 records, there's no point retrieving them in the 1st place.  It's like buying 5 gallons of milk from the store, when you only need 1. The money spent on the other 4 just goes to waste.  

Thanks.

But wow would one be able to optimally just retrieve the recent 10 records based on timestamp from a table that has 1000 lines of data? The way I understand, the only way to just retrieve a limited resualt set would be to use the 'limit' condition.

sorry the above sentence had a typo..."wow" should be "how".

But wow would one be able to optimally just retrieve the recent 10 records based on timestamp from a table that has 1000 lines of data

That is why I mentioned an index in my response.  With an index a sort of the thousand rows would not be necessary., but the gain for this small a data set would still not be large unless the server is very old and inefficient.

Thanks for the A. :^)


Cd&

Got it. Both of you did help me decide to use the caching method on top of the query for what I want to achieve. Thanks very much.