|
|
Posted by Roy Harvey on 10/29/06 13:40
Looping is always to be avoided if possible. What version of SQL
Server are you running? In the (unlikely) event it is 2005, I think
it should be possible to eliminate the looping, as TOP accepts a
variable in 2005, rather than requiring a constant.
Even with other releases I think I see a way to eliminate the loop.
Assuming that QualMismatchedAllocsRowNum in table
t_AuditQualifiedNumberExtractDetails_Temp is an identity column or
other unique value, I think this can replace the entire loop process.
INSERT INTO t_AuditQualifiedNumberExtractDetails
(BatchNumber,
EntryRecordID,
LN,
AdditionalQualCritPassed)
SELECT BatchNumber,
EntryRecordID,
LN,
AdditionalQualCritPassed
FROM t_AuditQualifiedNumberExtractDetails_Temp as A
WHERE TotalNumbers <=
(select count(*)
from t_AuditQualifiedNumberExtractDetails_Temp as B
where A.BatchNumber = B.BatchNumber
and A.RecordType = B.RecordType
and A.EntryRecordID = B.EntryRecordID
and A.QualMismatchedAllocsRowNum <=
B.QualMismatchedAllocsRowNum)
That would require a good index on the columns (BatchNumber,
RecordType, EntryRecordID).
I take it that QualMismatchedAllocsRowNum in table
@t_QualificationMismatchedAllocs is an identity column? Are the
values always starting at 1? My concern is looping through a lot of
numbers that might not exist in the table.
Looking at the tests that split the data into two categories:
>having count(*) = max(TotalNumbers)) as t2
> having count(*) <> max(TotalNumbers))
brings up a question. What sort of row count do we get from each of
the three relationships?
count(*) = max(TotalNumbers)
count(*) < max(TotalNumbers) -- If this is significant, see below.
count(*) > max(TotalNumbers)
The reason I ask is that I do not see a difference in the final
results between count(*) < max(TotalNumbers) and when they are
equal. In both those cases all the rows are used. The looping
process appears to me to only be required when there are MORE rows
(count(*) > max(TotalNumbers)) than should be inserted. If I have not
missed something, and if a significant part of the processing is for
count(*) < max(TotalNumbers), then the first test could be modified
to:
having count(*) <= max(TotalNumbers)) as t2
And the one used to generate the exceptions used in the loop changed
to:
having count(*) > max(TotalNumbers))
Which would cut down on the looping (or the alternate joining
proposed) process.
Roy Harvey
Beacon Falls, CT
On 28 Oct 2006 23:26:51 -0700, "Catch_22" <catch_20_2@yahoo.co.uk>
wrote:
>Hi,
>
>I have a stored procedure that has to extract the child records for
>particular parent records.
>
>The issue is that in some cases I do not want to extract all the child
>records only a certain number of them.
>
>Firstly I identify all the parent records that have the requird number
>of child records and insert them into the result table.
>
>insert into t_AuditQualifiedNumberExtractDetails
> (BatchNumber,
> EntryRecordID,
> LN,
> AdditionalQualCritPassed)
>(select t1.BatchNumber,
> t1.EntryRecordID,
> t1.LN,
> t1.AdditionalQualCritPassed
>from
>(select BatchNumber,
> RecordType,
> EntryRecordID,
> LN,
> AdditionalQualCritPassed
> from t_AuditQualifiedNumberExtractDetails_Temp) as t1
> inner join
>(select BatchNumber,
> RecordType,
> EntryRecordID,
> Count(*) as AssignedNumbers,
> max(TotalNumbers) as TotalNumbers
>from t_AuditQualifiedNumberExtractDetails_Temp
>group by BatchNumber, RecordType, EntryRecordID
>having count(*) = max(TotalNumbers)) as t2
>on t1.BatchNumber = t2.BatchNumber
>and t1.RecordType = t2.RecordType
>and t1.EntryRecordID = t2.EntryRecordID)
>
>then insert the remaining records into a temp table where the number of
>records required does not equal the total number of child records, and
>thenloop through each record manipulating the ROWNUMBER to only select
>the number of child records needed.
>
>insert into @t_QualificationMismatchedAllocs
> ([BatchNumber],
> [RecordType],
> [EntryRecordID],
> [AssignedNumbers],
> [TotalNumbers])
> (select BatchNumber,
> RecordType,
> EntryRecordID,
> Count(*) as AssignedNumbers,
> max(TotalNumbers) as TotalNumbers
> from t_AuditQualifiedNumberExtractDetails_Temp
> group by BatchNumber, RecordType, EntryRecordID
> having count(*) <> max(TotalNumbers))
>
> SELECT @QualificationMismatched_RowCnt = 1
>
> SELECT @MaxQualificationMismatched = (select count(*) from
>@t_QualificationMismatchedAllocs)
>
>while @QualificationMismatched_RowCnt <= @MaxQualificationMismatched
> begin
> --## Get Prize Draw to extract numbers for
> select @RecordType = RecordType,
> @EntryRecordID = EntryRecordID,
> @AssignedNumbers = AssignedNumbers,
> @TotalNumbers = TotalNumbers
> from @t_QualificationMismatchedAllocs
> where QualMismatchedAllocsRowNum = @QualificationMismatched_RowCnt
>
> SET ROWCOUNT @TotalNumbers
>
> insert into t_AuditQualifiedNumberExtractDetails
> (BatchNumber,
> EntryRecordID,
> LN,
> AdditionalQualCritPassed)
> (select BatchNumber,
> EntryRecordID,
> LN,
> AdditionalQualCritPassed
> from t_AuditQualifiedNumberExtractDetails_Temp
> where RecordType = @RecordType
> and EntryRecordID = @EntryRecordID)
>
> SET @QualificationMismatched_RowCnt =
>
>QualificationMismatched_RowCnt + 1
> SET ROWCOUNT 0
> end
>
>Is there a better methodology for doing this .....
>
>Is the use of a table variable here incorrect ?
>
>Should I be using a temporary table or indexed table if there are a
>large number of parent records where the child records required does
>not match the total number of child records ?
Navigation:
[Reply to this message]
|