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
 Efficient query required

Author  Topic 

ra_pa_ta
Starting Member

13 Posts
Posted - 2011-08-14 : 21:40:50
I have these tables Orders ,OrderDetails and OrderHistory and I need to apply these filter criteria to my result set

1. Get OrdersDetail records created that correspond with Orders creation (and up to the point in time the Order is pulled into the next extraction set).
• Joining with Orders table/feed on OrderID will be used to identify the delta records.
• Extract all OrdersDetail records where OrdersDetail.OrderID = Orders.OrderID and Orders.CreateDateTime > last extraction date.
2. Get OrdersDetail records created after the corresponding “Order” has been extracted.
• Joining with OrdersHistory table on OrderID will be used to identify the delta records
• Extract all OrdersDetail records where OrdersDetail.OrderID = OrdersHistory.OrderID and OrdersHistory.ProcessedDate > last extraction date AND where OrdersDetail.OrdersDetailID NOT IN scenario #1.
3. Get OrdersDetail records Updated since the last Extraction date
• Select all OrdersDetail records where OrdersDetail.LastUpdatedDateTime > Last Extraction date.

-----------------------------------------------------------------
This is the query I came up with ,but it take 10 minutes to run is there a more efficient way to write this?

select cast(od.OrderDetailID as varchar),cast(od.OrderID as varchar),cast(od.CustomerPrice as varchar),od.InStoreSKUNumber,
cast(od.AccountNumber as varchar),cast(od.MobileNumber as varchar),cast(od.ProductIDOriginal as varchar),cast(od.ProductIDReplacement as varchar),
od.IMEIReplacement,cast(od.OrderShipmentTrackingID as varchar),cast(od.InitialInstallment as varchar),od.StatusCode,od.Reason,
CONVERT(varchar,od.LastUpdatedDateTime,120),od.ChangedBy
from dbo.OrdersDetail od join dbo.Orders o
on od.OrderID = o.OrderID
where o.CreatedDateTime >= (Select Cast(Convert(varchar,getdate()-1,101) as datetime))
and o.CreatedDateTime < (Select Cast(Convert(varchar,getdate(),101) as datetime))
union
select cast(od.OrderDetailID as varchar),cast(od.OrderID as varchar),cast(od.CustomerPrice as varchar),od.InStoreSKUNumber,
cast(od.AccountNumber as varchar),cast(od.MobileNumber as varchar),cast(od.ProductIDOriginal as varchar),cast(od.ProductIDReplacement as varchar),
od.IMEIReplacement,cast(od.OrderShipmentTrackingID as varchar),cast(od.InitialInstallment as varchar),od.StatusCode,od.Reason,
CONVERT(varchar,od.LastUpdatedDateTime,120),od.ChangedBy
from dbo.OrdersDetail od join dbo.OrdersHistory oh on od.OrderID = oh.OrderID
where oh.ProcessedDate >= (Select Cast(Convert(varchar,getdate()-1,101) as datetime))
and oh.ProcessedDate < (Select Cast(Convert(varchar,getdate(),101) as datetime))
union
select cast(od.OrderDetailID as varchar),cast(od.OrderID as varchar),cast(od.CustomerPrice as varchar),od.InStoreSKUNumber,
cast(od.AccountNumber as varchar),cast(od.MobileNumber as varchar),cast(od.ProductIDOriginal as varchar),cast(od.ProductIDReplacement as varchar),
od.IMEIReplacement,cast(od.OrderShipmentTrackingID as varchar),cast(od.InitialInstallment as varchar),od.StatusCode,od.Reason,
CONVERT(varchar,od.LastUpdatedDateTime,120),od.ChangedBy
from dbo.OrdersDetail od
where od.LastUpdatedDateTime >= (Select Cast(Convert(varchar,getdate()-1,101) as datetime))
and od.LastUpdatedDateTime < (Select Cast(Convert(varchar,getdate(),101) as datetime))

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2011-08-14 : 21:45:36
We need to start with the execution plan and indexes. Please post that information for us to help you.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2011-08-14 : 21:54:30
1. why all the casting of columns to varchar ?
2. execute the query individually and see which one took the longest time.
3. do you have index on CreatedDateTime , ProcessedDate & LastUpdatedDateTime ?
4. the query looks identical with exception on the WHERE clause. You can combine 1st & 3rd query into one and change the WHERE clause to something like

WHERE 
(
    o.CreatedDateTime >= dateadd(day, datediff(day, 0, getdate()), -1) 
and o.CreatedDateTime < dateadd(day, datediff(day, 0, getdate()), 0)
)
OR
(
    o.LastUpdatedDateTime >= dateadd(day, datediff(day, 0, getdate()), -1) 
and o.LastUpdatedDateTime < dateadd(day, datediff(day, 0, getdate()), 0)
)


KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

ra_pa_ta
Starting Member

13 Posts
Posted - 2011-08-15 : 13:14:04
Thank you all for your ideas .I checked on the idexes on the tables and two of them did’nt have them and now the query runs instantaneously . I tried to combine the where clause of the first and third query but that seemed to make the query slower so I am sticking with this one.I have included the showplan but I could'nt make out much from it , can you please tell me what I should be looking for in it ? Here is the showplan.

StmtText
|--Parallelism(Gather Streams)
|--Sort(DISTINCT ORDER BY:([Union1049] ASC, [Union1050] ASC, [Union1051] ASC, [Union1052] ASC, [Union1053] ASC, [Union1054] ASC, [Union1055] ASC, [Union1056] ASC, [Union1057] ASC, [Union1058] ASC, [Union1059] ASC, [Union1060] ASC, [Union1061] ASC, [Union1062] ASC, [Union1063] ASC))
|--Concatenation
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1007], [Expr1008], [Expr1009], [od].[InStoreSKUNumber], [Expr1010], [Expr1011], [Expr1012], [Expr1013], [od].[IMEIReplacement], [Expr1014], [Expr1015], [od].[StatusCode], [od].[Reason], [Expr1016], [od].[ChangedBy]))
| |--Compute Scalar(DEFINE:([Expr1007]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[OrderDetailID] as [od].[OrderDetailID],0), [Expr1008]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[OrderID] as [od].[OrderID],0), [Expr1009]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[CustomerPrice] as [od].[CustomerPrice],0), [Expr1010]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[AccountNumber] as [od].[AccountNumber],0), [Expr1011]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[MobileNumber] as [od].[MobileNumber],0), [Expr1012]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[ProductIDOriginal] as [od].[ProductIDOriginal],0), [Expr1013]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[ProductIDReplacement] as [od].[ProductIDReplacement],0), [Expr1014]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[OrderShipmentTrackingID] as [od].[OrderShipmentTrackingID],0), [Expr1015]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[InitialInstallment] as [od].[InitialInstallment],0), [Expr1016]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[LastUpdatedDateTime] as [od].[LastUpdatedDateTime],120)))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([o].[OrderID]) OPTIMIZED)
| |--Parallelism(Distribute Streams, RoundRobin Partitioning)
| | |--Index Seek(OBJECT:([HandsetOrder].[dbo].[Orders].[IX_Orders_CreatedDateTime] AS [o]), SEEK:([o].[CreatedDateTime] >= CONVERT(datetime,CONVERT(varchar(30),getdate()-'1900-01-04 00:00:00.000',101),0) AND [o].[CreatedDateTime] < CONVERT(datetime,CONVERT(varchar(30),getdate(),101),0)) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([HandsetOrder].[dbo].[OrdersDetail].[IX_OrdersDetail_OrderID] AS [od]), SEEK:([od].[OrderID]=[HandsetOrder].[dbo].[Orders].[OrderID] as [o].[OrderID]) ORDERED FORWARD)
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1024], [Expr1025], [Expr1026], [od].[InStoreSKUNumber], [Expr1027], [Expr1028], [Expr1029], [Expr1030], [od].[IMEIReplacement], [Expr1031], [Expr1032], [od].[StatusCode], [od].[Reason], [Expr1033], [od].[ChangedBy]))
| |--Compute Scalar(DEFINE:([Expr1024]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[OrderDetailID] as [od].[OrderDetailID],0), [Expr1025]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[OrderID] as [od].[OrderID],0), [Expr1026]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[CustomerPrice] as [od].[CustomerPrice],0), [Expr1027]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[AccountNumber] as [od].[AccountNumber],0), [Expr1028]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[MobileNumber] as [od].[MobileNumber],0), [Expr1029]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[ProductIDOriginal] as [od].[ProductIDOriginal],0), [Expr1030]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[ProductIDReplacement] as [od].[ProductIDReplacement],0), [Expr1031]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[OrderShipmentTrackingID] as [od].[OrderShipmentTrackingID],0), [Expr1032]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[InitialInstallment] as [od].[InitialInstallment],0), [Expr1033]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[LastUpdatedDateTime] as [od].[LastUpdatedDateTime],120)))
| |--Nested Loops(Inner Join, OUTER REFERENCES:([oh].[OrderID], [Expr1070]) OPTIMIZED WITH UNORDERED PREFETCH)
| |--Index Seek(OBJECT:([HandsetOrder].[dbo].[OrdersHistory].[IX_OrdersHistory_ProcessedDate] AS [oh]), SEEK:([oh].[ProcessedDate] >= CONVERT(datetime,CONVERT(varchar(30),getdate()-'1900-01-04 00:00:00.000',101),0) AND [oh].[ProcessedDate] < CONVERT(datetime,CONVERT(varchar(30),getdate(),101),0)) ORDERED FORWARD)
| |--Clustered Index Seek(OBJECT:([HandsetOrder].[dbo].[OrdersDetail].[IX_OrdersDetail_OrderID] AS [od]), SEEK:([od].[OrderID]=[HandsetOrder].[dbo].[OrdersHistory].[OrderID] as [oh].[OrderID]) ORDERED FORWARD)
|--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1039], [Expr1040], [Expr1041], [od].[InStoreSKUNumber], [Expr1042], [Expr1043], [Expr1044], [Expr1045], [od].[IMEIReplacement], [Expr1046], [Expr1047], [od].[StatusCode], [od].[Reason], [Expr1048], [od].[ChangedBy]))
|--Compute Scalar(DEFINE:([Expr1039]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[OrderDetailID] as [od].[OrderDetailID],0), [Expr1040]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[OrderID] as [od].[OrderID],0), [Expr1041]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[CustomerPrice] as [od].[CustomerPrice],0), [Expr1042]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[AccountNumber] as [od].[AccountNumber],0), [Expr1043]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[MobileNumber] as [od].[MobileNumber],0), [Expr1044]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[ProductIDOriginal] as [od].[ProductIDOriginal],0), [Expr1045]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[ProductIDReplacement] as [od].[ProductIDReplacement],0), [Expr1046]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[OrderShipmentTrackingID] as [od].[OrderShipmentTrackingID],0), [Expr1047]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[InitialInstallment] as [od].[InitialInstallment],0), [Expr1048]=CONVERT(varchar(30),[HandsetOrder].[dbo].[OrdersDetail].[LastUpdatedDateTime] as [od].[LastUpdatedDateTime],120)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1036], [od].[OrderID], [Expr1071]) OPTIMIZED WITH UNORDERED PREFETCH)
|--Parallelism(Repartition Streams, RoundRobin Partitioning)
| |--Index Seek(OBJECT:([HandsetOrder].[dbo].[OrdersDetail].[IX_OrdersDetail_LastUpdatedDateTime] AS [od]), SEEK:([od].[LastUpdatedDateTime] >= CONVERT(datetime,CONVERT(varchar(30),getdate()-'1900-01-04 00:00:00.000',101),0) AND [od].[LastUpdatedDateTime] < CONVERT(datetime,CONVERT(varchar(30),getdate(),101),0)) ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([HandsetOrder].[dbo].[OrdersDetail].[IX_OrdersDetail_OrderID] AS [od]), SEEK:([od].[OrderID]=[HandsetOrder].[dbo].[OrdersDetail].[OrderID] as [od].[OrderID] AND [Uniq1036]=[Uniq1036]) LOOKUP ORDERED FORWARD)
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2011-08-15 : 13:16:31
It sounds like you fixed it:

