September 3, 2002 at 10:33 am
How can I optmize this query which takes too long to run. Any help will bve greatl appreciated.
============================================
SELECT
TableA."MyRef", TableA."BuildingName", TableA."BuildingNo", TableA."DStreet", TableA."LocalityName", TableA."RefNo", TableA."Postcode", TableA."StreetName",
TableB."East", TableB."North",
TableC."ActionDate", TableC."FieldName", TableC."OldValue", TableC."NewValue",
a."columnD",
e."columnD",
b."columnD",
c."columnD",
d."columnD"
FROM
DBB.dbo.TableA, DBB.dbo.columnA e , DBB.dbo.columnA d , DBB.dbo.columnA c , DBB.dbo.columnA b , DBB.dbo.columnA a , DBB.dbo.TableC, DBB.dbo.TableB
WHERE
(((a.columnB = 'valueB') AND (b.columnB = 'valueA') AND (c.columnB = 'valueC') AND (d.columnB = 'valueD') AND (e.columnB = 'valueE'))) AND (DBB.dbo.TableA.MyRef = DBB.dbo.TableB.MyRef) AND (DBB.dbo.TableA.MyRef = e.MyRef) AND (DBB.dbo.TableA.MyRef = d.MyRef) AND (DBB.dbo.TableA.MyRef = c.MyRef) AND (DBB.dbo.TableA.MyRef = b.MyRef) AND (DBB.dbo.TableA.MyRef = a.MyRef) AND (DBB.dbo.TableA.MyRef = DBB.dbo.TableC.MyRef)
ORDER BY
TableA."RefNo" ASC,
TableA."MyRef" ASC
==========================================
Thanks for your help.
Wale
September 4, 2002 at 1:14 am
Break this query...
say select first two tables with required columns into a new global temp table
select next two tables with required columns into a new global temp table
join these two tables into a new global temp table
Hope this will work fast.....
Cheers,
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
September 4, 2002 at 3:49 am
This may or may not make a difference, however this is the standard format for performing joining queries in SQL.
Now first thing I can see is that MyRef on each table if used in most queries often are good candidates for Clustered Indexes, otherwise should at least be a non-clustered index.
May even consider using columnB in tables aliased a-e in a non-clustered index instead of or in addition to the other index.
After that then to get a better understanding on what is happening with the query you should look at the query plan.
To do so run SET SHOWPLAN_TEXT ON to turn on, the run the query. If you have further questions please post the output so we can understand what is going on in the query.
Also, this is just one variation of dozens+ possible ways to do this.
SELECT
TableA."MyRef",
TableA."BuildingName",
TableA."BuildingNo",
TableA."DStreet",
TableA."LocalityName",
TableA."RefNo",
TableA."Postcode",
TableA."StreetName",
TableB."East",
TableB."North",
TableC."ActionDate",
TableC."FieldName",
TableC."OldValue",
TableC."NewValue",
a."columnD",
e."columnD",
b."columnD",
c."columnD",
d."columnD"
FROM
DBB.dbo.TableA
INNER JOIN
DBB.dbo.columnA e
ON
DBB.dbo.TableA.MyRef = e.MyRef AND
e.columnB = 'valueE'
INNER JOIN
DBB.dbo.columnA d
ON
DBB.dbo.TableA.MyRef = d.MyRef AND
d.columnB = 'valueD'
INNER JOIN
DBB.dbo.columnA c
ON
DBB.dbo.TableA.MyRef = c.MyRef AND
c.columnB = 'valueC'
INNER JOIN
DBB.dbo.columnA b
ON
DBB.dbo.TableA.MyRef = b.MyRef AND
b.columnB = 'valueA'
INNER JOIN
DBB.dbo.columnA a
ON
DBB.dbo.TableA.MyRef = a.MyRef AND
a.columnB = 'valueB'
INNER JOIN
DBB.dbo.TableC
ON
DBB.dbo.TableA.MyRef = DBB.dbo.TableC.MyRef
INNER JOIN
DBB.dbo.TableA.MyRef = DBB.dbo.TableB.MyRef
ORDER BY
TableA."RefNo" ASC,
TableA."MyRef" ASC
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply