Quick SQL challenges for mid-experienced+ DBAs (and advanced T-SQLers)

Question

Quick SQL challenges for mid-experienced+ DBAs (and advanced T-SQLers)

Viewing 10 posts - 31 through 39 (of 39 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 1

I've run it again after a reboot and closing the Chrome, the figures are more consistent but my first method is faster by a long way (on my desktop). For the other machines there is not much difference.

I've also tried it on my work laptop, an AWS RDS Server and an older production server.

My personal desktop i7-4790 CPU, 28 GB DDR3, SQL Server 2019

===== Scott's Code) =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop

 SQL Server Execution Times:
   CPU time = 3016 ms,  elapsed time = 2400 ms.

 SQL Server Execution Times:
   CPU time = 2483 ms,  elapsed time = 847 ms.

 SQL Server Execution Times:
   CPU time = 2454 ms,  elapsed time = 856 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's first answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop

 SQL Server Execution Times:
   CPU time = 671 ms,  elapsed time = 242 ms.

 SQL Server Execution Times:
   CPU time = 594 ms,  elapsed time = 236 ms.

 SQL Server Execution Times:
   CPU time = 751 ms,  elapsed time = 249 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's second answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop

 SQL Server Execution Times:
   CPU time = 2078 ms,  elapsed time = 1895 ms.

 SQL Server Execution Times:
   CPU time = 2045 ms,  elapsed time = 716 ms.

 SQL Server Execution Times:
   CPU time = 2065 ms,  elapsed time = 697 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's third answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop

 SQL Server Execution Times:
   CPU time = 3030 ms,  elapsed time = 17335 ms.

 SQL Server Execution Times:
   CPU time = 1954 ms,  elapsed time = 682 ms.

 SQL Server Execution Times:
   CPU time = 1593 ms,  elapsed time = 630 ms.
Batch execution completed 3 times.

Completion time: 2022-07-06T10:01:16.7537852+01:00

Work laptop i7-1061OU CPU 1.80GHz, 32 GB , SQL Server 2019

===== Scott's Code) =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop

 SQL Server Execution Times:
   CPU time = 4109 ms,  elapsed time = 911 ms.

 SQL Server Execution Times:
   CPU time = 4375 ms,  elapsed time = 913 ms.

 SQL Server Execution Times:
   CPU time = 4296 ms,  elapsed time = 931 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's first answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop

 SQL Server Execution Times:
   CPU time = 2704 ms,  elapsed time = 701 ms.

 SQL Server Execution Times:
   CPU time = 2749 ms,  elapsed time = 736 ms.

 SQL Server Execution Times:
   CPU time = 2719 ms,  elapsed time = 722 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's second answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop

 SQL Server Execution Times:
   CPU time = 3345 ms,  elapsed time = 845 ms.

 SQL Server Execution Times:
   CPU time = 3157 ms,  elapsed time = 856 ms.

 SQL Server Execution Times:
   CPU time = 3202 ms,  elapsed time = 826 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's third answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop

 SQL Server Execution Times:
   CPU time = 2923 ms,  elapsed time = 770 ms.

 SQL Server Execution Times:
   CPU time = 2608 ms,  elapsed time = 720 ms.

 SQL Server Execution Times:
   CPU time = 2766 ms,  elapsed time = 715 ms.
Batch execution completed 3 times.

Completion time: 2022-07-06T10:50:37.1584291+01:00

AWS RDS SQL db.m5d.2xlarge, 32 GB, 300GB NVMe, SQL Server 2019

***********************************************************************************************************************
===== Scott's Code) =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop

 SQL Server Execution Times:
   CPU time = 2499 ms,  elapsed time = 703 ms.

 SQL Server Execution Times:
   CPU time = 2657 ms,  elapsed time = 703 ms.

 SQL Server Execution Times:
   CPU time = 2469 ms,  elapsed time = 724 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's first answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop

 SQL Server Execution Times:
   CPU time = 2577 ms,  elapsed time = 702 ms.

 SQL Server Execution Times:
   CPU time = 2484 ms,  elapsed time = 716 ms.

 SQL Server Execution Times:
   CPU time = 2549 ms,  elapsed time = 711 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's second answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop

 SQL Server Execution Times:
   CPU time = 3390 ms,  elapsed time = 856 ms.

 SQL Server Execution Times:
   CPU time = 3407 ms,  elapsed time = 865 ms.

 SQL Server Execution Times:
   CPU time = 3420 ms,  elapsed time = 855 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's third answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop

 SQL Server Execution Times:
   CPU time = 3235 ms,  elapsed time = 768 ms.

 SQL Server Execution Times:
   CPU time = 3030 ms,  elapsed time = 797 ms.

 SQL Server Execution Times:
   CPU time = 3032 ms,  elapsed time = 789 ms.
Batch execution completed 3 times.

Completion time: 2022-07-06T08:42:51.4720635+00:00

Old production Xeon(R) CPU E5-2690 v4 @ 2.60GHx (4 processors), 32 GB, SQL Server 2012

***********************************************************************************************************************
===== Scott's Code) =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop

 SQL Server Execution Times:
   CPU time = 1969 ms,  elapsed time = 756 ms.

 SQL Server Execution Times:
   CPU time = 2064 ms,  elapsed time = 723 ms.

 SQL Server Execution Times:
   CPU time = 1921 ms,  elapsed time = 657 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's first answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop

 SQL Server Execution Times:
   CPU time = 2095 ms,  elapsed time = 700 ms.

 SQL Server Execution Times:
   CPU time = 2061 ms,  elapsed time = 690 ms.

 SQL Server Execution Times:
   CPU time = 2156 ms,  elapsed time = 675 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's second answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop

 SQL Server Execution Times:
   CPU time = 2562 ms,  elapsed time = 1073 ms.

 SQL Server Execution Times:
   CPU time = 2564 ms,  elapsed time = 924 ms.

 SQL Server Execution Times:
   CPU time = 2421 ms,  elapsed time = 893 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's third answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop

 SQL Server Execution Times:
   CPU time = 2297 ms,  elapsed time = 872 ms.

 SQL Server Execution Times:
   CPU time = 2390 ms,  elapsed time = 803 ms.

 SQL Server Execution Times:
   CPU time = 2297 ms,  elapsed time = 811 ms.
Batch execution completed 3 times.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 2

Steve Collins wrote:

select cast(year(v.sort_mo) as smallint), input_str.mo
from (values ('Jan'),('Feb'),('Mar'),
             ('Apr'),('May'),('Jun'),
             ('Jul'),('Aug'),('Sep'),
             ('Oct'),('Nov'),('Dec')) input_str(mo)
     cross apply (values (parse(concat(input_str.mo, '-01-1900') as date))) v(sort_mo)
order by v.sort_mo;

I don't like to presume on other people's code because I get in trouble for doing so. 😀 Where would the million row test table go in your code?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 100949 More actions · Answer 3

I wonder what Jonathan's parallelism settings are, and other SQL settings that might affect the run times. Some of those results are definitely out of place.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 4

ScottPletcher wrote:

I wonder what Jonathan's parallelism settings are, and other SQL settings that might affect the run times. Some of those results are definitely out of place.

I take it you mean on my personal desktop i7-4790 CPU 3.60GHz, 28 GB DDR3, SQL Server 2019?

tempdb is on a Fusion-IO 165 GB drive (about 9 years old)

Hyperthreading is switched off

Here are the advanced settings

sqlSettings

ScottPletcher SSC Guru Points: 100949 More actions · Answer 5

Yeah, Cost Threshold for Parallelism is way too low. That is likely causing the crazy spikes in response/CPU times.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 6

ScottPletcher wrote:

Yeah, Cost Threshold for Parallelism is way too low. That is likely causing the crazy spikes in response/CPU times.

I think 5 is the out of the box default setting.

I've changed it to 45 and the results don't look too different:

***********************************************************************************************************************
===== Scott's Code) =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop

 SQL Server Execution Times:
   CPU time = 2592 ms,  elapsed time = 2176 ms.

 SQL Server Execution Times:
   CPU time = 2704 ms,  elapsed time = 908 ms.

 SQL Server Execution Times:
   CPU time = 2703 ms,  elapsed time = 877 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's first answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop

 SQL Server Execution Times:
   CPU time = 686 ms,  elapsed time = 252 ms.

 SQL Server Execution Times:
   CPU time = 672 ms,  elapsed time = 238 ms.

 SQL Server Execution Times:
   CPU time = 641 ms,  elapsed time = 238 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's second answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop

 SQL Server Execution Times:
   CPU time = 2235 ms,  elapsed time = 1995 ms.

 SQL Server Execution Times:
   CPU time = 1922 ms,  elapsed time = 764 ms.

 SQL Server Execution Times:
   CPU time = 1906 ms,  elapsed time = 746 ms.
Batch execution completed 3 times.
***********************************************************************************************************************
===== Jonathan's third answer =====
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Beginning execution loop

 SQL Server Execution Times:
   CPU time = 2873 ms,  elapsed time = 17876 ms.

 SQL Server Execution Times:
   CPU time = 1938 ms,  elapsed time = 665 ms.

 SQL Server Execution Times:
   CPU time = 1939 ms,  elapsed time = 653 ms.
Batch execution completed 3 times.

Completion time: 2022-07-06T22:22:26.5949305+01:00

planetmatt SSCrazy Points: 2201 More actions · Answer 7

For 1, I tried a CTE to create a Month lookup table. No hardcoded string values

;WITH CTE_Months
AS
(
SELECT GETDATE() AS TheDate, LEFT(DATENAME(mm,GETDATE()),3) AS [MonthAbr],MONTH(GETDATE()) AS MonthOrder
UNION ALL
SELECT DATEADD(mm,1,TheDate),LEFT(DATENAME(mm,DATEADD(mm,1,TheDate)),3),MONTH(DATEADD(mm,1,TheDate))
FROM CTE_Months
WHERE TheDate < DATEADD(mm,11,GETDATE())
)

SELECT t.TheYear,t.TheMonth
FROM #MyTable t
JOIN CTE_Months m ON m.MonthAbr = t.themonth
ORDER BY TheYear,m.MonthOrder

For 3, this works for both BIT and INT fields

DROP TABLE IF EXISTS #BitFlip
GO
CREATE TABLE #BitFlip (BitField BIT, IntField INT)
INSERT INTO #BitFlip(BitField,IntField)
VALUES
(0,0),
(0,1),
(1,0),
(1,1)

SELECT 
BitField,ABS(BitField+-1) AS BitFieldFlipped,
IntField,ABS(IntField+-1) AS IntFieldFlipped
FROM #BitFlip

This reply was modified 1 year, 9 months ago by planetmatt.

ScottPletcher SSC Guru Points: 100949 More actions · Answer 8

(2) Here's my answer:

CREATE TABLE dbo.emp_training ( id int IDENTITY(1, 1) NOT NULL, emp_id int NOT NULL, training_id int NOT NULL, status char(1) NULL CHECK(status IN ('F', 'P')) );
/* DDL needing written ...*/
ALTER TABLE dbo.emp_training ADD pass_fail_id AS CASE WHEN status = 'P' THEN CAST(0 AS int) ELSE id END;
CREATE UNIQUE NONCLUSTERED INDEX index_name ON dbo.emp_training ( emp_id, training_id, pass_fail_id );
/* . */
INSERT INTO dbo.emp_training (emp_id, training_id, status ) VALUES(1, 1, 'P'); --Nondup
INSERT INTO dbo.emp_training (emp_id, training_id, status ) VALUES(2, 2, 'F'); --Nondup
INSERT INTO dbo.emp_training (emp_id, training_id, status ) VALUES(2, 2, 'F'); --Nondup
INSERT INTO dbo.emp_training (emp_id, training_id, status ) VALUES(2, 2, 'F'); --Nondup
INSERT INTO dbo.emp_training (emp_id, training_id, status ) VALUES(2, 2, 'P'); --Nondup
INSERT INTO dbo.emp_training (emp_id, training_id, status ) VALUES(1, 1, 'P'); --Dup
INSERT INTO dbo.emp_training (emp_id, training_id, status ) VALUES(2, 2, 'P'); --Dup

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 9

planetmatt wrote:

For 1, I tried a CTE to create a Month lookup table. No hardcoded string values

;WITH CTE_Months
AS
(
SELECT GETDATE() AS TheDate, LEFT(DATENAME(mm,GETDATE()),3) AS [MonthAbr],MONTH(GETDATE()) AS MonthOrder
UNION ALL
SELECT DATEADD(mm,1,TheDate),LEFT(DATENAME(mm,DATEADD(mm,1,TheDate)),3),MONTH(DATEADD(mm,1,TheDate))
FROM CTE_Months
WHERE TheDate < DATEADD(mm,11,GETDATE())
)

SELECT t.TheYear,t.TheMonth
FROM #MyTable t
JOIN CTE_Months m ON m.MonthAbr = t.themonth
ORDER BY TheYear,m.MonthOrder

That appears to only work for a 12 month time frame based on GETDATE(). Try it on the test table created at the following post.

https://www.sqlservercentral.com/forums/topic/quick-sql-challenges-for-mid-experienced-dbas-and-advanced-t-sqlers/page/2#post-4059350

You can get a copy of the fnTally() function at the similarly named link in my signature line below.

I also recommend that you don't use recursive CTEs to generate even 12 rows. Please see the following article for why.

https://www.sqlservercentral.com/articles/hidden-rbar-counting-with-recursive-ctes

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

planetmatt SSCrazy Points: 2201 More actions · Answer 10

Jeff Moden wrote:

planetmatt wrote:
For 1, I tried a CTE to create a Month lookup table. No hardcoded string values
;WITH CTE_Months
AS
(
SELECT GETDATE() AS TheDate, LEFT(DATENAME(mm,GETDATE()),3) AS [MonthAbr],MONTH(GETDATE()) AS MonthOrder
UNION ALL
SELECT DATEADD(mm,1,TheDate),LEFT(DATENAME(mm,DATEADD(mm,1,TheDate)),3),MONTH(DATEADD(mm,1,TheDate))
FROM CTE_Months
WHERE TheDate < DATEADD(mm,11,GETDATE())
)
SELECT t.TheYear,t.TheMonth
FROM #MyTable t
JOIN CTE_Months m ON m.MonthAbr = t.themonth
ORDER BY TheYear,m.MonthOrder
That appears to only work for a 12 month time frame based on GETDATE(). Try it on the test table created at the following post.
https://www.sqlservercentral.com/forums/topic/quick-sql-challenges-for-mid-experienced-dbas-and-advanced-t-sqlers/page/2#post-4059350
You can get a copy of the fnTally() function at the similarly named link in my signature line below.
I also recommend that you don't use recursive CTEs to generate even 12 rows. Please see the following article for why.
https://www.sqlservercentral.com/articles/hidden-rbar-counting-with-recursive-ctes

This does work correctly for all rows in your test table but I note your point of the performance issues of rCTEs.

This reply was modified 1 year, 9 months ago by planetmatt.