July 30, 2008 at 3:25 am
Hi All,
I hope all of you know about 'dbo.Tally' table. If not , please visit the following URL.
http://www.sqlservercentral.com/articles/TSQL/62867/">
http://www.sqlservercentral.com/articles/TSQL/62867/
Coming back to my research, I have executed the below script.
DECLARE @customers TABLE(AccountNum INT, DM_Source_Id INT, Address VARCHAR(10))
DECLARE @i INT,
@j-2 INT
SET @i = 1
SET @j-2 = 1
WHILE @i <= 10
BEGIN
WHILE @j-2 <= 10
BEGIN
IF @i = 1
BEGIN
INSERT INTO @customers
SELECT
@i,
@j-2,
END
ELSE
BEGIN
IF @j-2 % 3 = 0
BEGIN
INSERT INTO @customers
SELECT
@i,
@j-2,
END
ELSE
BEGIN
INSERT INTO @customers
SELECT
@i,
@j-2,
@i
END
END
END
SET @i = @i + 1
SET @j-2 = 1
END
The above code took 2 hour 10 minutes for inserting 1,000,000 records. But our actual requirement is to insert 11,000,000 records. So just imagine the execution time.
so what is the solution to this problem ?
Obviously 'Tally' table. I hope your people also agree.
My code:
-------------------------------------------------
select a.N as Number ,b.N as RunningNumber
into #t
from Tally a , Tally b
where b.N <= 1000
--------------------------------------------------
It took just 4 minutes !
------------------------ Execute ------------------------
(11000000 rows affected)
------------------------- Done --------------------------
But i may be wrong, so it would be appreciable if anybody share their experience and suggestions.
karthik
July 30, 2008 at 3:59 am
karthikeyan (7/30/2008)
Coming back to my research, I have executed the below script..
.
.
The above code took 2 hour 10 minutes for inserting 1,000,000 records.
It takes less than a millisecond on our server.
Perhaps performing an implicit convert to varchar affects your server particularly badly.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 30, 2008 at 4:37 am
Hi ,
Firstly yes the first query actaully also runs in ms on my server.
so I increased the volume to 10000
This took around 3.5secs
Where as the below code took a 1sec or just over.
INSERT INTO @customers2
(AccountNum,DM_Source_Id)
select
b.N as AccountNum
,a.N as DM_Source_Id
from (SELECT TOP 100 n FROM Tally) a , (SELECT TOP 100 n FROM Tally) b
UPDATE @customers2
SET Address = CASE WHEN (DM_Source_Id % 3 = 0) OR (AccountNum = 1) THEN DM_Source_Id ELSE AccountNum END
SELECT * FROM @customers2
Hope that helps
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 30, 2008 at 4:45 am
That sounds about right, Chris.
I've also increased i and j to 1000 to generate a 1 mill row table and that took 46 seconds - but first I changed the address column to INT ('cos that's what was being INSERTed into it)
The tally table version, as you show, is not only way easier to understand but it's quite a bit quicker too.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2008 at 1:35 am
which one will provide good performance ?
My code :
insert into @customers2
select a.N as AccountNumber ,b.N as DM_Source_ID,b.N as Address
from Tally a , Tally b
where b.N <= 1000
Christopher's Code:
INSERT INTO @customers2
(AccountNum,DM_Source_Id)
select
b.N as AccountNum
,a.N as DM_Source_Id
from (SELECT TOP 100 n FROM Tally) a , (SELECT TOP 100 n FROM Tally) b
Because i haven't seen any difference, thats why i am asking ?
karthik
July 31, 2008 at 1:39 am
Test them, Karthik!
The main difference that I can see is that Chris's version will always give 10,000 rows. The number of rows your version will give, will depend on how many rows you have in your tally table.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2008 at 1:52 am
I have executed the below code and note down the result.
Query:
---------------------------------------------------------------------------
select
a.N as AccountNum
,b.N as DM_Source_Id
from (SELECT TOP 100 N FROM Tally) a , (SELECT TOP 100 N FROM Tally) b
---------------------------------------------------------------------------
Result:
----------------
11
12
13
14
15
16
17
18
19
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
1100
But the requirement is,
1 1
1 2
1 3
.
.
.
1 1000
2 1
2 2
2 3
.
.
.
2 1000
.
.
.
.
1000 1
1000 2
.
.
.
.
1000 1000
My code gave the expected result.
I am not offending anybody. Just i wanted to know the correct query.
karthik
July 31, 2008 at 2:05 am
I see your point Karthik.
Chris's code generates repeats of 100, yours generates repeats of 1000 (multipled by the row count in your tally table). Do you think it's possible to modify Chris's code to generate repeats of 1000, resulting in a million row table?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2008 at 2:08 am
I think if we change his code from
select
a.N as AccountNum
,b.N as DM_Source_Id
from (SELECT TOP 100 N FROM Tally) a , (SELECT TOP 100 N FROM Tally) b
to
select
a.N as AccountNum
,b.N as DM_Source_Id
from Tally a , Tally b
where b.N <= 1000
should work.
karthik
July 31, 2008 at 2:13 am
karthikeyan (7/31/2008)
I think if we change his code fromselect
a.N as AccountNum
,b.N as DM_Source_Id
from (SELECT TOP 100 N FROM Tally) a , (SELECT TOP 100 N FROM Tally) b
to
select
a.N as AccountNum
,b.N as DM_Source_Id
from Tally a , Tally b
where b.N <= 1000
should work.
How many rows would your output have if the tally table had 100,000 rows - using your version and Chris's version modified as follows?
select
a.N as AccountNum
,b.N as DM_Source_Id
from (SELECT TOP 1000 N FROM Tally) a , (SELECT TOP 1000 N FROM Tally) b
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2008 at 2:19 am
Umm...I forgot to include the below line
and a.N <= 1000
karthik
July 31, 2008 at 2:19 am
😎
karthik
July 31, 2008 at 3:33 am
Hi Karthik,
The main difference between your query and mine, other than the fact that mine seems run much faster is the following.
I can control exactly how I want data split for example.
select
a.N as AccountNum
,b.N as DM_Source_Id
from (SELECT TOP 10 N FROM Tally) a , (SELECT TOP 100 N FROM Tally) b
If you want more of 1 grouping then simply change the top statements so this will return:(1000rows)
1 1
1 ...
1 100
... ...
10 1
10 ...
10 100
This will return:(also a 1000Rows)
select
a.N as AccountNum
,b.N as DM_Source_Id
from (SELECT TOP 100 N FROM Tally) a , (SELECT TOP 10 N FROM Tally) b
1 1
1 ...
1 10
... ...
100 1
100 ...
100 100.
Can you see the difference?
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 31, 2008 at 3:43 am
Chris, Karthik's corrected code has the same functionality but was it by design? 😛
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2008 at 4:10 am
Christopher,
SELECT
a.N AS AccountNum
,b.N AS DM_Source_Id
FROM (SELECT TOP 10 N FROM Tally) a , (SELECT TOP 10 N FROM Tally) b
can you post the output for this query ?
karthik
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply