looking value in table

Hello there
I have huge table with at least 20,000,000 records, On that I have Field1
who has unique values.
The same value sometimes shown on the same table on Field10 but not on the
same record and it isn't unique.
i need to build query that returs all the records with Field1Who exist
somewere on Field10.
Any query that i build took more then 1 hour to work.
How can i do it on query that will run fast?Roy, shalom
CREATE TABLE #Test (c1 INT NOT NULL PRIMARY KEY,c2 INT)
INSERT INTO #Test VALUES (1,10)
INSERT INTO #Test VALUES (2,1)
INSERT INTO #Test VALUES (3,4)
INSERT INTO #Test VALUES (4,5)
INSERT INTO #Test VALUES (5,4)
INSERT INTO #Test VALUES (6,7)
INSERT INTO #Test VALUES (7,10)
INSERT INTO #Test VALUES (8,1)
SELECT c1 FROM #Test
WHERE EXISTS (SELECT * FROM #Test T WHERE #Test.c1=T.c2)
Try create index on c1,c2 and see what is going on
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:e6sex1PbGHA.3740@.TK2MSFTNGP03.phx.gbl...
> Hello there
> I have huge table with at least 20,000,000 records, On that I have Field1
> who has unique values.
> The same value sometimes shown on the same table on Field10 but not on the
> same record and it isn't unique.
> i need to build query that returs all the records with Field1Who exist
> somewere on Field10.
> Any query that i build took more then 1 hour to work.
> How can i do it on query that will run fast?
>|||From what you had given, field1 is the primary key and it should have been
cluster-indexed. If so, then just do this
and try to execute the query
Create a non-clustered index on Field 10 alone
Hope this helps.
--
"Roy Goldhammer" wrote:

> Hello there
> I have huge table with at least 20,000,000 records, On that I have Field1
> who has unique values.
> The same value sometimes shown on the same table on Field10 but not on the
> same record and it isn't unique.
> i need to build query that returs all the records with Field1Who exist
> somewere on Field10.
> Any query that i build took more then 1 hour to work.
> How can i do it on query that will run fast?
>
>