November 20, 2016 at 3:11 am
I have inherited a project that uses MS Sql Server 2014 and some Stored Procedures.
In one of the Stored Procedure there is this select
IF(SELECT Max(rowcreateddt)
FROM serialno) IS NOT NULL
AND ( (SELECT Max(rowcreateddt)
FROM serialno) > @RunDateTime
OR ( @CheckIfInFuture = '1'
AND (SELECT Max(rowcreateddt)
FROM serialno) < @RunDateTime - @DaysIntoFuture ) )
The code is used to check that the time isn't changed in the computer.
Not being familiar with Sql Server and TSQL, I guess that the MAX(rowCreatedDT) will execute 3 times in this SELECT. Since there isn't an INDEX on rowCreatedDT and the table has more than 4 million rows. This must take time, or do SQL Server 'cache' the result of the MAX(rowCreatedDT) so it only execute it once?
I realize that I need to add an index on rowCreatedDT, but even then if the MAX(rowCreatedDT) is executed 3 times it will take more time than if I split the select into this
DECLARE @MaxRowCreatedDT DATETIME;
SELECT @MaxRowCreatedDT = Max(rowcreateddt)
FROM serialno;
IF ( @MaxRowCreatedDT IS NOT NULL )
AND ( ( @MaxRowCreatedDT > @RunDateTime )
OR ( @CheckIfInFuture = '1'
AND ( @MaxRowCreatedDT < @RunDateTime - @DaysIntoFuture ) ) )
November 20, 2016 at 5:12 am
andis59 (11/20/2016)
I have inherited a project that uses MS Sql Server 2014 and some Stored Procedures.In one of the Stored Procedure there is this select
IF(SELECT Max(rowcreateddt)
FROM serialno) IS NOT NULL
AND ( (SELECT Max(rowcreateddt)
FROM serialno) > @RunDateTime
OR ( @CheckIfInFuture = '1'
AND (SELECT Max(rowcreateddt)
FROM serialno) < @RunDateTime - @DaysIntoFuture ) )
The code is used to check that the time isn't changed in the computer.
Not being familiar with Sql Server and TSQL, I guess that the MAX(rowCreatedDT) will execute 3 times in this SELECT. Since there isn't an INDEX on rowCreatedDT and the table has more than 4 million rows. This must take time, or do SQL Server 'cache' the result of the MAX(rowCreatedDT) so it only execute it once?
I realize that I need to add an index on rowCreatedDT, but even then if the MAX(rowCreatedDT) is executed 3 times it will take more time than if I split the select into this
DECLARE @MaxRowCreatedDT DATETIME;
SELECT @MaxRowCreatedDT = Max(rowcreateddt)
FROM serialno;
IF ( @MaxRowCreatedDT IS NOT NULL )
AND ( ( @MaxRowCreatedDT > @RunDateTime )
OR ( @CheckIfInFuture = '1'
AND ( @MaxRowCreatedDT < @RunDateTime - @DaysIntoFuture ) ) )
You are most likely to benefit from this change but looking at the code, I get the suspicion that this is not the only problem.
😎
November 20, 2016 at 5:18 am
So the Max(rowCreatedDT) will execute 3 times!
So I thought.
And Yes, there is more problems in this and other stored procedures.
Need to learn about indexes, stored procedures and ... really fast!
Thank for your reply!
// Anders
November 21, 2016 at 8:30 am
>> I guess that the MAX(rowCreatedDT) will execute 3 times in this SELECT. Since there isn't an INDEX on rowCreatedDT and the table has more than 4 million rows. This must take time, or do SQL Server 'cache' the result of the MAX(rowCreatedDT) so it only execute it once? <<
Since the Max () function is deterministic, the ANSI/ISO standards say it is done once within the scope of the query. But there is a bigger problem! This is an audit trail, and we never do audits in the same table that is being audited. You will go to jail for this (part of my consulting is as an expert witness and I find this a lot more times and I would really like to ever see). The problem is that when the table is deleted or altered, so is the audit trail. Please remember that today ROI = "risk of incarceration" and that some expert witness (me) will testify against anyone who coded like this.
Also the code you posted implies that you have bit flags in SQL. That was assembly language and has no place in this language.
I know it is not your fault and not really yelling at you, but remember when we say "legacy code" we actually mean "the family curse" and might well want to update your resume before everything falls apart.
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
November 21, 2016 at 8:59 am
Well, not knowing SQL and not have english as my first language hinders me sometimes, so...
The MAX(rowCreatedDT) is only scanning the table once!
What is an Audit Trail?
The stored procedure that contain the code creates a new Serial Number record. The Serial Number contains the current date and a sequence number. The code I presented was for checking that the date of the computer hadn't be set back to an earlier date, since then we might end up with the same serial number again (or more likely an error when creating the row.
I'm guessing that you are refering to @CheckIfInFuture = '1' when you talk about bit flags? It's declared variable @CheckIfInFuture CHAR(1) = '0' that us used for
activating a function (or not) that checks so that the current date is not more than 30 days ago. Don't know why, but...
You are right in saying that "legacy code" is like a "curse" family or not...
// Anders
I
November 21, 2016 at 9:37 am
andis59 (11/21/2016)
The MAX(rowCreatedDT) is only scanning the table once!
No, this is incorrect, the code will result in three separate scans, using the variable will only do one scan.
😎
Contrary to what some have suggested, working with SQL server isn't all about standards;-)
November 21, 2016 at 11:30 am
CELKO (11/21/2016)
Also the code you posted implies that you have bit flags in SQL. That was assembly language and has no place in this language.
This language is T-SQL, not ANSI SQL. T-SQL does not implement the Boolean data type defined in ANSI SQL. We have to work with the tools at hand and that means bit flags do have a place in this language until such point as T-SQL includes the Boolean data type.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 21, 2016 at 11:49 am
The MAX(rowCreatedDT) is only scanning the table once!
There is a button in your SSMS window that displays "include actual execution plan" when you hover over it. You push this button and call your stored proc. This will tell you what the engine is actually doing from the SQL you have in the stored procedure. Is this where you looked to make this determination?
----------------------------------------------------
November 21, 2016 at 12:45 pm
MMartin1 (11/21/2016)
The MAX(rowCreatedDT) is only scanning the table once!
There is a button in your SSMS window that displays "include actual execution plan" when you hover over it. You push this button and call your stored proc. This will tell you what the engine is actually doing from the SQL you have in the stored procedure. Is this where you looked to make this determination?
No it was my deduction from what CELKO wrote. Which turned out to be wrong.
I have tried "include actual execution plan" and so far I don't understand what it means...
I really must find some book or video or ... that teaches this kind of things.
November 21, 2016 at 12:53 pm
andis59 (11/21/2016)
MMartin1 (11/21/2016)
The MAX(rowCreatedDT) is only scanning the table once!
There is a button in your SSMS window that displays "include actual execution plan" when you hover over it. You push this button and call your stored proc. This will tell you what the engine is actually doing from the SQL you have in the stored procedure. Is this where you looked to make this determination?
No it was my deduction from what CELKO wrote. Which turned out to be wrong.
I have tried "include actual execution plan" and so far I don't understand what it means...
I really must find some book or video or ... that teaches this kind of things.
Here's a book on Execution Plans. The PDF is free and you can order a hard copy if you prefer paper.
November 21, 2016 at 1:02 pm
Thanks Luis!
Looking forward to reading it!
November 21, 2016 at 1:18 pm
Once again, J.Celko was able to confuse instead of helping. He's absolutely correct, the MAX() function will be evaluated once within the scope of the query. The problem is that the original query calls it from 3 different scopes. It uses 3 unrelated queries, resulting on the table being scanned 3 times.
There's also the issue of the unnecessary evaluation of NULL. When using NULLs in a comparison, they'll never return true (unless they're properly handled).
Here's an example showing how the MAX function is evaluated just once, how the NULL validation is not necessary and how this can be done without variables or additional steps.
CREATE TABLE #serialno(
rowcreateddt datetime);
DECLARE @RunDateTime datetime = '20161124',
@CheckIfInFuture bit = 1,
@DaysIntoFuture int = 3;
INSERT INTO #serialno VALUES('20161120');
IF(SELECT Max(rowcreateddt)
FROM #serialno) IS NOT NULL
AND ( (SELECT Max(rowcreateddt) FROM #serialno) > @RunDateTime
OR ( @CheckIfInFuture = '1' AND (SELECT Max(rowcreateddt) FROM #serialno) < @RunDateTime - @DaysIntoFuture ) )
SELECT 1;
IF EXISTS( SELECT 1
FROM #serialno
HAVING Max(rowcreateddt) > @RunDateTime
OR ( @CheckIfInFuture = 1 AND Max(rowcreateddt) < @RunDateTime - @DaysIntoFuture))
SELECT 2;
GO
DROP TABLE #serialno;
If you use the option to include execution plan, you'll see 3 icons called "Table Scan" that means that the table is being read 3 times. In my suggestion, using EXISTS and HAVING, there's only one TableScan.
I hope that I've made myself clear and you can learn something from this. If you still have questions, please come back and ask them.
November 21, 2016 at 1:40 pm
Here too is a resource that can help you get started with graphical execution plans : https://www.mssqltips.com/sqlservertip/1873/how-to-read-sql-server-graphical-query-execution-plans/
----------------------------------------------------
November 21, 2016 at 2:40 pm
IF EXISTS( SELECT 1
FROM #serialno
HAVING Max(rowcreateddt) > @RunDateTime
OR ( @CheckIfInFuture = 1 AND Max(rowcreateddt) < @RunDateTime - @DaysIntoFuture))
SELECT 2;
If you compare your code with this
DECLARE @MaxRowCreatedDT DATETIME;
SELECT @MaxRowCreatedDT = Max(rowcreateddt)
FROM #serialno;
IF (@MaxRowCreatedDT IS NOT NULL)
AND (
(@MaxRowCreatedDT > @RunDateTime)
OR (
@CheckIfInFuture = '1'
AND (@MaxRowCreatedDT < @RunDateTime - @DaysIntoFuture)
)
)
SELECT 3;
Is there any difference in performance?
BTW, does the size of the database table has any impact on the Tuning Advisor and the time it takes to finish?
November 21, 2016 at 2:41 pm
MMartin1 (11/21/2016)
Here too is a resource that can help you get started with graphical execution plans : https://www.mssqltips.com/sqlservertip/1873/how-to-read-sql-server-graphical-query-execution-plans/
Looks like a good place to start!
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply