Query Help...very slow running
-
Good morning,
does anyone have any suggestions as to how I might be able to rewrite this query? It is taking forever to produce results. Thanks
SELECT timestamp, Computer_Name, Packet_Type, User_Name, Client_IP_Address, CASE WHEN(SELECT a2.Packet_Type
FROM accounting_data a2
WHERE a1.[timestamp] = a2.[timestamp] AND a2.Packet_Type <> 1) = 2 THEN 'Pass' WHEN
(SELECT a2.Packet_Type
FROM accounting_data a2
WHERE a1.[timestamp] = a2.[timestamp] AND a2.Packet_Type <> 1) = 3 THEN 'Fail' END AS Status
FROM accounting_data_BU AS a1
WHERE (Packet_Type = 1) AND (CONVERT(char(10), timestamp, 120) = '2012-01-23')
-
Try starting with this:
SELECT a1.timestamp, a1.Computer_Name, a1.Packet_Type, a1.User_Name, a1.Client_IP_Address,CASE
WHEN a2.Packet_Type = 2 THEN 'Pass'
WHEN a2.Packet_Type = 3 THEN 'Fail'
END AS Status
FROM accounting_data_BU AS a1
INNER JOIN accounting_data AS a2
ON a1.timestamp = a2.timestamp AND a2.packet_type <> 1
WHERE (Packet_Type = 1) AND (CONVERT(char(10), timestamp, 120) = '2012-01-23')
Can you please provide the ddl for accounting_data_BU and accounting_data along with some sample data?
Jared
CE - Microsoft -
You've got a non-SARGable predicate in the where clause. Change that date predicate that you have to this:
timestamp >= '2012-01-23' AND timestamp < DATEADD(dd,1, '2012-01-23')That assumes that the date literal is actually a parameter or variable or specified from somewhere.
If that doesn't help, please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
You're WHERE clause is not SARGable, because of the conversion of the timestamp field to character. Functions on fields generally prevent them from being SARGable, meaning that you cannot take advantage of indices on those fields. Instead of converting the field to character, specify the range you are interested in. Generally with dates, it's best to include the starting point and exclude the end point. Here I am calculating midnight today to midnight tomorrow.
WHERE (Packet_Type = 1)
AND timestamp >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
AND timestamp < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Thank you all for your responses! I used the query that "sqlknowitall" reworked for me and it is much more efficient. Thanks to all that responded.
-
mbrady5 (1/25/2012)
Thank you all for your responses! I used the query that "sqlknowitall" reworked for me and it is much more efficient. Thanks to all that responded.Great! However, don't discount what the others have said. As your data grows, this query will slow down if you do not make the appropriate changes to make the predicates in the WHERE clause SARGable.
Jared
CE - Microsoft -
If you add in what Gail and Drew have mentioned, you'll most likely see another significant performance gain. Chances are if you're running the optimized version that was provided on a few thousands records you will be happy with the outcome, However, it may not necessarily perform the same on a few million rows (where an index seek will out perform a table scan)...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
OK, here is my query now....are you all suggesting I make additional changes to enhance performance? Thanks
SELECT a1.timestamp, a1.Computer_Name, a1.Packet_Type, a1.User_Name, a1.Client_IP_Address,CASE WHEN a2.Packet_Type = 2 THEN 'Pass' WHEN a2.Packet_Type = 3 THEN 'Fail' END AS Status
FROM accounting_data AS a1 INNER JOIN
accounting_data AS a2 ON a1.timestamp = a2.timestamp AND a2.Packet_Type <> 1
WHERE (a1.Packet_Type = 1) AND (CONVERT(char(10), a2.timestamp, 120) = @date)
-
Yes. Change this:
(CONVERT(char(10), a2.timestamp, 120) = @date)to this:
timestamp > = @date AND timestamp < DATEADD(dd,1, @date)EDIT: Grr... No matter what I do I cannot change the > to > or the < to < :hehe:
Jared
CE - Microsoft -
Just so you know, it's the string function that gives you the non-SARGable predicate in the WHERE clause:
WHERE this = that AND
(CONVERT(char(10), a2.timestamp, 120) = @date)
As suggested, changing it to:
timestamp >= @date AND timestamp < DATEADD(dd,1, @date)
or
AND timestamp >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)AND timestamp < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1)
will give you the best performance.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
The rewrite to >= @date AND timestamp < DATEADD(dd,1, @date) is a sound practice, but it is not actually required to make the predicate SARGable. A simple CONVERT of the column to the date data type is SARGable:
DECLARE @data AS TABLE
(
DateColumn datetime NOT NULL PRIMARY KEY,
IntData integer NOT NULL
);
INSERT @data
(DateColumn, IntData)
VALUES
('2012-01-15 13:26:47', 100),
('2012-01-15 19:11:05', 200),
('2012-01-16 00:34:42', 250);
SELECT *
FROM @data AS d
WHERE CONVERT(date, d.DateColumn) = '2012-01-15';
Execution plan:
For more information, see http://sqlblog.com/blogs/paul_white/archive/2012/01/18/dynamic-seeks-and-hidden-implicit-conversions.aspx
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply