Speed up some queries on my Microsoft SQL Server (Views: 707)
|
Problem/Question/Abstract: 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 |