March 12, 2012 at 6:22 pm
Thanks Jeff!
I found this one that queries the ClkCaseHdr table joined to the CaseStatusHist table :
exec sp_executesql N'SELECT csh.*
FROM ClkCaseHdr AS cch INNER JOIN
CaseStatusHist AS csh ON cch.StatusIDCur = csh.StatusID
WHERE (cch.CaseCategoryKey = ''CV'') AND (cch.CaseStatClkCdID IN (15213, 15214)) AND (csh.TimeStampCreate > @prmDate)',N'@prmDate datetime',@prmDate='2012-03-13 00:00:00'
I had done a significant project using VB.Net/ASP.Net but never a windows formed based program. I'm surprised that it's so different as far as me not being able to find this select statement except by using SQL profiler. I imagine that there must be a way to find it in Visual Studio but I sure couldn't. In ASP.NET/VB.NET, I could find everything either in the code behind, the HTML, or the properties of the controls.
I hope Elliot sees this as he was asking about what was missing in the code I posted.
Thanks everyone.
March 13, 2012 at 6:37 am
In case anyone is interested, I found the "missing" SQL command in the resx file in XML:
<data name="SqlSelectCommand1.CommandText" xml:space="preserve">
<value>SELECT csh.*
FROM ClkCaseHdr AS cch INNER JOIN
CaseStatusHist AS csh ON cch.StatusIDCur = csh.StatusID
WHERE (cch.CaseCategoryKey = 'CV') AND (cch.CaseStatClkCdID IN (15213, 15214)) AND (csh.TimeStampCreate > @prmDate)</value>
</data>
I think this might be new in SQL 2008 and applies to ASP.NET as well, not just windows forms. My relatively limited experience in ASP.NET was in 2005.
I did a search of the entire solution and I landed on the Form1.resx tab. The developer of this code used a blank form so I guess he didn't feel renaming the form was worthwhile.
March 13, 2012 at 7:38 am
Also found out that I can go to "project" in the main menu of my visual studio project and select "show all files" to see the Form1.resx file in the Solution Explorer. If I double click on the file, instead of seeing all the XML markup and trying to find whatever Microsoft put in the resource file for us, I can see simply the name (in my case SQLSelectCommand1.CommandText), the Value (in my case the Select csh.* etc. string I described in an earlier post), and a Comment field, which in my case is blank. All of you Visual Studio 2008 and later pros already know all this but, in case someone who needs to know this googles for it, I wrote about it here.
March 13, 2012 at 2:58 pm
Ok, so it looks like the work table is built essentially from:
SELECT csh.*
FROM ClkCaseHdr AS cch INNER JOIN CaseStatusHist AS csh
ON cch.StatusIDCur = csh.StatusID
WHERE cch.CaseCategoryKey = 'CV'
AND cch.CaseStatClkCdID IN ( 15213, 15214 )
AND csh.TimeStampCreate > @prmDate
Still working on TAG1, was sick his morning. Just getting back into things..
CEWII
March 13, 2012 at 5:15 pm
Sorry to hear you were sick. I hope you are feeling back to normal now. Please don't worry about my problem if you're feeling peaked. Thanks for all your help so far!
March 13, 2012 at 11:05 pm
I have a handle on what is going on in TAG1, I'm trying to wrap my brain around how to do it as a set based operation.
CEWII
March 14, 2012 at 3:25 am
Cool. Thanks! I'll be very interested to see.
March 14, 2012 at 11:55 am
Ok, here is my rough solution..
I think it covers all the sections. But there are a couple spots you need to look at, particularly around line 68 related to the alias.
This takes all the vb code and replaces it with a set based operation that is fully self-contained and requires no user intervention.
I am including my notes as well.
DECLARE @prmDate AS DATETIME = CONVERT( DATE, CURRENT_TIMESTAMP )
DECLARE @iUpdatedCnt AS INTEGER
DECLARE @iReviewCnt AS INTEGER
--Since I don't have enough information to build the create statement I'm gonna
--cheat and do a SELECT INTO, the correct way is to build out the correct CREATE
--TABLE statement with the correct names and types. This builds out the complete
--list of items that we CAN work against. you could also use a table variable if
--there were not too many records in the record set.
SELECT csh.*
INTO #CaseStatusHist
FROM ClkCaseHdr AS cch INNER JOIN CaseStatusHist AS csh
ON cch.StatusIDCur = csh.StatusID
WHERE cch.CaseCategoryKey = 'CV'
AND cch.CaseStatClkCdID IN ( 15213, 15214 )
AND csh.TimeStampCreate > @prmDate
DELETE dbo.xCaseBaseCaseFlag
FROM dbo.xCaseBaseCaseFlag AS x INNER JOIN #CaseStatusHist AS c
ON x.CaseId = c.CaseId
WHERE CaseFlagID = 14998
UPDATE Justice.dbo.CaseEvent
Set ReviewCompleteDate = c.DtCaseStatus
FROM Justice.dbo.CaseEvent AS ce INNER JOIN Event AS e
ON ce.EventID = e.EventID
INNER JOIN #CaseStatusHist AS c
ON c.CaseId = ce.CaseId
WHERE e.eventtypeid BETWEEN 15244 AND 15248
AND ce.ReviewCompleteDate IS NULL
SELECT @iReviewCnt = @@ROWCOUNT
--This entire section can be replaced by a single query. This particular section
--only runs when there hearing events for a case, it fills in an integer array
--which is used later, however, it does this in two passes where I don't see why
--it couldn't be done in one.
--However, going further I think this entire section can be reduced to (using what
--we built above), again into a temp table with SELECT INTO, again it would be
--better to explicitly create the table with the names and fields:
SELECT CourtSessionBlockID,
s.HearingID,
s.SettingID,
s.CourtSessionBlockID,
csb.CourtSessionID,
csb.OffsetMinutes,
s.OffsetMinutes as SettingOffsetMinutes,
cs.SessionDate,
cs.TimeStampCreate,
cs.StartTime
INTO #CaseData
FROM HearingEvent AS he INNER JOIN Setting AS s
ON s.HearingID = he.HearingID
INNER JOIN Event as e
ON he.hearingID = e.EventID
INNER JOIN CtSessionBlock AS csb
ON csb.CourtSessionBlockID = s.CourtSessionBlockID
INNER JOIN CtSession AS cs
ON cs.CourtSessionID = csb.CourtSessionID
INNER JOIN Event AS e2
ON e2.EventId = s.HearingId
INNER JOIN Setting AS s2
ON s2.SettingID = s.Sett
INNER JOIN #CaseStatusHist AS c
ON c.CaseId = CaseId -- I need to prefix this but don't know the table
-- it is actually joining to..
WHERE ResultID IS NULL
AND RescheduleReasonID IS NULL
AND e.Deleted = 0
AND s.CancelledDateTime IS NULL
AND cs.SessionDate >= @prmDate
AND ( ( e2.TimeStampChange IS NULL AND e2.TimeStampCreate != @prmDate )
OR ( e2.TimeStampChange IS NOT NULL AND e2.TimeStampChange != @prmDate ) )
UPDATE s
SET CancelledStatus = 1,
CancelledReasonID = 108,
CancelledDateTime = CONVERT( DATETIME, CONVERT( DATE, cd.SessionDate ) ) + DATEADD( mi, ISNULL( cd.OffsetMinutes, 0 ) + ISNULL( cd.SettingOffsetMinutes, 0 ), CONVERT( TIME, cd.StartTime ) ),
UserIDChange = 442,
TimeStampChange = CURRENT_TIMESTAMP
FROM Setting AS s INNERT JOIN #CaseData AS cd
ON s.SettingId = cd.SettingId
UPDATE he
SET CancelledReasonID = 108
FROM HearingEvent AS he INNER JOIN #CaseData AS cd
ON he.HearingID = cd.HearingID
SELECT @iUpdatedCnt = @@ROWCOUNT
INSERT Worktables.dbo.tblScriptJobInfo
SELECT 'CivilCancelFutureHearings',
CURRENT_TIMESTAMP,
'Job completed successfully and updated ' + CONVERT( VARCHAR(11), @iUpdatedCnt )
INSERT Worktables.dbo.tblScriptJobInfo
SELECT 'CivilCancelFutureHearings',
CURRENT_TIMESTAMP,
'Updated Review Complete Dates = ' + CONVERT( VARCHAR(11), @iReviewCnt )
The code could use some optimization but without anything to test it on or the actual structures I was somewhat limited.
Enjoy..
CEWII
March 14, 2012 at 11:59 am
Elliot, I don't know what to say. You are the best!! I have been working on something else for the last couple of days so that's why I didn't post any attempts of my own. Pardon my gushing but I just never expected so much help. Thanks!!!
March 14, 2012 at 12:23 pm
I didn't quite plan to do the whole thing or spend so much time on it myself but I kind of treated it as a challenge to see whether I could do it. Especially not knowing basically anything about the environment or the data and just what I could glean from the code and what you told me.
The code I gave will likely run SUBSTANTIALLY faster once you get it tweaked for my mistakes. but I think it is really close as is. There were several points in the original code where it did cound checks to see if it needed to do anything. Those are generally unnecesaary due to the temp tables. If the record doesn't exist then nothing would happen. Also doing this as a set should increase performance by orders of magnitude..
How long does it currently run as an exe?
CEWII
March 14, 2012 at 12:37 pm
I'm sorry but I don't know because it is currently in production running on a job server via Windows Scheduler. I don't have security access to any of it. I will ask someone and post again if I can find anyone who has access or knows the answer. I ran the exe from a test server when I was testing it running from an SSIS package and it was fast but the data was old on the server so it really didn't have any work to do.
It may be tomorrow before anyone is around to ask about the existing setup. Basically the guy who wrote it left some time ago and it's been humming along since then but the impetus to migrate it to a stored procedure is to get it and three more .exes into sprocs so that everything will be standardized without "thingies" scattered around on ancillary servers doing old-fashioned or "rogue" things.
Thanks again. You're the best.
March 15, 2012 at 12:57 pm
Sorry for the late reply Elliot. I found out that it takes about 3 minutes to run. It doesn't have too many records to process each day though.
March 15, 2012 at 2:21 pm
3m huh.. Still, I bet the sproc version runs in under 20 seconds.
CEWII
March 15, 2012 at 2:24 pm
Great improvement. Plus it's just way more elegant and isn't a stray exe sitting out on an app server somewhere. Thanks!! My boss couldn't believe how nice you to help so much.
Viewing 14 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply