Extreamly slow performance on a view

Can anyone see anything that may be causing this view to return data
extreamly slowly, or have any tips on increasing the performance of this
particular view?
Alter View dbo.PreRegistration_V as
SELECT C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
NULL Level1,
Null Level2,
Null Level3,
NULL MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
Null COM_Position,
Null Committee,
null End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MTG.Meeting = 'F05'
UNION
SELECT
C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
NULL Level1,
NULL Level2,
NULL Level3,
NULL MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
CM.COM_Position,
CM.Committee,
CM.End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Committee_member CM WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and CM.Customer = MR.Ship_Customer
and MTG.Meeting = 'F05'
and (CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
'C0001000', 'C0001300') or CM.Committee like 'C0%')
UNION
SELECT C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
NULL Level1,
NULL Level2,
NULL Level3,
NULL MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
CM.COM_Position,
CM.Committee,
CM.End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Committee_member CM WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and CM.Customer = MR.Ship_Customer
and MTG.Meeting = 'F05'
and CM.COM_Position IN ('001CHAIR','062PP','060PRES','061VP','0
63EXECVP')
UNION
SELECT
C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
MD.Level1,
MD.Level2,
MD.Level3,
MBR.MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
Null COM_Position,
Null Committee,
Null End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Membership MBR WITH(NOLOCK),
MemberShip_Detail MD WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK),
Committee_Member CM WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Customer = MBR.Ship_Customer
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and MBR.Order_No = MD.order_No
and MD.Level2 in ('STAFF','IND','JR','STU')
and MBR.Order_Status = 'A'
and MTG.Meeting = 'F05'
UNION
SELECT
C.Customer,
C.Mail_Name,
C.First_Name,
C.Last_Name,
null Level1,
null Level2,
null Level3,
null MBRFULFILLST_Code,
A.Formatted_Line_1,
A.Formatted_Line_2,
A.Formatted_Line_3,
A.Formatted_Line_4,
A.Formatted_Line_5,
A.Formatted_Line_6,
A.Formatted_Line_7,
A.Country,
MR.Order_No,
MR.Order_Date,
MTG.Meeting_Name_1,
MTG.Meeting,
MR.Order_Total,
MR.User_N1,
MR.User_N2,
MR.User_n3,
MR.User_N4,
MR.Badge_Print_Status,
Null COM_Position,
Null Committee,
Null End_Date
from Customer C WITH(NOLOCK),
meeting_registrant MR WITH(NOLOCK),
Membership MBR WITH(NOLOCK),
MemberShip_Detail MD WITH(NOLOCK),
ACI_Current_Fellows CD WITH(NOLOCK),
Address A WITH(NOLOCK),
Meeting MTG WITH(NOLOCK)
WHERE C.Customer = MR.Ship_Customer
and C.Record_Type = 'I'
and MR.Order_Status = 'A'
and MR.Ship_Customer = MBR.Ship_Customer
and MR.Ship_Address_ID = A.Address_ID
and MR.Meeting = MTG.Meeting
and MBR.Order_No = MD.order_No
and MTG.Meeting = 'F05'Mike,
That's a pretty hefty view. Might consider using a stored procedure
instead. Have the appropriate indexes been created for each SELECT
statement? Are the indexes defraged, are the statistics up-to-date? Does
the data change very often - possibly an indexed view? Can you populate a
table with the results during off-peak hours and then query the table
directly (w/ appropriate indexes)?
Just a couple of thoughts.
HTH
Jerry
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:CC4032BC-603F-4846-B6F0-E06E676B6609@.microsoft.com...
> Can anyone see anything that may be causing this view to return data
> extreamly slowly, or have any tips on increasing the performance of this
> particular view?
> Alter View dbo.PreRegistration_V as
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> Null Level2,
> Null Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and (CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
> 'C0001000', 'C0001300') or CM.Committee like 'C0%')
> UNION
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and CM.COM_Position IN ('001CHAIR','062PP','060PRES','061VP','0
63EXECVP')
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> MD.Level1,
> MD.Level2,
> MD.Level3,
> MBR.MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK),
> Committee_Member CM WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MD.Level2 in ('STAFF','IND','JR','STU')
> and MBR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> null Level1,
> null Level2,
> null Level3,
> null MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> ACI_Current_Fellows CD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MTG.Meeting = 'F05'
>
>
>|||Hi, Mike,
It is hard to say what causes the query slow performance without knowing the
DDL for your tables (what are the Indexes defined in the tables?). But one
thing for sure:
this part in the query
(CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
'C0001000', 'C0001300') or CM.Committee like 'C0%')
can be mofdified as :
CM.Committee like 'C0%'.
And if you can use "UNION ALL" instead of "UNION", it will have better
performance. See BOL Union for detail.
Perayu
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:CC4032BC-603F-4846-B6F0-E06E676B6609@.microsoft.com...
> Can anyone see anything that may be causing this view to return data
> extreamly slowly, or have any tips on increasing the performance of this
> particular view?
> Alter View dbo.PreRegistration_V as
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> Null Level2,
> Null Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and (CM.Committee in ('C0003500', 'C0000100', 'C0009000', 'C0003000',
> 'C0001000', 'C0001300') or CM.Committee like 'C0%')
> UNION
> SELECT C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> NULL Level1,
> NULL Level2,
> NULL Level3,
> NULL MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> CM.COM_Position,
> CM.Committee,
> CM.End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Committee_member CM WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and CM.Customer = MR.Ship_Customer
> and MTG.Meeting = 'F05'
> and CM.COM_Position IN ('001CHAIR','062PP','060PRES','061VP','0
63EXECVP')
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> MD.Level1,
> MD.Level2,
> MD.Level3,
> MBR.MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK),
> Committee_Member CM WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MD.Level2 in ('STAFF','IND','JR','STU')
> and MBR.Order_Status = 'A'
> and MTG.Meeting = 'F05'
> UNION
> SELECT
> C.Customer,
> C.Mail_Name,
> C.First_Name,
> C.Last_Name,
> null Level1,
> null Level2,
> null Level3,
> null MBRFULFILLST_Code,
> A.Formatted_Line_1,
> A.Formatted_Line_2,
> A.Formatted_Line_3,
> A.Formatted_Line_4,
> A.Formatted_Line_5,
> A.Formatted_Line_6,
> A.Formatted_Line_7,
> A.Country,
> MR.Order_No,
> MR.Order_Date,
> MTG.Meeting_Name_1,
> MTG.Meeting,
> MR.Order_Total,
> MR.User_N1,
> MR.User_N2,
> MR.User_n3,
> MR.User_N4,
> MR.Badge_Print_Status,
> Null COM_Position,
> Null Committee,
> Null End_Date
> from Customer C WITH(NOLOCK),
> meeting_registrant MR WITH(NOLOCK),
> Membership MBR WITH(NOLOCK),
> MemberShip_Detail MD WITH(NOLOCK),
> ACI_Current_Fellows CD WITH(NOLOCK),
> Address A WITH(NOLOCK),
> Meeting MTG WITH(NOLOCK)
> WHERE C.Customer = MR.Ship_Customer
> and C.Record_Type = 'I'
> and MR.Order_Status = 'A'
> and MR.Ship_Customer = MBR.Ship_Customer
> and MR.Ship_Address_ID = A.Address_ID
> and MR.Meeting = MTG.Meeting
> and MBR.Order_No = MD.order_No
> and MTG.Meeting = 'F05'
>
>
>