May 13, 2009 at 9:18 am
I'm sure this has come up many times and I have read some articles pertaining to similar issues. We took the same database and restored one to 2000 and the other 2005. All the indexes were rebuilt and has the same exact data. The code is in a stored procedure without parameters so no sniffing should occur. Basically it truncates the table, inserts data into this table from two other tables, then loops through to fill in non business days. The code could probably be written better and have implemented the tally table and also created a date column with an index so that could be utilized but the code was originally written before those tricks, at least me learning that trick. Anyway, 2000 creates a nested loop join and 2005 uses a hash join. I even put the loop hint one procedure, ran it, then executed the other without the hints and 2005 still uses the hash. I've enclosed the script to show the work but the tables and data is a bit much since it's client data but can give more information on the tables and/or indexes if needed but wanted to start somewhere
TRUNCATE TABLE tAccountBalance
INSERT INTO tAccountBalance (
AccId,
bDate,
OpeningAvailable,
ClosingLedger,
ClosingCollected,
SystemBalance,
SystemAdjust,
BankBalId
)
SELECT bf.unique_id, DATEADD(DAY,1,bb.incur_date), bb.open_avail, bb.sum_015, bb.close_coll, bb.sys_bal, bb.sys_adjust, bb.unique_id
FROM bank_bal bb
INNER JOIN bnkfolio bf ON
bf.bank_code = bb.bank_code AND
bf.act_num = bb.act_num
OPTION(MAXDOP 1)
WHILE @@ROWCOUNT > 0
BEGIN
INSERT INTO tAccountBalance (
AccId,
bDate,
OpeningAvailable,
ClosingLedger,
ClosingCollected,
SystemBalance,
SystemAdjust,
BankBalId
)
SELECT
tAccountBalance.AccId,
DATEADD(DAY,1,tAccountBalance.bDate),
tAccountBalance.OpeningAvailable,
tAccountBalance.ClosingLedger,
tAccountBalance.ClosingCollected,
tAccountBalance.SystemBalance,
tAccountBalance.SystemAdjust,
tAccountBalance.BankBalId
FROM tAccountBalance
INNER /*LOOP*/ JOIN bnkfolio bf ON
bf.unique_id = tAccountBalance.AccId
INNER /*LOOP*/ JOIN calc ON
calc.cal_code = bf.cal_code
LEFT OUTER /*LOOP*/ JOIN clnd ON
clnd.cal_code = bf.cal_code AND
clnd.holiday = tAccountBalance.bDate
WHERE
(
'C' = CASE DATEPART(dw, tAccountBalance.bDate)
WHEN 1 THEN sunday
WHEN 2 THEN monday
WHEN 3 THEN tuesday
WHEN 4 THEN wednesday
WHEN 5 THEN thursday
WHEN 6 THEN friday
WHEN 7 THEN saturday
END OR
clnd.unique_id IS NOT NULL
) AND
NOT(EXISTS(
SELECT *
FROM tAccountBalance ex
WHERE ex.AccId = tAccountBalance.AccId
AND ex.bDate = DATEADD(DAY,1,tAccountBalance.bDate)
))
OPTION(MAXDOP 1)
END
TRUNCATE TABLE tBnkFolioLastPoll
INSERT INTO tBnkFolioLastPoll (
AccId,
pDate
)
SELECT bf.unique_id, DATEADD(DAY,1,MAX(bal.incur_date))
FROM bnkfolio bf
LEFT OUTER JOIN bank_bal bal ON
bal.bank_code = bf.bank_code AND
bal.act_num = bf.act_num
GROUP BY bf.unique_id
May 13, 2009 at 12:41 pm
yes they likely will be different - the optimiser was rewritten for SQL 2005.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 13, 2009 at 12:43 pm
Execution plans do in fact change, some slightly, some more so from 2000 to 2005. Did you update all statistics (preferably with fullscan) after migrating to SQL 2005?
A.J.
DBA with an attitude
May 14, 2009 at 10:43 am
Interestingly enough, I truncated the table, rebuilt statistics for everything and the estimated execution plan is in fact the nested loop. However during execution it reverts back to the merge. It has re-evaluate? I know plans can be kept and even use the hints, but why is the new optimizer choosing such a horible plan even after the estimate claims nested loops?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply