Dear Dan,
Thanks for your timely help. Yes, removing the concatenation has reduced the
running time nearly 10 times . So finally my sql looks like the below,
SELECT distinct
a.playlist,a.material_id,a.destination_locator,a.destination,a.air_time,a.traffic_duration,a.comment,a.title,a.device_name,a.source_locator,a.source
from
cch_dubber_dublist_view_a1 a left outer join cch_dubber_dublist_view_b1 b ON
a.material_id = b.material_id AND
a.destination_locator = b.destination_locator AND
a.device_name = b.device_name
where b.material_id is null
The number of records returned is 3566. The time it takes is 18 seconds.
My boss wants it to run in 4-5 seconds. If the number of records r more like
4-5000 then it takes even longer. How can i reduce the time even further.
Can i index the tables or nething will that help.
Help is greatly appreciated.
Thanks,
Regards
Harpreet Kaur
>From: [EMAIL PROTECTED] (Dan Kokenge)
>To: [EMAIL PROTECTED] ("Harpreet Kaur")
>Subject: Re: [PHP-DB] Re: subtracting one query from the other
>Date: Tue, 02 Jul 2002 00:33:21 -0400
>
>
>Hi Harpreet
>
> >SELECT distinct a.playlist,a.material_id,a.destination_locator,
> >a.destination,a.air_time,a.traffic_duration,a.comment,a.title,
> >a.device_name,a.source_locator,a.source from record1 a left outer join
> >record2 b on
>
>>a.material_id+a.destination_locator+a.device_name=b.material_id+b.destination_locator+b.device_name
> >where b.material_id+b.destination_locator+b.device_name is null
> >
>
>Not sure why you concatenate the fields together. This will take some time
>to bring the fields together before the compare. Why not simply compare the
>separate fields. Also why do you compare all the fields for null. Seems
>like you only need to compare the material_id, that is of course you don't
>have a matching material_id that can be null - and I doubt that.
>--------------------
>SELECT DISTINCT
>a.playlist,
>a.material_id,
>a.destination_locator,
>a.destination,
>a.air_time,
>a.traffic_duration,
>a.comment,
>a.title,
>a.device_name,
>a.source_locator,
>a.source
>
>FROM record1 a
>
>LEFT OUTER JOIN record2 b
>ON a.material_id = b.material_id
>AND a.destination_locator = b.destination_locator
>AND a.device_name = b.device_name
>
>WHERE b.material_id is NULL
>-----------------
>if you need all three you can change the where to:
>WHERE b.material_id is NULL
>AND b.destination_locator is NULL
>AMD b.device_name is null
>
>Actually this is a fairly common type command and I use it a lot. I don't
>know anything about your files. You didn't mention how large the files
>were. If they are huge the time may be ok. If not you may want to see if
>it's it indexed properly. A index on material_id at a minimum. If this file
>is fairly static, with only a few inserts and deletes a minute, then I'd
>index the hell out of it, and put an index on all 3 fields. If it's a vary
>active file with lot of inserts and deletes, then you have to be careful on
>your index scheme. Also if one file is vary large and one very small, then
>it is much faster to read the smaller file id's into arrays, and then read
>the large file and see if it has a matching id in the array. I've been
>called in to consult on problems where I've had to do 6 or 7 joins against
>huge files that I've dropped run time from hours to less than 5 minutes
>doing the array thing.
>
>HTH.. Good luck..
>Dan
>
>
>__________________________________________________________________
>Your favorite stores, helpful shopping tools and great gift ideas.
>Experience the convenience of buying online with Shop@Netscape!
>http://shopnow.netscape.com/
>
>Get your own FREE, personal Netscape Mail account today at
>http://webmail.netscape.com/
_________________________________________________________________
Join the world’s largest e-mail service with MSN Hotmail.
http://www.hotmail.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
