On Tuesday 29 April 2003 12:31, Rajesh Kumar Mallah wrote:
> Hi Can anyone tell if the case below is an acceptable
> performance ?
>
> I have a query that returns data and creates a table
> in 3 mins approx. This query is optimised and uses appropriate
> indexes for the NOT EXISTS part.
>
> CREATE TABLE t_a as SELECT
> email,country_code,city,title1,fname1,mname1,lname1,website,address,source,
>ifimporter, ifexporter,ifservice,ifmanu,creation_date from
> general.email_bank_import where not exists (select * from
> general.profile_master where
> email=general.email_bank_import.email) ;
> SELECT
> Time: 174637.31 ms (3 mins Approx)
>
>
>
> The problem is when i try to INSERT the data into another table
> it takes 23 mins Apprx to inser 412331 records the same query.
>
> I am providing the various details below:
>
> tradein_clients=# INSERT INTO general.profile_master
> (email,country_code,city,title1,fname1,mname1,lname1,website,address,source
>,ifimporter,ifexporter, ifservice, ifmanu,creation_date) SELECT
> email,country_code,
> city,title1,fname1,mname1,lname1,website,address,source,ifimporter,ifexport
>er,ifservice, ifmanu,creation_date from general.email_bank_import where
> not exists (select * from general.profile_master where
> email=general.email_bank_import.email) ;
> INSERT 0 412331
> Time: 1409510.63 ms
I am not sure if this would help but why you have to use all the fields in not
exists clause? How about not exists for a name or profile_id? Would it be any
faster
I assume if there are two records with half the info same, then not exists for
1 field with index would be significantly faster than 10 fields.
HTH
Shridhar
