Re: Selecting Rows in groups
From: Max (maxl_at_msn.com)
Date: 12/05/04
- Next message: Max: "Re: Selecting Rows in groups"
- Previous message: David Portas: "Re: if statement"
- In reply to: Adam Machanic: "Re: Selecting Rows in groups"
- Next in thread: Max: "Re: Selecting Rows in groups"
- Messages sorted by: [ date ] [ thread ]
Date: 5 Dec 2004 14:00:53 -0800
"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message news:<OXI4Lc71EHA.412@TK2MSFTNGP14.phx.gbl>...
> There are many ways to support paging from T-SQL, but no good ways.
> However, in web applications there is generally not much choice. Since
> you're not coding a web app, however, I think you have some additional
> options available to you that will work better than a pure T-SQL solution.
> Are your complete data sets small enough to cache on the client (all paging
> in that case would be UI-based)? If that's more data than you feel
> comfortable putting in memory, another option I've seen used to good effect
> is caching primary keys from the initial request and sending back the next
> 10 keys each time to retrieve the actual data. This is generally more
> efficient than re-sorting/filtering the actual data to only return 10 rows.
> If you do want a T-SQL solution, though:
>
> http://www.aspfaq.com/show.asp?id=2120
>
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>
> "Shallotx" <Shallotx@discussions.microsoft.com> wrote in message
> news:84A859C1-EEF7-498F-BF13-D5BFFD302073@microsoft.com...
> > I have seen a mumber of posts on this subject, but am still unsure as how
> to
> > implement.
> >
> > In my Windows Forms application, I am dynamically building a query based
> on
> > criteria the user chooses. In the event that the query will return a
> larger
> > number of rows (say 100 for discussion purposes), I want to return the
> rows
> > in groups (say 10 for discussion purposes). I will then allow the user to
> > return subsequent groups of 10 records if they desire.
> >
> > I understand I could write SELECT TOP 10 * FROM TABLE to return records
> > 1-10, but how do I then retreive 11-20, 21-30 etc. In the old days I know
> I
> > could use Cursors, but I believe there are better ways.
> >
> > Thank you
The more dynamically you retrive data, the more you will be compelled
to write stored procedures (a modular set of stored procedures, imho)
for retrieving only the data you need. This is especially true of
high-traffic web sites that perform searches against significant
entities with a core set (80%) of attributes that are relevant to most
of your customers, and then the other 20% applying to small sets of
your customers.
The paths you choose to get there are typically based on the
following:
1. volume of data stored for the entity
2. volume of data in a typical retrieval (typical web page search)
3. average and peak page requests per second from your (potentially)
most
expensive page, the one that has the search.
Choices for optimizing 3-tierd web solutions focus on the following:
1. minimizing data access times
1a. extremely fast disk subsystems
2a. large amount of memory for caching tables
3a. solid data architecture
2. determining the types of database calls - chunky or chatty.
2a. Chunky calls implie few connections are database
connections/retrievals
are required because all the page data can be returned with
just a few
stored procedureto calls. Chatty calls are pages filled with
a large
number (>10) of database retrievals.
3. maximizing the use of the database cache. the database cache
should be
mastered before creating a caching strategy for web servers.
this is
because most developers want to cache as much as they can on
the web
server. however, web servers are designed with their primary
function
being translating various scripts, images and HTML into a page
that
can be interpreted by a visitor to your site (via their web
browser).
thus, push as much caching off on to the db server, which is
essentially a large, intelligent caching engine.
4. minimizing web traffic b/w tiers.
4a. design as narrow result set as is possible. for example, if a
table
contains 20 columns, but only two columns are used by the web
page,
then return only two columns.
4b. design with result sets being as short as possible. only
return the
rows that are relative to the page being viewed. For example,
if 500
rows meet the user's serch criteria, but the user is viewing
page 5
and are viewing 25 rows per page, only retrieve rows 101 -
125.
5. maximizing network bandwidth. you can't have too much bandwidth.
it is
possible, for example, to max out a 1.2G ethernet server with
testing
tools spawning multiple browsers that are attempting as many
retrievals
as possible within a specific time frame. emperical data
indicates
99 rps is the maximum throughput for a well-designed retrieval
system
running on a single web server.
6. acknowledging and undestanding how the disk i/o, network
bandwidth, database
caching, web caching, program flow, indexing, etc. influence
testing,
thus the overall performance of your production environment.
7. Assuming a system has a high-performing, well-designed data
architecture,
the next step is to create a modular search that does the
following:
a. Returns TotalRecords in one result set, then the results for
page n of x
results to the client - based on pagesize and pagenumber.
b. Builds the Select clause for the query based on the data
architecure that
defines searches. For example, assume each customers' search
has a
unique identifier and is related to a set of columns that
should be
returned or Selected. This modular stored procedure,
Search_BuildSelectList @uniqueId = 1, @additionalFields =
'Color'
would return a string like 'Select colA, ColB'.
c. Build the From clause using the same architecture, based on
parameters
passed to the stored procedure: Search_BuildFromClause
@uniqueId = 1
, @maxPrice = 100.00, @additionalFields = 'Color' would return
'From Inventory i Join Color c On i.ColorId = c.ColorId
d. Build the Where clause using the same architecture, based on
parameters
passed to the stored procedure: Search_BuildWhereClause
@uniqueId = 1
, @maxPrice = 100.00 would return 'Where i.uniqueId = @uniqueId
And
i.MaxPrice = @MaxPrice.
e. Build the Order By clause based on an order passed with a field
string.
exec Search_BuildOrderByClause @uniqueId = 1, @OrderBy = 'Color
Desc'
would return 'Order By c.Color Desc, i.uniqueId'.
8. Putting it all together with the Search stored procedure results
in the
following:
a. exec Search @uniqueId = 1, @pageSize = 5, @pageNumber = 4,
@OrderBy =
'Color Desc', @maxPrice = 100, @additionalFields = 'Color'
with the following result:
b.
Declare @uniqueId int Set @uniqueId = 1
Select Count(1) As 'TotalRecordCount'
From Inventory i Join Color c On i.ColorId = c.ColorId
Join Color c On i.ColorId = c.ColorId
Where i.uniqueId = @uniqueId And i.MaxPrice = @MaxPrice) t2
Order By c.Color Desc, i.uniqueId
Select t1.*
From (
Select Top (PageNumber*PageSize)
i.uniqueId, i.Item, i.Location, c.Color
From Inventory i
Join Color c On i.ColorId = c.ColorId
Where i.uniqueId = @uniqueId And i.MaxPrice = @MaxPrice
Order By c.Color Desc, i.uniqueId
) t1
Left Join (
Select Top (PageNumber * (PageSize-1) i.uniqueId
From Inventory i
Join Color c On i.ColorId = c.ColorId
Where i.uniqueId = @uniqueId And i.MaxPrice = @MaxPrice)
t2
Order By c.Color Desc, i.uniqueId
Where t2.uniqueId Is Null
Order By t1.Color Desc, t1.uniqueId
9. Execute 9's result to bring back the 4th page of 5-row pages.
- Next message: Max: "Re: Selecting Rows in groups"
- Previous message: David Portas: "Re: if statement"
- In reply to: Adam Machanic: "Re: Selecting Rows in groups"
- Next in thread: Max: "Re: Selecting Rows in groups"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
