The T-SQL Quiz

Question

The T-SQL Quiz

Viewing 15 posts - 286 through 300 (of 309 total)

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

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

dfalso (6/29/2008)
So now, what's the minimum number of rows guaranteed to be in master's syscolumns?

Like Barry said... there's always at least 11,000 rows even in a brand new installation. In SQL Server 2000, Master.dbo.SysColumns always contains at least 4,000.

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Jeff Moden (6/29/2008)
rbarryyoung (6/29/2008)
Any of this cow left for me? 🙂
Here is my entry (really just a tweak of Jeff's):
Nicely done, Barry... but I get numbers just a bit slower (not much... about 20 ms on a million rows) because of the control you've put on the cross join... faster to let nature take its course.

This is wierd, because I really am getting substantially different numbers here.

For instance, here is the output when I set @Testsize up to 100,000,000:

[font="Courier New"]

====================================================================================================

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

===== Matt Miller's Method =====

SQL Server Execution Times:

CPU time = 13125 ms, elapsed time = 35721 ms.

====================================================================================================

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

===== Itzek's Method =====

SQL Server Execution Times:

CPU time = 78375 ms, elapsed time = 210652 ms.

====================================================================================================

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

===== Jeff Moden's Method

Table 'syscolrdb'. Scan count 2, logical reads 177, physical reads 2, read-ahead reads 97, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'syscolpars'. Scan count 2, logical reads 9, physical reads 2, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 60984 ms, elapsed time = 168206 ms.

====================================================================================================

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

===== RBarryYoung's Method

Table 'syscolrdb'. Scan count 2, logical reads 177, physical reads 2, read-ahead reads 97, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'syscolpars'. Scan count 2, logical reads 10, physical reads 1, read-ahead reads 47, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 60703 ms, elapsed time = 63785 ms.

[/font]

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

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

Similar error as to what someone recently did with dates 😉 Check the final value of @Bitbucket on Matt's...

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

Hey guys,

I've been out of town. Thanks for keeping the conversation going. Especially since you've been supplying answers much better than the ones I would have supplied.

"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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Jeff Moden (6/29/2008)
Similar error as to what someone recently did with dates 😉 Check the final value of @Bitbucket on Matt's...

OK, I see. :blush: I really need to take a break...

Anyways, that only affects Matt's and only on the 100,000,000 setting. I get differences for all of them. For instance, on my laptop, my tweak always clocks in at slightly faster than your original, for every setting that I have tried...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 6

It will vary from machine to machine... except on Tuesdays... 😛

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

Sergiy (5/28/2007)
If you don't specify the version that means it must work on EVERY version, not only the one you know.

Of course these means that CTE's are out as well then.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 8

Jack Corbett (1/12/2009)
Sergiy (5/28/2007)
If you don't specify the version that means it must work on EVERY version, not only the one you know.
Of course these means that CTE's are out as well then.

No way. As a matter of fact, since 2000 is out of support, you could argue that making it work there is out of bounds (I'm not arguing that, but one could).

"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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 9

Not to mention 6.5 which had no support for TOP operator... 😀

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 10

Grant Fritchey (1/13/2009)
Jack Corbett (1/12/2009)
Sergiy (5/28/2007)
If you don't specify the version that means it must work on EVERY version, not only the one you know.
Of course these means that CTE's are out as well then.
No way. As a matter of fact, since 2000 is out of support, you could argue that making it work there is out of bounds (I'm not arguing that, but one could).

True enough, 2000 is out of support... now, tell me it's out of use. Hmmm... that would make a good survey question... I'll think there's almost as many bytes stored under 2k as there are in 2k5.

--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: 1004407 More actions · Answer 11

How odd... here we're talking about portability between releases of SQL Server... the ANSI-only folks would have a ball with this. Of course, then I'd have to launch some pork chops because even current ANSI code isn't always backwards compatible, either. 😀

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 12

Jeff Moden (1/13/2009)
True enough, 2000 is out of support... now, tell me it's out of use. Hmmm... that would make a good survey question... I'll think there's almost as many bytes stored under 2k as there are in 2k5.

We'll we're running about 90% of our databases in 2000 still. So it's hardly a lost art around here. We're in the process of trying to move everyone to 2008. It looks like, by & large, 2005 is going to be skipped.

"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

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 13

Grant Fritchey (1/13/2009)
Jeff Moden (1/13/2009)
True enough, 2000 is out of support... now, tell me it's out of use. Hmmm... that would make a good survey question... I'll think there's almost as many bytes stored under 2k as there are in 2k5.
We'll we're running about 90% of our databases in 2000 still. So it's hardly a lost art around here. We're in the process of trying to move everyone to 2008. It looks like, by & large, 2005 is going to be skipped.

Yep... same here. The way we're shifting to 2008 is we need to do a 2.0 rewrite of our stuff... the legacy code we were left (and there's no "original" folks left) is in really sorry shape, very performance and scalability inhibited, and there's virtually no documentation in the code to speak of. Some of the code is just incredibly stupid. For example, someone actually made a function to do a modulo and someone else used INT to drop times from dates (rounding problem). I took one section of code that took 40 minutes just to get a file ready for import... admittedly, it's a highly import resistant file, but they do a couple of imports/re-exports, hit it with a Perl script, and a bunch of other hooie... I got it down to 90 seconds to do the actual import and that's without any tuning... Sorry... didn't mean to make that sound like a brag... it just shows how bad the legacy code is.

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 14

Grant Fritchey (1/13/2009)
Jeff Moden (1/13/2009)
True enough, 2000 is out of support... now, tell me it's out of use. Hmmm... that would make a good survey question... I'll think there's almost as many bytes stored under 2k as there are in 2k5.
We'll we're running about 90% of our databases in 2000 still. So it's hardly a lost art around here. We're in the process of trying to move everyone to 2008. It looks like, by & large, 2005 is going to be skipped.

Where I am at right now, part-time contract for 2 more weeks, there is one "minor" system on 2005, the rest are 2000 except for the Quality system which is 7.0 still. Of course this is what happens when you treat your IT staff like dirt, make them the first part of layoffs, and drive them all to find new jobs, even for less pay. One guy took a 50% pay cut to get out.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 15

Jack Corbett (1/13/2009)
Where I am at right now, part-time contract for 2 more weeks, there is one "minor" system on 2005, the rest are 2000 except for the Quality system which is 7.0 still. Of course this is what happens when you treat your IT staff like dirt, make them the first part of layoffs, and drive them all to find new jobs, even for less pay. One guy took a 50% pay cut to get out.

Holy cow. That is some serious desperation to take a 50% pay cut. Has consulting there been entertaining.

"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