I've got 2,037,890 rows (88620 pages, 23 items/page) that I created by
sending our company's Router's SysLog data to SQL DB. It takes 10 secs to
display a page using my solution in .Net 1.1.
In .Net 2.0 it takes... wow... it takes forever. You're right. Thanks for
pushing me to test.
I guess I should not trust Scott Gu's word that the paging is efficient. I
thought they were wrapping my SQL statement with some statements I saw once
that aids is paging in the DB.
I hope they get it right with LINQ, or I'll have to write it all by myself
then too.
Well, since I have to do it all myself, anyone have any better ideas?
Here's the way I've been doing it:
I have a config file that I hit depending on the type of data I want. I
execute the SQL statement according to the paging type here is a sample
config file
<?xml version="1.0"?>
<Root>
<Process is="NetLogWeb">
<Method is="PageData">
<Param is="WebBase.View.Xml.Style">NetLogWeb</Param>
<Param is="WebBase.Client.Method.Name">SetDocNL</Param>
<Param is="WebBase.Client.Task.Lid.Name">Main</Param>
<Param is="WebBase.Server.DataSet.Name">TransPak</Param>
<Param is="SQL.Database.Table.Name">SysLogRaw</Param>
<Param is="SQL.Database.Package.Size">4096</Param>
<Param is="SQL.Database.Table.Record.Size">23</Param>
<Param is="SQL.Database.Table.Record.Key">Id</Param>
<Param is="SQL.Database.Table.Record.Key.LastNew">Created </Param>
<Param is="SQL.Database.Script.RowCount">USE Transfer;
SELECT COUNT(*)
FROM SysLogRaw
WHERE (Id>'' {0})
</Param>
<Param is="SQL.Database.Script.First">USE Transfer;
SELECT TOP {0} Id, Created,Command, FromIP, FromPort, ToIP, ToPort, Text
FROM SysLogRaw WHERE (Id>'' {1})
ORDER BY Id
</Param>
<Param is="SQL.Database.Script.Next">USE Transfer;
SELECT Id, Created,Command, FromIP, FromPort, ToIP,ToPort, Text
FROM SysLogRaw
WHERE Id IN (SELECT TOP {0} Id FROM SysLogRaw WHERE Id > '{1}' {2} ORDER
BY Id)
ORDER BY Id
</Param>
<Param is="SQL.Database.Script.Prev">USE Transfer;
SELECT Id, Created,Command, FromIP, FromPort, ToIP,ToPort, Text
FROM SysLogRaw
WHERE Id IN (SELECT TOP {0} Id FROM SysLogRaw WHERE Id < '{1}' {2} ORDER
BY Id DESC)
ORDER BY Id
</Param>
<Param is="SQL.Database.Script.Last">USE Transfer;
SELECT Id, Created, Command, FromIP, FromPort, ToIP, ToPort, Text
FROM SysLogRaw
WHERE Id IN (SELECT TOP {0} Id FROM SysLogRaw WHERE Id >'' {1} ORDER BY
Id DESC)
ORDER BY Id
</Param>
<Param is="SQL.Database.Script.JumpPage">USE Transfer;
SELECT Id, Created, Command, FromIP, FromPort, ToIP, ToPort, Text FROM
SysLogRaw
WHERE Id IN (SELECT TOP {0} Id FROM SysLogRaw
WHERE Id IN (SELECT TOP {1} Id FROM SysLogRaw
WHERE Id>='0' {2}
ORDER BY Id) ORDER BY SysLogRaw.Id DESC) ORDER BY SysLogRaw.Id
</Param>
</Method>
</Process>
</Root>
,,,Donald
"John Saunders [MVP]" <john.saunders at trizetto.comwrote in message
news:eq**************@TK2MSFTNGP04.phx.gbl...
"Donald Adams" <BD******@hotmail.comwrote in message
news:B2**********************************@microsof t.com...
>I've implemented paging for a web/webservice back in .net 1.1 days.. but
it wasn't as efficient as .Net 2.0's. In .net 2.0 all I have to do with
my Data Source object is provide a SQL statement like the following:
SELECT * FROM TableA
and tell the datagrid I want to do paging. The rest is magic I'd like to
know, because they do it efficiently.
I wonder how you know whether it is done efficiently? Have you measured?
Have you tried it with 100,000 rows of data?
I don't know of any particularly great way of doing what you want, and I
don't think that there's a whole lot of magic going on - it's just that
it's going on behind the scenes. After executing the query, the data
source no doubt reads the result set into a DataSet or some other such
object. It then passes the requested data, starting at row pageSize *
pageOffset and for pageSize pages, to the grid. It might get a bit fancy
in terms of optimizing memory usage in the forward-only scenario, but it's
still pretty much reading all the data and returning it.
Sorry to be the bearer of bad news.
--
John Saunders [MVP]
