Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-04-18 : 17:38:22
|
Chris Rosner writes "Okay, a little background on what I'm trying to do, before we start. I have table with a bunch (say about 1000) articles in it. What I want to do is select partitions of these articles, in order, based on, say page numbers. I want to partition them so that on the home page of my website I only have to retrieve (from the sql server) the articles that will be displayed for that request (cutting down on latency by not sending extra articles to the web server).
Ideally, this would be in the form of a stored procedure that took two (additional) parameters @page and @rows with meanings of:
@page - The partition page number (0 to rows in table/@rows)
@rows - The number of rows to be returned on this query
So, calling the get articles stored procedure would get (with respect to sorted order) articles
@page*rows to (@page+1)*@rows-1 (total of @rows rows)
Is there any way to achieve this kind of partitioning in an efficient manner?
An example of my un-partitioned select statement is:
SELECT art_id, art_title, art_date FROM tblArticles ORDER BY art_date
Here's what I've tried already:
SET @grab_rows=@rows*(@page+1)
DROP TABLE #tmpArticles
SET rowcount @grab_rows
select
tblArticles.art_id,
tblArticles.art_title,
tblArticles.art_date
INTO #tmpArticles
FROM tblArticles
ORDER BY art_date DESC
SET @grab_rows=@max_rows*(@page)
SET rowcount @grab_rows
-- should delete @grab_rows number of rows
DELETE FROM #tmpArticles
SELECT art_id, art_title, art_date
FROM #tmpArticles order by art_date DESC
-- Turn off row limiting so it doesn't affect other queries
SET rowcount 0
The main problem with this approcah is that the rowcount is not applying to the DELETE opperation, plus, this is a bit slow, taking up to 3 seconds to execute. I wanted to know if there was a faster way of doing this, and also, how do you apply a rowcount to a delete operation?
(One thing to note in regaurds to the 3 seconds issue, is that the table has about 6 joins on it in my actual select, but this is not the main cause of the slowdown, I'm fairly sure... I'm going to add indexes very soon to speed that up.)
Any help that could be provided would be greatly appreciated.
I'm using SQL Server 7, don't know which service pack.
Thanks,
Chris Rosner" |
|
|
|
|
|
|
|
Partitioning SELECT results