> I'm looking for thoughts/advice on how to build the fastest search +
(paged)
> results-page in an .NET + SQL Server 2000 environment. I hope this is the
> proper group for this, otherwise i appologize for the spam.
>
> I have one table with around 100.000 records containing some text fields.
> What i need to do is free-text search through these records, and return a
> paged result to the client. The user should be able to select which field
to
> sort by. I'm estimating 10.000 concurrent users on the system.
>
> My initial approach to this would be to do the initial search once
> (selecting records using a full-text indexing), and throw the result into
a
> TEMP table. Each paged result the user requests would then be taken from
> this table, using DataAdapter.Fill(DataSet, startRecord, numberOfRecords)
>
> I am, however, not sure if this is the right approach. Not using a TEMP
> table would probably increase performance on the first results page, but I
> would assume the TEMP table helps decrease system load a lot on subsequent
> page requests. Also, I'm not completely sure if DataAdapter.Fill with
> startrecord, numberofrecords does only transfers the selected records to
the
> web server, or if the entire resultset is transfered to the webserver, and
> the dataset THEN populated with the selected records.
>
> Does anyone have any thoughts on the best way to implement a system like
> this?
>
> Cheers,
>
> Thomas
>
>

> I'm looking for thoughts/advice on how to build the fastest search +
(paged)
> results-page in an .NET + SQL Server 2000 environment. I hope this is the
> proper group for this, otherwise i appologize for the spam.
>
> I have one table with around 100.000 records containing some text fields.
> What i need to do is free-text search through these records, and return a
> paged result to the client. The user should be able to select which field
to
> sort by. I'm estimating 10.000 concurrent users on the system.
>
> My initial approach to this would be to do the initial search once
> (selecting records using a full-text indexing), and throw the result into
a
> TEMP table. Each paged result the user requests would then be taken from
> this table, using DataAdapter.Fill(DataSet, startRecord, numberOfRecords)
>
> I am, however, not sure if this is the right approach. Not using a TEMP
> table would probably increase performance on the first results page, but I
> would assume the TEMP table helps decrease system load a lot on subsequent
> page requests. Also, I'm not completely sure if DataAdapter.Fill with
> startrecord, numberofrecords does only transfers the selected records to
the
> web server, or if the entire resultset is transfered to the webserver, and
> the dataset THEN populated with the selected records.
>
> Does anyone have any thoughts on the best way to implement a system like
> this?
>
> Cheers,
>
> Thomas
>
>

"Anders Borum" <na@na.na> wrote in message
news:uvpHILCxDHA.2448@TK2MSFTNGP12.phx.gbl...
> Hello!
>
> While this approach currently is the best way to do paging in SQL Server,
> you might want to look out for ADO.NET 2.0. It allows you to do paging
with
> SQL Server - without first copying all your rows to a TEMP table first.
>
> As far as I remember, it was about using a new SqlDataReader object where
> you could specify page size and page position before getting the
> SqlDataReader.

> Miha,
>
> So if i understand you correctly;
>
> 1) Do search, fill TEMP table with resultset
> 2) Get each results page from the TEMP table - but use SQL logic to
retrieve
> page records, rather than ADO's .Fill.
>
> The problem with paging into an incremeting integer list of records is
that
> it will not work with different sorts. If the user eg. choses to display
the
> search result sorted by something else, I'd have to fire the original
search
> again.
>
> Thanks for the thoughts,
>
> /Thomas
>
> "Miha Markic" <miha at rthand com> wrote in message
> news:ejEqxKBxDHA.1996@TK2MSFTNGP12.phx.gbl...
> > Hi Thomas,
> >
> > I would use the following method (your fill won't work anyway because it
> > will use the original select to retrieve the data again) .
> > But with slight modifications it could work.
> > Here is the idea.
> > You do the search, but return only primary key values to a temp table.
> > Assuming pk is incrementing integer.
> >
> > When user needs data
> > You would create a select like: SELECT TOP NumberOfRecords ... .... FROm
> ...
> > WHERE PrimaryKey >= FirstPrimaryKey ORDER BY PrimaryKey
> > where NumberOfRecords is page size and FirstPrimaryKey is first pk to
> fetch.
> >
> > And just run adapter.Fill(datatable) when needed to fetch a page of
> records.
> >
> > --
> > Miha Markic - RightHand .NET consulting & development
> > miha at rthand com
> >
> > "Thomas Nielsen [AM Production A/S]" <jack_pot_dk@h0tmail.com> wrote in
> > message news:ep507uAxDHA.1576@TK2MSFTNGP11.phx.gbl...
> > > I'm looking for thoughts/advice on how to build the fastest search +
> > (paged)
> > > results-page in an .NET + SQL Server 2000 environment. I hope this is
> the
> > > proper group for this, otherwise i appologize for the spam.
> > >
> > > I have one table with around 100.000 records containing some text
> fields.
> > > What i need to do is free-text search through these records, and
return
> a
> > > paged result to the client. The user should be able to select which
> field
> > to
> > > sort by. I'm estimating 10.000 concurrent users on the system.
> > >
> > > My initial approach to this would be to do the initial search once
> > > (selecting records using a full-text indexing), and throw the result
> into
> > a
> > > TEMP table. Each paged result the user requests would then be taken
from
> > > this table, using DataAdapter.Fill(DataSet, startRecord,
> numberOfRecords)
> > >
> > > I am, however, not sure if this is the right approach. Not using a
TEMP
> > > table would probably increase performance on the first results page,
but
> I
> > > would assume the TEMP table helps decrease system load a lot on
> subsequent
> > > page requests. Also, I'm not completely sure if DataAdapter.Fill with
> > > startrecord, numberofrecords does only transfers the selected records
to
> > the
> > > web server, or if the entire resultset is transfered to the webserver,
> and
> > > the dataset THEN populated with the selected records.
> > >
> > > Does anyone have any thoughts on the best way to implement a system
like
> > > this?
> > >
> > > Cheers,
> > >
> > > Thomas
> > >
> > >
> >
> >
>
>

