Find Appropriate Event After An Incident

Question

Find Appropriate Event After An Incident

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Neil Burton SSC-Insane Points: 23000 More actions · Answer 1

ChrisM@Work (1/2/2015)
Thanks.
Query 1 (insert into #temp_violation etc.) has an odd filter:
and
(
cast(fh.startdate as date)<= cast(tv.startdate as date)
and
cast(fh.startdate as time)<= cast(tv.startdate as time)
)
This will capture datetimes from fh where they are less than or equal to tv.startdate (as date), but for any fh rows which match - which could be different days - then times after cast(tv.startdate as time) will be eliminated. I'm not sure this is what you want. Shouldn't you just be comparing the two datetimes directly? This is my best guess so far on this. Can I PM you about it? I don't want to broadcast exactly what I'm doing.
[/fh.startdate <= tv.startdate
Can you create any indexes on the tables in query 1? --Not on the Time Violation table. The database is read only, the others, maybe.
Query 2 (insert into #temp_breach) has an expensive key lookup to table IncidentExtensionBase. Can you add the columns which the key lookup collects to index IX_VI_OUTRES, or alternatively, create a new index which contains just the columns you need from the table? Better still, since you're only using a small number of columns from the view
(eai_orderno_int, vf_subjectorderid and vf_subjectid_lk from Vf_subjectorderExtensionBase)
do you actually need to reference the view at all? This might be more trouble than it's worth. The base tables don't have the text outcomes, only the GUID keys. I'd have to put case statements in place and then I'd run the risk of missing something.
convert the scalar function EM_Exit.dbo.G4STime() to an iTVF. I'll look into this. If I can improve it I may well be knighted. This function slows an awful lot of things down in an awful lot of places. EDIT: I've just opened this up to have a look at it and there's at least two levels of nested scalar functions in there.
Query 3 would be helped with an additional index on #temp_breach: orderid and DateIdentified Done; I was under the impression that indexes on GUIDs were to be avoided. Is that always the case?
Edit:
Here's the missing index hint for Query 3. Table TimeViolation isn't referenced in the query. Again this database is read only so as much as I''d like to put this in place I can''t. I''m only the lowly BI guy so I've not got access to everything.
/*
Missing Index Details from Ex_Plan.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 59.6517%.
*/
/*
USE [EMSTBLSN]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TimeViolation] ([ExcludeFromATV])
INCLUDE ([SiteID],[StartDate],[EndDate])
GO
*/

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

BWFC (1/2/2015)
ChrisM@Work (1/2/2015)
Thanks.
Query 1 (insert into #temp_violation etc.) has an odd filter:
and
(
cast(fh.startdate as date)<= cast(tv.startdate as date)
and
cast(fh.startdate as time)<= cast(tv.startdate as time)
)
This will capture datetimes from fh where they are less than or equal to tv.startdate (as date), but for any fh rows which match - which could be different days - then times after cast(tv.startdate as time) will be eliminated. I'm not sure this is what you want. Shouldn't you just be comparing the two datetimes directly? This is my best guess so far on this. Can I PM you about it? I don't want to broadcast exactly what I'm doing.
[/fh.startdate <= tv.startdate
Can you create any indexes on the tables in query 1? --Not on the Time Violation table. The database is read only, the others, maybe.
Query 2 (insert into #temp_breach) has an expensive key lookup to table IncidentExtensionBase. Can you add the columns which the key lookup collects to index IX_VI_OUTRES, or alternatively, create a new index which contains just the columns you need from the table? Better still, since you're only using a small number of columns from the view
(eai_orderno_int, vf_subjectorderid and vf_subjectid_lk from Vf_subjectorderExtensionBase)
do you actually need to reference the view at all? This might be more trouble than it's worth. The base tables don't have the text outcomes, only the GUID keys. I'd have to put case statements in place and then I'd run the risk of missing something.
convert the scalar function EM_Exit.dbo.G4STime() to an iTVF. I'll look into this. If I can improve it I may well be knighted. This function slows an awful lot of things down in an awful lot of places.
Query 3 would be helped with an additional index on #temp_breach: orderid and DateIdentified Done
Edit:
Here's the missing index hint for Query 3. Table TimeViolation isn't referenced in the query. Again this database is read only so as much as I''d like to put this in place I can''t. I''m only the lowly BI guy so I've not got access to everything.
/*
Missing Index Details from Ex_Plan.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 59.6517%.
*/
/*
USE [EMSTBLSN]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TimeViolation] ([ExcludeFromATV])
INCLUDE ([SiteID],[StartDate],[EndDate])
GO
*/

Sure, let's tick these off one at a time.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden