Tally Table

Question

Post reply

Tally Table

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

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

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

I don't think DBCC PINTABLE is available in SQL Server 2005, so that approach is a dead end.

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

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Does not really matter.

If table is heavily used it will be sitting in memory anyway because of its small size.

If it's used occasionally those microseconds needed to read extra extent will not make any difference.

_____________
Code for TallyGenerator

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

Sergiy (5/6/2008)
Matt Miller (5/6/2008) I just didn't think Sergiy knuckled under to office politics...:hehe::w00t:
Well, I'm probably lucky, but I'm not THAT lucky.
I'm not sure there is any scientific proof that such luck exists. :hehe:
Regarding number of pages to be read, let's do some calculations.
4 bytes(int) + 2 (nchar) + 4 (smalldatetime) + 1 + 1 + 1 + ...(tinyint for all those "days of week) = less than 20 bytes.
65K * 20 = 1.3 M
Plus size of indexes - you'll get 3 or 4 Meg in total.
How much memory you've got on you oldest and smallest server?;)
If you chasing for performance just do PINTABLE and you have already read all pages. Once and forever.

Oh - I get how small a cost this was, and a fair amount of what can be done with this in play. I was just curious if you had a trick up your sleeve with having it all in one table I wasn't seeing.

The big piece I see now with that approach is that one call for any of the utility lists (numbers, charactersm dates) would essentially cache all of the lists....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

karthik M One Orange Chip Points: 29139 More actions · Answer 4

is it possible to create Tally table starting with 'Zero' ?

karthik

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

karthikeyan (5/9/2008)
is it possible to create Tally table starting with 'Zero' ?

Ummm... you read the article... of course there is...

--===== Create and populate the Tally table on the fly

SELECT TOP 11000 --equates to more than 30 years of dates

IDENTITY(INT,0,1) AS N --Changed "start value" from 1 to 0

INTO dbo.Tally

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance

ALTER TABLE dbo.Tally

ADD CONSTRAINT PK_Tally_N

PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it

GRANT SELECT ON dbo.Tally TO PUBLIC

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

karthik M One Orange Chip Points: 29139 More actions · Answer 6

karthik M

One Orange Chip

Points: 29139

May 9, 2008 at 3:03 am

#812888

Jeff,

Thanks a lot.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 7

UPDATE #PfManager

SET monthEnd = ( select MAX(dt)

FROM PMag A

WHERE #PfManager.SP = A.SP

AND A.sno IN ( SELECT MID FROM Mgr WHERE SP = #PfManager.SP)

AND A.Type <>'T' Group BY SP)

where #PfManager.Flag ='P'

whether it comes under Set Based or RBAR or Hidden RBAR. I hope it comes under Hidden RBAR.

if yes, How to overcome Hidden RBAR in this kind of situation ?

---------------------------------------------------------------------------------------

when I read about (Traingular Join)Hidden RBAR, I got confused.

= or "Equality" Triangular Join

-----------------------------------------------------------------------------------------

SELECT MID,Status,SP,DateStart ,DateEnd ,CheckDate = DATEADD(dd,-(DATEPART(dd, DATEADD(mm,N,DateStart))), DATEADD(mm,N,DateStart))

INTO #FinalResult

FROM #Result,Tally

WHERE N <= DATEDIFF(MONTH,DateStart,DATEADD(MM,1,DateEnd))

whether it comes under Set Based or RBAR or Hidden RBAR.I hope it also comes under Hidden RBAR.

if yes, How to overcome Hidden RBAR in this kind of situation ?

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 8

Note: The above code has used cursor earlier,I have replaced it by using 'Iteration' or 'Tally' table.

Time taken before (cursor method) : 20 Minutes

Time taken after(After replacing Cursor with TALLY table) : 10 Minutes

Just i am thinking suppose assume the above code comes under Hidden RBAR, may be if i avoid Hidden RBAR there may be a chance to decrease the execution time.

I am not sure.

Suggestions are welcome !

karthik

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

karthikeyan (5/12/2008)
UPDATE #PfManager
SET monthEnd = ( select MAX(dt)
FROM PMag A
WHERE #PfManager.SP = A.SP
AND A.sno IN ( SELECT MID FROM Mgr WHERE SP = #PfManager.SP)
AND A.Type <>'T' Group BY SP)
where #PfManager.Flag ='P'

UPDATEq

SETq.MonthEnd = q.dt

FROM(

SELECTMonthEnd,

MAX(dt) OVER (PARTITION BY e.SP) AS dt

FROM#PfManager AS e

INNER JOINMgr AS r ON r.SP = e.SP

INNER JOINPmag AS j ON j.SP = e.SP

WHEREj.sno = r.MID

AND j.Type <> 'T'

AND e.Flag = 'P'

) AS q

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

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

karthikeyan (5/12/2008)
SELECT MID,Status,SP,DateStart ,DateEnd ,CheckDate = DATEADD(dd,-(DATEPART(dd, DATEADD(mm,N,DateStart))), DATEADD(mm,N,DateStart))
INTO #FinalResult
FROM #Result,Tally
WHERE N <= DATEDIFF(MONTH,DateStart,DATEADD(MM,1,DateEnd))

Use DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231')

to get last day of previous month. easier to read and quicker to execute.

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

karthik M One Orange Chip Points: 29139 More actions · Answer 11

karthik M

One Orange Chip

Points: 29139

May 12, 2008 at 8:52 am

#813867

Any Inputs ?

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 12

Can anybody list out some important uses ( Apart from Splitting the string,Date generation) of Tally table ?

karthik

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

Find bad characters in a string

Remove Digits from Strings

Remove Alpha from "numbers"

Change to Title Case

Histogram of characters in a column

Generate test data (not just dates)

Find gaps in serial numbers or IDs (although, shouldn't care about gaps in IDENTITY)

Generate time slices and shift information

Generate IP information

Use date generation to find missing days or give SUMs of 0 for missing days ir times

Split 1, 2, and 3 dimensional parameters (pass tables and arrays as parameters)

Generate "duplicate" rows based on a qty

Provide standard predictable "seed" for RAND() for testing

Just about anything having to do with strings at a character level or anything requiring a sequence in a single query.

Lot's of Tally table uses in SQL Server 2005 can be replaced with a particular type of high speed CTE... although I still like the Tally table for readable 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)

karthik M One Orange Chip Points: 29139 More actions · Answer 14

Jeff,

vow...Plenty of uses...

Could you please give me examples for all those uses ? I am eagerly waiting for your reply.

karthik

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 15

Karthik -

With all due respect - this might be a great opportunity to wade on in, roll the sleeves up, and see how many of these you can take on. Having the solution handed to you won't help you retain/understand the usage. This is one of those times where someone handing you the solution makes one go "that's cute", and MAYBE you remember it; on the other hand - taking it on means you will actually remember it.

There are examples of a bunch of these scattered all over SSC. I could swear I've seen a dozen threads on various Tally uses this week.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?