quote:

I checked on the idexes on the tables and two of them did’nt have them and now the query runs instantaneously.


Do you need further help?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2011-08-15 : 13:31:07
This should give the same results and may be faster without the UNIONs:
SELECT od.OrderDetailID, od.OrderID, od.CustomerPrice, od.InStoreSKUNumber,
od.AccountNumber, od.MobileNumber, od.ProductIDOriginal, od.ProductIDReplacement,
od.IMEIReplacement, od.OrderShipmentTrackingID, od.InitialInstallment, 
od.StatusCode,od.Reason, CONVERT(VARCHAR,od.LastUpdatedDateTime,120), od.ChangedBy
FROM dbo.OrdersDetail od 
WHERE 
EXISTS(SELECT * FROM dbo.Orders o 
	WHERE od.OrderID = o.OrderID AND 
	o.CreatedDateTime >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -1)
	AND o.CreatedDateTime < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
OR EXISTS(SELECT * FROM dbo.OrdersHistory oh 
	WHERE od.OrderID = oh.OrderID AND
	oh.ProcessedDate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -1)
	AND oh.ProcessedDate < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
OR (od.LastUpdatedDateTime >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -1) 
	AND od.LastUpdatedDateTime < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
Go to Top of Page

ra_pa_ta
Starting Member

13 Posts
Posted - 2011-08-15 : 14:20:07
My query does run fast now but I just want to know what I need to look for in the execution plan for my learning.
robvolk thank you for the query using EXISITS instead of UNION.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site