SQL Server Performance Forum – Threads Archive

Summarizing data using TSQL

Hello, I had two tables [x,y]. Both tables are joined using three coulmns
The number of records will be inserted into these tables as 1:4 ratio[my app insert one record to x and 4 or more records to y]. I need to summarize the data periodically[every half an hour or every 45 minutes] and move the data to another summary table z. My question is After joinng the records from table x to y, I need to group the data based on range of the value. What I did is as follow, I created the view for joining these tables
Create view dataview
as
select x.c1, y.d1,
from x,y
where
x.a = y.a and
x.b = y.b and
x.c = y.c My summary query will be
select sumcoun1 = count(*) from dataview where
dataview.c1 is between (timx) and (timex + 30) and
dataview.d1 < 0 and dataview.d1 <= 10 I have to calculate the sumcount for different set of ranges in the column d1,(0-10, 10-20,20-30,30-40,40-50, 50-60)
My question is instead of executing the above summary query 6 times, Can I do some thing like below. DECLARE sumCursor CURSOR for
select temp = dataview.d1 from dataview
dataview.c1 is between (timx) and (timex + 30) — Execute the cursor and calcualte the value
DECLARE var1,var2 …var6 and initialize to 0
OPEN SumCursor
while @@fetchstatus = 0
if (temp > 0 and temp <=10)
Var1 = var1 +1
else if (temp > 10 and temp <=20)
Var2 = var2 +1
.
.
..
else if (temp > 50 and temp <=60)
Var6 = var6 +1
FETCH NEXT RECORD
END Use var1 … var6 to get the individual count. My question here is instead of calculated these value based on Join query, get the value and keep it in the cursor and calculate the indiv count. The reason is the table x and y increasing each day (10,000:40,000+). I need not join these tables 6 times for every half an hour. BTW, this entire T-SQL is inside the Stored Proc..
Will it help to increase my performance…?
FYI, I created proper index for these tables Thank you for your time and comments. Appreciate your help. Thanks
prabhu

---

You can probably do it without cursor. I’ll start there and we can look at the rest of your code after…. select sumcoun1 = sum(case when dataview.d1 > 0 and dataview.d1 <= 10 then 1 else 0 end),
sumcoun2 = sum(case when dataview.d1 > 10 and dataview.d1 <= 20 then 1 else 0 end),
–etc
from dataview where
dataview.c1 is between (timx) and (timex + 30) This would get all your counts in a single select statement. I’ll need to look at the rest later.

---

---

If dataview.d1 is an integer, you could summarize to a temp table then summarize again to your final table. This would be more likely to work in other databases (if that’s a concern). select dataview.d1 as d1, count(*) as sumcoun1
into summ_temp
from dataview
where dataview.c1 is between (timx) and (timex + 30)
group by databiew.d1 Now execute your 6 queries on the much, much smaller summ_temp table select sum(sumcoun1)
from summ_temp
where d1 > 10
and d1 <= 20

---

]]>