LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » please optimize this query to remove joins

please optimize this query to remove joins

Options

Previous Topic · Next Topic

sparrow37

Posted: Friday, June 05, 2009 2:45:55 PM

Rank: Newbie

Joined: 6/5/2009
Posts: 1
Points: 3
Where do you live?: lahore

Hi all,

I have written following query which uses lots of joins. can you please remove extra joins which are insise subquery as well as outer query. I want to get all users who are active and are wholesaler but they have met wholesaler criteria ( i.e) purchased more than min amount between today and date when they became wholesalers but the difference between these two date must be equal to or greater than wholesale criteria duration.

My query is:

-- select all users whose wholesaler bit is on
-- and they are active
-- and they have purchased more than min amount
-- between today and their wholesaler startdate
-- where difference between today and wholesale startdate > = duration.

DECLARE @duration int
DECLARE @minAmount money

-- get Wholesale criteria from wholesalecriteria table
select @duration = duration,
@minAmount = minAmount
from
WholesaleCriteria
where
isActive = 1

select * ,w.*
from users u inner join Wholesalers w
on u.userid = w.userid
where
w.isactive = 1 -- select active wholesaers only
and
u.isactive = 1 -- select active users only
and u.iswholesaler = 1 -- select wholesalers only
and w.IsValidationRequired = 1 -- temporary Wholesalers who must meet wholesalecriteria
and datediff(day,w.wholesalestartdate,getdate()) >= @duration -- difference od wholesalestartdate and today >= @duration
and u.userid in

( -- select all customers who have purchased more than minamount between their wholesalestartdate and today
select O.Customerid
from orders o inner join orderdetails od
on o.Orderid = od.Orderid
inner join wholesalers w
on w.userid = o.customerid
where o.DateCreated
--between dateadd(day, -@Duration, getdate()) and getdate()
between w.wholesalestartdate and getdate()
and datediff(day,w.wholesalestartdate,getdate()) >= @duration
group by O.customerid
having sum(od.subtotal) > @duration

Also , will following condition from above query work or fail.

where o.DateCreated
--between dateadd(day, -@Duration, getdate()) and getdate()
between w.wholesalestartdate and getdate()
and datediff(day,w.wholesalestartdate,getdate()) >= @duration

I have added it to get all orders which were created between today and the date when user became wholesaler but difference between 2 dates is >= @duration ( e.g 7 days). If it is not ok then is commented code better. please give me better solution to it as current query is very slow.

Waiting for your response,

Regards,
Asif Hameed

bklr

Posted: Monday, August 17, 2009 3:57:17 AM

Rank: PFY

Joined: 1/21/2009
Posts: 27
Points: 81
Where do you live?: India

see this link it may useful
http://tonesdotnetblog.wordpress.com/2008/05/26/twelve-tips-for-optimising-sql-server-2005-queries/

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.