SQL Server Performance

SQL SERVER WEIRD Performance problem. Please help.

Discussion in 'ALL SQL SERVER QUESTIONS' started by Munscio, Apr 2, 2012.

  1. Munscio New Member

    SQL SERVER WEIRD Performance problem. Please help.

    Hi, thank you for reading my first post. ;)

    I’m migrating a Classic ASP app from ACCESS to SQL Server. I RE-wrote queries, I’m not using Access with Linked Tables to SQL SRV. I have a page that is almost 10 times slower than the Access version of the app!!! It is a soccer school. The page is to Staff Coaches to Classes. It has VERY SIMPLE queries, but each one is repeated many times (one per coach). For example:

    1) Main query gets all coaches that are ACTIVE. All the rest happens inside this loop
    2) For each coach on the loop, I query the DB to check it he/she is available for the class (date and time)
    3) If available, I have another query that check if she/he has any VACATION request (thus voiding availability).
    4) If coach has no vacation, I query the DB again to see if coach if already staffed in a conflicting class (date/ time overlaps the class im searching)
    5) If not in a conflicting class, I list the coach as available.
    6) Cycle repeater per coach.

    All tables have proper indexes (I even rebuilt them). When running any query listed above isolated to test latency, SQL Server runs it faster than access. Im using SQL Express 2008 for development. I even tried with an INSTANCE that is on another computer. Same problem. I even tried migrating tables used in this page to MySql just to see performance, and it works wonderfully. I don’t think that a stored procedure helps here. The logic is on the page as explained. Queries are simple and work ok, so no bottle neck here

    Does any want know what is going on? I’m searching for weeks.

    In Performance monitor I see there are many batch req /sec and compilatiosn per sec.

    Looks like Sql Server doesn't like to compile many many queries in a short time, even being simple queries?

    I REALLY appreciate any comment.

    Thank you very much!
  2. Luis Martin Moderator

    Welcome to the forums.
    Could you share your hardware and sql configurations?
  3. Munscio New Member

    Hi! actually very simple.....im in the developing stage. Win xp with IIS with SQL Express 2008. In reality i'd like to discuss something in particular. Otherwise the thread can divert to OT things, or blaming my page is poorly designed,etc..... to make it simpler, forget the logic and 'correctness'. this is what happend:

    set db= server.CreateObject("ADODB.Connection")
    db.open Application("conn_str")
    Set rs = Server.CreateObject("ADODB.Recordset")

    query1 = xxxxx
    rs.open query1 ,db


    query2 = xxxxx
    rs.open query2,db

    query3 = xxxxx
    rs.open query3,db

    query4 = xxxxx
    rs.open query4,db

    All Queries 1,2,3, and 4 run 250 times for 250 different coaches.

    If I run this test against MS Access or MySql it takes aprox. 4000ms.
    If I run this test against Sql Server takes aprox. 8800ms. !!!!!!

    why is that?? again...if I measure latency for each query, it will be faster in Sql Sever, but the iteration of queries is much slower in sql server.

    Thank you very much!

Share This Page