Script running forever.....Help
-
I have created a function. When I Exec it runs forever (over 48 hours, then I stopped it). If I hard code an accountno it updates in 3 seconds. I have over 300,000 records in the db. I know I have missed something easy, but I don't know what. Please help.
to exec I do.....
updated contact2
set udefcon = dbo.calc_score(contact2.accountno)
from contact2
The function it calls is
CREATE FUNCTION calc_score (@acctnumber varchar(20))
Returns numeric
AS
BEGIN
DECLARE @totalscore numeric(4)
SELECT @totalscore =
CASE
WHEN key1 IN('APP', 'ADM', 'CNF', 'DNY', 'ENR') THEN 100 --Add 100 points if key1 has a VALUE in the field
ELSE 0
END +
CASE
WHEN uactequiv BETWEEN 1 AND 19 THEN 25 -- add points for uactequiv value
WHEN uactequiv BETWEEN 20 AND 21 THEN 50
WHEN uactequiv BETWEEN 22 AND 23 THEN 75
When uactequiv BETWEEN 24 and 26 THEN 100
When uactequiv BETWEEN 27 and 30 THEN 125
WHEN uactequiv >=31 THEN 150
ELSE 0
END +
CASE
WHEN userdef02 IS NOT NULL or userdef03 is NOT NULL THEN 50 -- Add 50 points if userdef02 not null
ELSE 0
END +
CASE
WHEN uconflg = 'Y' or UVSARRIVDT > ' ' THEN 150 --Add 150 points if uconflg has a Y in the field
ELSE 0
END +
CASE
WHEN uacurank = '1' THEN 50 --Add 50 points if UACURANK has a 1 in the field
ELSE 0
END +
CASE
WHEN uacurank = '2' THEN 30 --Add 30 points if UACURANK has a 2 in the field
ELSE 0
END +
CASE
WHEN uacurank = '3' THEN 20 --Add 20 points if UACURANK has a 3 in the field
ELSE 0
END +
CASE
WHEN uleadcamp = 'Y' THEN 100 --Add 100 points if uleadcamp equals Y
ELSE 0
END +
CASE
when uacadarea1 IN ('BUS', 'BID', 'JOU', 'PSY', 'MED', 'DEN') OR
umajor IN ('ACCT', 'FIN', 'MKTG', 'MGMT', 'BIBL', 'BIBM', 'BIBY', 'BIVM', 'BMCF', 'BMIS', 'BYFM', 'IMC', 'JELM', 'JOUR', 'PSYC') OR
department IN ('PRDE', 'PRME') THEN 50
ELSE 0
END +
CASE
when upsupport = 'Y' THEN 50 --add 50 points if upsupport is marked Y
ELSE 0
END +
CASE
WHEN ubot >' ' or ugoldlist > ' ' or ufacstaff > ' ' THEN 100 --add 100 points if ubot has a VALUE in the field
ELSE 0
END +
CASE
WHEN ualumni = 'Y' THEN 50 --add 50 points if ualumni has a VALUE that equals Y
ELSE 0
END +
CASE
WHEN usibling = 'Y' THEN 100 --add 100 points if usibling has a VALUE that equals Y
ELSE 0
END +
CASE
WHEN uchurchact IN('A', 'B') THEN 50 --add 50 points if uchurchact has an A or B in the field
ELSE 0
END
FROM
contact2, contact1
WHERE contact2.accountno = contact2.accountno
and contact2.accountno = @acctnumber
RETURN(@totalscore)
END
-
Use of a UDF in the SET portion of an Update is highly inefficient. It becomes almost like a cursor-based operation, because the function and the joins within it get re-evaluated for each of your 300K rows.
You'll get performance several orders of magnitude better if you can re-code it as a set-based Update operation without a UDF.
-
Example:
UPDATE
contact2
SET udefcon =
CASE
WHEN key1 IN('APP', 'ADM', 'CNF', 'DNY', 'ENR')
THEN 100 --Add 100 points if key1 has a VALUE in the field
ELSE 0
END
+
CASE
WHEN uactequiv BETWEEN 1 AND 19
THEN 25 -- add points for uactequiv value
WHEN uactequiv BETWEEN 20 AND 21
THEN 50
WHEN uactequiv BETWEEN 22 AND 23
THEN 75
WHEN uactequiv BETWEEN 24 AND 26
THEN 100
WHEN uactequiv BETWEEN 27 AND 30
THEN 125
WHEN uactequiv >=31
THEN 150
ELSE 0
END
+
CASE
WHEN userdef02 IS NOT NULL
OR userdef03 is NOT NULL
THEN 50 -- Add 50 points if userdef02 not null
ELSE 0
END
+
CASE
WHEN uconflg = 'Y'
OR UVSARRIVDT > ' '
THEN 150 --Add 150 points if uconflg has a Y in the field
ELSE 0
END
+
CASE
WHEN uacurank = '1'
THEN 50 --Add 50 points if UACURANK has a 1 in the field
ELSE 0
END
+
CASE
WHEN uacurank = '2'
THEN 30 --Add 30 points if UACURANK has a 2 in the field
ELSE 0
END
+
CASE
WHEN uacurank = '3'
THEN 20 --Add 20 points if UACURANK has a 3 in the field
ELSE 0
END
+
CASE
WHEN uleadcamp = 'Y'
THEN 100 --Add 100 points if uleadcamp equals Y
ELSE 0
END
+
CASE
WHEN uacadarea1 IN ('BUS', 'BID', 'JOU', 'PSY', 'MED', 'DEN')
OR umajor IN ('ACCT', 'FIN', 'MKTG', 'MGMT', 'BIBL', 'BIBM', 'BIBY', 'BIVM', 'BMCF', 'BMIS', 'BYFM', 'IMC', 'JELM', 'JOUR', 'PSYC')
OR department IN ('PRDE', 'PRME')
THEN 50
ELSE 0
END
+
CASE
WHEN upsupport = 'Y'
THEN 50 --add 50 points if upsupport is marked Y
ELSE 0
END
+
CASE
WHEN ubot >' '
OR ugoldlist > ' '
OR ufacstaff > ' '
THEN 100 --add 100 points if ubot has a VALUE in the field
ELSE 0
END
+
CASE
WHEN ualumni = 'Y'
THEN 50 --add 50 points if ualumni has a VALUE that equals Y
ELSE 0
END
+
CASE
WHEN usibling = 'Y'
THEN 100 --add 100 points if usibling has a VALUE that equals Y
ELSE 0
END
+
CASE
WHEN uchurchact IN('A', 'B')
THEN 50 --add 50 points if uchurchact has an A or B in the field
ELSE 0
END
FROM contact2,
contact1
WHERE contact2.accountno = contact1.accountno
AND contact2.accountno = @acctnumber
-
Assuming you have 300 000 records in each table
FROM
contact2, contact1
WHERE contact2.accountno = contact2.accountno
and contact2.accountno = @acctnumber
this will cause sql server to make a cross join of 90 000 000 000 records (every time you call the function) before filtering with the where condition.
I would try this :
FROM
contact2 inner join contact1
on contact2.accountno = contact1.accountno
and contact2.accountno = @acctnumber
also I assume that contact2.accountno = contact2.accountno is a typo because this won't filter anything (Should be contact1.accountno)
I would also consider using this join to give the query optmizer to pick out the best index :
FROM
contact2 inner join contact1
on contact2.accountno = contact1.accountno
and contact2.accountno = @acctnumber
and contact1.accountno = @acctnumber
This brings me to my last point... make sure that contact2.accountno and contact1.accountno are indexed
One final thing that would immensly improve performance would be to convert the udf to a derived table and join to that table so instead of 300 000 queries you get only 2.
-
As PW advised, having table access in UDF will be extremely slow. Recommend that all table access be outside of the UDF and use the columns as parameters to the UDF.
Here is a simplied example:
CREATE FUNCTION calc_score
(@key1 varchar(20)
, @uactequiv integer)
Returns numeric
AS
BEGIN
DECLARE @totalscore numeric(4)
set @totalscore = 0
--Add 100 points if key1 has a VALUE in the field
SET @totalscore = @totalscore +
CASE WHEN @key1 IN('APP', 'ADM', 'CNF', 'DNY', 'ENR') THEN 100 ELSE 0 END
-- add points for uactequiv value
SET @totalscore = @totalscore +
CASE WHEN uactequiv BETWEEN 1 AND 19 THEN 25
WHEN uactequiv BETWEEN 20 AND 21 THEN 50
WHEN uactequiv BETWEEN 22 AND 23 THEN 75
When uactequiv BETWEEN 24 and 26 THEN 100
When uactequiv BETWEEN 27 and 30 THEN 125
WHEN uactequiv >=31 THEN 150
ELSE 0 END
return @totalscore
end
go
Update contact2
set udefcon = dbo.calc_score(key1 , uactequiv -- more parameters as needed)
from contact2
join contact1
on contact1.accountno = contact2.accountno
go
SQL = Scarcely Qualifies as a Language
-
OMG, It worked with a few modifications........I have worked for weeks on this trying to figure this out. Yall are AWESOME and I appreciate you very much.
-
Could you post the final script so that people that come across the same problem can see the working solution?
-
First I created this function:
CREATE FUNCTION calc_score (@acctnumber varchar(20))
Returns numeric
AS
BEGIN
DECLARE @totalscore numeric(4)
SELECT @totalscore =
CASE
WHEN key1 IN('APP', 'ADM', 'CNF', 'DNY', 'ENR') THEN 100 --Add 100 points if key1 has a VALUE in the field
ELSE 0
END +
CASE
WHEN uactequiv BETWEEN 1 AND 19 THEN 25 -- add points for uactequiv value
WHEN uactequiv BETWEEN 20 AND 21 THEN 50
WHEN uactequiv BETWEEN 22 AND 23 THEN 75
When uactequiv BETWEEN 24 and 26 THEN 100
When uactequiv BETWEEN 27 and 30 THEN 125
WHEN uactequiv >=31 THEN 150
ELSE 0
END +
CASE
WHEN userdef02 IS NOT NULL or userdef03 is NOT NULL THEN 50 -- Add 50 points if userdef02 not null
ELSE 0
END +
CASE
WHEN uconflg = 'Y' or UVSARRIVDT > ' ' THEN 150 --Add 150 points if uconflg has a Y in the field
ELSE 0
END +
CASE
WHEN uacurank = '1' THEN 50 --Add 50 points if UACURANK has a 1 in the field
ELSE 0
END +
CASE
WHEN uacurank = '2' THEN 30 --Add 30 points if UACURANK has a 2 in the field
ELSE 0
END +
CASE
WHEN uacurank = '3' THEN 20 --Add 20 points if UACURANK has a 3 in the field
ELSE 0
END +
CASE
WHEN uleadcamp = 'Y' THEN 100 --Add 100 points if uleadcamp equals Y
ELSE 0
END +
CASE
when uacadarea1 IN ('BUS', 'BID', 'JOU', 'PSY', 'MED', 'DEN') OR
umajor IN ('ACCT', 'FIN', 'MKTG', 'MGMT', 'BIBL', 'BIBM', 'BIBY', 'BIVM', 'BMCF', 'BMIS', 'BYFM', 'IMC', 'JELM', 'JOUR', 'PSYC') OR
department IN ('PRDE', 'PRME') THEN 50
ELSE 0
END +
CASE
when upsupport = 'Y' THEN 50 --add 50 points if upsupport is marked Y
ELSE 0
END +
CASE
WHEN ubot >' ' or ugoldlist > ' ' or ufacstaff > ' ' THEN 100 --add 100 points if ubot has a VALUE in the field
ELSE 0
END +
CASE
WHEN ualumni = 'Y' THEN 50 --add 50 points if ualumni has a VALUE that equals Y
ELSE 0
END +
CASE
WHEN usibling = 'Y' THEN 100 --add 100 points if usibling has a VALUE that equals Y
ELSE 0
END +
CASE
WHEN uchurchact IN('A', 'B') THEN 50 --add 50 points if uchurchact has an A or B in the field
ELSE 0
END
FROM
contact2 inner join contact1
on contact2.accountno = contact1.accountno
and contact2.accountno = @acctnumber
and contact1.accountno = @acctnumber
RETURN(@totalscore)
END
To exec:
update contact2
set udefcon = dbo.calc_score(contact2.accountno)
from contact2
join contact1
on contact1.accountno = contact2.accountno
GO
And...finished in under 2 minutes....
The SQLSERVERCENTRAL knowledge is unbelieveable. I am greatly appreciative.
-
Well, congrats on getting it working, but the final solution is not optimal and will seriously degrade performance as your table gets larger.
As suggested above, you should keep your tables outside the function and pass in the column values needed to generate the score outcome.
Think about portability & reusability - what happens if you want to replicate this scoring process on different tables ? Now you'd need a 2nd function because the tables are explicitly referenced in the UDF - if you pass values into the UDF, instead of a key for the UDF to lookup the same values, you have much better performance and reusability.
i.e:
CREATE FUNCTION calc_score (@Key1, @uactequiv, @userdef02 ... <other columns needed>
Returns numeric
AS
-
Remi;
>...this will cause sql server to make a cross join of 90 000 000 000 records ...
That is not quite correct. Assuming (as you did) that Shelly had the type in "contact2.accountno = contact2.accountno". specifying the join in the where clause is acceptable and (usually) works just like the JOIN clause syntax. Specifying joins using the where clause was the SQL standard before JOIN clause was introduced in ANSI SQL-92, and SQL Server 7 and 2000 recognize and handle both syntaxes.
That being said, I would not be surprised if Shelly's problem was with the where clause where we assumed a typo. The query as she indicated would indeed make the cross join you describe, since the where clause would then not have anything with to relate the two tables. Her comment "If I hard code an accountno.." would reduce the cross join from 90,000,000 rows to 300,000 (with 1 row in contact2 being updated), assuming both contact1 and contact2 had 300k rows. So I would not be surprised if that was the original problem.
But that show why I said "(usually)" in the first paragraph. Having the JOIN syntax separates table join relations from filter criteria, and helps to debug errors. It would have been easier to see the cross-join with the JOIN syntax, and her implementing probably resolved the problem. So I do consider it "better practice" to use JOIN syntax, although both will work.
Mark -
Ok, the script was modified once again and the time is 14 seconds. AMAZING. Here is what I did in case this helps anyone else out.
Created Function
CREATE FUNCTION calc_score
(@key1 varchar(20),
@uactequiv smallint,
@userdef02 varchar(20),
@userdef03 varchar(20),
@uconflg varchar(10),
@uvsarrivdt datetime,
@uacurank varchar(10),
@uleadcamp varchar(10),
@uacadarea1 varchar(10),
@umajor varchar(3),
@department varchar(35),
@upsupport varchar(10),
@ubot varchar(15),
@ugoldlist varchar(10),
@ufacstaff varchar(10),
@ualumni varchar(10),
@usibling varchar(10),
@uchurchact varchar(10)
 
Returns numeric
AS
BEGIN
DECLARE @totalscore numeric(4)
SET @totalscore = 0
--Add 100 points if key1 has a VALUE in the field
SET @totalscore = @totalscore +
CASE
WHEN @key1 IN('APP', 'ADM', 'CNF', 'DNY', 'ENR') THEN 100
ELSE 0
END
-- add points for uactequiv value
SET @totalscore = @totalscore +
CASE
WHEN @uactequiv BETWEEN 1 AND 19 THEN 25
WHEN @uactequiv BETWEEN 20 AND 21 THEN 50
WHEN @uactequiv BETWEEN 22 AND 23 THEN 75
When @uactequiv BETWEEN 24 and 26 THEN 100
When @uactequiv BETWEEN 27 and 30 THEN 125
WHEN @uactequiv >=31 THEN 150
ELSE 0
END
-- Add 50 points if userdef02 not null
SET @totalscore = @totalscore +
CASE
WHEN @userdef02 IS NOT NULL OR @userdef03 is NOT NULL THEN 50
ELSE 0
END
--Add 150 points if uconflg has a Y in the field
SET @totalscore = @totalscore +
CASE
WHEN @uconflg = 'Y' OR @uvsarrivdt > ' ' THEN 150
ELSE 0
END
--Add points for UACURANK
SET @totalscore = @totalscore +
CASE
WHEN @uacurank = '1' THEN 50 --Add 50 points if UACURANK has a 1 in the field
WHEN @uacurank = '2' THEN 30 --Add 30 points if UACURANK has a 2 in the field
WHEN @uacurank = '3' THEN 20 --Add 20 points if UACURANK has a 3 in the field
ELSE 0
END
--Add 100 points if uleadcamp equals Y
SET @totalscore = @totalscore +
CASE
WHEN @uleadcamp = 'Y' THEN 100
ELSE 0
END
--Add points for academics
SET @totalscore = @totalscore +
CASE
when @uacadarea1 IN ('BUS', 'BID', 'JOU', 'PSY', 'MED', 'DEN') OR
@umajor IN ('ACCT', 'FIN', 'MKTG', 'MGMT', 'BIBL', 'BIBM', 'BIBY', 'BIVM', 'BMCF', 'BMIS', 'BYFM', 'IMC', 'JELM', 'JOUR', 'PSYC') OR
@department IN ('PRDE', 'PRME') THEN 50
ELSE 0
END
--add 50 points if upsupport is marked Y
SET @totalscore = @totalscore +
CASE
when @upsupport = 'Y' THEN 50
ELSE 0
END
--add 100 points if ubot has a VALUE in the field
SET @totalscore = @totalscore +
CASE
WHEN @ubot >' ' OR @ugoldlist > ' ' OR @ufacstaff > ' ' THEN 100
ELSE 0
END
--add 50 points if ualumni has a VALUE that equals Y
SET @totalscore = @totalscore +
CASE
WHEN @ualumni = 'Y' THEN 50
ELSE 0
END
--add 100 points if usibling has a VALUE that equals Y
SET @totalscore = @totalscore +
CASE
WHEN @usibling = 'Y' THEN 100
ELSE 0
END
--add 50 points if uchurchact has an A or B in the field
SET @totalscore = @totalscore +
CASE
WHEN @uchurchact IN('A', 'B') THEN 50
ELSE 0
END
RETURN(@totalscore)
END
Then I ran the update stmt that calls the function--
completes in 14 seconds. WOW
update contact2
set udefcon = dbo.calc_score
(contact1.key1,
contact2.uactequiv,
contact2.userdef02,
contact2.userdef03,
contact2.uconflg,
contact2.uvsarrivdt,
contact2.uacurank,
contact2.uleadcamp,
contact2.uacadarea1,
contact2.umajor,
contact1.department,
contact2.upsupport,
contact2.ubot,
contact2.ugoldlist,
contact2.ufacstaff,
contact2.ualumni,
contact2.usibling,
contact2.uchurchact
)
from contact2
join contact1
on contact1.accountno = contact2.accountno
GO
--now select top 1000 scores
select unamefull, udefcon
from contact2
where udefcon > 0
order by udefcon desc
GO
-
Sounds more like it... From incomplete after 2 days to 14 secs... how many folds are we talking about here??
-
<>
BTW the update would have taken about 10.5 days to complete (assuming nothing major would have slowed it down further like a huge transaction log growth).
I was wondering if you could do another test for us :
Could you rerun the update but using the function's code directly in the update statement. I'm wondering how much of an overhead this is causing sql server to call the same function 300 k times (14 sec) vs not calling it (?? sec).
This has already proven that functions run a little like cursor if you make a select in them and that you can greatly improve performance by doing the select outside the function and simply doing maths in it. But I'd like to know how much faster, if any, it would be to not call it at all and doing everything in the update statement.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply