Sub-selects taking way too long.. 
Author Message
 Sub-selects taking way too long..

Quote:

> Ok.. I know its provably something im doing dumb..
> but here it goes..

> I have 2 tables that are the same:

> "temp_table" and "table"

> "temp _table" has 7,761 rows and "table" is empty

> the columns for both tables are: ID (primary key sequence), index, column1,
> column2

> when i run:

> Insert Into table
> select index, column1, column2
> from temp_table
> where index NOT IN (select index from table)

IN is unfortunately implemented slowly (I think the FAQ answer has more
details)

You can often get better performance using exists, I think the equivalent
would be:
insert into table
select index, column1, column2 from temp_table
where NOT EXISTS (select * from table where table.index=temp_Table.index)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://www.***.com/



Sun, 26 Sep 2004 05:07:08 GMT
 Sub-selects taking way too long..

thanks i'll try that :)
Quote:
----- Original Message -----



Sent: Tuesday, April 09, 2002 3:56 PM
Subject: Re: [GENERAL] Sub-selects taking way too long..


> > Ok.. I know its provably something im doing dumb..
> > but here it goes..

> > I have 2 tables that are the same:

> > "temp_table" and "table"

> > "temp _table" has 7,761 rows and "table" is empty

> > the columns for both tables are: ID (primary key sequence), index,
column1,
> > column2

> > when i run:

> > Insert Into table
> > select index, column1, column2
> > from temp_table
> > where index NOT IN (select index from table)

> IN is unfortunately implemented slowly (I think the FAQ answer has more
> details)

> You can often get better performance using exists, I think the equivalent
> would be:
> insert into table
> select index, column1, column2 from temp_table
> where NOT EXISTS (select * from table where table.index=temp_Table.index)

> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?

> http://archives.postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command




Sun, 26 Sep 2004 05:14:33 GMT
 Sub-selects taking way too long..
cool.. used the EXISTS and now it does it in 3 seconds instead of 40
minutes..
wow.. heheh

ThanX!! hehe

Quote:
----- Original Message -----


Sent: Tuesday, April 09, 2002 4:09 PM
Subject: Re: [GENERAL] Sub-selects taking way too long..

> thanks i'll try that :)

> ----- Original Message -----



> Sent: Tuesday, April 09, 2002 3:56 PM
> Subject: Re: [GENERAL] Sub-selects taking way too long..


> > > Ok.. I know its provably something im doing dumb..
> > > but here it goes..

> > > I have 2 tables that are the same:

> > > "temp_table" and "table"

> > > "temp _table" has 7,761 rows and "table" is empty

> > > the columns for both tables are: ID (primary key sequence), index,
> column1,
> > > column2

> > > when i run:

> > > Insert Into table
> > > select index, column1, column2
> > > from temp_table
> > > where index NOT IN (select index from table)

> > IN is unfortunately implemented slowly (I think the FAQ answer has more
> > details)

> > You can often get better performance using exists, I think the
equivalent
> > would be:
> > insert into table
> > select index, column1, column2 from temp_table
> > where NOT EXISTS (select * from table where

table.index=temp_Table.index)

Quote:

> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?

> > http://archives.postgresql.org

> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Sun, 26 Sep 2004 06:13:09 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Select with view join takes too long.

2. select into table takes too long

3. Select with view join takes too long

4. SELECT Takes Long Time to Execute]

5. select-sql takes too long resolution, new question

6. Select Count(*) FROM taking a long time

7. select-sql takes too long

8. PB6: SELECT Takes Long Time to Execute

9. Select query taking extremely long time

10. Simple SELECT taking long time...

11. select * is taking too long

12. SELECT takes too long


 
Powered by phpBB® Forum Software