March 20, 2009 at 1:38 pm
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
March 20, 2009 at 2:20 pm
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
March 20, 2009 at 5:54 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply