Speed up some queries on my Microsoft SQL Server (Views: 100)
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 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)
select * into #tempTable from BigTable
<< Back to main page