SQL brian teaser...

Question

SQL brian teaser...

Viewing 15 posts - 31 through 45 (of 70 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

WayneS (7/3/2009)
@Jeff - would you mind trying out this little piece of code and let me know how fast it is on your "computer in a deathbox"?:-D

No problem... would you post the code you used to build the Tally table please?

--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)

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

Also, please answer the following questions:

How many time have you had to split something in real production work that actually consisted of more than 8k bytes? What was it for?

--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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Last but not least, Wayne... I ran your code and the XML section returns nothing. Perhaps the forum ate some of your code. Can you attach the code, please?

--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)

WayneS SSC Guru Points: 95460 More actions · Answer 4

Jeff Moden (7/4/2009)
WayneS (7/3/2009)
@Jeff - would you mind trying out this little piece of code and let me know how fast it is on your "computer in a deathbox"?:-D
No problem... would you post the code you used to build the Tally table please?

Sure, but I figured that you had one available...

create table dbo.Numbers (Number int not null)

insert into dbo.Numbers

select top 1000000

row_number() OVER (ORDER BY s1.object_id) AS Number

from master.sys.objects s1, master.sys.objects s2, master.sys.objects s3, master.sys.objects s4

alter table dbo.Numbers ADD CONSTRAINT [PK_Numbers] PRIMARY KEY CLUSTERED (Number)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Jeff Moden (7/4/2009)
Last but not least, Wayne... I ran your code and the XML section returns nothing. Perhaps the forum ate some of your code. Can you attach the code, please?

Never mind my last... I was able to get the complete code by quoting your post.

I'd still like to see your code for the Tally table creation and still like to know how many times and what the purpose of you actually needing to split more than 8k characters in a production environment was.

--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)

WayneS SSC Guru Points: 95460 More actions · Answer 6

Jeff Moden (7/4/2009)
Also, please answer the following questions:
How many time have you had to split something in real production work that actually consisted of more than 8k bytes? What was it for?

Once. A SSRS report with a multi-select parameter that could potentially have > 30,000 items in it. I argued against it, but the PM won... 🙁

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 7

Jeff Moden (7/4/2009)
Last but not least, Wayne... I ran your code and the XML section returns nothing. Perhaps the forum ate some of your code. Can you attach the code, please?

See attached. I added to it to build the tally table in AdventureWorks also.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

WayneS (7/3/2009)
And, just to be complete, I ran this test again including Lynn's CTE method (DelimitedSplit):
XML: 1571/1100/1350/1134/1130
Tally: 1429/1313/1306/1317/1288
CTE: 6283/6221/6292/6445/6569

Heh... looks like the Tally table won twice there.

But, I agree... joining to a VARCHAR(MAX) is comparatively slow. That's why I'm asking how many times someone actually needs to split something more than 8k bytes in length.

Thanks for the testing ideas, though... I've got a couple of different things I've been trying and this adds to those.

As you requested, on my old box, the Tally table gets beat by about 10% in the CPU department, as you would suspect it would. Here's the run...

---- XML ----

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 938 ms, elapsed time = 990 ms.

(31465 row(s) affected)

SQL Server Execution Times:

CPU time = 1500 ms, elapsed time = 3380 ms.

(EDIT: Total CPU = 2438, Total Elapsed = 4370)

---- TALLY ----

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

(31465 row(s) affected)

SQL Server Execution Times:

CPU time = 2703 ms, elapsed time = 4233 ms.

(EDIT: Total CPU = 2703 , Total Elapsed = 4233)

--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)

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

WayneS (7/4/2009)
Jeff Moden (7/4/2009)
WayneS (7/3/2009)
@Jeff - would you mind trying out this little piece of code and let me know how fast it is on your "computer in a deathbox"?:-D
No problem... would you post the code you used to build the Tally table please?
Sure, but I figured that you had one available...
create table dbo.Numbers (Number int not null)
insert into dbo.Numbers
select top 1000000
row_number() OVER (ORDER BY s1.object_id) AS Number
from master.sys.objects s1, master.sys.objects s2, master.sys.objects s3, master.sys.objects s4
alter table dbo.Numbers ADD CONSTRAINT [PK_Numbers] PRIMARY KEY CLUSTERED (Number)

I absolutely do have my own code for a Tally table. I just wanted to make sure that yours had a Clustered PK on it because a lot of folks forget that. Thanks, Wayne.

--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)

WayneS SSC Guru Points: 95460 More actions · Answer 10

Jeff Moden (7/4/2009)
... I just wanted to make sure that yours had a Clustered PK on it because a lot of folks forget that. Thanks, Wayne.

Ahh, now I understand your strange request. 😉

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 11

Jeff Moden (7/4/2009)
...Thanks for the testing ideas, though... I've got a couple of different things I've been trying and this adds to those.
As you requested, on my old box, ...

Thanks for doing this Jeff! And I'm glad I could give you something else to try out...

I do agree that both the Tally table and the pseudo-tally table (CTE) method are excellent methods, esp. when dealing with smaller strings than what I threw at it. And any of these three methods is preferable to any cursor / looping method out there. I was shocked at how much slower the CTE method was when I threw the large string at it... I tested it expecting it to be nearly as competitive as the tally table was.

Thanks again for your testing.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

You bet, Wayne. Thanks for answering my seemingly unrelated questions. They go far in helping me understand the needs of folks so I can setup certain tests for certain scenarios.

--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)

WayneS SSC Guru Points: 95460 More actions · Answer 13

Jeff Moden (7/4/2009)
You bet, Wayne. Thanks for answering my seemingly unrelated questions. They go far in helping me understand the needs of folks so I can setup certain tests for certain scenarios.

No problem. The Tally table one threw me... heck, I got it from you in the first place! And asking the business reason for something weird (in this case, a comma-delimited string of > 30,000 items definitely qualifies) is fair... and another pair of eyes can often provide an alternate solution for what's being done.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Digs SSCertifiable Points: 7333 More actions · Answer 14

Here is another tab split function that I pulled from a SQL 2000 book by Andrew Novick : Transact SQL UDFs

Which is better Lynns or Andrews ??? 😎

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 15

Digs (7/4/2009)
Here is another tab split function that I pulled from a SQL 2000 book by Andrew Novick : Transact SQL UDFs
Which is better Lynns or Andrews ??? 😎

We've given you the ability to do your own testing. Test it and report back to us. 😉 You might also want to actually read the rest of this thread because, as good as it is, the Base 10 CTE isn't the one you should be comparing performance against.

--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)