Fetching Records FROM mssql...

[LamSoft]

I've got some perfermance issue while fetching data from MSSQL

I have a gridview, the datasource is a objectdatasource, and the
objectdatasource is pointed to the dataset.

The Select Query in dataset is something like "SELECT * FROM myTable"
while the "myTable" has more than 1M records..

While run the project, the ASP.NET seems try to retrieve all the records
before doing some procedure such as Paging or Sorting...

Is there any other method to do increase the performance?

Thanks

 

[Cowboy \(Gregory A. Beamer\)]

Yes, it does.

Okay, think about this. I am going to give you a million numbers and you
have to put them in the right order. Now, here are 1,000, so you should
start. From a human standpoint, it still makes sense, as we have unlimited
space to drag things out and arrange them. The computer never knows what you
are going to throw at it until you do it.

You should NEVER use SELECT * on a table, especially when you are not
filtering the records. Seriously, who do you think will actually page
through that many records?

I hope this does not come across as harsh, but you are probably using GB
worth of memory before you decide what you are going to display on the page.
It is extremely inefficient.

 

[LamSoft]

I have to display all the field in the tables to the client
Mainly I don't want the ASP.NET retrieve all the records then do the paging,
I want select a range of records in the table, so that the paging will be
done on the SQL server not on the Web Server, that's why I do paging in the
GridView.

It seems that MSSQL does not support a SQL statement like this "SELECT
myfield1, myfield2, myfield3 in myTables LIMIT 50,100"
So it is quite difficult to do paging on SQL Server...


Thanks

 

[JC]

Select top 50100 columna, columnb
from tablename

I have to display all the field in the tables to the client
Mainly I don't want the ASP.NET retrieve all the records then do the
paging, I want select a range of records in the table, so that the paging
will be done on the SQL server not on the Web Server, that's why I do
paging in the GridView.

It seems that MSSQL does not support a SQL statement like this "SELECT
myfield1, myfield2, myfield3 in myTables LIMIT 50,100"
So it is quite difficult to do paging on SQL Server...


Thanks

 

[Paul]

I've got some perfermance issue while fetching data from MSSQL

I have a gridview, the datasource is a objectdatasource, and the
objectdatasource is pointed to the dataset.

The Select Query in dataset is something like "SELECT * FROM myTable"
while the "myTable" has more than 1M records..

While run the project, the ASP.NET seems try to retrieve all the records
before doing some procedure such as Paging or Sorting...

Is there any other method to do increase the performance?

Thanks

For paging, I use the 1.1 control DataGrid and extend it to allow for
numbered paging. This control allows you to implement custom paging
using VirtualItemCount. You simply pass a count(*) to this for it to
work out the number of pages needed, then you just return the records
for one page. So in an app it doesn't matter if there are 5 million
records in a DB, you'll only ever return 10-15-20 etc at a time. It's
a bit more coding, but it's a lot more efficient.

 

[Aidy]

So in an app it doesn't matter if there are 5 million

records in a DB, you'll only ever return 10-15-20 etc at a time.

But how do you work it so that SQL only results the 10-15-20 records you
want?

 

[Aidy]

Have a read through this for some ideas;

http://www.aspfaq.com/show.asp?id=2120

(BTW page was not working at time of posting, but if memory serves me it has
the info you need)

 

[Paul]

But how do you work it so that SQL only results the 10-15-20 records you
want?

The current project i'm working on has a MySQL backend, but I simply
pass in the current page number to the query and for a page size of 15
do something like:

page = currentPageNum * PageSize;

then:

"SELECT foo FROM foo.table ORDER BY something ASC LIMIT page,
pageSize"

Page and pageSize passed as params into the SQL statement.

MsSQL has a similar function to limit which enables such functionality.

 

[Aidy]

That's a handy feature, would be good if SQL Server had that cos writing
paging is a PITA.

 

[Cowboy \(Gregory A. Beamer\)]

Okay, you have the * part justified, but why all 1 million+ records. I would
use a where clause and filter down. Even chunking by a number or alpha range
would save lots of wear and tear on the DB's procs.

 

[Cowboy \(Gregory A. Beamer\)]

This would be a good start, but it is more likely he will have to do an
architectural review and figure out what the end users are attempting to
accomplish on this page and then think it through.

 

[LamSoft]

So if it is necessary to show up those 5 million records, and there is not
where clause or filter, it means that we cannot do this through ASP.NET?
In addition, MSSQL does not have "LIMIT" keyword.... as I remembered~

 

[Mark Rae [MVP]]

So if it is necessary to show up those 5 million records, and there is not
where clause or filter, it means that we cannot do this through ASP.NET?

Yes. Even if fetching the 5 million records out of SQL Server was reasonably
fast, it would take ASP.NET simply ages (in computing terms) to render the
HTML output, and even longer for the browser to display it.

As others have hinted, trying to display that amount of data really is
symptomatic of a poor design...