Author: Daaron Dwyer 


What can I do to speed up some queries on my Microsoft SQL Server?

Answer:

I have found that queries like: 

select * from table1 innerjoin table2 on table1.field=table2.field 

...sometimes will query quickly, but takes time to return a result. 

The solution I have found to work is to insert the first query to a temporary 
table, then query the second, like: 


select * into #temptable from table1 innerjoin table2 on table1.field=table2.field 

select * from #temptable 


The "#temptable" can be anything starting with the pound sign.  The temporary table 
will be released when your connection is closed.

I have found what I think is the answer here-- table locking. 
When I query active tables, I fight with other applications having locks on various 
rows and tables.  When the query takes part into a temporary table, the lock is not 
there. 

This article then has a really silly premise, I concur. 

What should be used rather than temporary tables in a select statement is the "with 
(nolock)" feature that does a dirty read. Like: 

select * from BigTable with (nolock) 

rather than: 
select * into #tempTable from BigTable