July 24, 2009 at 8:49 am
Hi guys,
I'm looking for a cunning solution - i know a few of you are proboably cleverer than me, so here goes
our web developers currently drag huge amounts of data from the database and create Paged data on their web pages using web side controls. (think "results 50-100 out of 800")
however some of the queries return Thousands of rows, which jams up the web server memory, and also takes forever on our sql boxes.
so.. we want to implement paging at the db side - nice and easy
here's my sample proc
create proc usp_someproc @startat int,@numrows int
as
select * from (select *, row_number() over (order by name) as rowcntr from mytable )
where rowcntr between @startat and @startat+@numrows-1
nice and simple - yes?
but they also need to know the total number of rows possible - now i could do a
select count(*) from mytable
in this instance and send that back as a second result set , but some of these queries are very complex and involve a lot of joins, so it looks very innefficient.....
is there a way of getting the max value from the row_number() function????
i don't care if it's a seperate data set, an extra column, or an extra row in the reults - we can deal with that, but i want to not run expensive queries twice.
any thoughts ????
MV
MVDBA
July 24, 2009 at 8:59 am
ok - slight modification to my post
i can't supply it as an extra row in the result set - sorry
MVDBA
July 24, 2009 at 9:31 am
Something like this?
declare @PageNum int,
@RowsPerPage int;
set @PageNum = 1;
Set @RowsPerPage = 10;
set statistics io on;
set statistics time on;
with PagedData as (
select
row_number() over (order by AccountID asc) as RowNum,
(select count(*) from dbo.JBMTest) as CountRows,
AccountID,
Amount,
Date
from
dbo.JBMTest
)
select
RowNum,
CountRows,
AccountID,
Amount,
Date
from
PagedData
where
RowNum between (@RowsPerPage * (@PageNum - 1)) + 1 and (@RowsPerPage * @PageNum)
order by
RowNum;
set statistics time off;
set statistics io off;
set @PageNum = 200;
Set @RowsPerPage = 10;
set statistics io on;
set statistics time on;
with PagedData as (
select
row_number() over (order by AccountID asc) as RowNum,
(select count(*) from dbo.JBMTest) as CountRows,
AccountID,
Amount,
Date
from
dbo.JBMTest
)
select
RowNum,
CountRows,
AccountID,
Amount,
Date
from
PagedData
where
RowNum between (@RowsPerPage * (@PageNum - 1)) + 1 and (@RowsPerPage * @PageNum)
order by
RowNum;
set statistics time off;
set statistics io off;
July 24, 2009 at 9:39 am
that's not really do-able for me
as i said, this might be a huge query with maybe 15 tables
so your example of
with PagedData as (
select
row_number() over (order by AccountID asc) as RowNum,
(select count(*) from dbo.JBMTest) as CountRows,
AccountID,
Amount,
Date
from
dbo.JBMTest
)
works fine if it's a simple query, but imagine my "from" and "where" clause was maybe 50 lines long.....?
i don't really want to do a select count(*) from if i can avoid it... is there anything that either a CTE or RANK/ROW_NUMBER can expose ??
MVDBA
July 24, 2009 at 9:50 am
michael vessey (7/24/2009)
that's not really do-able for meas i said, this might be a huge query with maybe 15 tables
so your example of
with PagedData as (
select
row_number() over (order by AccountID asc) as RowNum,
(select count(*) from dbo.JBMTest) as CountRows,
AccountID,
Amount,
Date
from
dbo.JBMTest
)
works fine if it's a simple query, but imagine my "from" and "where" clause was maybe 50 lines long.....?
i don't really want to do a select count(*) from if i can avoid it... is there anything that either a CTE or RANK/ROW_NUMBER can expose ??
Now your changing requirements. Your original post only showed a simple table so I went with that.
There is another way, but I need to ask how long does the current method take response time wise?
July 24, 2009 at 9:52 am
Still using a simple table, but probably meets your requirements. Each run of the query took approximately 8 seconds.
declare @PageNum int,
@RowsPerPage int;
set @PageNum = 1;
Set @RowsPerPage = 10;
set statistics io on;
set statistics time on;
with PagedData as (
select
row_number() over (order by AccountID asc) as RowNum,
count(*) over (partition by 1) as CountRows,
AccountID,
Amount,
Date
from
dbo.JBMTest
)
select
RowNum,
CountRows,
AccountID,
Amount,
Date
from
PagedData
where
RowNum between (@RowsPerPage * (@PageNum - 1)) + 1 and (@RowsPerPage * @PageNum)
order by
RowNum;
set statistics time off;
set statistics io off;
set @PageNum = 200;
Set @RowsPerPage = 10;
set statistics io on;
set statistics time on;
with PagedData as (
select
row_number() over (order by AccountID asc) as RowNum,
count(*) over (partition by 1) as CountRows,
AccountID,
Amount,
Date
from
dbo.JBMTest
)
select
RowNum,
CountRows,
AccountID,
Amount,
Date
from
PagedData
where
RowNum between (@RowsPerPage * (@PageNum - 1)) + 1 and (@RowsPerPage * @PageNum)
order by
RowNum;
set statistics time off;
set statistics io off;
July 24, 2009 at 11:34 am
Should have also noted that the table in my query has 1,000,000 records as well.
July 27, 2009 at 2:37 am
lynn thanks - i will review this
but to note - changeing my original requirements??:-P LOL -
i think you'll find the original post said
"In this instance and send that back as a second result set , but some of these queries are very complex and involve a lot of joins"
thanks for the additional post
MV
MVDBA
July 27, 2009 at 6:08 am
Based on the original sample using only one table, yes, your requirements changed.
Going to more complex queries, it may have helped to show that instead of a single table query, even if it was just a two table join.
Still, based on that, you should be able to take that latest single table sample and build on it.
July 27, 2009 at 6:28 am
Hi mate,
I'm not sure exactly about your requirements But you can try this script(nice & easy) if it could help you?????
declare @startRow int ,@Totalpages int,@RowsEffected int,@pagesLeft int
create table #pgeResults(
id int identity(1,1) not null,
table_name varchar(255) null,
column_name varchar(255) null
)
insert into #pgeResults(Table_name, column_name) -- create ur table with ur own requirement
select table_name, column_name -- your query
from [INFORMATION_SCHEMA].columns
order by [table_name], [column_name]
select @RowsEffected=count(*)from #pgeResults
if @RowsEffected % 50 >=1
begin
set @totalpages= (@RowsEffected /50)+1
end
else
begin
set @totalpages=@RowsEffected/50
end
if @pagenumber<=0
begin
raiserror ('### PAGE NUMBER CAN NOT BE LESS THAN 1 ###',16,2)
return 0
end
else if isnull(@pagenumber,1)=1 or @pagenumber=1
begin
set @startRow=1
set @pagenumber=1
end
else if @pagenumber<=@Totalpages
begin
set @startRow=((@pagenumber-1) * 50)+1
end
else
raiserror ('--- OUT OF BOUND PAGE NUMBER... THERE ARE ONLY %d PAGES ---',17,3,@Totalpages)
--return 0
select ID,Table_Name, Column_Name
from #pgeResults
where id between @startRow and @startrow + 49
order by id
set @pagesLeft= case when @pagenumber <=0 then @Totalpages else @Totalpages - @pagenumber end
print 'Page ' +convert(varchar,@pagenumber)+' of '+convert(varchar,@totalpages)+''
drop table #pgeResults
*** Sajid ***
July 27, 2009 at 6:31 am
ok - let me be clear
the original post indicated that i wanted an Efficient method of getting the max value from Rown_number()
but the query inside the CTE/subquery may have anything like 15 tables in the from clause and maybe 30 criteria in the WHERE clause - my code indicated a SIMPLE demonstration of what we we were trying to acheive. I'm not loking for a complete solution, just a pointer in the right direction.
my original post indicated that the sample query was a simplification and stated clearly "some of these queries are very complex and involve a lot of joins, so it looks very innefficient....."
i also indicated that I KNOW i can do a "select count(*) from"
I'm looking for a way to do this efficiently - and running the same query twice is not as efficient as i'd like.
MVDBA
July 27, 2009 at 8:52 am
Mike,
Could a view/indexed view help then use the simple solution against the view?
Mark
July 27, 2009 at 2:58 pm
michael vessey (7/27/2009)
ok - let me be clearthe original post indicated that i wanted an Efficient method of getting the max value from Rown_number()
but the query inside the CTE/subquery may have anything like 15 tables in the from clause and maybe 30 criteria in the WHERE clause - my code indicated a SIMPLE demonstration of what we we were trying to acheive. I'm not loking for a complete solution, just a pointer in the right direction.
my original post indicated that the sample query was a simplification and stated clearly "some of these queries are very complex and involve a lot of joins, so it looks very innefficient....."
i also indicated that I KNOW i can do a "select count(*) from"
I'm looking for a way to do this efficiently - and running the same query twice is not as efficient as i'd like.
Did you look at my second query I posted??
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply