Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Posts
    22

    Unanswered: slow SQL queries

    Hi All,

    Am very new to SQL server so don't really understand what effects the speed of queries. I have the two below queries, which are nearly the same apart from one has a right join and the other doesn't. The both return about 5000 records, and I am implementing this query from an accss databse with an odbc link to sql server. What I don't understand is it takes about 8 seconds for the query with the right join in to return the records and only about 4 seconds for the one without. What I'm after really is just some general advice on how to bulid fast queries, and any advice on the two below queries would be nice. Thanks


    SELECT Employees.Name, Calls.CallDate, Calls.CallTime, Calls.Callername, Contacts.CompanyID, Contacts.ContactID, Calls.CallerNumber, Calls.CallerCompany, Calls.ActionTakenID, Calls.OperatorID, Calls.Confirmed, Calls.Charged, Calls.Notes, Company.CompanyName, Operators.Operatorname, Calls.CallID, Calls.ShortMessage
    FROM (Contacts INNER JOIN Company ON Contacts.CompanyID = Company.CompanyID) INNER JOIN (Operators INNER JOIN (Employees RIGHT JOIN Calls ON Employees.EmployeesID = Calls.EmployeesID) ON Operators.ID = Calls.OperatorID) ON Contacts.ContactID = Calls.ContactID
    WHERE (((Contacts.ContactID)=1442))
    ORDER BY Calls.CallDate DESC , Calls.CallTime DESC;


    SELECT Employees.Name, Calls.CallDate, Calls.CallTime, Calls.Callername, Contacts.CompanyID, Contacts.ContactID, Calls.CallerNumber, Calls.CallerCompany, Calls.ActionTakenID, Calls.OperatorID, Calls.Confirmed, Calls.Charged, Calls.Notes, Company.CompanyName, Operators.Operatorname, Calls.CallID, Calls.ShortMessage
    FROM (Contacts INNER JOIN Company ON Contacts.CompanyID = Company.CompanyID) INNER JOIN (Operators RIGHT JOIN (Employees RIGHT JOIN Calls ON Employees.EmployeesID = Calls.EmployeesID) ON Operators.ID = Calls.OperatorID) ON Contacts.ContactID = Calls.ContactID
    WHERE (((Contacts.ContactID)=1442))
    ORDER BY Calls.CallDate DESC , Calls.CallTime DESC;

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I would read about indexes and query execution plans and think about the benefits of creating a denormalized set of data for reporting purposes.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Dec 2004
    Posts
    22

    eek

    Think that means I need to go on a course or get a book, anyoe know any good website links to look at in the mean time.
    Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Alright, maybe I was being a little short. You can generally increase query performance by applying indexes to fields that are commonly searched or joined on. See Create Index in SQL Server Books Online.

    However, too many indexes and cause poor performing inserts statements of online transaction programs, so sometimes it is worth going through the trouble of creating a periodically refreshed copy of the data that is used for reporting and decision support. This copy you can heavily index.

    If you are still having problems you can transform this decision support copy of the database during the copying process (using DTS perhaps) to create a copy of the data that is denormalized. This basically means you have all of the data you need for your common queries in one table instead a bunch of tables joined together.

    There are no magic bullets. I wish there a make this faster button to switch on. Some people throw hardware at performance problems but I have seen situations where the background design and setup was so bad that new and better hardware did'nt do anything.

    But no one ever listens to me.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I hear you, brother.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Dec 2004
    Location
    CA, USA
    Posts
    63

  7. #7
    Join Date
    Dec 2004
    Posts
    22

    Thanks

    Thanks, will look see, it all seem a bi like a dark art at the moment but have managed to convinced my boss to send me on a week course in March so soon I'll know everything !!!(only jokes)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •