asp.net database speed?

[Fat Tone]

Hi

I'm inheriting an online app written in asp.net, running on a hosted dedicated server (3200+, 1GB, Windows Server 2003, 70GB RAID) with SQL server. Its also running a managed exchange service for a handful of users.

I'm finding the part that manages membership to be very slow, e.g. 15 seconds to show me the membership page with the first few records on it, and then another 15 seconds after I chose a search criteria (Name etc) but then I get instant results when I type the name in.

There are <4000 records, so surely this is just bad programming?


Cheers,

T.

[KeiFront]

Place some indexes on the table (you can do this with the SQL server enterprise manager), asp.net is slow but not that slow.

[cjolley]

Unless the mathematicians at MS live in a different universe than the ones at Oracle an index would not be used on a table with <4000 members.
It would be faster to read the entire table into memory and do a search there than do a bunch of separate index lookups.

Maybe the SQL Server engine is just that different, but do watch for the case where indexes can slow you down when they are on small tables.

Though my guess is that it's the app code it's self.

[Fat Tone]

Keifront has done some timings on a simillar size table and they are over 10 times quicker than these. The developers seem to be reading the entire table in to memory first. I think perhaps there is a memory usage issue + whatever else is going on.

[cjolley]

...The developers seem to be reading the entire table in to memory first...

Are the developers using their own search instead of letting the db do it?
Why?

Was this thing originally developed in Access by any chance?

[schmosef]

It would take a lot of effort in the .Net space to result in performance that bad.

My guess is that it's probably a botched attempt at manual paging of a datagrid/gridview. The developers are preloading all the data instead of keeping track of grid page and using that to pull only the data required to render the page.

Is this a .Net 1.1 app or a .Net 2.0 app? Does the app use a stored procedure or is the SQL expression for searching the database inline with the code?

SQL has an index tuning wizard that you can use but, generally speaking, having one index on a table is not generally a big peformance hit; having too many is bad. This doesn't sound like a SQL problem anyway.

PM me if you want me to take a look at the code.

[Fat Tone]

Thanks guys. I don't get to see the code until the project is signed off. The developers have been nothing but trouble since the start and my friend is anxious to get me on board as soon as possible. There are several real bugs that I'm getting the developers to fix before handover. This is an annoying performance issue that fortunately won't affect clients greatly, mostly just us.

Its .net 1.1, never been in Access AFAIK. I'm just getting responses such as "Yes, the reason for this is because all records are processed making the
search performance much quicker when the name is typed in and the search
button clicked on. Rather than the process take longer at the end it takes
longer at the beginning so multiple name searches can be performed more
effectively."
When I suggest it might be an under specced server I get 'we can't comment' even though they specced it, and they deny any issue with the code.

I'll get back to them armed with your thoughts and see if I can get more details. May well be taking you up on that offer Schmo . Time for me to re-read my .net course manual!

Cheers,

T.

[schmosef]

here's a good article on implementing custom paging with asp.net 1.1: link

by far the best books on asp.net are "asp.net unleashed" and "asp.net 2.0 unleashed" both by sams publishing.

[Fat Tone]

[schmosef]

i hadn't looked at the custom paging article too carefully before linking it.

turns out it leaves a lot of room for optimization. i'm surprised because i've used the 4guysfromrolla site a lot and some of the best articles i've ever read are from there.

here's a much better example (way more optimized) of custom paging via dotnetjunkies: link.

[Fat Tone]

Just wanted to say thanks for the help. I stll don't have the code (any day now hopefully) but armed with your suggestions I was able to embarrass the developers in to admitting that there was a more efficient way of handling the data, and now performance is pretty much instantaneous It was actually fixed a week or so after this thread started.

Thanks again