What is this called?

[codecutie]

Ok, say you have a database with 16000 records in it, but you only want to
call out say 2000 records at a time as the search/query is performed, then
store the first 2000 in a session and then retrieve the next 2000 etc etc as
a way to minimize server strain?

(I'm tasked to do this and ....)
1. don't know what this is called to google it...partioning results/data ???

2. Is there a better way to deal with retrieval of large amounts of data
from a large table without choking the server.....

3. Is it possible at all....

Basically I need to know what it is I'm looking to do, it's not getting
explained in an understandable way here....which makes google useless....

I love my job, I love my job....I love...my job...monday Monday monday



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[Stuart-47]

2009/7/6 Miller, Terion <tmiller@...>:

> Ok, say you have a database with 16000 records in it, but you only want to
> call out say 2000 records at a time as the search/query is performed, then
> store the first 2000 in a session and then retrieve the next 2000 etc etc as
> a way to minimize server strain?
>
> (I'm tasked to do this and ....)
> 1. don't know what this is called to google it...partioning results/data ???
>
> 2. Is there a better way to deal with retrieval of large amounts of data
> from a large table without choking the server.....
>
> 3. Is it possible at all....
>
> Basically I need to know what it is I'm looking to do, it's not getting
> explained in an understandable way here....which makes google useless....

Generally known as paging, and every database server I'm aware of supports it.

MySQL for example...

select * from table limit <page>,<numrows>

I would strongly recommend against storing any large dataset in a
session since it will likely be as expensive to load it in as it would
be to do the database query again.

Based on the way you've phrased your question and the 2000 per page
I'm guessing you're not doing this for display purposes. If you're
wanting to lighten the load on the server while you process the 16,000
rows you might want to look into mysql_unbuffered_query which requires
a lot less memory than mysql_query. See the manual for details.

-Stuart

--
http://stut.net/

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[Shawn McKenzie]

Miller, Terion wrote:

> Ok, say you have a database with 16000 records in it, but you only want to
> call out say 2000 records at a time as the search/query is performed, then
> store the first 2000 in a session and then retrieve the next 2000 etc etc as
> a way to minimize server strain?
>
> (I'm tasked to do this and ....)
> 1. don't know what this is called to google it...partioning results/data ???
>
> 2. Is there a better way to deal with retrieval of large amounts of data
> from a large table without choking the server.....
>
> 3. Is it possible at all....
>
> Basically I need to know what it is I'm looking to do, it's not getting
> explained in an understandable way here....which makes google useless....
>
> I love my job, I love my job....I love...my job...monday Monday monday
>
>

It's called a bad idea.  Instead of doing one query to get your data,
you're going to do eight?

--
Thanks!
-Shawn
http://www.spidean.com

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[Daniel Brown-7]

On Mon, Jul 6, 2009 at 10:48, Miller,
Terion<tmiller@...> wrote:
> Ok, say you have a database with 16000 records in it, but you only want to
> call out say 2000 records at a time as the search/query is performed, then
> store the first 2000 in a session and then retrieve the next 2000 etc etc as
> a way to minimize server strain?
>
> (I'm tasked to do this and ....)
> 1. don't know what this is called to google it...partioning results/data ???

    As Stuart mentioned, it's known as "paging."

> 2. Is there a better way to deal with retrieval of large amounts of data
> from a large table without choking the server.....

    Well, it depends.  You mentioned only that it's a database.  Is it
an SQL database?  If so, what's the engine (MySQL, PostgreSQL,
Informix, SQLite, MSSQL, etc.)?  If not, what's the format?

    Also, how frequently will this be called, how large is each row of
data (in bytes or kilobytes), and what other things are running on the
same server at the same time?  Is it a production web server or
internal intranet?  Is it a shared web server?

    As you see, there are a ton of other factors and variables involved.  ;-P

> 3. Is it possible at all....

    Absolutely.  Through PHP, al(most al)l things are possible.

> Basically I need to know what it is I'm looking to do, it's not getting
> explained in an understandable way here....which makes google useless....

    We all have days like that, Teri.  I have ~365.25 of them each year

--
</Daniel P. Brown>
daniel.brown@... || danbrown@...
http://www.parasane.net/ || http://www.pilotpig.net/
Check out our great hosting and dedicated server deals at
http://twitter.com/pilotpig

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[Wolf-6]

---- "Miller wrote:

> Ok, say you have a database with 16000 records in it, but you only want to
> call out say 2000 records at a time as the search/query is performed, then
> store the first 2000 in a session and then retrieve the next 2000 etc etc as
> a way to minimize server strain?
>
> (I'm tasked to do this and ....)
> 1. don't know what this is called to google it...partioning results/data ???
>
> 2. Is there a better way to deal with retrieval of large amounts of data
> from a large table without choking the server.....
>
> 3. Is it possible at all....
>
> Basically I need to know what it is I'm looking to do, it's not getting
> explained in an understandable way here....which makes google useless....
>

Basically, go smack whomever told you to load all that stuff into a session.  It's a paging query that you want to do, but I'd not recommend doing it to store it in a session.

You can store all that stuff into a session, but you risk putting a greater load on the users session then you would be putting on the database server.

If you are running MySQL, go get a 486, put Fedora on it and use it for the heavy queries.  

HTH,
Wolf

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[codecutie]

On 7/6/09 10:07 AM, "Daniel Brown" <danbrown@...> wrote:

On Mon, Jul 6, 2009 at 10:48, Miller,
Terion<tmiller@...> wrote:
> Ok, say you have a database with 16000 records in it, but you only want to
> call out say 2000 records at a time as the search/query is performed, then
> store the first 2000 in a session and then retrieve the next 2000 etc etc as
> a way to minimize server strain?
>
> (I'm tasked to do this and ....)
> 1. don't know what this is called to google it...partioning results/data ???

    As Stuart mentioned, it's known as "paging."

> 2. Is there a better way to deal with retrieval of large amounts of data
> from a large table without choking the server.....

    Well, it depends.  You mentioned only that it's a database.  Is it
an SQL database?  If so, what's the engine (MySQL, PostgreSQL,
Informix, SQLite, MSSQL, etc.)?  If not, what's the format?

    Also, how frequently will this be called, how large is each row of
data (in bytes or kilobytes), and what other things are running on the
same server at the same time?  Is it a production web server or
internal intranet?  Is it a shared web server?

    As you see, there are a ton of other factors and variables involved.  ;-P

> 3. Is it possible at all....

    Absolutely.  Through PHP, al(most al)l things are possible.

> Basically I need to know what it is I'm looking to do, it's not getting
> explained in an understandable way here....which makes google useless....

    We all have days like that, Teri.  I have ~365.25 of them each year

--
</Daniel P. Brown>
daniel.brown@... || danbrown@...
http://www.parasane.net/ || http://www.pilotpig.net/
Check out our great hosting and dedicated server deals at
http://twitter.com/pilotpig


LOL Daniel I myself have at least 365 days of them too....

It seems like Pagination except I already have that in place for all records, so maybe I'm just looking for a way to page the search results...
Boss kept referring to "partitioning results"




--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[Daniel Brown-7]

On Mon, Jul 6, 2009 at 11:15, Miller,
Terion<tmiller@...> wrote:
>
> It seems like Pagination except I already have that in place for all records, so maybe I'm just looking for a way to page the search results...
> Boss kept referring to "partitioning results"

    By definition, yes, but if you use the term 'partition' in
industry terminology - indeed, in SQL commands - you'll wind up with a
different result.

    If you haven't yet come across it, look up 'mysql range
partitioning' on Google.

--
</Daniel P. Brown>
daniel.brown@... || danbrown@...
http://www.parasane.net/ || http://www.pilotpig.net/
Check out our great hosting and dedicated server deals at
http://twitter.com/pilotpig

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[Daevid Vincent]

> -----Original Message-----
> From: Wolf [mailto:lonewolf@...]
> Sent: Monday, July 06, 2009 8:10 AM
>
> If you are running MySQL, go get a 486, put Fedora on it and
> use it for the heavy queries.  

You didn't seriously just tell someone to use an ancient-ass __80486__ PC
for their "heavy database queries" did you? Aside from the fact that machine
is like 20 years old and not only would hardware be difficult to find, setup
and maintain, it's bound to die at any moment. You're talking ISA (maybe a
PCI if you're lucky), IDE drives, slow RAM that probably maxes at 512MB or
something... No USB ports, so have fun finding an old mouse/keyboard.

A computer with vastly superior technology, reliability and performance can
be had for a hundred bucks and probably even free. I just gave away a 1Ghz
Celleron the other day that was collecting dust. I wouldn't even use THAT as
my database server with hardware as dirt cheap as it is...

Dude.

Save yourself the headaches and just get a modern, NEW barebones PC:

http://www.pricewatch.com/barebones_computers/
http://www.pricewatch.com/computer_systems_no_os/

and throw your favorite Linux distro on it
(I'm not touching that holy war with a 10' eth0 cord)

Most all distros come with LAMP stacks as an option and you're off and
running in about an hour.

:D:


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[Daevid Vincent]

> -----Original Message-----
> From: Wolf [mailto:lonewolf@...]
> Sent: Monday, July 06, 2009 8:10 AM
>
> Basically, go smack whomever told you to load all that stuff
> into a session.  It's a paging query that you want to do, but
> I'd not recommend doing it to store it in a session.
>
> You can store all that stuff into a session, but you risk
> putting a greater load on the users session then you would be
> putting on the database server.

http://dev.mysql.com/doc/refman/5.0/en/select.html

You want to use "limit".

As for storing all the results, there most likely isn't a reason to, however
if you must do it that way (perhaps you have a << and >> button or something
to step through), then I suggest you save the __row ID's only__ in a
$_SESSION array variable and do a SELECT via the ID (and "LIMIT 1" can't
hurt either in case you're storing a SKU_ID or some other unique key but
it's not a primary key) as you're stepping. This way your bulky query is
only done once. This is a more advanced technique however and may be
confusing to you if you don't know what you're doing.

You can also pull all these items faster on subsequent pages by doing an
"WHERE item.id IN ($myarray)" type of deal (pseudo code of course).


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

[Michael A. Peters]

>
> and throw your favorite Linux distro on it
> (I'm not touching that holy war with a 10' eth0 cord)

I'll touch it.
It shouldn't be Fedora - Fedora has too short of a lifetime before major
version update is necessary to get patches. The main advantages of
Fedora are how new and shiny the desktop is, but a box just for SQL
isn't going to care about new and shiny gnome with the pretty widgets.

Use RHEL/CentOS for this if you like RPM.

Just replace the archaic php 5.1.x and yer good to go ;)

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php