"Miha Markic" <miha at rthand com> wrote in message
news:ejEqxKBxDHA.1996@TK2MSFTNGP12.phx.gbl...
> Hi Thomas,
>
> I would use the following method (your fill won't work anyway because it
> will use the original select to retrieve the data again) .
> But with slight modifications it could work.
> Here is the idea.
> You do the search, but return only primary key values to a temp table.
> Assuming pk is incrementing integer.
>
> When user needs data
> You would create a select like: SELECT TOP NumberOfRecords ... .... FROm
....
> WHERE PrimaryKey >= FirstPrimaryKey ORDER BY PrimaryKey
> where NumberOfRecords is page size and FirstPrimaryKey is first pk to
fetch.
>
> And just run adapter.Fill(datatable) when needed to fetch a page of
records.
>
> --
> Miha Markic - RightHand .NET consulting & development
> miha at rthand com
>
> "Thomas Nielsen [AM Production A/S]" <jack_pot_dk@h0tmail.com> wrote in
> message news:ep507uAxDHA.1576@TK2MSFTNGP11.phx.gbl...
> > I'm looking for thoughts/advice on how to build the fastest search +
> (paged)
> > results-page in an .NET + SQL Server 2000 environment. I hope this is
the
> > proper group for this, otherwise i appologize for the spam.
> >
> > I have one table with around 100.000 records containing some text
fields.
> > What i need to do is free-text search through these records, and return
a
> > paged result to the client. The user should be able to select which
field
> to
> > sort by. I'm estimating 10.000 concurrent users on the system.
> >
> > My initial approach to this would be to do the initial search once
> > (selecting records using a full-text indexing), and throw the result
into
> a
> > TEMP table. Each paged result the user requests would then be taken from
> > this table, using DataAdapter.Fill(DataSet, startRecord,
numberOfRecords)
> >
> > I am, however, not sure if this is the right approach. Not using a TEMP
> > table would probably increase performance on the first results page, but
I
> > would assume the TEMP table helps decrease system load a lot on
subsequent
> > page requests. Also, I'm not completely sure if DataAdapter.Fill with
> > startrecord, numberofrecords does only transfers the selected records to
> the
> > web server, or if the entire resultset is transfered to the webserver,
and
> > the dataset THEN populated with the selected records.
> >
> > Does anyone have any thoughts on the best way to implement a system like
> > this?
> >
> > Cheers,
> >
> > Thomas
> >
> >
>
>

"Anders Borum" <na@na.na> wrote in message
news:uvpHILCxDHA.2448@TK2MSFTNGP12.phx.gbl...
> Hello!
>
> While this approach currently is the best way to do paging in SQL Server,
> you might want to look out for ADO.NET 2.0. It allows you to do paging
with
> SQL Server - without first copying all your rows to a TEMP table first.
>
> As far as I remember, it was about using a new SqlDataReader object where
> you could specify page size and page position before getting the
> SqlDataReader.
>
> --
> venlig hilsen / with regards
> anders borum
> --
>
>