Store db resultset in a session?

[Steve]

Hi,

I have a web application where there are a number of columns a user can
sort on (price, item etc). The database itself is HUGE (around 6 million
rows) and even with all the indexing it slows down a bit if too many
users start browsing. To be able to sort on a column, the webpage
reloads and does a requery ORDERing on that particular column. How can I
speed this up? Can I store a wholre resultset in a session variable? I
tried doing this but it won't work. Any tip would be appreciated. Thanks,
Steve

[Eric Bohlman]

Steve <nospam@nopes> wrote in news:40********@clarion.carno.net.au:

Hi,

I have a web application where there are a number of columns a user can
sort on (price, item etc). The database itself is HUGE (around 6 million
rows) and even with all the indexing it slows down a bit if too many
users start browsing. To be able to sort on a column, the webpage
reloads and does a requery ORDERing on that particular column. How can I
speed this up? Can I store a wholre resultset in a session variable? I
tried doing this but it won't work. Any tip would be appreciated. Thanks,

Do you have a separate index for each field that people are likely to want
to sort on?

[Jochen Daum]

Hi Steve,

On Mon, 08 Mar 2004 12:38:13 +1100, Steve <nospam@nopes> wrote:

Hi,

I have a web application where there are a number of columns a user can
sort on (price, item etc). The database itself is HUGE (around 6 million
rows) and even with all the indexing it slows down a bit if too many
users start browsing. To be able to sort on a column, the webpage
reloads and does a requery ORDERing on that particular column. How can I
speed this up? Can I store a wholre resultset in a session variable? I
tried doing this but it won't work. Any tip would be appreciated. Thanks,

I think you need a good categorisation for your resultset, as it seems
it returns quite a number of rows. I don't know what this is for, so I
cannot recommend any categories. With good categories you will have
selective Indizes, which will be speedy.

Maybe you can post your data model.

HTH, Jochen
--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/

[Steve]

Eric Bohlman wrote:

Steve <nospam@nopes> wrote in news:40********@clarion.carno.net.au:

Hi,

I have a web application where there are a number of columns a user can
sort on (price, item etc). The database itself is HUGE (around 6 million
rows) and even with all the indexing it slows down a bit if too many
users start browsing. To be able to sort on a column, the webpage
reloads and does a requery ORDERing on that particular column. How can I
speed this up? Can I store a wholre resultset in a session variable? I
tried doing this but it won't work. Any tip would be appreciated. Thanks,

Do you have a separate index for each field that people are likely to want
to sort on?

Yes I do. Almost every single field! And the combination can be very
very bizarre. People can select different shops etc and different price
ranges and this gets pretty complicated at times - not to mention slow.

Steve

[Steve]

Jochen Daum wrote:

Hi Steve,

On Mon, 08 Mar 2004 12:38:13 +1100, Steve <nospam@nopes> wrote:

Hi,

I have a web application where there are a number of columns a user can
sort on (price, item etc). The database itself is HUGE (around 6 million
rows) and even with all the indexing it slows down a bit if too many
users start browsing. To be able to sort on a column, the webpage
reloads and does a requery ORDERing on that particular column. How can I
speed this up? Can I store a wholre resultset in a session variable? I
tried doing this but it won't work. Any tip would be appreciated. Thanks,

I think you need a good categorisation for your resultset, as it seems
it returns quite a number of rows. I don't know what this is for, so I
cannot recommend any categories. With good categories you will have
selective Indizes, which will be speedy.

Maybe you can post your data model.

HTH, Jochen

The problem is that is more of an 'admin' utility than a user-based
tool. We like to monitor on almost every parameter there is and people
can search on anything they want. There are a set of advanced filters
that can be applied and so there's no telling what the user might
filter/search on. Besides, the thing can't be split up into categories
more than it already has :( Is there no other way?
Cheers,
Steve

[Chung Leong]

Are you using TOP or LIMIT in your SQL statement? You don't want your
database server to sort stuff that isn't going to be displayed anyway,
assuming that you're not showing all 6 million in a single page.

Uzytkownik "Steve" <nospam@nopes> napisal w wiadomosci
news:40********@clarion.carno.net.au...

Hi,

I have a web application where there are a number of columns a user can
sort on (price, item etc). The database itself is HUGE (around 6 million
rows) and even with all the indexing it slows down a bit if too many
users start browsing. To be able to sort on a column, the webpage
reloads and does a requery ORDERing on that particular column. How can I
speed this up? Can I store a wholre resultset in a session variable? I
tried doing this but it won't work. Any tip would be appreciated. Thanks,
Steve

[Jochen Daum]

Hi,

On Mon, 08 Mar 2004 16:03:06 +1100, Steve <nospam@nopes> wrote:

Jochen Daum wrote:

Hi Steve,

On Mon, 08 Mar 2004 12:38:13 +1100, Steve <nospam@nopes> wrote:

Hi,

I have a web application where there are a number of columns a user can
sort on (price, item etc). The database itself is HUGE (around 6 million
rows) and even with all the indexing it slows down a bit if too many
users start browsing. To be able to sort on a column, the webpage
reloads and does a requery ORDERing on that particular column. How can I
speed this up? Can I store a wholre resultset in a session variable? I
tried doing this but it won't work. Any tip would be appreciated. Thanks,

I think you need a good categorisation for your resultset, as it seems
it returns quite a number of rows. I don't know what this is for, so I
cannot recommend any categories. With good categories you will have
selective Indizes, which will be speedy.

Maybe you can post your data model.

HTH, Jochen

The problem is that is more of an 'admin' utility than a user-based
tool. We like to monitor on almost every parameter there is and people
can search on anything they want. There are a set of advanced filters
that can be applied and so there's no telling what the user might
filter/search on. Besides, the thing can't be split up into categories
more than it already has :( Is there no other way?

There are typically columns, which have a fixed set of values. These
should be indexed and incorporated into the filters. Its typically
good to use unique indexes, because they can be used in a merge or
hash join.

Also, try to use like 'text%' instead of like '%text%' if possible.

Indexes on date ranges also work well.

Maybe also your data model could be more normalized.

Maybe you want to post your data model...

HTH, Jochen


Cheers,
Steve

--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/