March 23, 2011 at 1:27 pm
I have a some what interesting sql select query that I run a daily bases. When I run it CPU usage goes to 100% for about 25 secs ( well enough to run the query ). If I change the where clause it will run faster but the results are not exactly what I need.
Here is the query that I run. I know there is most likely a better why to do this, would someone help me with this?
Where is the query:
SELECT dbo.Notes.Completed, CONVERT(char(10), dbo.Notes.FollowUp, 101) AS Followup, dbo.Notes.Date, dbo.Notes.Note, dbo.Notes.LeadingNoteID,
dbo.[Active Patients].PatientID, dbo.[Active Patients].FirstName, dbo.[Active Patients].LastName, dbo.[Active Patients].ReferredBy,
dbo.[Active Patients].ProviderAcctNumber, dbo.[Active Patients].Status, dbo.Notes.NoteID, dbo.Notes.Enteredby, dbo.Notes.Action, dbo.Notes.Priority,
dbo.Notes.[Group], dbo.Notes.[Collection Priority], dbo.Notes.Response, dbo.Notes.Attachment, dbo.[Active Patients].[Admit Date], dbo.[Active Patients].Discharge,
dbo.ARorGBal.ARB, REPLACE(CONVERT(char(10), dbo.Notes.FollowUp, 111), '/', '') AS Expr1, dbo.Notes.Dialer, dbo.REF.RefCODE
FROM dbo.[Active Patients] INNER JOIN
dbo.Notes ON dbo.[Active Patients].PatientID = dbo.Notes.PatientID INNER JOIN
dbo.ARorGBal ON dbo.[Active Patients].PatientID = dbo.ARorGBal.PatientID INNER JOIN
dbo.REF ON dbo.[Active Patients].ReferredBy = dbo.REF.RefID
WHERE
(dbo.Notes.Completed = 0) AND (dbo.[Active Patients].Status LIKE N'%0 - In House%') AND (dbo.Notes.Priority LIKE N'%UT Initial%') or
(dbo.Notes.Completed = 0) AND (dbo.Notes.Dialer IS NULL OR dbo.Notes.Dialer = 0) AND (CONVERT(datetime, dbo.Notes.FollowUp, 101) BETWEEN CONVERT(datetime, GETDATE() - 9000 + '00:00:00', 101) AND CONVERT(datetime, GETDATE() - 1 + '23:59:59', 101)) AND (NOT (dbo.[Active Patients].Status LIKE N'%0 - In House%')) OR
(dbo.Notes.Completed = 0) AND (dbo.Notes.Dialer IS NULL OR dbo.Notes.Dialer = 0) AND (CONVERT(datetime, dbo.Notes.FollowUp, 101) BETWEEN CONVERT(datetime, GETDATE() - 9000 + '00:00:00', 101) AND CONVERT(datetime, GETDATE() - 1 + '23:59:59', 101)) AND (dbo.[Active Patients].Status LIKE N'%0 - In House%') AND (dbo.Notes.Priority LIKE N'%MED Initial%')
March 23, 2011 at 1:36 pm
well, i can point to a few things that you might take under consideration:
If you use LIKE '%..., it's a guaranteed table scan...that means every rows gotta be analyzed. that means the servers gonna use whatever it can to try and do the job.
you have a lot of OR statements as well.
that can also cause a full table scan as well; sometimes what you want to do instead is to UNION different selects together in order to get the data you want instead.
in the WHERE statements, you've got a CONVERT(DATETIMECOLUMN) BETWEEN ....
if you can leave the column alone, it might take advantage of an existing index...but if you wrap it in a function, it will also require a table scan...(every column value converted, then sued to test the BETWEEN)
Lowell
March 23, 2011 at 1:41 pm
looking again, it looks like you have some fields that are varchar, but storing dates in them, hence your conversions.
can you convert the columns to real datetime fields instead?
if not, can you add a persisted calculated column that converts that field to datetime, and then put an index the persisted calculation instead?
Lowell
March 23, 2011 at 1:42 pm
Thank You for responding.
In regards to the time and date. I also have tried (REPLACE(CONVERT(char(10), dbo.Notes.FollowUp, 111), '/', '') <= REPLACE(CONVERT(char(10), GETDATE(), 111), '/', '')).
But I still get the same CPU percentage.
What could I do different with the like and the OR?
March 23, 2011 at 1:46 pm
I just check the table and followup is a datetime column.
Thank You
March 23, 2011 at 1:48 pm
well, as an example of the OR, something like this might help...the three queries might be faster seperately than together...you'd have to test:
SELECT [STUFF] FROM [...]
WHERE (dbo.Notes.Completed = 0)
AND (dbo.[Active Patients].Status LIKE N'%0 - In House%')
AND (dbo.Notes.Priority LIKE N'%UT Initial%')
UNION ALL
SELECT [STUFF] FROM [...]
WHERE ISNULL(dbo.Notes.Dialer,0) = 0
AND (CONVERT(datetime, dbo.Notes.FollowUp, 101) BETWEEN CONVERT(datetime, GETDATE() - 9000 + '00:00:00', 101) AND CONVERT(datetime, GETDATE() - 1 + '23:59:59', 101))
AND (NOT (dbo.[Active Patients].Status LIKE N'%0 - In House%'))
SELECT [STUFF] FROM [...]
WHERE (dbo.Notes.Completed = 0)
AND (dbo.Notes.Dialer IS NULL OR dbo.Notes.Dialer = 0)
AND (CONVERT(datetime, dbo.Notes.FollowUp, 101) BETWEEN CONVERT(datetime, GETDATE() - 9000 + '00:00:00', 101) AND CONVERT(datetime, GETDATE() - 1 + '23:59:59', 101))
AND (dbo.[Active Patients].Status LIKE N'%0 - In House%')
AND (dbo.Notes.Priority LIKE N'%MED Initial%')
Lowell
March 23, 2011 at 1:56 pm
nick 13424 (3/23/2011)
I just check the table and followup is a datetime column.Thank You
ok if it is a datetime, isn't this selecting a date that is between , say 9000 days ago(1986-08-01) and now?
do you have any real dates outside that daterange?
AND (CONVERT(datetime, dbo.Notes.FollowUp, 101) BETWEEN CONVERT(datetime, GETDATE() - 9000 + '00:00:00', 101) AND CONVERT(datetime, GETDATE() - 1 + '23:59:59', 101))
to keep everything in the same datatype, you can use DATEADD to get teh between dates instead:
AND dbo.Notes.FollowUp
BETWEEN --Midnight for the Current Day
DATEADD(dd, DATEDIFF(dd,0,getdate() - 9000), 0)
--Midnite tomorrow minus 3 milliseconds
AND DATEADD(ms,-3,DATEADD(dd, DATEDIFF(dd,0,getdate()+ 1 ), 0))
Lowell
March 23, 2011 at 2:17 pm
Thank You for your Help. I changed the Where clause up a little bit and that seemed to help. I wished I could make it faster but I guess this is the best it can get.
I tried the date format you outline but it would miss some records.
Old
SELECT dbo.Notes.Completed, CONVERT(char(10), dbo.Notes.FollowUp, 101) AS Followup, dbo.Notes.Date, dbo.Notes.Note, dbo.Notes.LeadingNoteID,
dbo.[Active Patients].PatientID, dbo.[Active Patients].FirstName, dbo.[Active Patients].LastName, dbo.[Active Patients].ReferredBy,
dbo.[Active Patients].ProviderAcctNumber, dbo.[Active Patients].Status, dbo.Notes.NoteID, dbo.Notes.Enteredby, dbo.Notes.Action, dbo.Notes.Priority,
dbo.Notes.[Group], dbo.Notes.[Collection Priority], dbo.Notes.Response, dbo.Notes.Attachment, dbo.[Active Patients].[Admit Date], dbo.[Active Patients].Discharge,
dbo.ARorGBal.ARB, REPLACE(CONVERT(char(10), dbo.Notes.FollowUp, 111), '/', '') AS Expr1, dbo.Notes.Dialer, dbo.REF.RefCODE
FROM dbo.[Active Patients] INNER JOIN
dbo.Notes ON dbo.[Active Patients].PatientID = dbo.Notes.PatientID INNER JOIN
dbo.ARorGBal ON dbo.[Active Patients].PatientID = dbo.ARorGBal.PatientID INNER JOIN
dbo.REF ON dbo.[Active Patients].ReferredBy = dbo.REF.RefID
WHERE (dbo.Notes.Completed = 0) AND (REPLACE(CONVERT(char(10), dbo.Notes.FollowUp, 111), '/', '') <= REPLACE(CONVERT(char(10), GETDATE(), 111), '/', '')) AND
(dbo.Notes.Dialer IS NULL OR
dbo.Notes.Dialer = 0) AND (NOT (dbo.[Active Patients].Status LIKE N'%0 - In House%')) OR
(dbo.Notes.Completed = 0) AND (REPLACE(CONVERT(char(10), dbo.Notes.FollowUp, 111), '/', '') <= REPLACE(CONVERT(char(10), GETDATE(), 111), '/', '')) AND
(dbo.Notes.Dialer IS NULL OR
dbo.Notes.Dialer = 0) AND (dbo.[Active Patients].Status LIKE N'%0 - In House%') AND (dbo.Notes.Priority LIKE N'%MED Initial%') OR
(dbo.[Active Patients].Status LIKE N'%0 - In House%') AND (dbo.Notes.Priority LIKE N'%UT Initial%')
New
SELECT dbo.Notes.Completed, CONVERT(char(10), dbo.Notes.FollowUp, 101) AS Followup, dbo.Notes.Date, dbo.Notes.Note, dbo.Notes.LeadingNoteID,
dbo.[Active Patients].PatientID, dbo.[Active Patients].FirstName, dbo.[Active Patients].LastName, dbo.[Active Patients].ReferredBy,
dbo.[Active Patients].ProviderAcctNumber, dbo.[Active Patients].Status, dbo.Notes.NoteID, dbo.Notes.Enteredby, dbo.Notes.Action, dbo.Notes.Priority,
dbo.Notes.[Group], dbo.Notes.[Collection Priority], dbo.Notes.Response, dbo.Notes.Attachment, dbo.[Active Patients].[Admit Date],
dbo.[Active Patients].Discharge, dbo.ARorGBal.ARB, REPLACE(CONVERT(char(10), dbo.Notes.FollowUp, 111), '/', '') AS Expr1, dbo.Notes.Dialer,
dbo.REF.RefCODE
FROM dbo.[Active Patients] INNER JOIN
dbo.Notes ON dbo.[Active Patients].PatientID = dbo.Notes.PatientID INNER JOIN
dbo.ARorGBal ON dbo.[Active Patients].PatientID = dbo.ARorGBal.PatientID INNER JOIN
dbo.REF ON dbo.[Active Patients].ReferredBy = dbo.REF.RefID
WHERE (dbo.Notes.Completed = 0) AND (dbo.[Active Patients].Status LIKE N'%0 - In House%') AND (dbo.Notes.Priority LIKE N'%UT Initial%') OR
(dbo.Notes.Completed = 0) AND (NOT (dbo.[Active Patients].Status LIKE N'%0 - In House%')) AND (dbo.Notes.Dialer IS NULL OR
dbo.Notes.Dialer = 0) AND (CONVERT(char(10), dbo.Notes.FollowUp, 111) <= CONVERT(char(10), GETDATE(), 111)) OR
(dbo.Notes.Completed = 0) AND (dbo.[Active Patients].Status LIKE N'%0 - In House%') AND (dbo.Notes.Priority LIKE N'%MED Initial%') AND
(dbo.Notes.Dialer IS NULL OR
dbo.Notes.Dialer = 0) AND (CONVERT(char(10), dbo.Notes.FollowUp, 111) <= CONVERT(char(10), GETDATE(), 111))
March 23, 2011 at 3:48 pm
As long as you're running this function on the column, you're guaranteed table scans: CONVERT(char(10), dbo.Notes.FollowUp, 111)
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 23, 2011 at 4:30 pm
Grant Fritchey (3/23/2011)
As long as you're running this function on the column, you're guaranteed table scans: CONVERT(char(10), dbo.Notes.FollowUp, 111)
Unfortunately, as does this:
AND (NOT (dbo.[Active Patients].Status LIKE N'%0 - In House%'))
There's a lot of non-SARGable clauses in there, and I'm not sure there's a lot that can be done about it in this query. What I would recommend is perhaps finding the table that is the most restricted by this process, running the clauses against that so you table scan one of them once, then use that from a #tmp to the rest of the connections doing index seeks with restrictions being applied after (if my guess is right about the indexing on this structure).
You're going to have to get an inspired solution to get this to run well.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 23, 2011 at 4:50 pm
I reformatted this to use aliasing and cleaned up the where clause to reduce repetitive logic and try to clean up the parenthentic pathing. Hopefully it'll help others to be able to read this easier.
to the OP: You need to control your AND/ORs with parens, not the unique pieces of the code. I THINK this is right and what you mean, but I'm not sure, so you'll want to doublecheck me.
Also, if Notes.Followup is a datetime field, you'll want to take a look at the change I made to use dateadd(datediff()) to simply trim the time off getdate() for what you're looking for. This will allow for a better comparison. How many notes do you have in the future, or are you just looking to avoid notes from today?
What are the indexes on the notes table? This is your best shot at trying to restrict data to this structure, on the .completed field. What percentage of the table is completed = 0 on a normal day? Completed and Dialer look like your best bets for data restriction, as at a guess all followups before or equal to the end of today is probably 99% of your table.
SELECT
n.Completed,
CONVERT(char(10), n.FollowUp, 101) AS Followup,
n.Date,
n.Note,
n.LeadingNoteID,
ap.PatientID,
ap.FirstName,
ap.LastName,
ap.ReferredBy,
ap.ProviderAcctNumber,
ap.Status,
n.NoteID,
n.Enteredby,
n.Action,
n.Priority,
n.[Group],
n.[Collection Priority],
n.Response,
n.Attachment,
ap.[Admit Date],
ap.Discharge,
agrb.ARB,
REPLACE(CONVERT(char(10), n.FollowUp, 111), '/', '') AS Expr1,
n.Dialer,
r.RefCODE
FROM
dbo.[Active Patients] AS ap
JOIN
dbo.Notes AS n
ONap.PatientID = n.PatientID
JOIN
dbo.ARorGBal AS argb
ONap.PatientID = agrb.PatientID
JOIN
dbo.REF AS r
ONap.ReferredBy = r.RefID
WHERE
n.Completed = 0
AND
(ap.Status LIKE N'%0 - In House%'
AND
( n.Priority LIKE N'%UT Initial%'
OR
( n.Priority LIKE N'%MED Initial%'
AND
( n.Dialer IS NULL
OR n.Dialer = 0
)
-- this change assumes followup is a datetime field.
-- Strip the time off getdate(), add a day, and then remove two milliseconds
--to use the datetime field properly for all records today and prior.
AND n.FollowUp <= dateadd( ms, -2, DATEADD( dd, DATEDIFF( dd, 0, getdate()) + 1, 0)) -- 2 milliseconds because of rounding issues.
)
)
OR
(ap.Status NOT LIKE N'%0 - In House%'
AND
( n.Dialer IS NULL
OR n.Dialer = 0
)
AND n.FollowUp <= dateadd( ms, -2, DATEADD( dd, DATEDIFF( dd, 0, getdate()) + 1, 0)) -- 2 milliseconds because of rounding issues.
)
)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply