placing table on the memory

Hello there
I have reference localAreaCode table with 3000 records , i need to check on
existing phone table that the area code exist on LocalAreaCode table.
So far the query worked very slow.
Is there a way to place the LocalAreaCode table at the memory, to inprove
perfomance?Hi Roy,
In SQL Server 2000 you can use:
1) DBCC PINTABLE
2) sp_tableoption
See Books Online for more info about this.
I'm pretty sure that DBCC PINTABLE does nothing in SQL Server 2005 and
for sp_tableoption the choice to pin a table in memory is no longer
there. The rationale is that using these options can cause bad things
to happen. For example, in SQL Server 2005 Books Online we have the
following in the DBCC PINTABLE entry:
==============================
This functionality was introduced for performance in SQL Server version
6.5. DBCC PINTABLE has highly unwanted side-effects. These include the
potential to damage the buffer pool. DBCC PINTABLE is not required and
has been removed to prevent additional problems. The syntax for this
command still works but does not affect the server.
==============================
Sorry I can't be of more help|||Maybe we should first take look at the query. Please post it.
Also check the execution plan to see whether indexes are used properly. The
table is indexed, isn't it?
ML
http://milambda.blogspot.com/|||You could do this in 2000, using sp_tableoption. This was misused, though so
I believe that removed
it in 2005. If the table is accessed frequently enough, it will be cache in
memory, especially with
only 3000 rows. Still, you want to make sure that queries against it are eff
icient (create
supporting indexes etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roy Goldhammer" <roy@.hotmail.com> wrote in message news:uetz2fwfGHA.4276@.TK2MSFTNGP03.phx.
gbl...
> Hello there
> I have reference localAreaCode table with 3000 records , i need to check o
n existing phone table
> that the area code exist on LocalAreaCode table.
> So far the query worked very slow.
> Is there a way to place the LocalAreaCode table at the memory, to inprove
perfomance?
>|||With only 3000 rows this should never be slow, so I suspect problems
with the table design or the query. You need to post the table
design, all keys and indexes, and the query that is slow.
Roy Harvey
Beacon Falls, CT
On Wed, 24 May 2006 10:58:04 +0300, "Roy Goldhammer" <roy@.hotmail.com>
wrote:

>Hello there
>I have reference localAreaCode table with 3000 records , i need to check on
>existing phone table that the area code exist on LocalAreaCode table.
>So far the query worked very slow.
>Is there a way to place the LocalAreaCode table at the memory, to inprove
>perfomance?
>