Post reply

Order By Query - How can I get it to run faster?

  • July 29, 2006 at 2:18 am

    #113908

    Hi,

    I have a query that join six tables (indexed) and which also includes an order by. Running the query takes 12-15 seconds at the best of times :S However, when I take out the Order by statement the query runs in 1-3 seconds. Unfortunately I need to have the order by in the query but how can I possibly get it to run faster?

    And help would be most appreciated!

    Cheers

    Reet x

  • July 29, 2006 at 2:35 am

    #652229

    I've put a GROUP BY clause in it and it seems to have done the trick!

  • July 30, 2006 at 8:44 pm

    #652273

    Did you check that you still get the same results?

    A group by will very probably change what data gets returned.

    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

  • July 30, 2006 at 9:16 pm

    #652276

    maybe you can post your query here. I am sure we can help to you make it run faster

  • July 31, 2006 at 2:59 am

    #652301

    Hi,

    I check my results and they seem fine but if there is another way then i would be very grateful to hear from you. The query that I am using now is as follows and is a view:

    SELECT OMS.ID, OMS.omOrderDate, OMS.omPart, OMS.omDesc, OMS.omOrderNumber, OMS.omCustomerID, OMS.omQuantity,

    OMS.omCostPrice, OMS.omSellPrice, OMS.omDistStock, OMS.omDistID, OMS.omPO, OMS.omETA, OMS.omPriority, OMS.omShipdate,

    OMS.omConsignment, OMS.omNotes, cust.name, cust.lastName, sup.supplierName, exciteuser.Status.statusName, OMS.omStatusID,

    prod.Manufacturers, cust.email, prod.idProduct, OMSps.costPrice, OMSps.stockLevel, OMSps.supplierID,

    sup2.supplierName AS spSupplierName

    FROM exciteuser.OMSystem AS OMS LEFT OUTER JOIN

    dbo.suppliers AS sup ON OMS.omDistID = sup.idSupplier INNER JOIN

    dbo.customers AS cust ON OMS.omCustomerID = cust.idcustomer INNER JOIN

    exciteuser.Status ON OMS.omStatusID = exciteuser.Status.statusID INNER JOIN

    dbo.products AS prod ON OMS.omPart = prod.MFID COLLATE Latin1_General_CI_AS INNER JOIN

    exciteuser.OMS_ProductSupplier AS OMSps ON OMS.omPart = OMSps.partCode INNER JOIN

    dbo.suppliers AS sup2 ON OMSps.supplierID = sup2.idSupplier

    GROUP BY OMS.omOrderNumber, OMS.omOrderDate, OMS.ID, OMS.omPart, OMS.omDesc, OMS.omCustomerID, OMS.omQuantity, OMS.omCostPrice,

    OMS.omSellPrice, OMS.omDistStock, OMS.omDistID, OMS.omPO, OMS.omETA, OMS.omPriority, OMS.omShipdate, OMS.omConsignment,

    OMS.omNotes, cust.name, cust.lastName, sup.supplierName, exciteuser.Status.statusName, OMS.omStatusID, prod.Manufacturers, cust.email,

    prod.idProduct, OMSps.costPrice, OMSps.stockLevel, OMSps.supplierID, sup2.supplierName

    On the original query I didn't have the group by clause and used an order by statement on the view as follows:

    SELECT [selected fields] from vw_OMS_Details ORDER BY omOrderNumber DESC

    This query took 12-15 secs with the order by which is why i put in the GROUP BY clause.

    Cheers

    Reet

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply