September 30, 2008 at 2:29 am
selectpf_id,1,1,1,name,name,ltrim(rtrim(description)) Short_description,ltrim(rtrim(description)) description,NULL
, '~/Images/'+image_thumb
fromSAN_TEST_CD..cd_Product
left outer join SAN_TEST_CD..shirt_internetstock sh on pf_id= comp_grn+shade
and not exists(select * from products)
group bypf_id,name,name,ltrim(rtrim(description)),ltrim(rtrim(description)),'~/Images/'+image_thumb
-----------------------------------------------------------
Above query tkes 4 seconds to execute and it returns 71996 records. If I comment "and not exists(select * from products)" line then it takes 16 seconds and returns the same number of records (71996) Actualy, I dint understand the difference. There are 71664 records in products tables..
What diff it makes by adding "and not exists(select * from products)" condition..?
Thanks,
Santhosh M.V.
September 30, 2008 at 3:50 am
You are not qualifying the "and not exists..." part of the query.
I presume it should be something along the lines of:-
and not exists (select * from products where product.productid = sh.productid).
With your current query, as long as there is a single record in the Product table, your left join will return nothing. I expect the SQL Server query optimiser will have spotted this, and will use a query plan that completely ignore the left join table. When you comment the "and not exists out, it then has to include the left join table.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply