Still More SQL Fun

Being a manager, I don't seem to have as much time to "get my hands dirty"
as it were. Yet, I'm still apparently able to impress the youn'ns.
Yesterday, we ran into an issue with our new system that required
immediate attention. We needed to create an ad-hoc query on our database
to produce a report for another office. It needed to happen by today, or
there would be 200 people without work.

Some of my analysts took on the job and wrote this monster query using
various joins and whatnot. The query locked most of the database and ran
for roughly 30 minutes. I decided to have a go at it. In one hour, I had
a query which did the same job in 34 seconds, not locking any tables, and
got the results formatted as the other division needed.

In terms of numbers, I figured the following:

One of the tables has 39,867,766 records... ...another one has 39,868,348
records... ...the other join has 223 records...
....another join has 145,138,930 records.

I still got it! :)

Here's my query. Take it apart as you feel appropriate. I'm sure I could
do better...

select
       
        convert(varchar,rd.RecordedTS,101) as DateRecorded,
        rd.DocumentID,
        rd.RecordedDocumentNumber,
        dt.TitleCode


into #T1
       
from
        DocumentTitles dt,
        RecordedDocuments rd
where
        TitleCode In
                (select TitleCode from Titles where
                AssessorCopy = 1)
AND
        dt.Sequence = 1
AND
        dt.DocumentID = rd.DocumentID


AND
        rd.RecordedDocumentNumber LIKE '2007%'
---Second Temp Table


select
        ti.TransactionItemID,
        ti.TransactionID,
        ti.DocumentID,
        t1.DateRecorded,
        t1.RecordedDocumentNumber,
        t1.TitleCode
into #T2
from
        TransactionItems ti,
        #T1 t1
where
        ti.DocumentID = t1.DocumentID


select
        #T2.DateRecorded,
        #T2.RecordedDocumentNumber as DocumentNumber, #T2.TitleCode
        as DeedCode,
        tt.TotalTransferTax as TransferTax
from
        TransferTaxes tt,
        #T2
where
        tt.TransactionItemID = #T2.TransactionItemID
order by #T2.DateRecorded desc



--
kai

Free Compean and Ramos
http://www.perfectreign.com/?q=node/46
.