Migrated classic ASP to Win2008/IIS7/SQL Server 2008. Now some inserts/updates are very slow from ASP, but very fast from Management Studio

1
votes

Title covers it pretty well. I am new to Windows 2008 R2, IIS7, and SQL Server 2008.

Some very basic insert and update statements run very fast from Management Studio, but take several seconds each when run from classic ASP pages.

As you might expect, everything worked fine on the previous Windows 2003 Box with IIS 6 and SQL Server 2000.

Any hints on where to start?

3
sqlsql-server-2008iis-7asp-classic
Either it's server related and the question is off-topic, or it's in the code we need to see. – JJJ
sommarskog.se/query-plan-mysteries.html – Mitch Wheat
@MitchWheat Thanks for the tip. Actually I've seen that page before. I think in this case the problem is not in the query plan. These are extremely simple statements AND they run fast the very first time and every time in SSMS and run slow the first time and every time in ASP. – DG.
@DG. - Can you show the queries? – Martin Smith
@DG. - Yes no longer relevant. Maybe run a profiler trace and see if you can see what's going on. i.e. are the statements themselves taking longer or is there some delay in the statements being sent? – Martin Smith

3 Answers

3
votes

Found the solution myself.

Seems that using the outdated SQL connection type that worked fine on the previous 32 bit Windows 2003 server was terrible when used on the new 64 bit Windows 2008 server.

Previous connection string:

"Driver={SQL Server};SERVER=(local);DATABASE=db1;UID=me;Password=pass;"

Updating to this made things much faster:

"Provider=SQLNCLI10;Data Source=(local);Initial Catalog=db1;User ID=me;Password=pass"
0
votes

Change SQL provider does not help on my machine, then I run the scripts by opening and closing the DB connections manually:

conn.Open
sql = "SELECT ... :"
Set rs_test= Conn.Execute(sql, ,1)

conn.Close
conn.Open

sql = "SELECT ... :"
Set rs_test= Conn.Execute(sql, ,1)

conn.Close
conn.Open

sql = "SELECT ... :"
Set rs_test= Conn.Execute(sql, ,1)

conn.Close
conn.Open

It does run much faster.

0
votes

In addition to the solutiosn above, changing the connection string to use the server name instead of . or (local) worked for me.

Change this

"Provider=SQLNCLI10;Data Source=(local);Initial Catalog=db1;User ID=me;Password=pass"

To

"Provider=SQLNCLI10;Data Source=MYSERVERNAME;Initial Catalog=db1;User ID=me;Password=pass"