MEDITECH Reports

SQL Server Temp Table Best Practices (v0.01) for MEDITECH

Tweet

 

Gary my DBA says, you're trying to do too much in your query.  With over 20 joins and 200+ fields in my MEDITECH DR query; I knew he was right.  Gary suggested Temp Tables over the Common Table Expression (CTE) I was using.

At 50,000 feet creating and using temp tables goes like this.

My newest script utilizing temp tables, can be found at the MEDITECH Open Source - SQL for Data Repository, and may be just what you need to improve performance on a T-SQL script of your own.

MEDITECH DR SQL Repository >> SQL Server Temp Table Script

SQL Server temp tables can be just the solution for queries with many joins on large MEDITECH DR tables. 

UPDATE: after implementing the query using temp tables, the run time improved from 9 hours to a blistering 52 seconds. 

References:

1. Temp Tables in SQL Server
2. SQL Server CTEs vs Temp Tables vs Table Variables
3. SQL Server Temporary Table Usage

Want an alert when the next post is published? Click the phone and Subscribe to the MEDITECH Reports Blog!