View performance problems

Question

Post reply

View performance problems

davidsalazar01

SSCarpal Tunnel

Points: 4072
More actions
July 15, 2009 at 2:24 pm

#206990

😉 Hello Developers/DBA's,
We recently upgraded a system and one of the views was updated by a 3rd party software provider we received the db upgrade scripts from. In the scripts they provided us the view has changed as shown below. Since the upgrade we're getting "Server encountered an error" when a user tries to run a transaction report thru the web UI.
Just an FYI there were also 2 fields that were added to this view that reside in a table over 12+ million rows.
Anybody have any thoughts??? Your input is greatly appreciated!!! 😀
Two new fields in the view ---
dbo.TRX_Header_T.BatchID_VC , dbo.TRX_Header_T.Reversal_Flag_CH -- new fields in the new view (possibly needs an index, what think?)
old view ---
dbo.CC_Info_T INNER JOIN
dbo.TRX_Card_T ON dbo.CC_Info_T.Card_Info_Key = dbo.TRX_Card_T.Card_Info_Key INNER JOIN
dbo.TRX_Header_T ON dbo.TRX_Card_T.TRX_HD_Key = dbo.TRX_Header_T.TRX_HD_Key LEFT OUTER JOIN
dbo.TRX_Receipt_T ON dbo.TRX_Header_T.TRX_HD_Key = dbo.TRX_Receipt_T.TRX_HD_Key
new view ---
dbo.CC_Info_T INNER JOIN
dbo.TRX_Card_T ON dbo.CC_Info_T.Card_Info_Key = dbo.TRX_Card_T.Card_Info_Key INNER JOIN
dbo.TRX_Header_T ON dbo.TRX_Card_T.TRX_HD_Key = dbo.TRX_Header_T.TRX_HD_Key LEFT OUTER JOIN
(SELECT TRX_HD_Key, COUNT(*) AS TRX_Receipt_Cnt
FROM dbo.TRX_Receipt_T AS TRX_Receipt_T_1
GROUP BY TRX_HD_Key) AS TRX_Receipt_T ON dbo.TRX_Header_T.TRX_HD_Key = TRX_Receipt_T.TRX_HD_Key

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

Don't touch a thing. Get that 3rd party vendor on the bloody phone and have them fix it now!

--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)

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

While Jeff is likely correct, as usual, what happens if you run the same query in SSMS/Query Analyzer? You will probably get a better error message or might find that the error is in the web UI.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 3

You done great in identifying it and yes i would be on the phone too.....after all you pay support they fix it.

Changing code on vendor software sometimes make it unsupportable...so work with them.