Numbers table and a join

Question

Numbers table and a join

trans54

Ten Centuries

Points: 1357
More actions
March 20, 2009 at 1:38 pm

#136024

Hi all,
I have this table:
CREATE TABLE [dbo].[Merchant](
[MerchantID] [int] IDENTITY(1,1) NOT NULL,
[Cost] [decimal](5, 2) NULL,
[Purge] [bit] NOT NULL CONSTRAINT [PK_Merchant] PRIMARY KEY CLUSTERED
(
[MerchantID] ASC
)
and i have a Numbers table with 8000 rows from 0 to 8000 and clustered index defined on the Counter column in this table.
The Merchant table has around 15000 rows.
For each MerchantID i need to join to the Numbers table based on the number passed in the variable so each merchantid will have 90 rows in this case.
For example:
declare @Counter int
set @Counter =90
select * FROM dbo.Merchant (NOLOCK)
INNER LOOP JOIN dbo.Numbers WITH (NOLOCK)
ON id BETWEEN 0 AND ISNULL(@Counter,0)
The final result should be like this
MerchantidCostPurgeCounter
10.0100
10.0211
10.0102
...
20.0100
20.0211
20.0102
...
The reason why i am doing this is because i will need these records later.
So my question is is there a better way to do this in sql 2005? i also did a cross apply and performance is the same as linner loop join.
When i add io statistics and pass high number to the variable i see that CPU time is high compared when i pass only 0 or 1 to the variable.
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

That's pretty much the most efficient way to do that.

Cross apply will do the same thing as inner join will, in this case, because you aren't passing any values from the first table to the thing you're joining to.

As a side note: I'd take extra care before using all those hints. Why force the server that way? Also, you should be aware that NOLOCK can result in dirty reads, which means it can result in wrong results. Be careful of that. If you've already considered all those things and implemented this query with those possible problems taken into account, good, keep it as it is. But if you are just adding them because someone told you to always add them, do some research and check it out, they can be a very, very bad thing.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

Also, there is no need for the highlighted section of the following line of code...

ON id BETWEEN 0 AND [highlight]ISNULL([/highlight]@Counter[highlight],0)[/highlight]

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