Still More SQL Fun
- From: PerfectReign <perfectreign@xxxxxxxxxxxxxxxxxxxx>
- Date: 1 Feb 2007 16:53:54 GMT
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
convert(varchar,rd.RecordedTS,101) as DateRecorded,
(select TitleCode from Titles where
AssessorCopy = 1)
dt.Sequence = 1
dt.DocumentID = rd.DocumentID
rd.RecordedDocumentNumber LIKE '2007%'
---Second Temp Table
ti.DocumentID = t1.DocumentID
#T2.RecordedDocumentNumber as DocumentNumber, #T2.TitleCode
tt.TotalTransferTax as TransferTax
tt.TransactionItemID = #T2.TransactionItemID
order by #T2.DateRecorded desc
Free Compean and Ramos