Author: Daaron Dwyer
What can I do to speed up some queries on my Microsoft SQL Server?
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
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)
select * into #tempTable from BigTable