Improving perfromance

[Otis Mukinfus]

Hi all
I have a application that connects to a sql server database.
I have many rows about above 100,000 records and when I want to fill my
sqlDataAdaptor and show results in a grid it takes above 15s,how Can I
improve this ??
Is this a good way to fill million of records in fewer time??

thanks in advance

This sounds like a design issue to me.

Who is your user that needs to visually process 100k or 1m rows of data
visually? If the user could visually process each row in one second it would
take her 27.8 hours to view the content of 100,000 rows. For one million rows
it would take 277.8 hours to view the rows in the grid. Of course I didn't add
in lunch time, breaks or sleep time.

My suggestion is that you consider letting the user tell you some sort of
criteria for the data they wish to view and limit the return to between 25 and
50 rows.

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[Burak SARICA]

Do you bind a dataset to your grid?
Instead of DataAdapter, you may use datareader, if you do not use update
etc. features in the grid.

Burak SARICA


perspolis yazmýþ:

 

[Alvin Bruney]

Go back and read otis' comments. You have a design issue that needs fixing
before anything else.

--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ www.lulu.com/owc
Forth-coming VSTO.NET - Wrox/Wiley 2006
-------------------------------------------------------

 

[Jason Hales]

If you must handle so many records, can you not use some form of data
paging, either by using ADO's built-in paging mechansim or if you're
using stored procs get them to do the paging for you?

Are you only bringing back the most necessary number of columns?

Is your SQL query optimised to use all possible indexes rather than
doing table scans?

 

[perspolis]

Hi all
I have a application that connects to a sql server database.
I have many rows about above 100,000 records and when I want to fill my
sqlDataAdaptor and show results in a grid it takes above 15s,how Can I
improve this ??
Is this a good way to fill million of records in fewer time??

thanks in advance

 

[Ignacio Machin \( .NET/ C# MVP \)]

HI,

IMO it's a very bad idea to show that huge amount of info in a single page,
do you really need all that data?

What if you use paging, ni this way you get from the DB the 25 or 30 rows
you will show in a single page only.

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Even so, dumping 100K rows in a single page is not helpful at all. Just page
it and shows a reasonable amount ( 100 or less) per page.

IF this is historic data (which does not change during queries) it's even
easier to have them returned in chunks.

 

[perspolis]

yes I'm using databinding..

Do you bind a dataset to your grid?
Instead of DataAdapter, you may use datareader, if you do not use update
etc. features in the grid.

Burak SARICA


perspolis yazmýþ:

 

[Nick Hounsome]

I have a report part than user can select a range of dates.
then if he/she selects a large range I'll have many rows to show to user..

Just select "TOP 100 ...." and tell the user that you've trimmed the results
if you get them all (I know it is not strictly correct if there are exactly
100 but it's close enough).

 

[Otis Mukinfus]

I have a report part than user can select a range of dates.
then if he/she selects a large range I'll have many rows to show to user..

Surely there is some additional criteria you can use to pare the results down. I
think a user interview in depth is in order here.

Even paging is a bad idea. I am currently supporting a product with only 200k
records, but the people who designed it set the paging at 25 rows per page and
some of the queries will return hundreds of rows. It's a very slow web
application and the users complain all the time about it's slowness, but
management will not change it because they paid an outside vender so much for
it.

Reconsider your design. No user will be happy to scroll or page through even a
few hundred rows if you don't give them a better way of choosing the criteria
for the rows returned. Think about how much labor your customer or employer
will have to pay their employees to visually process such a list. make the
computer do the work. That's what it's for.

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Even paging is a bad idea. I am currently supporting a product with only
200k
records, but the people who designed it set the paging at 25 rows per page
and
some of the queries will return hundreds of rows. It's a very slow web
application and the users complain all the time about it's slowness, but
management will not change it because they paid an outside vender so much
for
it.

Paging is not a bad idea, it's the way to go . IMO your app is getting all
the records from the DB and doing the paging in the datagrid. So you are
transfering huge amounts of daa frmo your DB anyway. But if you set the
paging logic in the DB you only transfer 25 records to the web app and
everything is MUCH faster.

I have such a solution in place and it does work nicely.

 

[perspolis]

I have a report part than user can select a range of dates.
then if he/she selects a large range I'll have many rows to show to user..