April 3, 2012 at 5:41 pm
I am executing below query ....when i execute ..It keep on in executing mode. I think it might be locking issue on two tables. Will any one suggest me how to change below query to get free from locking issue.
DECLARE @count int
DECLARE @doctorID int
DECLARE @statusCode smallint
DECLARE @currentDate datetime
SET @currentDate = ' 2012-03-22';
--Create Temporary Table to populate DoctorID
DECLARE @Temp
TABLE(
DoctorID int,
StatusCode smallint,
IsProcessed bit
)
-- INSERT INTO TEMP TABLE
Insert Into @Temp
SELECT DISTINCT (DC.DoctorID),
0,
0
FROM schDIA.tblDoctor AS DC
INNER JOIN schDIA.tblDoctorContact AS DCC ON DC.DoctorID = DCC.DoctorID
INNER JOIN schDIA.tblDoctorLicenseHistory AS DLH ON DC.DoctorID=DLH.DoctorID
WHERE CONVERT( date , DC.LastUpdatedDate) = @currentDate AND CONVERT( date , DLH.LastUpdatedDate) = @currentDate
SET @count = (SELECT COUNT(*) FROM @temp)
-- RETRIEVE LATEST LICENSE STATUS
WHILE( @count > 0)
BEGIN
SELECT TOP 1 @doctorID = DoctorID
FROM @temp
WHERE IsProcessed = 0
SELECT TOP 1 @statusCode = StatusCode
FROM schDIA.tblDoctorLicenseHistory
WHERE DoctorID = @doctorID ORDER BY CreatedDate DESC
UPDATE @temp
SET IsProcessed = 1,
StatusCode = @statusCode
WHERE DoctorID = @doctorID
SET @count = @count - 1
END
April 3, 2012 at 6:19 pm
Have you looked at sys.sysprocesses while it's running to determine if it is being blocked? That's not a code issue, that's a concurrency issue (unless you're deadlocking yourself, which is a whole different problem).
Besides that, why are you forcing the loop here? I haven't completely disassembled your code but this looks like it could easily be done in a single UPDATE pass. Otherwise, it doesn't look like you've got an endless loop, and the queries just need to be reviewed and optimized.
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
April 3, 2012 at 6:46 pm
i haven't seen something blocking in sysprocessors, but for hundred records it is executing fine but when the count increase to 10000 records. It keep on executing. So suggest if i need to change with code .
April 3, 2012 at 6:49 pm
EKF is 100% correct - this should be a single update with no loop.
Try rewriting it that way to see if your execution time problem goes away.
If you're not sure how to do it, post some sample data, DDL, etc. and someone here will be able to help.
Above all, follow my mantra:
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 3, 2012 at 7:00 pm
sqlmaverick (4/3/2012)
i haven't seen something blocking in sysprocessors, but for hundred records it is executing fine but when the count increase to 10000 records. It keep on executing. So suggest if i need to change with code .
Yeah, definately a code rework then. Can you post the entire code, the underlying schema (with indexes) of the used tables, and preferably a few execution plans? Sounds like either you're hitting a tipping point in the tables causing a scan instead of seek or you're just overloading available memory or doing swapfiles or... a number of things.
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
April 4, 2012 at 10:21 am
Thanks for suggestion guys, I rewrote the code as shown below ... it is working right now.
DECLARE @currentDate datetime = ' 2012-03-22'
DECLARE @rowCount Int
DECLARE @count int
DECLARE @doctorID int
DECLARE @statusCode int
DECLARE @temp
TABLE( RowID INT IDENTITY PRIMARY KEY,
DoctorID int
)
DECLARE @temp2
TABLE(RowID INT IDENTITY PRIMARY KEY,
DoctorID INT,
StatusCode INT
)
INSERT INTO @temp
SELECT DISTINCT (DC.DoctorID) FROM schDIA.tblDoctor AS D
INNER JOIN schDIA.tblDoctorContact AS DC ON D.DoctorID = DC.DoctorID
INNER JOIN schDIA.tblDoctorLicenseHistory AS DLH ON D.DoctorID=DLH.DoctorID
WHERE CONVERT( date , D.LastUpdatedDate) = @currentDate
OR CONVERT( date , DC.LastUpdatedDate) = @currentDate
OR CONVERT( date , DLH.LastUpdatedDate) = @currentDate
SET @count = (SELECT COUNT(*) FROM @temp)
SET @rowCount=1
WHILE( @count > 0)
BEGIN
SELECT @doctorID = DoctorID
FROM @temp
WHERE RowID = @rowCount
SELECT @statusCode = StatusCode
FROM schDIA.tblDoctorLicenseHistory
WHERE DoctorID = @doctorID ORDER BY CreatedDate
Insert INTO @temp2
SELECT @statusCode, @doctorID
--UPDATE @temp2
--SET IsProcessed = 1,
-- StatusCode = @statusCode
--WHERE DoctorID = @doctorID
SET @rowCount = @rowCount+1
SET @count = @count - 1
END
SELECT T2.DoctorID, T2.StatusCode FROM @temp T
Inner join @temp2 T2 on t.RowID= T2.RowID
April 4, 2012 at 12:37 pm
This, I believe, will work better for you:
DECLARE @currentDate datetime = ' 2012-03-22'
SELECT DISTINCT
DC.DoctorID, ca.StatusCode
FROM schDIA.tblDoctor AS D
INNER JOIN schDIA.tblDoctorContact AS DC ON D.DoctorID = DC.DoctorID
INNER JOIN schDIA.tblDoctorLicenseHistory AS DLH ON D.DoctorID=DLH.DoctorID
CROSS APPLY ( SELECT TOP 1 StatusCode
FROMschDIA.tblDoctorLicenseHistory AS dlh2
WHEREdlh2.DoctorID = d.DoctorID
ORDER BY CreatedDate DESC) AS ca
WHERE
CONVERT( date , D.LastUpdatedDate) = @currentDate
OR CONVERT( date , DC.LastUpdatedDate) = @currentDate
OR CONVERT( date , DLH.LastUpdatedDate) = @currentDate
I didn't alter the logic against DoctorLicenseHistory but my guess is the second call to the same table could probably be adjusted to include both pieces of logic, but this will get you only the most recent date for each doctor. Cross Apply can be a serious boon to things like this where you want to loop and don't really need to.
Another option would be subselecting the MAX(CreatedDate) and using that as a join against DLH, but this will work equivalently. If you want the first CreatedDate instead of the last (I was working off your Update component's logic) just swap the ORDER BY to ASC.
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply