Slow performance issues for newly converted SQL Server Database

[link9]

Let me start by saying that yesterday was my first look at SQL Server, but I have been using Access for quite some time.

I was running asp with Access on the backend, and had developed a full blown web application using it. However, multi-user issues forced me to move the entire database over to SQL Server yesterday.

So, I used DTS, imported the database, went back through and redefined all my primary keys and such, and then re-did the connection to the database in the asp script. (That is the only variable I changed -- the connection)

Now, it is so slow, it's painful. I have run the queries on the database in the analyzer itself, and they seem to be running fine, but the actual execution times of the scripts have increased I don't know how many fold (ALOT!).

The fact that the queries execute fine in the database leads me to believe that maybe there is some special way I need to deal with the recordset(s) once I have them in my scripts.

Has anyone experienced this type of slow down when moving from Access to SQL Server, and if so, what was the cause? I'm praying for a quick fix, but if not, then I'm ready to get in the trenches.

ANY wisdom regarding the move between these two products would be GREATLY appreciated.

Thank you,
Paul Prewett

 

[MarkSweetland]

Paul,

What kind of connection are you using between your web server and the SQL Server? (ADO, ODBC ? )

 

[link9]

I am using an ADO connection.

paul

 

[MarkSweetland]

Since the queries run fine on the Analyzer, and you haven't changed the ADO recordset object creation, I would look at the connection properties in your asp file (Provider, server, initial catalog, etc..)

I do take it you are not using MTS Components...

Mark

 

[link9]

Give an example of one of these components, please.

It doesn't ring a bell, but I'm not ruling it out.

thx
paul

 

[MarkSweetland]

MTS = Microsoft Transaction Server
If you're not familiar with it I'm sure you're not using it...

How are you defining the Connection when you execute the ADODB Recordset Open method? Or, are you creating an ADODB Connection object as well? Can you post (minus any username/password of course) the Connection string?


Mark

 

[BootMeUp]

Link9,
Two things to check: Use SQL Server Profiler (run from mmc) to see if the reponse problem is on the server. While it could be data transfer, the way your VBScript code is being parsed by the query facility could be different than interactive (with Analyzer). See Article Q224587 and Q243589 at MS support/kb/articles.
Also set up your SQL performance monitor to track web performance items (there's an object for web - "Web Service&quot. You may also want to compare performance of the same sql code between interactive and web requests (check page faults, buffer cache hit ratio, etc). The MS support site and/or BOL should have articles about using the perf monitor.
Beyond this, I'd look at network related issues - network contention? Change something in code and see if it makes a difference.
Good luck

 

[link9]

The connection:

strConn = "Provider=SQLOLEDB; "
strConn = strConn & "Data Source=theServer; "
strConn = strConn & "Initial Catalog=heclientrg; "
strConn = strConn & "User Id=thisUser; "
strConn = strConn & "Password=myPassword"

set con = server.CreateObject ("ADODB.Connection")
con.ConnectionTimeout = 100

con.ConnectionString = strConn

con.open

I have also tried using a DSN Connection, with the results staying exactly the same.

paul

 

[MarkSweetland]

The connection string looks ok. Have you determined if additional time seems to be spent on the retrieving the recordset or rendering the HTML view of the data (i.e. put a Response.write Time() before and after the recordset creation, open, and HTML rendering)? This might identify the area where you what to focus more on.

Another thing you might check is the CursorType on the ADODB.Recordset and set it as an adOpenForwardOnly. You may see performance increase by using a single pass cursortype to render the HTML.

Mark

 

[calahans]

Are the web server, application and datbase on different boxes? If so you would need to be very careful with the cursor type you select. If you have a server side cursor then you will make trip over the distributed environment for each row.
Cal

Remeber: What the large print givith, the small print taketh away...