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 worlds 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
