Making a command more efficient
-
Can anyone please help me make the following command more efficient:
INSERT INTO Mimic_table (
Year_of_death,
Ageyears,
Sex,
Undcause,
ICD,
OAcode)
select Year_ref,
Age_start,
Sex,
ICD_ref,
ICD_number,
'XXXXXXXXXX'
from Eng_Wales t
join Number_list n
on n.number < t.Person_count
WHERE t.Year_ref IS NOT NULL
AND t.Age_start IS NOT NULL
AND t.Sex IS NOT NULL
AND t.ICD_ref IS NOT NULL
AND t.ICD_number IS NOT NULL
The table Number_list is simply a table of numbers from 1 to 16000, one number per row.
The command won't even perform properly because 16000 is too great a number and it fills up the transaction log for the database.
-
What does this return:
SELECT COUNT(*), SUM(Person_Count)
FROM Eng_Wales
-
It returns the two numbers:
449291 13591434
-
You're join to Number_List is as follows:
on n.number < t.Person_count
Less than ?
Based on the number of rows in Eng_Wales and the values of Person_Count, your intention is to create a 13+ million row table using this less-than join ?
-
Is that a problem?
-
No, if you'v got shares in Intel or/and HP.
_____________
Code for TallyGenerator -
What are you trying to achieve? Surely this is going to give billions of rows in the result. Each row in Eng_Wales is going to join with every row in Number_List that has a lower Person_Count.
-
I've had to limit the number of years that I can apply this operation to. The reason I wanted to perform this operation is so i could maintain the method that the database uses by creating a table that mimics another (which is smaller than the billions of rows you talk about.
-
What does the number of years have to do with the person count?
Moot point... the problem is that you are creating a "triangular join" with the "less than" condition... it's only half as bad as a full cross join...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply