I heard in an online video by Scott Gu, that there is an option to have the
asp.net datagrid in .net 2.0 do the paging in SQL Server 2005 rather than
pull all the data from the database and do it on the web server.
I've been unable to find any good documentation on this. I saw a property
to turn on paging if the select supports it, but that did not give me enough
information to complete my task.
Does anyone know where I can find documentation how to do this?
Thanks in advance,
Donald Adams 8 1455
re:
!I've been unable to find any good documentation on this.
I googled for : ( "SQL Server 2005" paging "Scott Guthrie" )
....and came up with the article in 5 seconds. http://weblogs.asp.net/scottgu/archi...01/434314.aspx
Scott provides sample code to experiment with.
Juan T. Llibre, asp.net MVP
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en espanol : http://asp.net.do/foros/
=============== =============== ========
"Donald Adams" <BD******@hotma il.comwrote in message
news:55******** *************** ***********@mic rosoft.com...
>I heard in an online video by Scott Gu, that there is an option to have the asp.net datagrid in .net 2.0 do the paging in SQL Server 2005 rather than pull all the data from the database and do it on the web server.
I've been unable to find any good documentation on this. I saw a property to turn on paging if
the select supports it, but that did not give me enough information to complete my task.
Does anyone know where I can find documentation how to do this?
Thanks in advance,
Donald Adams
This works ok only on small sets (like 100000 records or around)
Problem is this approach requires order by clause, which is very inefficient
on big tables (>1Mio records)
I have 2.5Mio records table, and selection of page in 1Mio range takes
around 30 seconds on my PC. In beginning (1st, 2nd etc pages) selection
takes 10-15 seconds. Even though I have index on table. If order by is same
as index, selection is a bit faster, but just a bit (maybe 30% in best case)
I wonder if it is possible to use row_number without order by to speed up
selection. I have index already, so SQL Server should use it by default I
think.
Alex
"Juan T. Llibre" <no***********@ nowhere.comwrot e in message
news:uJ******** *****@TK2MSFTNG P05.phx.gbl...
re:
!I've been unable to find any good documentation on this.
I googled for : ( "SQL Server 2005" paging "Scott Guthrie" )
...and came up with the article in 5 seconds.
http://weblogs.asp.net/scottgu/archi...01/434314.aspx
Scott provides sample code to experiment with.
Juan T. Llibre, asp.net MVP
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en espanol : http://asp.net.do/foros/
=============== =============== ========
"Donald Adams" <BD******@hotma il.comwrote in message
news:55******** *************** ***********@mic rosoft.com...
>>I heard in an online video by Scott Gu, that there is an option to have the asp.net datagrid in .net 2.0 do the paging in SQL Server 2005 rather than pull all the data from the database and do it on the web server. I've been unable to find any good documentation on this. I saw a property to turn on paging if the select supports it, but that did not give me enough information to complete my task. Does anyone know where I can find documentation how to do this?
Thanks in advance, Donald Adams
"Juan T. Llibre" <no***********@ nowhere.comwrot e in message
news:uJ******** *****@TK2MSFTNG P05.phx.gbl...
!I've been unable to find any good documentation on this.
I googled for : ( "SQL Server 2005" paging "Scott Guthrie" )
...and came up with the article in 5 seconds.
That's just showing off...! ;-)
-- http://www.markrae.net
re:
!That's just showing off...! ;-)
To quote the *first* recommendation in my ASP.NET FAQ :
---000---
With that in mind, the first FAQ recommendation is that you use search engines to
find out if the question you intend to ask has been already answered in newsgroups.
Use resources like : Google Groups Advanced Search and MSN Search to search for the text of
the problem which is stumping you, and save everybody the waste of time that asking a FAQ entails.
Remember : thousands of programmers will read your post. Be considerate of their time.
---000---
In this case, it wasn't a FAQ ( in fact, it was, rather pointedly, a *non-FAQ* ), but searching
Google and/or MSN *first* should be a pre-requisite to posting *any* question here.
:-)
Juan T. Llibre, asp.net MVP
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en español : http://asp.net.do/foros/
=============== =============== ========
"Mark Rae" <ma**@markNOSPA Mrae.netwrote in message news:%2******** *******@TK2MSFT NGP06.phx.gbl.. .
"Juan T. Llibre" <no***********@ nowhere.comwrot e in message
news:uJ******** *****@TK2MSFTNG P05.phx.gbl...
>!I've been unable to find any good documentation on this.
I googled for : ( "SQL Server 2005" paging "Scott Guthrie" ) ...and came up with the article in 5 seconds.
That's just showing off...! ;-)
-- http://www.markrae.net
Putting more than 100,000 records in a grid is never going to be efficicient,
whether you have paging turned on or not. I'd revisit your design. I doubt
your user's are really going to want to look at more than a 100,000 records
in a session. I suggest changing your query to only bring back a subset of
your data and if the record the user wants isn't there, go get another chunk
of data, etc.
--
Jim Anderson, MCSD
Consultant
Columbus, Ohio
"AlexS" wrote:
This works ok only on small sets (like 100000 records or around)
Problem is this approach requires order by clause, which is very inefficient
on big tables (>1Mio records)
I have 2.5Mio records table, and selection of page in 1Mio range takes
around 30 seconds on my PC. In beginning (1st, 2nd etc pages) selection
takes 10-15 seconds. Even though I have index on table. If order by is same
as index, selection is a bit faster, but just a bit (maybe 30% in best case)
I wonder if it is possible to use row_number without order by to speed up
selection. I have index already, so SQL Server should use it by default I
think.
Alex
"Juan T. Llibre" <no***********@ nowhere.comwrot e in message
news:uJ******** *****@TK2MSFTNG P05.phx.gbl...
re:
!I've been unable to find any good documentation on this.
I googled for : ( "SQL Server 2005" paging "Scott Guthrie" )
...and came up with the article in 5 seconds. http://weblogs.asp.net/scottgu/archi...01/434314.aspx
Scott provides sample code to experiment with.
Juan T. Llibre, asp.net MVP
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en espanol : http://asp.net.do/foros/
=============== =============== ========
"Donald Adams" <BD******@hotma il.comwrote in message
news:55******** *************** ***********@mic rosoft.com...
>I heard in an online video by Scott Gu, that there is an option to have the asp.net datagrid in .net 2.0 do the paging in SQL Server 2005 rather than pull all the data from the database and do it on the web server.
I've been unable to find any good documentation on this. I saw a
property to turn on paging if
the select supports it, but that did not give me enough information to
complete my task.
Does anyone know where I can find documentation how to do this?
Thanks in advance,
Donald Adams
Yes, and I might add that the ASP.NET Quickstarts should be added as a third
option. I've been doing this stuff since 2000, and I still refer to it.
Cheers,
Peter
--
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
Short urls & more: http://ittyurl.net
"Juan T. Llibre" wrote:
re:
!That's just showing off...! ;-)
To quote the *first* recommendation in my ASP.NET FAQ :
---000---
With that in mind, the first FAQ recommendation is that you use search engines to
find out if the question you intend to ask has been already answered in newsgroups.
Use resources like : Google Groups Advanced Search and MSN Search to search for the text of
the problem which is stumping you, and save everybody the waste of time that asking a FAQ entails.
Remember : thousands of programmers will read your post. Be considerate of their time.
---000---
In this case, it wasn't a FAQ ( in fact, it was, rather pointedly, a *non-FAQ* ), but searching
Google and/or MSN *first* should be a pre-requisite to posting *any* question here.
:-)
Juan T. Llibre, asp.net MVP
asp.net faq : http://asp.net.do/faq/
foros de asp.net, en español : http://asp.net.do/foros/
=============== =============== ========
"Mark Rae" <ma**@markNOSPA Mrae.netwrote in message news:%2******** *******@TK2MSFT NGP06.phx.gbl.. .
"Juan T. Llibre" <no***********@ nowhere.comwrot e in message
news:uJ******** *****@TK2MSFTNG P05.phx.gbl...
!I've been unable to find any good documentation on this.
I googled for : ( "SQL Server 2005" paging "Scott Guthrie" )
...and came up with the article in 5 seconds.
That's just showing off...! ;-)
-- http://www.markrae.net
On Jun 5, 7:22 pm, Jim Anderson <anderson_jim at Excite DotComwrote:
Putting more than 100,000 records in a grid is never going to be efficicient,
whether you have paging turned on or not. I'd revisit your design. I doubt
your user's are really going to want to look at more than a 100,000 records
in a session. I suggest changing your query to only bring back a subset of
your data and if the record the user wants isn't there, go get another chunk
of data, etc.
--
Jim Anderson, MCSD
Consultant
Columbus, Ohio
I fully agree with Jim.
Anyway, there are some things that you can check, Alex.
1) Index for the ORDER BY field. Update statistics for the table
2) For the first N pages you could do the following query
SELECT * FROM (SELECT TOP 110 ROW_NUMBER()
OVER (order by id) AS RowId, * FROM Table1) a
WHERE RowId 100 AND RowId <= 110
In this example I select 10 rows starting from 101 through 110 (page
#10)
This should be faster on the large tables
3) and finally http://www.4guysfromrolla.com/webtech/042606-1.shtml
Hope it helps
On Jun 6, 2:22 pm, Alexey Smirnov <alexey.smir... @gmail.comwrote :
SELECT * FROM (SELECT TOP 110 ROW_NUMBER()
OVER (order by id) AS RowId, * FROM Table1) a
WHERE RowId 100 AND RowId <= 110
Also do not use SELECT * FROM :-)
Specify only the fields you need. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Christopher Pragash |
last post by:
Hello All
I am having a pretty wired problem...I am writing a web part (SPS 2003) that
uses a Datagrid. In the web part, I create the datagird assign some
properties and add event handlers for paging and sorting using AddHandler.
While browsing, I sort the grid and do paging, it works fine for increasing
page numbers...ie if I go from 1 - to - 2 - to - 3 the sort is maintained
and the paging works fine. If I come back to page 2 from page...
|
by: Rathtap |
last post by:
I am displaying data in a bound datagrid. Is there a way to provide
paging through client-side script? That is, without re-loading the
page. I am willing to have the initial overhead of bringing in a lot
of data.
|
by: Natan Vivo |
last post by:
I had to built a custom data paging control for my asp.net app and SQL
Server.
This is what I'm doing:
1. do a SELECT to a SqlDataReader
2. create a DataTable with data from GetSchema()
3. loop trough the datareader using .Read() to the point I want to start
4. add data to DataTable with max of records
5. close the data reader and return
|
by: Manuel Alves |
last post by:
Scenario:
ASP 2.0 on XP pro dev machine works fine.
Publishing to windows 2003 server worked OK until I included paging for a
datagrid.
On page load I cal sub binddata
Private Sub binddata()
Dim ds As DataSet = getMydataset()
|
by: IanIpp |
last post by:
We have a 3 month old quad processor/dual core server running SQL
Server 2005 and already it is getting close to hitting the CPU wall.
An 8 way CPU box is prohibitively expensive and out of the question. I
am looking desperately for a way to TRULY scale out SQL server...in the
same way that IIS can be scaled out via App Center.
The "in the box" solution for SQL Server 2005 scaling out is the DMV.
Unfortunately this solution makes...
| |
by: mrcraze |
last post by:
Hi Everyone!
We are using a cursor for paging results in SQL server, mainly due to
the performance gains achieved when working with large results sets.
We have found this to be of great benefit when working with SQL Server
2000, but have run into major problems when using it on SQL Server
2005.
The query goes like this:
|
by: rbg |
last post by:
I am using derived tables to Page data on the SQL Server side.
I used this link as my mentor for doing paging on the SQL
Serverhttp://msdn2.microsoft.com/en-us/library/ms979197.aspx
I wanted to use USER PAGING, thus I used the following code:
CREATE PROCEDURE UserPaging
(
@currentPage int = 1, @pageSize int =1000
)
AS
|
by: rbg |
last post by:
I did use query plans to find out more. ( Please see the thread BELOW)
I have a question on this, if someone can help me with that it will be
great.
In my SQL query that selects data from table, I have a where clause
which states :
where PermitID like @WorkType
order by WorkStart DESC
|
by: Ilyas |
last post by:
Hi all
I need to implmenet paging across different tables. The tables all
have a different name eg Data01, data02 data03 etc, however they are
columns which are common to each table, but each table also has some
unique columns
My questions is that I want to display data from any one of these
tables - I wont know which one until runtime, but since they contains
large amounts of data, I only want to display say 10 at a time. Also
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |