2009. december 21., hétfő

Speed up some queries on my Microsoft SQL Server


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

Nincsenek megjegyzések:

Megjegyzés küldése