RE: Database Performance

[rlgordon]

Hi:

I have a page that uses MS Access to query a table with about 500 names and that returns from 1 to 50 depending on the query. On my dev server (Windows 2000, Dell PowerEdge 300 with 256MB) the response in under 1 second. On your site it takes minutes... I find this totally unacceptable.. What gives? I know I am
the only one on, since it is not yet publically available.

PS: I have successfully used much larger 100MB Access DB's querying 50,000 names with a response time of 5-10 seconds on other sites.

[rlgordon]

I have code to do it both ways, and I am currently doing it dsnless viaa connection string. The same code is used on my server and others and I haven't seen much perfomance difference either way. I am not closing connections because I assume that the end of a page will close them. Again, not an issue to date on my or other servers.

[bluebeard96]

Read this online:
[b]


However, both IIS and PWS offer a better alternative, connection pooling, which provides much more efficient resource allocation. When connection pooling is on, the server doesn?t automatically close your connection to the database when the Connection object goes out of scope. The connection is kept open for a while in case your page or one in another session needs it in the near future. The server may even use the same connection among multiple concurrent processes. </BLOCKQUOTE>
Possible solution (other than explicitly closing you connections) is to use ADO to connect to your database. Every time you open your connection, ADO will look for a pooled connection first. I think that your string currently is causing multiple connections to be open on the DASP server (maybeIIS is configured differently at your location).

http://www.microsoft.com/mind/0599/basics/basics0599.asp

Try an ADO connection string and let us know if that helps.


Mike Reilly, Secretary/Webmaster
Kiwanis Club of Rancho Penasquitos
"Serving the Children of the World"
Mike@KiwanisPQ.org
(760) 419-7429

[bluebeard96]

How are you connecting to the database (ODBC, dsnless, etc)? Are your recordsets and connections being closed?



Mike Reilly, Secretary/Webmaster
Kiwanis Club of Rancho Penasquitos
"Serving the Children of the World"
Mike@KiwanisPQ.org
(760) 419-7429

[bruce]

The server hosting your site is much beefier than you dev server (PowerEdge 2850 Dual Xeon with 4G of memory).

In addition, we didn't get any complaint from other users regarding latency issue. You may want to contact support.

Bruce

DiscountASP.NET
www.DiscountASP.NET (http://www.DiscountASP.NET)

[bruce]

This is not possible!!!

ODBC is always slower than DSNless as ODBC is another layer on top of OleDB.

Bruce

DiscountASP.NET
www.DiscountASP.NET (http://www.DiscountASP.NET)

[bluebeard96]

Bruce: Is connection pooling enabled on the servers? Could the issue be multiple connections, as they are not (at this point) being closed? My understanding is that when connection pooling is enabled, the database objects are NOT closed at the end of the ASP page.



Mike Reilly, Secretary/Webmaster
Kiwanis Club of Rancho Penasquitos
"Serving the Children of the World"
Mike@KiwanisPQ.org
www.KiwanisPQ.org (http://www.KiwanisPQ.org)
(760) 419-7429

[bruce]

connection pooling is enabled.

connection pooling should increase performance as it save the application from reopening the DB.

I am totally baffled.

Bruce

DiscountASP.NET
www.DiscountASP.NET (http://www.DiscountASP.NET)

[rlgordon]

Set up the site to use a DSN connection and performance is much better,
although still very slow.. Instead of 1 minute I am down to 30 seconds or
so, but still not as good as on other servers.

[roryknowles]

always close your connections no matter what, that is the proper way to do it. Also, there are many ways to make it even faster, but it should be fast no matter once generally code correct.

[diltonm]

I've found that the best performance in is achieved in ADO.NET by localizing the open/close to the smallest section of code possible.

//open the connection
//run query
//close connection

This would result in terrible performance in ADO. ADO.NET is completely different.

[roryknowles]

??

thats how its always been for asp and database connections.

[diltonm]

Hmm..maybe I'm comparing to pre-pooled connection days. /emoticons/smhair.gif

[roryknowles]

dont know, ive done it like that since 1999, most of the help i got came from learnasp.com, asp101.com, and 4guysfromrolla.com, initially. Always was told to close all objects and connections as soon as you are finished with them. Either way, writing it now, closing as soon as you are done is the way to do it. Get rows also ...always ..when you can. /emoticons/smile.gif Ill get into .net later this year (i mean 2005!) when i get a chance /emoticons/smile.gif Probably alot less work than classic ASP and ASP VB Classes ... just finishing up a 2000+ line ASP VB class, fun, if you know what i mean! Arghhh ..

[diltonm]

I see. Good luck with that class, it sounds beasty. /emoticons/smile.gif