April 22, 2008 at 8:01 am
The first part finds all of the records that we want to aggregate and joins them to the previous record and next record. Having all three records in a row allows us to build the weighted averages that will become the new record.
The second part joins the orignal data to a subquery of the records we are going to aggregate and excludes them giving us the records we are not aggregating.
we union those together so we can get all of the records. I'm really bad about commenting my code while I'm working on it but once I get it put together I will go back and comment.
April 22, 2008 at 8:28 am
April 22, 2008 at 8:33 am
I had gotten this far;
SELECT
CUR.HoleId
,CUR.EvalNum
,PREV.TopDepth
,NEX.BaseDepth
,CAST((((PREV.MBit * PREV.Length) + (CUR.MBit * CUR.Length) + (NEX.MBit * NEX.Length)) / (PREV.Length + CUR.Length + NEX.Length)) AS NUMERIC(3,2)) AS MBit
,CAST((((PREV.PHIE * PREV.Length) + (CUR.PHIE * CUR.Length) + (NEX.PHIE * NEX.Length)) / (PREV.Length + CUR.Length + NEX.Length)) AS NUMERIC(3,2)) AS PHIE
,CAST((((PREV.SwE * PREV.Length) + (CUR.SwE * CUR.Length) + (NEX.SwE * NEX.Length)) / (PREV.Length + CUR.Length + NEX.Length)) AS NUMERIC(3,2)) AS SwE
,CAST((((PREV.Vsh * PREV.Length) + (CUR.Vsh * CUR.Length) + (NEX.Vsh * NEX.Length)) / (PREV.Length + CUR.Length + NEX.Length)) AS NUMERIC(3,2)) AS Vsh
,PREV.Length + CUR.Length + NEX.Length AS Length
,'Y' AS OreFlag
FROM ##Aggregated_Intervals CUR
LEFT JOIN ##Aggregated_Intervals PREV
ON PREV.HoleId = CUR.HoleId
AND PREV.EvalNum = CUR.EvalNum
AND PREV.Seq = CUR.Seq - 1
LEFT JOIN ##Aggregated_Intervals NEX
ON NEX.HoleId = CUR.HoleId
AND NEX.EvalNum = CUR.EvalNum
AND NEX.Seq = CUR.Seq + 1
WHERE
CUR.OreFlag = 'N'
AND CUR.LENGTH <= 1
AND CUR.LENGTH <= PREV.LENGTH
AND CUR.LENGTH <= NEX.LENGTH
AND (((PREV.MBit * PREV.Length) + (CUR.MBit * CUR.Length) + (NEX.MBit * NEX.Length)) / (PREV.Length + CUR.Length + NEX.Length)) >= 0.06
One of the things that I discovered was that there may be situations where consecutive "sets of three" records may pass the conditions. In this situation they would share a common "Y" record which would create overlapping intervals and throw off the correct calculation of thickness/length weighted averages. I can't presently think of a solution for this.
April 22, 2008 at 8:34 am
Lol!
We've tumbled to the same problem at almost the same instant!
April 22, 2008 at 9:04 am
Here is a fix but it will add iterations since we will work with the first set from each wellnum at a time.
select holeid,evalnum,basedepth,topdepth,mbit,phie,swe,vsh,length,oreflag from (
select
a.holeid,a.evalnum,c.seq
/*a.seq,a.topdepth,a.basedepth,a.mbit,a.phie,a.swe,a.vsh,a.length,a.oreflag,b.seq prec,c.seq nrec,a.mbit*a.length wambit,a.phie*a.length waphie,a.vsh*a.length wavsh,a.swe*a.length waswe*/
,case when case when a.basedepth > b.basedepth then a.basedepth else b.basedepth end > c.basedepth then case when a.basedepth > b.basedepth then a.basedepth else b.basedepth end else c.basedepth end basedepth
,case when case when a.topdepth < b.topdepth then a.topdepth else b.topdepth end < c.topdepth then case when a.topdepth < b.topdepth then a.topdepth else b.topdepth end else c.topdepth end topdepth
,((a.mbit*a.length) +(b.mbit*b.length) + (c.mbit*c.length)) /(a.length+b.length + c.length) mbit
,((a.phie*a.length) +(b.phie*b.length) + (c.phie*c.length)) /(a.length+b.length + c.length) phie
,((a.swe*a.length) +(b.swe*b.length) + (c.swe*c.length)) /(a.length+b.length + c.length) swe
,((a.vsh*a.length) +(b.vsh*b.length) + (c.vsh*c.length)) /(a.length+b.length + c.length) vsh
, (a.length+b.length + c.length) length
,case when ((a.mbit*a.length) +(b.mbit*b.length) + (c.mbit*c.length)) /(a.length+b.length + c.length) > .06 then 'Y' else 'N' end oreflag
from wells a
join wells b on a.evalnum = b.evalnum and a.holeid=b.holeid and a.seq = b.seq -1 and b.oreflag = 'y'
join wells c on a.evalnum = c.evalnum and a.holeid=c.holeid and a.seq = c.seq +1 and c.oreflag = 'y'
join -- only get first set to be aggregated
(select a.holeid,a.evalnum,min(a.seq) seq from wells a
join wells b on a.evalnum = b.evalnum and a.holeid=b.holeid and a.seq = b.seq -1 and b.oreflag = 'y'
join wells c on a.evalnum = c.evalnum and a.holeid=c.holeid and a.seq = c.seq +1 and c.oreflag = 'y'
where a.oreflag = 'n' and a.length < 1 and a.length < b.length and a.length < c.length
group by a.holeid,a.evalnum) fs on fs.seq = a.seq
where a.oreflag = 'n' and a.length < 1 and a.length < b.length and a.length < c.length
union
select
holeid,evalnum,wells.seq,basedepth,topdepth,mbit,phie,swe,vsh,length,oreflag
from wells
left join
(select a.seq seq1,b.seq pseq,c.seq nseq
from wells a
join wells b on a.evalnum = b.evalnum and a.holeid=b.holeid and a.seq = b.seq -1 and b.oreflag = 'y'
join wells c on a.evalnum = c.evalnum and a.holeid=c.holeid and a.seq = c.seq +1 and c.oreflag = 'y'
join-- only get first set to be aggregated
(select a.holeid,a.evalnum,min(a.seq) seq from wells a
join wells b on a.evalnum = b.evalnum and a.holeid=b.holeid and a.seq = b.seq -1 and b.oreflag = 'y'
join wells c on a.evalnum = c.evalnum and a.holeid=c.holeid and a.seq = c.seq +1 and c.oreflag = 'y'
where a.oreflag = 'n' and a.length < 1 and a.length < b.length and a.length < c.length
group by a.holeid,a.evalnum) fs on fs.seq = a.seq
where a.oreflag = 'n' and a.length < 1and a.length < b.length and a.length < c.length
) aggs on wells.seq = aggs.seq1 or wells.seq = aggs.pseq or wells.seq = aggs.nseq
where aggs.seq1 is null
) n
order by seq asc
April 22, 2008 at 9:18 am
Ok, so I ended up with building 3 identical tables.
wells holds the initial data you provided
wellsa gets the first aggregate set
wellsb gets the aggregated set from wellsa
I bounce back and forth from wellsa to wellsb until I ended up with a matching number of rows between the 2 which was 895 from the test data.
--takes data from wells and aggregates into wellsa
truncate table wellsa
insert into wellsa (holeid,evalnum,basedepth,topdepth,mbit,phie,swe,vsh,length,oreflag)
select holeid,evalnum,basedepth,topdepth,mbit,phie,swe,vsh,length,oreflag from (
select
a.holeid,a.evalnum,c.seq
/*a.seq,a.topdepth,a.basedepth,a.mbit,a.phie,a.swe,a.vsh,a.length,a.oreflag,b.seq prec,c.seq nrec,a.mbit*a.length wambit,a.phie*a.length waphie,a.vsh*a.length wavsh,a.swe*a.length waswe*/
,case when case when a.basedepth > b.basedepth then a.basedepth else b.basedepth end > c.basedepth then case when a.basedepth > b.basedepth then a.basedepth else b.basedepth end else c.basedepth end basedepth
,case when case when a.topdepth < b.topdepth then a.topdepth else b.topdepth end < c.topdepth then case when a.topdepth < b.topdepth then a.topdepth else b.topdepth end else c.topdepth end topdepth
,((a.mbit*a.length) +(b.mbit*b.length) + (c.mbit*c.length)) /(a.length+b.length + c.length) mbit
,((a.phie*a.length) +(b.phie*b.length) + (c.phie*c.length)) /(a.length+b.length + c.length) phie
,((a.swe*a.length) +(b.swe*b.length) + (c.swe*c.length)) /(a.length+b.length + c.length) swe
,((a.vsh*a.length) +(b.vsh*b.length) + (c.vsh*c.length)) /(a.length+b.length + c.length) vsh
, (a.length+b.length + c.length) length
,case when ((a.mbit*a.length) +(b.mbit*b.length) + (c.mbit*c.length)) /(a.length+b.length + c.length) > .06 then 'Y' else 'N' end oreflag
from wells a
join wells b on a.evalnum = b.evalnum and a.holeid=b.holeid and a.seq = b.seq -1 and b.oreflag = 'y'
join wells c on a.evalnum = c.evalnum and a.holeid=c.holeid and a.seq = c.seq +1 and c.oreflag = 'y'
join
(select a.holeid,a.evalnum,min(a.seq) seq from wells a
join wells b on a.evalnum = b.evalnum and a.holeid=b.holeid and a.seq = b.seq -1 and b.oreflag = 'y'
join wells c on a.evalnum = c.evalnum and a.holeid=c.holeid and a.seq = c.seq +1 and c.oreflag = 'y'
where a.oreflag = 'n' and a.length < 1 and a.length < b.length and a.length < c.length
group by a.holeid,a.evalnum) fs on fs.seq = a.seq
where a.oreflag = 'n' and a.length < 1 and a.length < b.length and a.length < c.length
union
select
holeid,evalnum,wells.seq,basedepth,topdepth,mbit,phie,swe,vsh,length,oreflag
from wells
left join
(select a.seq seq1,b.seq pseq,c.seq nseq
from wells a
join wells b on a.evalnum = b.evalnum and a.holeid=b.holeid and a.seq = b.seq -1 and b.oreflag = 'y'
join wells c on a.evalnum = c.evalnum and a.holeid=c.holeid and a.seq = c.seq +1 and c.oreflag = 'y'
join
(select a.holeid,a.evalnum,min(a.seq) seq from wells a
join wells b on a.evalnum = b.evalnum and a.holeid=b.holeid and a.seq = b.seq -1 and b.oreflag = 'y'
join wells c on a.evalnum = c.evalnum and a.holeid=c.holeid and a.seq = c.seq +1 and c.oreflag = 'y'
where a.oreflag = 'n' and a.length < 1 and a.length < b.length and a.length < c.length
group by a.holeid,a.evalnum) fs on fs.seq = a.seq
where a.oreflag = 'n' and a.length < 1and a.length < b.length and a.length < c.length
) aggs on wells.seq = aggs.seq1 or wells.seq = aggs.pseq or wells.seq = aggs.nseq
where aggs.seq1 is null
) n
order by seq asc
--takes data from wellsa and aggregates into wellsb
truncate table wellsb
insert into wellsb (holeid,evalnum,basedepth,topdepth,mbit,phie,swe,vsh,length,oreflag)
select holeid,evalnum,basedepth,topdepth,mbit,phie,swe,vsh,length,oreflag from (
select
a.holeid,a.evalnum,c.seq
/*a.seq,a.topdepth,a.basedepth,a.mbit,a.phie,a.swe,a.vsh,a.length,a.oreflag,b.seq prec,c.seq nrec,a.mbit*a.length wambit,a.phie*a.length waphie,a.vsh*a.length wavsh,a.swe*a.length waswe*/
,case when case when a.basedepth > b.basedepth then a.basedepth else b.basedepth end > c.basedepth then case when a.basedepth > b.basedepth then a.basedepth else b.basedepth end else c.basedepth end basedepth
,case when case when a.topdepth < b.topdepth then a.topdepth else b.topdepth end < c.topdepth then case when a.topdepth < b.topdepth then a.topdepth else b.topdepth end else c.topdepth end topdepth
,((a.mbit*a.length) +(b.mbit*b.length) + (c.mbit*c.length)) /(a.length+b.length + c.length) mbit
,((a.phie*a.length) +(b.phie*b.length) + (c.phie*c.length)) /(a.length+b.length + c.length) phie
,((a.swe*a.length) +(b.swe*b.length) + (c.swe*c.length)) /(a.length+b.length + c.length) swe
,((a.vsh*a.length) +(b.vsh*b.length) + (c.vsh*c.length)) /(a.length+b.length + c.length) vsh
, (a.length+b.length + c.length) length
,case when ((a.mbit*a.length) +(b.mbit*b.length) + (c.mbit*c.length)) /(a.length+b.length + c.length) > .06 then 'Y' else 'N' end oreflag
from wellsa a
join wellsa b on a.evalnum = b.evalnum and a.holeid=b.holeid and a.seq = b.seq -1 and b.oreflag = 'y'
join wellsa c on a.evalnum = c.evalnum and a.holeid=c.holeid and a.seq = c.seq +1 and c.oreflag = 'y'
join
(select a.holeid,a.evalnum,min(a.seq) seq from wellsa a
join wellsa b on a.evalnum = b.evalnum and a.holeid=b.holeid and a.seq = b.seq -1 and b.oreflag = 'y'
join wellsa c on a.evalnum = c.evalnum and a.holeid=c.holeid and a.seq = c.seq +1 and c.oreflag = 'y'
where a.oreflag = 'n' and a.length < 1 and a.length < b.length and a.length < c.length
group by a.holeid,a.evalnum) fs on fs.seq = a.seq
where a.oreflag = 'n' and a.length < 1 and a.length < b.length and a.length < c.length
union
select
holeid,evalnum,wellsa.seq,basedepth,topdepth,mbit,phie,swe,vsh,length,oreflag
from wellsa
left join
(select a.seq seq1,b.seq pseq,c.seq nseq
from wellsa a
join wellsa b on a.evalnum = b.evalnum and a.holeid=b.holeid and a.seq = b.seq -1 and b.oreflag = 'y'
join wellsa c on a.evalnum = c.evalnum and a.holeid=c.holeid and a.seq = c.seq +1 and c.oreflag = 'y'
join
(select a.holeid,a.evalnum,min(a.seq) seq from wellsa a
join wellsa b on a.evalnum = b.evalnum and a.holeid=b.holeid and a.seq = b.seq -1 and b.oreflag = 'y'
join wellsa c on a.evalnum = c.evalnum and a.holeid=c.holeid and a.seq = c.seq +1 and c.oreflag = 'y'
where a.oreflag = 'n' and a.length < 1 and a.length < b.length and a.length < c.length
group by a.holeid,a.evalnum) fs on fs.seq = a.seq
where a.oreflag = 'n' and a.length < 1and a.length < b.length and a.length < c.length
) aggs on wellsa.seq = aggs.seq1 or wellsa.seq = aggs.pseq or wellsa.seq = aggs.nseq
where aggs.seq1 is null
) n
order by seq asc
--takes data from wellsb and aggregates into wellsa
truncate table wellsa
insert into wellsa (holeid,evalnum,basedepth,topdepth,mbit,phie,swe,vsh,length,oreflag)
select holeid,evalnum,basedepth,topdepth,mbit,phie,swe,vsh,length,oreflag from (
select
a.holeid,a.evalnum,c.seq
/*a.seq,a.topdepth,a.basedepth,a.mbit,a.phie,a.swe,a.vsh,a.length,a.oreflag,b.seq prec,c.seq nrec,a.mbit*a.length wambit,a.phie*a.length waphie,a.vsh*a.length wavsh,a.swe*a.length waswe*/
,case when case when a.basedepth > b.basedepth then a.basedepth else b.basedepth end > c.basedepth then case when a.basedepth > b.basedepth then a.basedepth else b.basedepth end else c.basedepth end basedepth
,case when case when a.topdepth < b.topdepth then a.topdepth else b.topdepth end < c.topdepth then case when a.topdepth < b.topdepth then a.topdepth else b.topdepth end else c.topdepth end topdepth
,((a.mbit*a.length) +(b.mbit*b.length) + (c.mbit*c.length)) /(a.length+b.length + c.length) mbit
,((a.phie*a.length) +(b.phie*b.length) + (c.phie*c.length)) /(a.length+b.length + c.length) phie
,((a.swe*a.length) +(b.swe*b.length) + (c.swe*c.length)) /(a.length+b.length + c.length) swe
,((a.vsh*a.length) +(b.vsh*b.length) + (c.vsh*c.length)) /(a.length+b.length + c.length) vsh
, (a.length+b.length + c.length) length
,case when ((a.mbit*a.length) +(b.mbit*b.length) + (c.mbit*c.length)) /(a.length+b.length + c.length) > .06 then 'Y' else 'N' end oreflag
from wellsb a
join wellsb b on a.evalnum = b.evalnum and a.holeid=b.holeid and a.seq = b.seq -1 and b.oreflag = 'y'
join wellsb c on a.evalnum = c.evalnum and a.holeid=c.holeid and a.seq = c.seq +1 and c.oreflag = 'y'
join
(select a.holeid,a.evalnum,min(a.seq) seq from wellsb a
join wellsb b on a.evalnum = b.evalnum and a.holeid=b.holeid and a.seq = b.seq -1 and b.oreflag = 'y'
join wellsb c on a.evalnum = c.evalnum and a.holeid=c.holeid and a.seq = c.seq +1 and c.oreflag = 'y'
where a.oreflag = 'n' and a.length < 1 and a.length < b.length and a.length < c.length
group by a.holeid,a.evalnum) fs on fs.seq = a.seq
where a.oreflag = 'n' and a.length < 1 and a.length < b.length and a.length < c.length
union
select
holeid,evalnum,wellsb.seq,basedepth,topdepth,mbit,phie,swe,vsh,length,oreflag
from wellsb
left join
(select a.seq seq1,b.seq pseq,c.seq nseq
from wellsb a
join wellsb b on a.evalnum = b.evalnum and a.holeid=b.holeid and a.seq = b.seq -1 and b.oreflag = 'y'
join wellsb c on a.evalnum = c.evalnum and a.holeid=c.holeid and a.seq = c.seq +1 and c.oreflag = 'y'
join
(select a.holeid,a.evalnum,min(a.seq) seq from wellsb a
join wellsb b on a.evalnum = b.evalnum and a.holeid=b.holeid and a.seq = b.seq -1 and b.oreflag = 'y'
join wellsb c on a.evalnum = c.evalnum and a.holeid=c.holeid and a.seq = c.seq +1 and c.oreflag = 'y'
where a.oreflag = 'n' and a.length < 1 and a.length < b.length and a.length < c.length
group by a.holeid,a.evalnum) fs on fs.seq = a.seq
where a.oreflag = 'n' and a.length < 1and a.length < b.length and a.length < c.length
) aggs on wellsb.seq = aggs.seq1 or wellsb.seq = aggs.pseq or wellsb.seq = aggs.nseq
where aggs.seq1 is null
) n
order by seq asc
I think we can convert these into stored procs and make them loop until the record count matches but first I want to load your dull data and see how long it takes.
April 22, 2008 at 9:32 am
April 22, 2008 at 9:34 am
Yes. in this instance null is effectively zero.
April 22, 2008 at 10:32 am
April 22, 2008 at 11:46 am
I'm not sure what I did but I think I managed to upload a version of the csv which I'd looked at in Excel and then saved.
I'm having trouble uploading the file (again). I think it has something to do with the size. It's 7,807kb zipped. There's 366,590 rows! It looks like it's uploading (for about 2-3 minutes) and then it gives me the "page can not be displayed" screen.
My first round of aggregation takes me from 9,401,166 rows down to 366,590 in about 17 minutes.
I think that if it's taking 10 minutes to process 65k rows that maybe this solution isn't going to work for as much data as I have.
Maybe this is one of those rare cases where a cursor based approach will outperform a set based approach.
April 22, 2008 at 11:59 am
It's weird because the file I have is 7MB but only the 65K rows and I can't open it in a text editor because it is unicode.
10 minutes is on my little SQL7 server with 512MB of Memory and 2 300mhz processors. Might also need to look at the indexes as I am getting a few seeks but mostly scans. I'm suprised no one else has jumped in on this topic as there are a number of people better at this than I am that may come up with something different.
10 minutes was also the first run at it, the second pass was 7, and I''m running a full loop now.
April 22, 2008 at 12:07 pm
The upload thing is kind of vexing. I figured out how to get around the unicode thing (Management studio just assumes everyone want's to export everything in unicode and makes it unintuitive to change the coding scheme) - in unicode, the entire file, unzipped was 56Mb! In ASCII (I'm presuming) it's down to 28Mb and then zipping it takes it down to 7+Mb but I'm still having problems getting it uploaded.
I'm going to try to cut the file in two and see if that will work.
Just want to say again how much I appreciate your efforts on this!
April 22, 2008 at 12:29 pm
The whole thing took 29 passes over 55 minutes for the 65K rows. I'll start looking at the performance peice of it. The first 2 passes took a while but the rest were only a minute or 2. The problem is that we are working small sets each time which makes this act like a cursor or RBAR.
Iteration 1 : Apr 22 2008 1:26PM
(60917 row(s) affected)
wellsa rowcount = 60917
wellsb rowcount = 57989
Iteration 2 : Apr 22 2008 1:37PM
(57989 row(s) affected)
Iteration 3 : Apr 22 2008 1:44PM
(56083 row(s) affected)
wellsa rowcount = 56083
wellsb rowcount = 57989
Iteration 4 : Apr 22 2008 1:48PM
(54785 row(s) affected)
Iteration 5 : Apr 22 2008 1:51PM
(53811 row(s) affected)
wellsa rowcount = 53811
wellsb rowcount = 54785
Iteration 6 : Apr 22 2008 1:53PM
(53053 row(s) affected)
Iteration 7 : Apr 22 2008 1:55PM
(52429 row(s) affected)
wellsa rowcount = 52429
wellsb rowcount = 53053
Iteration 8 : Apr 22 2008 1:57PM
(51935 row(s) affected)
Iteration 9 : Apr 22 2008 1:58PM
(51511 row(s) affected)
wellsa rowcount = 51511
wellsb rowcount = 51935
Iteration 10 : Apr 22 2008 1:59PM
(51183 row(s) affected)
Iteration 11 : Apr 22 2008 2:01PM
(50931 row(s) affected)
wellsa rowcount = 50931
wellsb rowcount = 51183
Iteration 12 : Apr 22 2008 2:02PM
(50721 row(s) affected)
Iteration 13 : Apr 22 2008 2:04PM
(50565 row(s) affected)
wellsa rowcount = 50565
wellsb rowcount = 50721
Iteration 14 : Apr 22 2008 2:05PM
(50443 row(s) affected)
Iteration 15 : Apr 22 2008 2:06PM
(50359 row(s) affected)
wellsa rowcount = 50359
wellsb rowcount = 50443
Iteration 16 : Apr 22 2008 2:07PM
(50299 row(s) affected)
Iteration 17 : Apr 22 2008 2:07PM
(50245 row(s) affected)
wellsa rowcount = 50245
wellsb rowcount = 50299
Iteration 18 : Apr 22 2008 2:08PM
(50197 row(s) affected)
Iteration 19 : Apr 22 2008 2:08PM
(50161 row(s) affected)
wellsa rowcount = 50161
wellsb rowcount = 50197
Iteration 20 : Apr 22 2008 2:09PM
(50133 row(s) affected)
Iteration 21 : Apr 22 2008 2:09PM
(50115 row(s) affected)
wellsa rowcount = 50115
wellsb rowcount = 50133
Iteration 22 : Apr 22 2008 2:10PM
(50103 row(s) affected)
Iteration 23 : Apr 22 2008 2:11PM
(50093 row(s) affected)
wellsa rowcount = 50093
wellsb rowcount = 50103
Iteration 24 : Apr 22 2008 2:11PM
(50089 row(s) affected)
Iteration 25 : Apr 22 2008 2:12PM
(50085 row(s) affected)
wellsa rowcount = 50085
wellsb rowcount = 50089
Iteration 26 : Apr 22 2008 2:12PM
(50081 row(s) affected)
Iteration 27 : Apr 22 2008 2:13PM
(50079 row(s) affected)
wellsa rowcount = 50079
wellsb rowcount = 50081
Iteration 28 : Apr 22 2008 2:14PM
(50079 row(s) affected)
Iteration 29 : Apr 22 2008 2:15PM
(50079 row(s) affected)
wellsa rowcount = 50079
wellsb rowcount = 50079
April 22, 2008 at 3:34 pm
I resurected some old cursor-based code which I've used in the past and modified it for this process. It's terribly slow but maybe it contains the kernel of an idea. Sorry about the messed up tabs.
In short - what it does is;
- Creates a cursor of the "N" records which we know will need to be aggregated
- walks the table using the cursor, updating the first record of the three involved and deleting the 2nd and 3rd ones
I'm sure that the reason it's so slow is that I'm doing all of those selects with every update. If I could figure out how to get all of the information in one go I think I might have a solution that's workable.
IF OBJECT_ID(N'TEMPDB..[##ASSEMBLED_INTERVALS]',N'U') IS NOT NULL DROP TABLE [##ASSEMBLED_INTERVALS]
SELECT
*
INTO ##ASSEMBLED_INTERVALS
FROM
##Aggregated_Intervals
CREATE UNIQUE CLUSTERED INDEX ASSEMBLED_INTERVALS_PK ON ##ASSEMBLED_INTERVALS(HoleID, EvalNum, Seq)
DECLARE ASSEMBLY_CSR CURSOR FOR
SELECT
CUR.HoleId
,CUR.EvalNum
,CUR.Seq
,CUR.Length
,CUR.MBit
FROM ##Assembled_Intervals CUR
LEFT JOIN ##Assembled_Intervals PREV
ON PREV.HoleId = CUR.HoleId
AND PREV.EvalNum = CUR.EvalNum
AND PREV.Seq = CUR.Seq - 1
LEFT JOIN ##Assembled_Intervals NEX
ON NEX.HoleId = CUR.HoleId
AND NEX.EvalNum = CUR.EvalNum
AND NEX.Seq = CUR.Seq + 1
WHERE
CUR.OreFlag = 'N'
AND CUR.LENGTH <= 1
AND CUR.LENGTH <= PREV.LENGTH
AND CUR.LENGTH <= NEX.LENGTH
AND (((PREV.MBit * PREV.Length) + (CUR.MBit * CUR.Length) + (NEX.MBit * NEX.Length)) / (PREV.Length + CUR.Length + NEX.Length)) >= 0.06
ORDER BY
CUR.HoleId
,CUR.EvalNum
,CUR.MBit DESC
,CUR.Length ASC
OPEN ASSEMBLY_CSR
DECLARE
@PREV_Seq INT,
@CUR_Seq INT,
@NEX_Seq INT,
@CUR_HoleId VARCHAR(13),
@CUR_EvalNum FLOAT,
@CUR_Length FLOAT,
@CUR_MBit FLOAT,
@Combined_Length FLOAT
FETCH FROM ASSEMBLY_CSR
INTO
@CUR_HoleId,
@CUR_EvalNum,
@CUR_Seq,
@CUR_Length,
@CUR_MBit
SET @PREV_Seq = (SELECT MAX(Seq) FROM ##ASSEMBLED_INTERVALS WHERE Seq < @CUR_Seq AND HOLEID = @CUR_HOLEID AND EvalNum = @CUR_EvalNum)
SET @NEX_Seq = (SELECT MIN(Seq) FROM ##ASSEMBLED_INTERVALS WHERE Seq > @CUR_Seq AND HOLEID = @CUR_HOLEID AND EvalNum = @CUR_EvalNum)
SET @Combined_Length= (SELECT SUM(LENGTH) FROM ##ASSEMBLED_INTERVALS WHERE Seq BETWEEN @PREV_Seq AND @NEX_Seq)
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE ##ASSEMBLED_INTERVALS
SETBaseDepth = (SELECT BaseDepth FROM ##ASSEMBLED_INTERVALS WHERE Seq = @NEX_Seq),
Length = @Combined_Length,
MBit= ((SELECT MBit * LENGTH FROM ##ASSEMBLED_INTERVALS WHERE Seq = @PREV_Seq) + (SELECT MBit * LENGTH FROM ##ASSEMBLED_INTERVALS WHERE Seq = @CUR_Seq) + (SELECT MBit * LENGTH FROM ##ASSEMBLED_INTERVALS WHERE Seq = @NEX_Seq)) / @Combined_Length,
PHIE= ((SELECT PHIE * LENGTH FROM ##ASSEMBLED_INTERVALS WHERE Seq = @PREV_Seq) + (SELECT PHIE * LENGTH FROM ##ASSEMBLED_INTERVALS WHERE Seq = @CUR_Seq) + (SELECT PHIE * LENGTH FROM ##ASSEMBLED_INTERVALS WHERE Seq = @NEX_Seq)) / @Combined_Length,
SwE = ((SELECT SwE * LENGTH FROM ##ASSEMBLED_INTERVALS WHERE Seq = @PREV_Seq) + (SELECT SwE * LENGTH FROM ##ASSEMBLED_INTERVALS WHERE Seq = @CUR_Seq) + (SELECT SwE * LENGTH FROM ##ASSEMBLED_INTERVALS WHERE Seq = @NEX_Seq)) / @Combined_Length,
Vsh = ((SELECT Vsh * LENGTH FROM ##ASSEMBLED_INTERVALS WHERE Seq = @PREV_Seq) + (SELECT Vsh * LENGTH FROM ##ASSEMBLED_INTERVALS WHERE Seq = @CUR_Seq) + (SELECT Vsh * LENGTH FROM ##ASSEMBLED_INTERVALS WHERE Seq = @NEX_Seq)) / @Combined_Length,
OreFlag = 'Y'
WHERE Seq = @PREV_Seq
DELETE FROM ##ASSEMBLED_INTERVALS WHERE Seq IN (@CUR_Seq, @NEX_Seq)
FETCH FROM ASSEMBLY_CSR
INTO
@CUR_HoleId,
@CUR_EvalNum,
@CUR_Seq,
@CUR_Length,
@CUR_MBit
SET @PREV_Seq = (SELECT MAX(Seq) FROM ##ASSEMBLED_INTERVALS WHERE Seq < @CUR_Seq AND HOLEID = @CUR_HOLEID AND EvalNum = @CUR_EvalNum)
SET @NEX_Seq = (SELECT MIN(Seq) FROM ##ASSEMBLED_INTERVALS WHERE Seq > @CUR_Seq AND HOLEID = @CUR_HOLEID AND EvalNum = @CUR_EvalNum)
SET @Combined_Length= (SELECT SUM(LENGTH) FROM ##ASSEMBLED_INTERVALS WHERE Seq BETWEEN @PREV_Seq AND @NEX_Seq)
END
CLOSE ASSEMBLY_CSR
DEALLOCATE ASSEMBLY_CSR
April 22, 2008 at 3:47 pm
I am getting ready to leave for the day but I will take a look at this tomorrow. I have been playing with the indexes and can't seem to get a faster initial run. I would like to get the full data set to see how much more time the additional rows will add.
When you say the cursor is slow, how long does it take to complete?
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply