September 30, 2021 at 3:46 pm
Hello,
While running the above query thru SSMS we see odd behavior with the time it takes to run the report:
If we try with users like: some users it is taking about 50 secs., to run the query.
and some users taking more than 10 mins.
Can someone help me please: execution time is different between user to user in SQL Server
SELECT DISTINCT cast(eds.[ClickCount] as int) as 'Email Stats - Click Count' , case when ISNULL(rtrim(ltrim(eds.[ClickedDate])),'') <> '' THEN convert(varchar(12),cast(eds.[ClickedDate] as datetime), 111) ELSE NULL END as 'Email Stats - Clicked Date' , eds. as 'Email Stats - url' , 'Processed' AS NRorR FROM dbo.vw_DataRecord pd (NOLOCK) LEFT JOIN dbo.vwEmailDataRecordStats eds (NOLOCK) ON eds.ClientID = pd.ClientId AND eds.DataRecordID = pd.DataRecordID WHERE pd.[ClientId] = 164 AND ( (Cast(Convert(varchar(12), pd.[DateProcessed] , 101) as DateTime)= cast(Convert(varchar(12),DATEADD(DD,-1,getDate()),101) as DateTime)) )
September 30, 2021 at 3:52 pm
People will probably ask you to post execution plans (ideally for slow and fast executions) in order to be able to help you.
Here is that query again, in human-readable form:
SELECT DISTINCT
[Email Stats - Click Count] = CAST(eds.ClickCount AS INT)
,[Email Stats - Clicked Date] = CASE
WHEN ISNULL(RTRIM(LTRIM(eds.ClickedDate)), '') <> '' THEN
CONVERT(VARCHAR(12), CAST(eds.ClickedDate AS DATETIME), 111)
ELSE
NULL
END
,[Email Stats - url] = eds.url
,NRorR = 'Processed'
FROM dbo.vw_DataRecord pd (NOLOCK)
LEFT JOIN dbo.vwEmailDataRecordStats eds (NOLOCK)
ON eds.ClientID = pd.ClientId
AND eds.DataRecordID = pd.DataRecordID
WHERE pd.ClientId = 164
AND ((CAST(CONVERT(VARCHAR(12), pd.DateProcessed, 101) AS DATETIME) = CAST(CONVERT(
VARCHAR(12)
,DATEADD(DD, -1, GETDATE())
,101
) AS DATETIME)
)
);
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 30, 2021 at 4:44 pm
First, get rid of the functions against table columns in the WHERE clause. Those are always a terrible idea for performance. (Code reformatted but so that it can be read without scanning 50 feet to the right.)
SELECT DISTINCT
CAST(eds.ClickCount AS INT) AS 'Email Stats - Click Count',
CASE WHEN eds.ClickedDate <> ''
THEN CONVERT(VARCHAR(12), CAST(eds.ClickedDate AS DATETIME), 111)
ELSE NULL END AS 'Email Stats - Clicked Date',
eds.url AS 'Email Stats - url',
'Processed' AS NRorR
FROM dbo.vw_DataRecord pd WITH (NOLOCK)
LEFT JOIN dbo.vwEmailDataRecordStats eds WITH (NOLOCK) ON eds.ClientID = pd.ClientId AND eds.DataRecordID = pd.DataRecordID
WHERE pd.ClientId = 164 AND
pd.DateProcessed >= DATEADD(DAY, -1, CAST(GETDATE() AS date)) AND
--^^--GOT RID OF FUNCTION(s) AGAINST pd.DateProcessed; function against GETDATE() irrelevant
pd.DateProcessed < CAST(GETDATE() AS date)
--^^--GOT RID OF FUNCTION(s) AGAINST pd.DateProcessed; function against GETDATE() irrelevant
Next, make sure you have the proper indexes to support the query. By far the single most important index for performance is the clustered index, thus try to make sure your tables have the best clus index possible for that table (hint: usually this is NOT on an identity column).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 30, 2021 at 4:46 pm
That might give clues, but when people ask you to post execution plans, what they really want is to post the xml -- i.e., right click on the execution plan in SSMS, and select Save as Execution Plan, and post that. If you have confidentiality concerns, you can open the plan in SQL Sentry Plan Explorer, and select Tools - Anonymize Plan, and then save & post the anonymized plan.
that sqlplan file contains details not visible in your picture. Some are visible in tooltips when you hover over the plan in SSMS or SQL Sentry Explorer. But a picture does not provide the important details.
September 30, 2021 at 4:57 pm
(Code reformatted but so that it can be read without scanning 50 feet to the right.)
50 feet? Is your browser set to Mr Magoo mode?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 30, 2021 at 7:05 pm
Thank you very much, but if I get rid of functions my code will not give what I need.
September 30, 2021 at 7:35 pm
That code should return exactly the same datetime range as your original code. If not, please post what strings your current code is returning for a couple of rows against the table column and getdate().
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 1, 2021 at 5:05 pm
Simple fact of the matter is, those functions prevent statistics use and lead to index scans. No matter what. If you cannot remove them, you cannot improve performance by query tuning and/or index/structure adjustment. Instead, you can only improve performance by purchasing more, bigger, faster hardware. Sorry, but there it is. There may be other tuning opportunities as well, but those functions are a fundamental problem. That's why they were the first thing spotted.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 1, 2021 at 5:20 pm
ScottPletcher wrote:(Code reformatted but so that it can be read without scanning 50 feet to the right.)
50 feet? Is your browser set to Mr Magoo mode?
Hyperbole.
But, c'mon man, an 86-byte indentation (from "AND" to the last VARCHAR) is just ridiculous:
WHERE pd.ClientId = 164
AND ((CAST(CONVERT(VARCHAR(12), pd.DateProcessed, 101) AS DATETIME) = CAST(CONVERT(
VARCHAR(12)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 1, 2021 at 5:44 pm
Thank you very much, but if I get rid of functions my code will not give what I need.
WHERE pd.ClientId = 164
AND pd.DateProcessed >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), -1)
AND pd.DateProcessed < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
Also, who knows what horrors are inside dbo.vwEmailDataRecordStats. We cannot see your system.
October 2, 2021 at 6:49 pm
Phil Parkin wrote:ScottPletcher wrote:(Code reformatted but so that it can be read without scanning 50 feet to the right.)
50 feet? Is your browser set to Mr Magoo mode?
Hyperbole.
But, c'mon man, an 86-byte indentation (from "AND" to the last VARCHAR) is just ridiculous:
WHERE pd.ClientId = 164
AND ((CAST(CONVERT(VARCHAR(12), pd.DateProcessed, 101) AS DATETIME) = CAST(CONVERT(
VARCHAR(12)
I agree it wasn't pretty, but that's what SQL Prompt gave me and I wasn't going to faff around manually reformatting the results. I still think it was much better than the version which came in the original post.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 4, 2021 at 2:34 pm
What you recommend?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply