Is a Temporary Table Really Necessary? - PART II:

New Query (I usually name my temporary table after the stored procedure that created it so I can troubleshoot any problems in tempdb from the use of temporary tables faster.)

CREATE TABLE #tquery2a (multiplecolumns DATATYPES)

CREATE TABLE #tquery2b(mulitplecolumns DATATYPES)



INSERT INTO #tquery2a
SELECT columns
FROM dbo.table2
WHERE table2.strPhoneNumber = '5555555555'


INSERT INTO #tquery2b
SELECT columns
FROM dbo.table3
WHERE table3.strPhoneNumberCalled = '1234561234'


SELECT table1.numCustID, #tquery2a.strPhoneNumber, #tquery2b.strPhoneNumberCalled
FROM dbo.table1 table1
INNER JOIN #tquery2a #tquery2a ON table1.numBillID = #tquery2a.numBillID
INNER JOIN #tquery2b #tquery2b ON #tquery2a.numBillDtlID = #tquery2b.numBillDtlID
WHERE table1.numCustID = '5555'
ORDER BY #tquery2b.dtmCalled DESC


Believe it or not this method works, especially with the ORDER BY statement and its performance is vastly better than the original query.Reporting off an OLTP designed databases is not always the easiest thing to do.


The database is just built to maximize reports that executives want. Using temporary tables to stage the results from numerous SELECT statements, aggregate those results before displaying them is sometimes the only way to can get reports out of an OLTP database.


Working in a call center application you are usually asked to produce reports that summarize what the call center reps are doing on a time filtered basis. Working your way through all the tables to gather the data and then summarizing it in multiple ways can only be accomplished with the use of temporary tables.


Before any comes up with this argument: I know I work in a multi-billion company but that doesn't mean that executives are willing to listen to our arguments that they need a data warehouse or a simple reporting database if it means they have to spend money to get one when they can just as easy piggy-back off of the OLTP database and blame me if the queries are too slow and cause a performance headache for the servers. Sorry, that was for the theoretical guys out there who have magically gotten everything they wanted no matter the cost or the size of the companies they worked for.


The last argument for the use of a temporary table is to replace a cursor. I am not fond of cursors and advocate doing anything possible to replace the cursor (performance of your solution needs to be tested against the performance of the cursor though).