June 21, 2017 at 6:30 am
I'm utterly perplexed by something I'm seeing. I'd always been under the impression that there should be negligible difference between truncating a table and dropping/creating it fresh. But I have a script that seems to give lie to that idea.
It's a fairly long and involved script, but the key part is right at the beginning. If I start the script with this:
IF EXISTS (SELECT name FROM sys.tables WHERE name = N'cus_Measure_PREV9')
BEGIN
DROP TABLE cus_Measure_PREV9;
END;
CREATE TABLE cus_Measure_PREV9
(
PID numeric(19,0) PRIMARY KEY,
Age int,
MedicareId varchar(25),
FirstName varchar(35),
LastName varchar(60),
DOB varchar(15),
Sex varchar(1),
VisitDate datetime,
Excluded bit,
BMINormal bit,
Followup bit
);
...the script runs in about 50 seconds. If I start it with this:
IF EXISTS (SELECT name FROM sys.tables WHERE name = N'cus_Measure_PREV9')
BEGIN
TRUNCATE TABLE cus_Measure_PREV9;
END
ELSE
BEGIN
CREATE TABLE cus_Measure_PREV9
(
PID numeric(19,0) PRIMARY KEY,
Age int,
MedicareId varchar(25),
FirstName varchar(35),
LastName varchar(60),
DOB varchar(15),
Sex varchar(1),
VisitDate datetime,
Excluded bit,
BMINormal bit,
Followup bit
)
END;
...it takes almost five minutes. This is literally the only part of the script I'm changing, so it appears as though truncating is adding a full four minutes to the script. The table contains just over 15,000 records, so it's not like we're talking about a massive amount of data. I've switched back and forth several times and the results are consistent. Can anyone help me understand these results?
Thanks,
Ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
June 21, 2017 at 6:34 am
It's probably not worth stating, but just in case... I did run this part of the script in isolation and confirmed that the time difference is not taking place here.
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
June 21, 2017 at 7:07 am
Did you check for any blocking happening while testing?
June 21, 2017 at 7:14 am
Luis Cazares - Wednesday, June 21, 2017 7:07 AMDid you check for any blocking happening while testing?
It's a test environment, and I'm currently the only one with access to this database.
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
June 21, 2017 at 7:49 am
Did you check for waits, see if the query is waiting for something and if so, what?
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
June 21, 2017 at 9:04 am
GilaMonster - Wednesday, June 21, 2017 7:49 AMDid you check for waits, see if the query is waiting for something and if so, what?
Honestly I don't know a lot about waits, so I ran Paul Randal's script (<link) and got identical results whether the TRUNCATE script was running, the DROP/CREATE script was running, or neither script was running. I'm guessing that means we can move on, but please correct me if I'm wrong. Sorry, I'm pretty clueless there.
I decided to try running the script in chunks from the top down (there is an INSERT followed by a series of UPDATEs) to see if there was a specific block that was getting caught up. At first things looked more or less the same in both scenarios, within a few seconds here and there. But then I got to one UPDATE block with more joins than are typical for the script (but nothing crazy), including a join to a table variable. As soon as I included this block, the run time jumped up to four minutes and change.
UPDATE p9
SET Followup = 1
FROM cus_Measure_PREV9 p9
INNER JOIN [blah blah blah...]
INNER JOIN [blah blah blah...]
INNER JOIN @Obs o
ON p9.PID = o.PID
AND (
(p9.Age >= 65 AND o.OBSVALUE >= 30)
OR
(p9.Age BETWEEN 18 AND 64 AND o.OBSVALUE >= 25)
)
Just on a whim - I have no idea what made me try it - I excluded the join to the table variable. Suddenly the run time was down to under a minute again. I went back and forth a few times to confirm my findings, and the difference remained consistent. I then switched back to the DROP/CREATE block and repeated the experiment. The script up to that point ran for the same amount of time (within a second) whether or not I included the join to the table variable. The plot thickens.
So now I'm left with the conclusion that my table, when truncated, performs dramatically worse in a join with a table variable than it does when the table is dropped and re-created. Does that make any kind of sense at all?
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
June 21, 2017 at 11:00 am
The server-wide waits are uninteresting here, and since they're aggregated from the server start, they're not going to tell you the difference between two statements.
sys.dm_exec_request and the wait_type and wait_resource columns are the wait information for that query.
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
June 21, 2017 at 11:03 am
ronmoses - Wednesday, June 21, 2017 9:04 AMSo now I'm left with the conclusion that my table, when truncated, performs dramatically worse in a join with a table variable than it does when the table is dropped and re-created. Does that make any kind of sense at all?
Yes. Truncate doesn't invalidate statistics, so the row estimations will be out, and with table variables not having accurate row estimations either, you've pretty much asked the optimiser to take a wild-ass guess based on incorrect information to generate a query plan. No surprise it gets it wrong.
Do the inserts into the truncated table and then do a full stats update (and consider replacing the table variable with a temp table if good performance is important to you)
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
June 21, 2017 at 11:09 am
GilaMonster - Wednesday, June 21, 2017 11:03 AMronmoses - Wednesday, June 21, 2017 9:04 AMSo now I'm left with the conclusion that my table, when truncated, performs dramatically worse in a join with a table variable than it does when the table is dropped and re-created. Does that make any kind of sense at all?
Yes. Truncate doesn't invalidate statistics, so the row estimations will be out, and with table variables not having accurate row estimations either, you've pretty much asked the optimiser to take a wild-ass guess based on incorrect information to generate a query plan. No surprise it gets it wrong.
Do the inserts into the truncated table and then do a full stats update (and consider replacing the table variable with a temp table if good performance is important to you)
Amazing. Thank you Gail! You're an asset to the community. (But you knew that.)
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply