SQL Server Performance Forum – Threads Archive

Bad Query – – Dont know where to start

I got this query from one of our developers- It takes 2 hours to run. I am new to this and just looking for advice on where to start and what to look for. Every time this is ran the Disk usage and queue length hit 100% on the partition that stores the tempdb. ——————————————-
SELECT
_tInvTranDetail."ItemID",
_tInvTranDetail."Description",
_tInvTranDetail."QtyShipped",
_tInvTranDetail."UnitPrice",
_tInvTranDetail."ExtPrice",
_tInvTranDetail."ExtCost",
_tInvTranDetail."ProductLine",
_tInvTranHeader."InvoiceNo",
_tInvTranHeader."InvoiceDate",
_tInvTranHeader."CustID",
_tInvTranHeader."CustName",
_tInvTranHeader."CustClassID",
_tInvTranHeader."BillToPhone",
_tInvTranHeader."BillToFax",
tciAddress."AddrLine1",
tciAddress."City",
tciAddress."PostalCode",
tciAddress."StateID",
tarSalesTerritory."SalesTerritoryID"
FROM
(((((mas500_app.dbo._tInvTranDetail _tInvTranDetail INNER JOIN mas500_app.dbo.tarInvoice tarInvoice ON
_tInvTranDetail."InvcKey" = tarInvoice."InvcKey")
INNER JOIN mas500_app.dbo._tInvTranHeader _tInvTranHeader ON
tarInvoice."InvcKey" = _tInvTranHeader."InvcKey")
INNER JOIN mas500_app.dbo.tarCustomer tarCustomer ON
_tInvTranHeader."CustID" = tarCustomer."CustID")
INNER JOIN mas500_app.dbo.tciAddress tciAddress ON
tarCustomer."PrimaryAddrKey" = tciAddress."AddrKey")
INNER JOIN mas500_app.dbo.tarCustAddr tarCustAddr ON
tciAddress."AddrKey" = tarCustAddr."AddrKey")
INNER JOIN mas500_app.dbo.tarSalesTerritory tarSalesTerritory ON
tarCustAddr."SalesTerritoryKey" = tarSalesTerritory."SalesTerritoryKey"
WHERE
tarSalesTerritory."SalesTerritoryID" = ‘Georgia’
ORDER BY
tarSalesTerritory."SalesTerritoryID" ASC,
_tInvTranHeader."CustID" ASC,
_tInvTranHeader."InvoiceNo" ASC ——————————————- The query seems pretty simple, any help? Thanks Donovan

---

Try using temp table…
select SalesTerritoryID, SalesTerritoryKey into #temp from mas500_app.dbo.tarSalesTerritory
where tarSalesTerritory."SalesTerritoryID" = ‘Georgia’ SELECT
_tInvTranDetail."ItemID",
_tInvTranDetail."Description",
_tInvTranDetail."QtyShipped",
_tInvTranDetail."UnitPrice",
_tInvTranDetail."ExtPrice",
_tInvTranDetail."ExtCost",
_tInvTranDetail."ProductLine",
_tInvTranHeader."InvoiceNo",
_tInvTranHeader."InvoiceDate",
_tInvTranHeader."CustID",
_tInvTranHeader."CustName",
_tInvTranHeader."CustClassID",
_tInvTranHeader."BillToPhone",
_tInvTranHeader."BillToFax",
tciAddress."AddrLine1",
tciAddress."City",
tciAddress."PostalCode",
tciAddress."StateID",
tarSalesTerritory."SalesTerritoryID"
FROM
(((((mas500_app.dbo._tInvTranDetail _tInvTranDetail
INNER JOIN mas500_app.dbo.tarInvoice tarInvoice ON
_tInvTranDetail."InvcKey" = tarInvoice."InvcKey")
INNER JOIN mas500_app.dbo._tInvTranHeader _tInvTranHeader ON
tarInvoice."InvcKey" = _tInvTranHeader."InvcKey")
INNER JOIN mas500_app.dbo.tarCustomer tarCustomer ON
_tInvTranHeader."CustID" = tarCustomer."CustID")
INNER JOIN mas500_app.dbo.tciAddress tciAddress ON
tarCustomer."PrimaryAddrKey" = tciAddress."AddrKey")
INNER JOIN mas500_app.dbo.tarCustAddr tarCustAddr ON
tciAddress."AddrKey" = tarCustAddr."AddrKey")
INNER JOIN #temp tarSalesTerritory ON
tarCustAddr."SalesTerritoryKey" = tarSalesTerritory."SalesTerritoryKey"
ORDER BY
tarSalesTerritory."SalesTerritoryID" ASC,
_tInvTranHeader."CustID" ASC,
_tInvTranHeader."InvoiceNo" ASC
Mohammed U.

---

When I get a new query like this that is performing poorly, I ways start by getting a Graphical Execution Plan from Query Analyzer or Managment Studio. Many time, you can identify what is causing the problem from the plan. —————————–
Brad M. McGehee, SQL Server MVP

---

From the bracketing, I would suspect that the query was originally written in Access. If this is a database that was upgraded from an Access MDB, then I would wonder if there are any indexes on the tables. There should be indexes covering all those columns whose names end in "key".

---

Thanks Mohammed, This made a huge difference, what took over two hours was reduced to less than 30 seconds on average. I see now how much I truly have to learn. Why would making a temp table make such a huge difference, and, is it always better to create a temp when working with queries like this? Thanks again from all the help. ~Donovan

---

Close, the "deveolper" is really more of a BI type person and she uses access to develop her queries. All of the columns that are "keys" are indexed nightly.

---

Temp table is not always better… it depends what you are doing…<br /><br />Try to work with small chunk of data instead of all data…<br /><br />Good to know it improved <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br /><br /><br />Mohammed U.

---

]]>