query for consecutive alphabets

Question

query for consecutive alphabets

Viewing 8 posts - 31 through 37 (of 37 total)

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

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

SQLKnowItAll (4/5/2012)
Jeff Moden (4/5/2012)
SQLKnowItAll (4/5/2012)
GilaMonster (4/5/2012)
My question would be why do TOP <x> in an EXISTS when the point of an EXISTS (or NOT EXISTS) is to terminate as soon as it finds a row.
http://sqlinthewild.co.za/index.php/2011/04/05/to-top-or-not-to-top-an-exists/
Count 1 and count * are synonymous. So are EXISTS (select 1 ...) EXISTS (select * ...) and EXISTS (select <column>...)
So adding the TOP is useless in the EXISTS (or NOT EXISTS). So really, its just extra typing and script that can become confusing? 🙂
Yes... that's what happens when I post at 1:17AM. 😛 I was trying two different methods and forgot to remove the TOP. :blush:
Of course I meant no disrespect 🙂 Especially to my mentor!

Heh... I didn't take it that way at all, Jared. I just wanted you to know that I wasn't in full control of my faculties when I made that boo-boo. Gettin' old, I guess. The wee hours of the morning used to be when I was at my best. 😀

--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: 1004318 More actions · Answer 2

Jeff Moden (4/5/2012)
sharky (4/5/2012)
SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 130 ms. ========== Craig's Method ====================================================== SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (1 row(s) affected) SQL Server Execution Times: CPU time = 193908 ms, elapsed time = 68966 ms. ========== Jeff's Method ======================================================= (1 row(s) affected) SQL Server Execution Times: CPU time = 172601 ms, elapsed time = 55602 ms. ========== Stan's Method ======================================================= (1 row(s) affected) SQL Server Execution Times:
CPU time = 17425 ms, elapsed time = 5899 ms.
I'm thinking that there's either something wrong with either your machine or your Tally table for the Tally table to do that badly on your box. What version/edition/sp/cu of SQL Server are you running and would you mind posting the code you used to create the Tally table? Thanks.

I ran my test harness on the server at work. (couldn't run Craig's because it's a 2k5 Enterprise box). Here are the results.

========== Jeff's Method =======================================================

SQL Server Execution Times:

CPU time = 30778 ms, elapsed time = 15417 ms.

========== Stan's Method =======================================================

SQL Server Execution Times:

CPU time = 21185 ms, elapsed time = 10525 ms.

========== Stan's Method modified by UPPER =======================================================

SQL Server Execution Times:

CPU time = 24616 ms, elapsed time = 12318 ms.

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

sharky Default port Points: 1494 More actions · Answer 3

Hi Jeff,

Thanks for the UPPER fix.

I have created the table using a clustered index with 50 rows

--

SELECT TOP 50 IDENTITY(INT,1,1) AS N

INTO dbo.Tally

FROM Master.dbo.SysColumns

ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally PRIMARY KEY CLUSTERED

On such a small in memory table, I think it should have made only a slight difference even if it was a heap?

I have run this on my very modest laptop with 2008 R2 Ent. This might explain me and Craig’s more similar results. Perhaps I can use this as a motivation for an upgrade? 🙂 What is however slightly more puzzling is why the ASCII method takes longer on your system than on mine?

Your previous point on the Tally table. I know, and I have been using a similar Numbers table in all my production systems since 4.2. I am just saying that if we want to ship a generic function using a Tally/Numbers functionality we also have to rely on the implementation of that table. As you said it is probably not such a big deal.

CapnHector SSCoach Points: 16143 More actions · Answer 4

sharky (4/6/2012)
Hi Jeff,
Thanks for the UPPER fix.
I have created the table using a clustered index with 50 rows
--
SELECT TOP 50 IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns
ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally PRIMARY KEY CLUSTERED
On such a small in memory table, I think it should have made only a slight difference even if it was a heap?
I have run this on my very modest laptop with 2008 R2 Ent. This might explain me and Craig’s more similar results. Perhaps I can use this as a motivation for an upgrade? 🙂 What is however slightly more puzzling is why the ASCII method takes longer on your system than on mine?
Your previous point on the Tally table. I know, and I have been using a similar Numbers table in all my production systems since 4.2. I am just saying that if we want to ship a generic function using a Tally/Numbers functionality we also have to rely on the implementation of that table. As you said it is probably not such a big deal.

jeff is running it on 1 million rows of GUID's. why its taking longer for his tests.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

sharky Default port Points: 1494 More actions · Answer 5

No, we should both be running the same million record table. This is just the tally table creation. The size should not be make a difference. I only added 50 rows as this is sufficient for the test.

Here is the code I used to create the 1000000 row table. I Presume we both used the same VARCHAR(36) as Craig's original test?

SELECT TOP 1000000

CONVERT(VARCHAR(36),NEWID()) AS name

INTO yourTable

FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;

sharky Default port Points: 1494 More actions · Answer 6

Don't get me wrong on being a disciple for Set based solutions!! I am all for what SQL is good at and what not.

For a relatively small Tally used in a generic function, I will implement a on the fly Table variable, but that is a personal choice and some will probably disagree.

There must be a valid reason why a medium sized numbers/tally table is not implemented as default in TEMPDB? It will do so much if it can be included in BOL to educate a set based approach..

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

capn.hector (4/6/2012)
sharky (4/6/2012)
Hi Jeff,
Thanks for the UPPER fix.
I have created the table using a clustered index with 50 rows
--
SELECT TOP 50 IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns
ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally PRIMARY KEY CLUSTERED
On such a small in memory table, I think it should have made only a slight difference even if it was a heap?
I have run this on my very modest laptop with 2008 R2 Ent. This might explain me and Craig’s more similar results. Perhaps I can use this as a motivation for an upgrade? 🙂 What is however slightly more puzzling is why the ASCII method takes longer on your system than on mine?
Your previous point on the Tally table. I know, and I have been using a similar Numbers table in all my production systems since 4.2. I am just saying that if we want to ship a generic function using a Tally/Numbers functionality we also have to rely on the implementation of that table. As you said it is probably not such a big deal.
jeff is running it on 1 million rows of GUID's. why its taking longer for his tests.

If you look at my test harness, all 3 tests are running on a million rows of GUIDs so that's not the reason.

--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: 1004318 More actions · Answer 8

sharky (4/6/2012)
Hi Jeff,
Thanks for the UPPER fix.
I have created the table using a clustered index with 50 rows
--
SELECT TOP 50 IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns
ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally PRIMARY KEY CLUSTERED
On such a small in memory table, I think it should have made only a slight difference even if it was a heap?
I have run this on my very modest laptop with 2008 R2 Ent. This might explain me and Craig’s more similar results. Perhaps I can use this as a motivation for an upgrade? 🙂 What is however slightly more puzzling is why the ASCII method takes longer on your system than on mine?
Your previous point on the Tally table. I know, and I have been using a similar Numbers table in all my production systems since 4.2. I am just saying that if we want to ship a generic function using a Tally/Numbers functionality we also have to rely on the implementation of that table. As you said it is probably not such a big deal.

That size Tally table is certainly sufficient for this job.

I'll run some additional tests tonight. I've found that SET STATISTICS will sometimes cause queries to actually run slower (and sometimes significantly so) depending on the machine it's running on. I've not experienced the same problem with SQL Profiler so I'll run the test using that, instead. If you have the chance, setup profiler for the SPID you're testing against and test for RPC Complete and SQL Batch Complete. Return Duration, CPU, Reads, Writes, and Rowcount. You might see one heck of a difference or no difference at all. It will be interesting to find out if statistics is having a problem here.

I've also found that the amount of disk cache cache plays a significant role in these things and will vary greatly from laptop to laptop.

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