Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
maximus_vj
Yak Posting Veteran
88 Posts |
 Posted - 2004-06-08 : 20:41:14
|
| Hello,My application (ASP) runs slowly after some time. This is what I observed when I restart the SQL Serevr it runs fast after a while it slows down.This is my Setup:I am using SQL Server 2000 and my ASP application is using DSN COnnection to connect to my SQL Server( I know DSN less connection is better for now I cannot change the way it is done). I take full backups and transactional log backups. I am also using the Full text Indexing on some of the tables.I read in an article that - if you are using ODBC connection pooling and temporary stored procedures at the same time your SQL Server will experience a performance hit. Because my ODBC driver is 3.85 by default I will have coonection pooling and because I am using DSN by default it will convert my transact SQL from my application to temporary Stored Procedures. Is this a reason that is causing my app to slow down? And how will I know if "temporary stored procedures" is turned on or not? And how will I turn off temporary Stored Procedures?when I am in the Servers ODBC Data Sources screen in the third window there is an option "Create Stored procedures for teh prepared SQL and drop only when you disconnect". But this option is Checked and dimmed out. So I cannot possibly uncheck that option.Any suggestions?Thanks,maximus |
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-06-08 : 21:22:57
|
| Make sure that you're releasing your ADO objects after use (i.e. by setting them to Nothing). Do this for your recordsets, connections and commands. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-06-08 : 22:24:16
|
| Try dbcc freeproccachedbcc dropcleanbuffersIt should get rid of unused things.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
maximus_vj
Yak Posting Veteran
88 Posts |
Posted - 2004-06-08 : 23:11:45
|
Hello Timmy,This is what I am doing in my ASP application. I have an include file at the beginning of each file with code similar to this. In this page I creat the connection object.If IsObject(Session("db_conn")) Then Set conn = Session("db_conn")Else Set conn = Server.CreateObject("ADODB.Connection") conn.open "dbName","","" Set Session("db_conn") = connEnd IfAnd I am not closing the connection object at the end of teh file. But I am closing all other objects properly. And I am closing the connection object when the user logs off from the application. The reason why I am doing thsi is I do not want to create and close connection at each and every page so I am using a session to see if the user hasn't logged out if he is not logged out I am using the same connection object. Do you think this is causing these problems? Let me know.Thanks a bunch,maximusquote:
Originally posted by timmy
Make sure that you're releasing your ADO objects after use (i.e. by setting them to Nothing). Do this for your recordsets, connections and commands.
|
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-06-08 : 23:36:30
|
| In short:Don't store objects in the session ojbect...You might want to check out these links:http://www.devx.com/asp/Article/16802/0/page/2http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q243/5/43.ASP&NoWebContent=1Tim |
|
|
|
maximus_vj
Yak Posting Veteran
88 Posts |
Posted - 2004-06-09 : 01:26:25
|
| Thanks Timmy |
|
|
|
|
|
|
DSN & temporary Stored Procedures