please optimize this query to remove joins

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

please optimize this query to remove joins

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-06-05 : 15:38:22

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-05 : 16:02:22

hard to tell without sample data and expceted output. See this link for how to supply that information:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

But, one thing that stands out is you are applying a function (DATEDIFF) to the WholeSaleStartDate so you cannot take advantage of any indexes on that column, assuming one exists. You should change that to something like:

AND w.wholesalestartdate <= DATEADD(DAY, - @duration, GETDATE())

But, even that may not be correct unless you strip off the Time portion of the DateTime.

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-06-05 : 16:12:12

Hi Lamprey,

I am sending all 5 tables involved in this relationship

1- users ( normal user)
2- wholesalers ( special users with extra benefits but they must purchanse more than minamount from wholesale criteria
3- wholesalecriteria ( min amount and durationd defined for wholesaler that he must meet that is. he must purchase more than min amount in duration number o days
4 orders table
5 order details table

CREATE TABLE [dbo].[Users](
[UserId] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](50) NOT NULL,
[Password] [varchar](50) NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[Gender] [varchar](50) NULL,
[Email] [varchar](200) NOT NULL,
[FAX] [varchar](50) NULL,
[DOB] [datetime] NULL,
[IsEnable] [bit] NOT NULL CONSTRAINT [DF_Users_IsEnable] DEFAULT ((0)),
[PrefferedLanguage] [varchar](50) NOT NULL,
[ReceiveEmails] [bit] NULL,
[IsWholesaler] [bit] NOT NULL CONSTRAINT [DF_Users_IsWholesaler] DEFAULT ((0)),
[Activekey] [uniqueidentifier] NULL CONSTRAINT [DF_Users_Activekey] DEFAULT (newid()),
[CreditCard] [varchar](1000) NULL,
[IsActive] [bit] NULL,
[EndDate] [datetime] NULL,
[CreatedBy] [varchar](50) NULL,
[CreatedOn] [datetime] NULL,
[ModifiedBy] [varchar](50) NULL,
[ModifiedOn] [datetime] NULL,
CONSTRAINT [PK_Users_1] PRIMARY KEY CLUSTERED
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

CREATE TABLE [dbo].[WholeSalers](
[WholesalerID] [int] IDENTITY(1,1) NOT NULL,
[userID] [int] NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[BusinessName] [varchar](50) NOT NULL,
[TaxIDNumber] [varchar](50) NOT NULL,
[Email] [varchar](50) NULL,
[Address] [varchar](50) NULL,
[City] [varchar](50) NULL,
[State] [varchar](50) NULL,
[PostalCode] [varchar](50) NULL,
[Phone] [varchar](50) NULL,
[FAX] [varchar](50) NULL,
[Comments] [varchar](500) NULL,
[WholeSaleStartDate] [datetime] NULL,
[WholeSaleEndDate] [datetime] NULL,
[ReminderSentOn] [datetime] NULL,
[statusChangedOn] [datetime] NULL,
[IsValidationRequired] [bit] NOT NULL CONSTRAINT [DF_WholeSalers_IsValidationRequired] DEFAULT ((1)),
[IsFailedWholeSaler] [bit] NULL,
[IsActive] [bit] NULL,
[EndDate] [datetime] NULL,
[CreatedBy] [varchar](50) NULL,
[CreatedOn] [datetime] NULL,
[ModifiedBy] [varchar](50) NULL,
[ModifiedOn] [datetime] NULL,
CONSTRAINT [PK_WholeSalers] PRIMARY KEY CLUSTERED
(
[WholesalerID] ASC,
[userID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

CREATE TABLE [dbo].[WholeSaleCriteria](
[CriteriaID] [int] IDENTITY(1,1) NOT NULL,
[minAmount] [money] NOT NULL,
[Duration] [int] NOT NULL,
[IsActive] [bit] NULL,
[EndDate] [datetime] NULL,
[CreatedBy] [varchar](50) NULL,
[CreatedOn] [datetime] NULL,
[ModifiedBy] [varchar](50) NULL,
[ModifiedOn] [datetime] NULL,
CONSTRAINT [PK_WholeSaleCriteria] PRIMARY KEY CLUSTERED
(
[CriteriaID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

CREATE TABLE [dbo].[Orders](
[orderId] [int] IDENTITY(1,1) NOT NULL,
[DateCreated] [smalldatetime] NOT NULL CONSTRAINT [DF_Orders_CreatedDate] DEFAULT (getdate()),
[DateShipped] [smalldatetime] NULL,
[verified] [bit] NOT NULL CONSTRAINT [DF_Orders_verified] DEFAULT ((0)),
[canceled] [bit] NOT NULL CONSTRAINT [DF_Orders_cancelled] DEFAULT ((0)),
[completed] [bit] NOT NULL CONSTRAINT [DF_Orders_completd] DEFAULT ((0)),
[comments] [varchar](50) NULL,
[customerName] [varchar](50) NULL,
[ShippingAddress] [varchar](50) NULL,
[CustomerEmail] [varchar](50) NULL,
[CustomerID] [int] NULL,
[status] [int] NULL,
[TranscID] [varchar](50) NULL,
[ShippingCost] [money] NULL,
[Discount] [money] NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[orderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Transaction ID of transaction' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Orders', @level2type=N'COLUMN',@level2name=N'TranscID'

CREATE TABLE [dbo].[OrderDetails](
[OrderID] [int] NOT NULL,
[ProductId] [int] NOT NULL,
[ProductName] [varchar](50) NOT NULL,
[Quantity] [int] NOT NULL,
[UnitCost] [money] NULL,
[SubTotal] AS ([Quantity]*[UnitCost]),
CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED
(
[OrderID] ASC,
[ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[OrderDetails] WITH CHECK ADD CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([orderId])
GO
ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [FK_OrderDetails_Orders]

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-06-05 : 16:14:20

Also,

Desired output is list of users who purchased more than minamount between today and wholsalestartdate where difference of both is greater or equal to duration number of days.

Regards,
Asif Hameed

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-06-05 : 23:17:18

Can anyone give me suggestion please ?

sparrow37
Posting Yak Master

148 Posts

Posted - 2009-06-08 : 03:33:50

Can someone optimize above query please ?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-06-08 : 10:09:21

Re-read the first link in my signature. You gave us the table structure, but no sample data, and no sample output.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources