August 3, 2015 at 7:39 am
coolchaitu (7/24/2015)
Thanks a lot for replying. Actually, the stored proc calls another stored proc and hence there are 2 execution plans.Could you please advise on how to improve this proc.
The first query has three references to a couple of tables and there's duplicated filtration at more than one nesting level. Both will make this query more expensive than it needs to be. Tweaking would be difficult without reference data to run against but at a simplistic level you could remove the unnecessary nesting levels to make the query a little simpler and replace the multiple references to tblCabDeviceMappingDetail and tblCabMaster by unioning the other tables first. Unless the 'JobID NOT IN' filters eliminate a significant proportion of rows, move them to the outside.
There's plenty of scope for improvement in both of the stored procedures. They may have been written by an inexperienced developer in a hurry, maybe not. Both should be rewritten by a competent developer. Key lookups and clustered index scans indicate that indexing of these tables is suboptimal. It's a pointless exercise chasing server settings and wait states when you know that your queries are poorly written and unsupported by the few indexes you have.
None of this is advanced SQL and if you are unsure of any part of it, then hire a professional, who will rearrange indexing to support the new queries - as part of the job.
Here's the first queryreformatted for readability:
SELECT
JobID,
isnull(NStime,'NA') AS NoShow,
isnull(SAtime,'NA') AS Arrived,
isnull(CabLocation,'NA') AS CabLocation,
Speed
FROM ( -- c
SELECT
JobID,
SAtime,
CabRegistrationNo,
CabLocation,
Speed
FROM ( -- a
SELECT
JobID,
cm.CabRegistrationNo,
cm.CabLocation,
CM.Speed
FROM dbo.tblBidMaster BM WITH(NOLOCK)
INNER JOIN dbo.tblBidMasterStatusDetail BMS WITH(NOLOCK) ON BM.BidID = BMS.BidID
INNER JOIN dbo.tblJobAwardDetail JA WITH(NOLOCK) ON BM.BidID = JA.BidID AND JobCancelID IS NULL
INNER JOIN dbo.tblJobAwardStatusDetail JAS WITH(NOLOCK) ON JA.JobAwardID = JAS.JobAwardID
INNER JOIN dbo.tblCabDeviceMappingDetail CD WITH(NOLOCK) ON JA.MappingID = CD.MappingID
INNER JOIN dbo.tblCabMaster CM WITH(NOLOCK) ON CD.CabID = CM.CabID
WHERE CM.CityID = @CityID
AND JobConfirmDateTime BETWEEN @FromDate AND @ToDate
AND BM.JobID not in (Select TS.JobID From tblTripStartDetail TS with (nolock))
AND BMS.BidStatusID = 11 --AND (@CallerID IS NULL OR CabRegistrationNo = @CallerID)
UNION ALL
SELECT
JobID,
cm.CabRegistrationNo,
cm.CabLocation,
CM.Speed
FROM dbo.tblManulJobAwardDetail JM WITH(NOLOCK)
INNER JOIN dbo.tblDeviceMaster DM WITH(NOLOCK) ON JM.DeviceID = DM.DeviceID
INNER JOIN dbo.tblCabDeviceMappingDetail CD WITH(NOLOCK) ON DM.DeviceID = CD.DeviceID
INNER JOIN dbo.tblCabMaster CM WITH(NOLOCK) ON CD.CabID = CM.CabID
WHERE CM.CityID = @CityID --AND (@CallerID IS NULL OR CabRegistrationNo = @CallerID)
AND JAMSentTime BETWEEN @FromDate AND @ToDate
AND JobID NOT IN (SELECT JobID FROM dbo.tblJobCancelDetail WITH(NOLOCK))
AND JobID not in (Select TS.JobID From tblTripStartDetail TS with (nolock))
UNION ALL
SELECT
SD.JobID,
Cab.CabRegistrationNo,
Cab.CabLocation,
Cab.Speed
FROM tblSpecialDirectTripInfo SD
INNER JOIN tblDeviceMaster DM ON SD.SiebelDeviceID = DM.SiebelDeviceID
INNER JOIN tblCabDeviceMappingDetail CD ON DM.DeviceID = CD.DeviceID
INNER JOIN dbo.tblCabMaster Cab with (nolock) ON CD.CabID = Cab.CabID
Where SD.CityID = @CityID
AND CommandSentTime BETWEEN @FromDate AND @ToDate
AND SD.JobID NOT IN (SELECT JobID FROM dbo.tblJobCancelDetail WITH(NOLOCK))
AND SD.JobID not in (Select TS.JobID From tblTripStartDetail TS with (nolock))
AND SD.IsDeleted = 0
) a
LEFT JOIN ( -- b
SELECT
JobID AS AJobID,
CONVERT(VARCHAR(17),MsgReceivedDateTime,113) AS SAtime
FROM dbo.tblSendArrivalSMSDetail WITH(NOLOCK)
WHERE MsgReceivedDateTime BETWEEN @FromDate AND @ToDate
) b
ON a.JobID = b.AJobID
) c
LEFT JOIN ( -- d
SELECT
JobID AS NJobID,
CONVERT(VARCHAR(17),MsgReceivedDateTime,113) AS NStime
FROM dbo.tblStandardMessageReceivedDetail WITH(NOLOCK)
WHERE MsgReceivedDateTime BETWEEN @FromDate AND @ToDate
) d
ON c.JobID = d.NJobID
WHERE JobID NOT IN (SELECT JobID FROM dbo.tblTripStartDetail TS WITH(NOLOCK))
AND JobID NOT IN (SELECT JobID FROM dbo.tblTripEndDetail TE WITH(NOLOCK))
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
August 3, 2015 at 8:08 am
Chris Sir,thanks a lot. I have pasted a latest post. Could you please see that screenshot and advise.
The SPIDs are changing frequently and they are multi-threaded. Wait type is Latch_Ex and Resource is ACCESS_METHODS_SCAN_RANGE_GENERATOR. I see same SPIds in suspended state and blocking each other. This is causing prouction issue and users are getting affected. Please help on how to fix this sir.
August 3, 2015 at 8:57 am
coolchaitu (8/3/2015)
Chris Sir,thanks a lot. I have pasted a latest post. Could you please see that screenshot and advise.The SPIDs are changing frequently and they are multi-threaded. Wait type is Latch_Ex and Resource is ACCESS_METHODS_SCAN_RANGE_GENERATOR. I see same SPIds in suspended state and blocking each other. This is causing prouction issue and users are getting affected. Please help on how to fix this sir.
I see poorly written queries with inadequate indexing, both of which are likely to lead to performance problems whatever method you use to measure performance - and you've tried a few. Addressing these would be a priority for any experienced TSQL developer. Tweaking server-level settings may help a little in some parts of your application but it's not really an appropriate response. A few index tweaks could also get you out of trouble for a while but the problem would still exist and come back to bite you when data volumes increase.
Rewriting just these two stored procedures and constructing the supporting indexes from whatever is there already is perhaps one day's work. Can you do this? If not, you may wish to consider hiring a professional. Whilst the folks here could help, it would take you some time to construct a data set for them to develop against.
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
August 3, 2015 at 9:01 am
I am not developer sir. There is Dev team and those developers/programmers have written the code. Please help sir
August 3, 2015 at 9:52 am
coolchaitu (8/3/2015)
I am not developer sir. There is Dev team and those developers/programmers have written the code. Please help sir
Why are you unable to push this issue back to the development team?
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
August 3, 2015 at 10:01 am
Sir, dev team is saying that dba should be doing it.
August 3, 2015 at 10:05 am
coolchaitu (8/3/2015)
Sir, dev team is saying that dba should be doing it.
Are you the DBA, by any chance?
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
August 3, 2015 at 10:13 am
Yes sir, i am the junior dba
August 3, 2015 at 10:26 am
coolchaitu (8/3/2015)
Yes sir, i am the junior dba
So escalate it to your boss. This isn't an issue for a junior. Whilst he's dealing with that, get yourself some decent indexing articles and study them. You cannot escape TSQL if you wish to call yourself a DBA so you will have to learn that too.
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
August 3, 2015 at 11:26 am
If your developers are unwilling to do their job (developing correct, working code), then suggest to your boss that he consider getting someone in who can fix these kinds of problems (SQL consultant)
A lot of us here do that kind of work, and so far you've been having these problems for well over a week without any improvements and there's no magic solution here. Someone (you, your senior, the developers, a consultant) has to analyse the code and rewrite the problematic queries.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 3, 2015 at 11:46 am
Dear Gail Shaw Sir,
Thanks for the inputs. Can you please suggest an excellent comprehensive book/material that covers beginning to expert dba internals and concepts
August 3, 2015 at 11:57 am
Start with Itzik Ben-Gan's book T-SQL Fundamentals. It's not a performance tuning book, but it'll give you a good grounding in writing T-SQL properly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 3, 2015 at 1:05 pm
Gail sir,
How to calculate value that cost threshold for parallelism needs to be set to?
August 3, 2015 at 1:09 pm
There's no easy way, and to be honest the hard way is not worth doing. Most people just use a value larger than 5. Some say 20, some say 30, some say 50.
Changing it is not going to magically fix your problems. It may help a little, but do not expect that you set it and your problem is fixed. It won't be.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2015 at 11:46 am
Madam, i am junior dba. I was told that this forum have great,SQL experts,kind hearted people with good virtues like all of you here and willing to help. Our environment is very laid back and not at all critical, hardly we get transactions. So, no problem if i take 3 or more weeks of time to do improvement. Please help on resolving the issue.
Viewing 15 posts - 31 through 45 (of 48 total)
You must be logged in to reply to this topic. Login to reply