August 18, 2015 at 6:20 am
Good morning,
The below query is running for long time:
August 18, 2015 at 6:59 am
Try this:
SELECT
TE.JobID,
TotalDistanceTravelled as Distance,-- what table is this from?
TotalTripFare AS TotalFare -- what table is this from?
FROM [sqlcdscluster\sqlcds].cdsbusiness.dbo.Tbljobbooking MW
INNER remote JOIN dbo.tbltripenddetail TE
ON MW.JobID = TE.jobID
AND te.TripEndTime > DATEADD(hour,-1,GETDATE())
AND te.TripendStatus = 0
AND TE.JOBID != 0
INNER JOIN [sqlcdscluster\sqlcds].cdsbusiness.dbo.tblCitrusSendingDetails CS
ON TE.JobID = CS.JobID
AND (
(
CS.Response IS NOT NULL AND CS.Response != '' AND te.TripEndTime >= DATEADD(minute,-20,GETDATE())
)
OR te.TripEndTime <= DATEADD(minute,-21,GETDATE())
)
ORDER BY TE.JobID DESC
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 18, 2015 at 7:01 am
What datatype is te.TripEndTime?
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 18, 2015 at 7:08 am
If you ask me, I would rewrite the query like this even if it's not exactly what you have right now:
SELECT TE.JobID,
TotalDistanceTravelled as Distance,--convert(varchar(30),TotalFare) as TotalFare,
TotalTripFare AS TotalFare
FROM [sqlcdscluster\sqlcds].cdsbusiness.dbo.Tbljobbooking MW
INNER JOIN dbo.tbltripenddetail TE ON MW.JobID=TE.jobID
INNER JOIN [sqlcdscluster\sqlcds].cdsbusiness.dbo.tblCitrusSendingDetails CS ON TE.JobID=CS.JobID
WHERE TripEndTime >= DATEADD( HH, - 1,GETDATE())
AND TripendStatus =0
AND TE.JOBID!=0
AND ( ( CS.Response!='' AND TripEndTime >= DATEADD( MI, - 20, GETDATE())
OR TripEndTime < DATEADD( MI, DATEDIFF(MI, - 20, GETDATE()))
ORDER BY TE.JobID DESC
To be more accurate to your current query, I'd do it like this:
DECLARE @PreviousHour datetime,
@Minus20Minutes datetime;
SELECT @PreviousHour = DATEADD( HH, DATEDIFF(HH, '2000', GETDATE()) - 1,'2000'),
@Minus20Minutes = DATEADD( MI, DATEDIFF(MI, '2000', GETDATE()) - 20,'2000');
SELECT TE.JobID,
TotalDistanceTravelled as Distance,--convert(varchar(30),TotalFare) as TotalFare,
TotalTripFare AS TotalFare
FROM [sqlcdscluster\sqlcds].cdsbusiness.dbo.Tbljobbooking MW
INNER JOIN dbo.tbltripenddetail TE ON MW.JobID=TE.jobID
INNER JOIN [sqlcdscluster\sqlcds].cdsbusiness.dbo.tblCitrusSendingDetails CS
ON TE.JobID=CS.JobID
WHERE TripEndTime >= @PreviousHour
AND TripendStatus =0
AND TE.JOBID!=0
AND ( ( CS.Response!='' AND TripEndTime >= @Minus20Minutes
OR TripEndTime < @Minus20Minutes )
ORDER BY TE.JobID DESC
This might not help much on performance because the problem seems to come from the remote query and the best option for this is to keep everything in the same server. That would help to prevent the problem of bringing 1.6 million rows from the remote server to return 14.
The estimate rows compared to the actual rows seem to be wrong, probably from your conditions or maybe because of statistics.
For further details, please post DDL for tables and indexes, amount of data processed in average, frequency of execution, current execution time and expected execution time. Or check this articles which explains in more detail: http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 18, 2015 at 7:12 am
I am currently blocked from looking at the query plan but would make the following points.
The basic problem with queries involving remote tables is that the full table will be copied to the local instance first. You can get around this by:
1. Using transactional replication to keep a R/O copy of the required tables in the current instance. Latency should only be a few seconds.
or
2. Creating #temp tables with a Primary Key of JobID etc and populating only the required rows and columns with OPENQUERY first.
Get rid of WITH (NOLOCK) - it is not a magic cure for blocking.
Alias all your columns so people can see what table they come from.
Using a function on a column name stops any index being used. ie Replace expresssions like:
DATEDIFF(MINUTE,TripEndTime,Getdate())<=20
with something like:
TripEndTime <= DATEADD(minute, -20, CURRENT_TIMESTAMP)
August 18, 2015 at 11:13 am
TotalDistanceTravelled is from table tbltripenddetail
August 18, 2015 at 11:14 am
Its datatype is varchar
August 18, 2015 at 11:30 am
We have Update Stats configured to run every week on Thursday, Sunday at 6:00:00 AM.
On the remote server, this job runs every week on Sunday at 7:00:00 AM
August 18, 2015 at 11:39 am
coolchaitu (8/18/2015)
Its datatype is varchar
Datatype of what?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
August 18, 2015 at 11:52 am
Datatype of te.TripEndTime. You had asked what is the datatype of te.TripEndTime.
August 18, 2015 at 12:07 pm
coolchaitu (8/18/2015)
Datatype of te.TripEndTime. You had asked what is the datatype of te.TripEndTime.
It wasn't clear.
You should change the datatype to datetime as a matter of priority.
Did you run the query I posted?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
August 20, 2015 at 1:00 am
Dear Chris sir,
There is remote join in the query you posted. I came across an article that says Remote join doesn’t work well on SQL server 2005. Ours is 2005.
https://www.mssqltips.com/sqlservertip/2765/revisit-your-use-of-the-sql-server-remote-join-hint/
August 20, 2015 at 1:46 am
coolchaitu (8/20/2015)
Dear Chris sir,There is remote join in the query you posted. I came across an article that says Remote join doesn’t work well on SQL server 2005. Ours is 2005.
https://www.mssqltips.com/sqlservertip/2765/revisit-your-use-of-the-sql-server-remote-join-hint/
At the end of the article Mr Bertrand states "Test the queries you find with and without the REMOTE keyword, and decide if you want to keep them that way.".
So what did you find? What were your timings with and without the REMOTE hint?
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 20, 2015 at 6:29 am
Despite using this forum for some time you still do not seem to know how to ask a question.
Please read the following:
You have been asked a number of questions and given poor replies.
As mentioned before, the fundamental problem is efficiently retrieving the data from the remote server.
Making the following assumptions:
1. All the required columns (JobID, TotalDistanceTravelled, TotalTripFare) come from dbo.tbltripenddetail
2. dbo.tbltripenddetail.TripEndTime which you say is a string, will always cast reliably as a datetime in your environment.
I suspect this is doubtful and, as previously mentioned, you should change the data type to a datetime as a matter of urgency.
I once came across a system which used strings for dates where the dates had been entered in both American and European format!
3. JobID is an integer.
4. You have sysadmin rights in the [sqlcdscluster\sqlcds] instance.
The following should speed up your query:
In the [sqlcdscluster\sqlcds] instance run:
USE cdsbusiness;
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE TYPE dbo.IDTable
AS TABLE
(
ID int NOT NULL
);
GO
CREATE PROCEDURE dbo.TripJobs
(
@PossibleTripJobs AS dbo.IDTable READONLY
)
AS
SET NOCOUNT ON;
SELECT P.ID
,CAST(CASE WHEN COALESCE(D.Response, '') = '' THEN 0 ELSE 1 END AS bit) AS IsResponse
FROM @PossibleTripJobs P
JOIN dbo.tblCitrusSendingDetails D
ON P.ID = D.JobID
-- check on Tbljobbooking may not be needed if JobIDs in tblCitrusSendingDetails
-- are also a sub-set of those in Tbljobbooking
WHERE EXISTS
(
SELECT 1
FROM dbo.Tbljobbooking B
WHERE B.JobID = P.ID
);
GO
GRANT EXEC ON dbo.TripJobs TO <UserWhoConnectsFromOtherInstance>;
GO
From your local instance, try:
CREATE TABLE #CheckJobs
(
JobID int NOT NULL PRIMARY KEY
,IsResponse bit NOT NULL
);
DECLARE @PossibleTripJobs TABLE (JobID int NOT NULL);
INSERT INTO @PossibleTripJobs
SELECT JobID
FROM dbo.tbltripenddetail
WHERE TripendStatus =0
AND JobID <> 0
AND CAST(TripEndTime AS datetime) >= DATEADD(hour, -1, CURRENT_TIMESTAMP);
INSERT INTO #CheckJobs
EXEC [sqlcdscluster\sqlcds].cdsbusiness.dbo.TripJobs @PossibleTripJobs;
SELECT JobID, TotalDistanceTravelled, TotalTripFare
FROM dbo.tbltripenddetail T
WHERE EXISTS
(
SELECT 1
FROM #CheckJobs C
WHERE C.JobID = T.JobID
AND
(
(C.IsResponse = 1 AND CAST(T.TripEndTime AS datetime) >= DATEADD(minute, -20, CURRENT_TIMESTAMP))
OR
(C.IsResponse = 0 AND CAST(T.TripEndTime AS datetime) < DATEADD(minute, -20, CURRENT_TIMESTAMP))
)
);
August 21, 2015 at 12:50 am
Ken sir,
Thanks for your response
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply