SLOW JOIN QUERY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SLOW JOIN QUERY

Author

Topic

neo_6053
Starting Member

24 Posts

Posted - 2008-11-21 : 02:41:00

i need to do a global search function that search through 20+ table.
and some table contain about 100K records. i used LEFT OUTER JOIN to do it . it's extreamly slow. is there anything i can do ?

my query is simple enuf

select col1,col2.....
from table1 LEFT OUTER JOIN
table2 on ... LEFT OUTER JOIN
table3 on ... LEFT OUTER JOIN
.....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 02:46:37

Maybe.
We will have to see the full query to determine.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 02:49:07

Analyse execution plan and see the bottleneck steps. Also see if your tables have appropriate indexes.

neo_6053
Starting Member

24 Posts

Posted - 2008-11-21 : 03:08:08

the problem is the result that i need to generate out, get from all the tables.
this is actually a view, then i search via this view.
this is a global search function which search through many parameters and generate result which contain columns from many tables too.

================== VIEW ====================
SELECT dbo.msaSpot.SpotCost, dbo.msaSpot.Currency, dbo.msaSpot.SpotStatus, dbo.msaSpot.SpotPosition, dbo.msaSpot.SpotDuration,
dbo.msaContract.ContractNo, dbo.msaContract.ContractCampaignNo, dbo.msaContract.ContractExecutiveID, dbo.msaContract.ContractOrderDate,
dbo.msaContract.BrandID, dbo.msaOrder.OrderLineNo, dbo.msaOrder.OrderDayPart, dbo.msaOrder.OrderPosition, dbo.msaOrder.OrderSpotType,
dbo.msaOrder.OrderSpotStatus, dbo.msaOrderProgramme.ProgrammeID AS OrderProgrammeID, dbo.msaOrderChannel.ChannelID AS OrderedChannel,
dbo.msaOrderDayPart.OrderDayPartStartTime, dbo.msaOrderDayPart.OrderDayPartEndTime, dbo.msaOrderRegion.RegionID AS OrderedRegion,
dbo.msaOrderBreak.BreakTypeID AS OrderedBreakID, dbo.msaCommercial.CommercialID, dbo.msaCommercial.CommercialTitle,
dbo.msaCommercial.CommercialCopyNo, dbo.msaCommercial.CommercialHouseNo, dbo.msaDeal.DealNo, dbo.msaTraffic.ChannelID,
dbo.msaTraffic.RegionID, dbo.msaTraffic.TrafficDate, dbo.msaTraffic.TrafficBilledTime, dbo.msaTraffic.BreakTypeID,
dbo.msaPlanning.ProgrammeID AS PlanningProgrammeID, dbo.msaBreakType.BreakType, dbo.msaOrderComponent.ComponentID,
dbo.msaComponent.ComponentDescription AS OrderedComponentDescription, dbo.msaPresentation.PresentationID,
dbo.msaPresentation.PresentationTime, dbo.msaTrafficPostTX.TrafficPostTXBilledTime, dbo.msaRefTeam.TeamID, dbo.msaRefTeam.TeamName,
dbo.msaExecutive.ExecutiveName, dbo.msaContractBrandCategory.BrandCategoryID, dbo.msaSpot.SpotTypeID, dbo.msaOrder.OrderID,
dbo.msaOrderWeek.OrderWeekStartDate AS Date, dbo.msaOrderWeek.OrderWeekEndDate, dbo.msaContractAgent.AgentID,
dbo.msaContractAdvertiser.AdvertiserID, dbo.msaTrafficPostTX.TrafficPostTXTime, dbo.msaTrafficComponent.TrafficComponentDescription
FROM dbo.msaSpot LEFT OUTER JOIN
dbo.msaOrder ON dbo.msaSpot.OrderID = dbo.msaOrder.OrderID LEFT OUTER JOIN
dbo.msaOrderChannel ON dbo.msaOrder.OrderID = dbo.msaOrderChannel.OrderID LEFT OUTER JOIN
dbo.msaOrderRegion ON dbo.msaOrder.OrderID = dbo.msaOrderRegion.OrderID LEFT OUTER JOIN
dbo.msaOrderProgramme ON dbo.msaOrder.OrderID = dbo.msaOrderProgramme.OrderID LEFT OUTER JOIN
dbo.msaOrderDayPart ON dbo.msaOrder.OrderID = dbo.msaOrderDayPart.OrderID LEFT OUTER JOIN
dbo.msaOrderBreak ON dbo.msaOrder.OrderID = dbo.msaOrderBreak.OrderID LEFT OUTER JOIN
dbo.msaOrderComponent ON dbo.msaOrder.OrderID = dbo.msaOrderComponent.OrderID LEFT OUTER JOIN
dbo.msaOrderWeek ON dbo.msaOrderWeek.OrderWeekID = dbo.msaSpot.OrderWeekID LEFT OUTER JOIN
dbo.msaComponent ON dbo.msaOrderComponent.ComponentID = dbo.msaComponent.ComponentID LEFT OUTER JOIN
dbo.msaContract ON dbo.msaOrder.ContractID = dbo.msaContract.ContractID LEFT OUTER JOIN
dbo.msaContractAdvertiser ON dbo.msaContractAdvertiser.ContractID = dbo.msaContract.ContractID LEFT OUTER JOIN
dbo.msaContractAgent ON dbo.msaContract.ContractID = dbo.msaContractAgent.ContractID LEFT OUTER JOIN
dbo.msaContractBrandCategory ON dbo.msaContract.ContractID = dbo.msaContractBrandCategory.ContractID LEFT OUTER JOIN
dbo.msaCommercial ON dbo.msaSpot.SpotCommericalID = dbo.msaCommercial.CommercialID LEFT OUTER JOIN
dbo.msaDeal ON dbo.msaDeal.DealID = dbo.msaContract.DealID LEFT OUTER JOIN
dbo.msaTraffic ON dbo.msaTraffic.TrafficID = dbo.msaSpot.TrafficID LEFT OUTER JOIN
dbo.msaBreakType ON dbo.msaTraffic.BreakTypeID = dbo.msaBreakType.BreakTypeID LEFT OUTER JOIN
dbo.msaPlanning ON dbo.msaTraffic.TrafficPlanningID = dbo.msaPlanning.PlanningID LEFT OUTER JOIN
dbo.msaPresentation ON dbo.msaTraffic.TrafficID = dbo.msaPresentation.TrafficID LEFT OUTER JOIN
dbo.msaSpotPostTX ON dbo.msaSpotPostTX.SpotID = dbo.msaSpot.SpotID LEFT OUTER JOIN
dbo.msaTrafficPostTX ON dbo.msaSpotPostTX.TrafficPostTXID = dbo.msaTrafficPostTX.TrafficPostTXID LEFT OUTER JOIN
dbo.msaTrafficComponent ON dbo.msaTraffic.TrafficID = dbo.msaTrafficComponent.TrafficID LEFT OUTER JOIN
dbo.msaExecutive ON dbo.msaContract.ContractExecutiveID = dbo.msaExecutive.ExecutiveID LEFT OUTER JOIN
dbo.msaRefTeam ON dbo.msaExecutive.ExecutiveTeamID = dbo.msaRefTeam.TeamID
WHERE (dbo.msaOrder.OrderID IS NOT NULL)
UNION ALL
SELECT msaSpot_1.SpotCost, msaSpot_1.Currency, msaSpot_1.SpotStatus, msaSpot_1.SpotPosition, msaSpot_1.SpotDuration, msaContract_1.ContractNo,
msaContract_1.ContractCampaignNo, msaContract_1.ContractExecutiveID, msaContract_1.ContractOrderDate, msaContract_1.BrandID,
msaOrder_1.OrderLineNo, msaOrder_1.OrderDayPart, msaOrder_1.OrderPosition, msaOrder_1.OrderSpotType, msaOrder_1.OrderSpotStatus,
msaOrderProgramme_1.ProgrammeID AS OrderProgrammeID, msaOrderChannel_1.ChannelID AS OrderedChannel,
msaOrderDayPart_1.OrderDayPartStartTime, msaOrderDayPart_1.OrderDayPartEndTime, msaOrderRegion_1.RegionID AS OrderedRegion,
msaOrderBreak_1.BreakTypeID AS OrderedBreakID, msaCommercial_1.CommercialID, msaCommercial_1.CommercialTitle,
msaCommercial_1.CommercialCopyNo, msaCommercial_1.CommercialHouseNo, msaDeal_1.DealNo, msaTraffic_1.ChannelID, msaTraffic_1.RegionID,
msaTraffic_1.TrafficDate AS Date, msaTraffic_1.TrafficBilledTime, msaTraffic_1.BreakTypeID, msaPlanning_1.ProgrammeID AS PlanningProgrammeID,
msaBreakType_1.BreakType, msaOrderComponent_1.ComponentID, msaComponent_1.ComponentDescription AS OrderedComponentDescription,
msaPresentation_1.PresentationID, msaPresentation_1.PresentationTime, msaTrafficPostTX_1.TrafficPostTXBilledTime, msaRefTeam_1.TeamID,
msaRefTeam_1.TeamName, msaExecutive_1.ExecutiveName, msaContractBrandCategory_1.BrandCategoryID, msaSpot_1.SpotTypeID,
msaOrder_1.OrderID, msaOrderWeek_1.OrderWeekStartDate, msaOrderWeek_1.OrderWeekEndDate, msaContractAgent_1.AgentID,
msaContractAdvertiser_1.AdvertiserID, msaTrafficPostTX_1.TrafficPostTXTime, msaTrafficComponent_1.TrafficComponentDescription
FROM dbo.msaSpot AS msaSpot_1 LEFT OUTER JOIN
dbo.msaOrder AS msaOrder_1 ON msaSpot_1.OrderID = msaOrder_1.OrderID LEFT OUTER JOIN
dbo.msaOrderChannel AS msaOrderChannel_1 ON msaOrder_1.OrderID = msaOrderChannel_1.OrderID LEFT OUTER JOIN
dbo.msaOrderRegion AS msaOrderRegion_1 ON msaOrder_1.OrderID = msaOrderRegion_1.OrderID LEFT OUTER JOIN
dbo.msaOrderProgramme AS msaOrderProgramme_1 ON msaOrder_1.OrderID = msaOrderProgramme_1.OrderID LEFT OUTER JOIN
dbo.msaOrderDayPart AS msaOrderDayPart_1 ON msaOrder_1.OrderID = msaOrderDayPart_1.OrderID LEFT OUTER JOIN
dbo.msaOrderBreak AS msaOrderBreak_1 ON msaOrder_1.OrderID = msaOrderBreak_1.OrderID LEFT OUTER JOIN
dbo.msaOrderComponent AS msaOrderComponent_1 ON msaOrder_1.OrderID = msaOrderComponent_1.OrderID LEFT OUTER JOIN
dbo.msaOrderWeek AS msaOrderWeek_1 ON msaOrderWeek_1.OrderWeekID = msaSpot_1.OrderWeekID LEFT OUTER JOIN
dbo.msaComponent AS msaComponent_1 ON msaOrderComponent_1.ComponentID = msaComponent_1.ComponentID LEFT OUTER JOIN
dbo.msaContract AS msaContract_1 ON msaOrder_1.ContractID = msaContract_1.ContractID LEFT OUTER JOIN
dbo.msaContractAdvertiser AS msaContractAdvertiser_1 ON msaContractAdvertiser_1.ContractID = msaContract_1.ContractID LEFT OUTER JOIN
dbo.msaContractAgent AS msaContractAgent_1 ON msaContract_1.ContractID = msaContractAgent_1.ContractID LEFT OUTER JOIN
dbo.msaContractBrandCategory AS msaContractBrandCategory_1 ON
msaContract_1.ContractID = msaContractBrandCategory_1.ContractID LEFT OUTER JOIN
dbo.msaCommercial AS msaCommercial_1 ON msaSpot_1.SpotCommericalID = msaCommercial_1.CommercialID LEFT OUTER JOIN
dbo.msaDeal AS msaDeal_1 ON msaDeal_1.DealID = msaContract_1.DealID LEFT OUTER JOIN
dbo.msaTraffic AS msaTraffic_1 ON msaTraffic_1.TrafficID = msaSpot_1.TrafficID LEFT OUTER JOIN
dbo.msaBreakType AS msaBreakType_1 ON msaTraffic_1.BreakTypeID = msaBreakType_1.BreakTypeID LEFT OUTER JOIN
dbo.msaPlanning AS msaPlanning_1 ON msaTraffic_1.TrafficPlanningID = msaPlanning_1.PlanningID LEFT OUTER JOIN
dbo.msaPresentation AS msaPresentation_1 ON msaTraffic_1.TrafficID = msaPresentation_1.TrafficID LEFT OUTER JOIN
dbo.msaSpotPostTX AS msaSpotPostTX_1 ON msaSpotPostTX_1.SpotID = msaSpot_1.SpotID LEFT OUTER JOIN
dbo.msaTrafficPostTX AS msaTrafficPostTX_1 ON msaSpotPostTX_1.TrafficPostTXID = msaTrafficPostTX_1.TrafficPostTXID LEFT OUTER JOIN
dbo.msaTrafficComponent AS msaTrafficComponent_1 ON msaTraffic_1.TrafficID = msaTrafficComponent_1.TrafficID LEFT OUTER JOIN
dbo.msaExecutive AS msaExecutive_1 ON msaContract_1.ContractExecutiveID = msaExecutive_1.ExecutiveID LEFT OUTER JOIN
dbo.msaRefTeam AS msaRefTeam_1 ON msaExecutive_1.ExecutiveTeamID = msaRefTeam_1.TeamID
WHERE (msaOrder_1.OrderID IS NULL)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 03:14:28

At a glance it seems to me you can delete UNION ALL and everything after that, and also delete the remaining WHERE clause.

E 12°55'05.63"
N 56°04'39.26"

neo_6053
Starting Member

24 Posts

Posted - 2008-11-21 : 03:17:33

actually it takes alot of time just to run the 1st portion . =_="

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 03:28:35

This is your code today

SELECT		dbo.msaSpot.SpotCost,
		dbo.msaSpot.Currency,
		dbo.msaSpot.SpotStatus,
		dbo.msaSpot.SpotPosition,
		dbo.msaSpot.SpotDuration,
		dbo.msaContract.ContractNo,
		dbo.msaContract.ContractCampaignNo,
		dbo.msaContract.ContractExecutiveID,
		dbo.msaContract.ContractOrderDate,
		dbo.msaContract.BrandID,
		dbo.msaOrder.OrderLineNo,
		dbo.msaOrder.OrderDayPart,
		dbo.msaOrder.OrderPosition,
		dbo.msaOrder.OrderSpotType,
		dbo.msaOrder.OrderSpotStatus,
		dbo.msaOrderProgramme.ProgrammeID AS OrderProgrammeID,
		dbo.msaOrderChannel.ChannelID AS OrderedChannel,
		dbo.msaOrderDayPart.OrderDayPartStartTime,
		dbo.msaOrderDayPart.OrderDayPartEndTime,
		dbo.msaOrderRegion.RegionID AS OrderedRegion,
		dbo.msaOrderBreak.BreakTypeID AS OrderedBreakID,
		dbo.msaCommercial.CommercialID,
		dbo.msaCommercial.CommercialTitle,
		dbo.msaCommercial.CommercialCopyNo,
		dbo.msaCommercial.CommercialHouseNo,
		dbo.msaDeal.DealNo,
		dbo.msaTraffic.ChannelID,
		dbo.msaTraffic.RegionID,
		dbo.msaTraffic.TrafficDate AS Date,
		dbo.msaTraffic.TrafficBilledTime,
		dbo.msaTraffic.BreakTypeID,
		dbo.msaPlanning.ProgrammeID AS PlanningProgrammeID,
		dbo.msaBreakType.BreakType,
		dbo.msaOrderComponent.ComponentID,
		dbo.msaComponent.ComponentDescription AS OrderedComponentDescription,
		dbo.msaPresentation.PresentationID,
		dbo.msaPresentation.PresentationTime,
		dbo.msaTrafficPostTX.TrafficPostTXBilledTime,
		dbo.msaRefTeam.TeamID,
		dbo.msaRefTeam.TeamName,
		dbo.msaExecutive.ExecutiveName,
		dbo.msaContractBrandCategory.BrandCategoryID,
		dbo.msaSpot.SpotTypeID,
		dbo.msaOrder.OrderID,
		dbo.msaOrderWeek.OrderWeekStartDate AS Date,
		dbo.msaOrderWeek.OrderWeekEndDate,
		dbo.msaContractAgent.AgentID,
		dbo.msaContractAdvertiser.AdvertiserID,
		dbo.msaTrafficPostTX.TrafficPostTXTime,
		dbo.msaTrafficComponent.TrafficComponentDescription
FROM		dbo.msaSpot
LEFT JOIN	dbo.msaOrder ON dbo.msaSpot.OrderID = dbo.msaOrder.OrderID
LEFT JOIN	dbo.msaOrderChannel ON dbo.msaOrder.OrderID = dbo.msaOrderChannel.OrderID
LEFT JOIN	dbo.msaOrderRegion ON dbo.msaOrder.OrderID = dbo.msaOrderRegion.OrderID
LEFT JOIN	dbo.msaOrderProgramme ON dbo.msaOrder.OrderID = dbo.msaOrderProgramme.OrderID
LEFT JOIN	dbo.msaOrderDayPart ON dbo.msaOrder.OrderID = dbo.msaOrderDayPart.OrderID
LEFT JOIN	dbo.msaOrderBreak ON dbo.msaOrder.OrderID = dbo.msaOrderBreak.OrderID
LEFT JOIN	dbo.msaOrderComponent ON dbo.msaOrder.OrderID = dbo.msaOrderComponent.OrderID
LEFT JOIN	dbo.msaOrderWeek ON dbo.msaOrderWeek.OrderWeekID = dbo.msaSpot.OrderWeekID
LEFT JOIN	dbo.msaComponent ON dbo.msaOrderComponent.ComponentID = dbo.msaComponent.ComponentID
LEFT JOIN	dbo.msaContract ON dbo.msaOrder.ContractID = dbo.msaContract.ContractID
LEFT JOIN	dbo.msaContractAdvertiser ON dbo.msaContractAdvertiser.ContractID = dbo.msaContract.ContractID
LEFT JOIN	dbo.msaContractAgent ON dbo.msaContract.ContractID = dbo.msaContractAgent.ContractID
LEFT JOIN	dbo.msaContractBrandCategory ON dbo.msaContract.ContractID = dbo.msaContractBrandCategory.ContractID
LEFT JOIN	dbo.msaCommercial ON dbo.msaSpot.SpotCommericalID = dbo.msaCommercial.CommercialID
LEFT JOIN	dbo.msaDeal ON dbo.msaDeal.DealID = dbo.msaContract.DealID
LEFT JOIN	dbo.msaTraffic ON dbo.msaTraffic.TrafficID = dbo.msaSpot.TrafficID
LEFT JOIN	dbo.msaBreakType ON dbo.msaTraffic.BreakTypeID = dbo.msaBreakType.BreakTypeID
LEFT JOIN	dbo.msaPlanning ON dbo.msaTraffic.TrafficPlanningID = dbo.msaPlanning.PlanningID
LEFT JOIN	dbo.msaPresentation ON dbo.msaTraffic.TrafficID = dbo.msaPresentation.TrafficID
LEFT JOIN	dbo.msaSpotPostTX ON dbo.msaSpotPostTX.SpotID = dbo.msaSpot.SpotID
LEFT JOIN	dbo.msaTrafficPostTX ON dbo.msaSpotPostTX.TrafficPostTXID = dbo.msaTrafficPostTX.TrafficPostTXID
LEFT JOIN	dbo.msaTrafficComponent ON dbo.msaTraffic.TrafficID = dbo.msaTrafficComponent.TrafficID
LEFT JOIN	dbo.msaExecutive ON dbo.msaContract.ContractExecutiveID = dbo.msaExecutive.ExecutiveID
LEFT JOIN	dbo.msaRefTeam ON dbo.msaExecutive.ExecutiveTeamID = dbo.msaRefTeam.TeamID
WHERE		dbo.msaOrder.OrderID IS NOT NULL

UNION ALL

SELECT		msaSpot_1.SpotCost,
		msaSpot_1.Currency,
		msaSpot_1.SpotStatus,
		msaSpot_1.SpotPosition,
		msaSpot_1.SpotDuration,
		msaContract_1.ContractNo,
		msaContract_1.ContractCampaignNo,
		msaContract_1.ContractExecutiveID,
		msaContract_1.ContractOrderDate,
		msaContract_1.BrandID,
		msaOrder_1.OrderLineNo,
		msaOrder_1.OrderDayPart,
		msaOrder_1.OrderPosition,
		msaOrder_1.OrderSpotType,
		msaOrder_1.OrderSpotStatus,
		msaOrderProgramme_1.ProgrammeID AS OrderProgrammeID,
		msaOrderChannel_1.ChannelID AS OrderedChannel,
		msaOrderDayPart_1.OrderDayPartStartTime,
		msaOrderDayPart_1.OrderDayPartEndTime,
		msaOrderRegion_1.RegionID AS OrderedRegion,
		msaOrderBreak_1.BreakTypeID AS OrderedBreakID,
		msaCommercial_1.CommercialID,
		msaCommercial_1.CommercialTitle,
		msaCommercial_1.CommercialCopyNo,
		msaCommercial_1.CommercialHouseNo,
		msaDeal_1.DealNo,
		msaTraffic_1.ChannelID,
		msaTraffic_1.RegionID,
		msaTraffic_1.TrafficDate AS Date,
		msaTraffic_1.TrafficBilledTime,
		msaTraffic_1.BreakTypeID,
		msaPlanning_1.ProgrammeID AS PlanningProgrammeID,
		msaBreakType_1.BreakType,
		msaOrderComponent_1.ComponentID,
		msaComponent_1.ComponentDescription AS OrderedComponentDescription,
		msaPresentation_1.PresentationID,
		msaPresentation_1.PresentationTime,
		msaTrafficPostTX_1.TrafficPostTXBilledTime,
		msaRefTeam_1.TeamID,
		msaRefTeam_1.TeamName,
		msaExecutive_1.ExecutiveName,
		msaContractBrandCategory_1.BrandCategoryID,
		msaSpot_1.SpotTypeID,
		msaOrder_1.OrderID,
		msaOrderWeek_1.OrderWeekStartDate,
		msaOrderWeek_1.OrderWeekEndDate,
		msaContractAgent_1.AgentID,
		msaContractAdvertiser_1.AdvertiserID,
		msaTrafficPostTX_1.TrafficPostTXTime,
		msaTrafficComponent_1.TrafficComponentDescription
FROM		dbo.msaSpot
LEFT JOIN	dbo.msaOrder ON msaSpot_1.OrderID = msaOrder_1.OrderID
LEFT JOIN	dbo.msaOrderChannel ON msaOrder_1.OrderID = msaOrderChannel_1.OrderID
LEFT JOIN	dbo.msaOrderRegion ON msaOrder_1.OrderID = msaOrderRegion_1.OrderID
LEFT JOIN	dbo.msaOrderProgramme ON msaOrder_1.OrderID = msaOrderProgramme_1.OrderID
LEFT JOIN	dbo.msaOrderDayPart ON msaOrder_1.OrderID = msaOrderDayPart_1.OrderID
LEFT JOIN	dbo.msaOrderBreak ON msaOrder_1.OrderID = msaOrderBreak_1.OrderID
LEFT JOIN	dbo.msaOrderComponent ON msaOrder_1.OrderID = msaOrderComponent_1.OrderID
LEFT JOIN	dbo.msaOrderWeek ON msaOrderWeek_1.OrderWeekID = msaSpot_1.OrderWeekID
LEFT JOIN	dbo.msaComponent ON msaOrderComponent_1.ComponentID = msaComponent_1.ComponentID
LEFT JOIN	dbo.msaContract ON msaOrder_1.ContractID = msaContract_1.ContractID
LEFT JOIN	dbo.msaContractAdvertiser ON msaContractAdvertiser_1.ContractID = msaContract_1.ContractID
LEFT JOIN	dbo.msaContractAgent ON msaContract_1.ContractID = msaContractAgent_1.ContractID
LEFT JOIN	dbo.msaContractBrandCategory ON msaContract_1.ContractID = msaContractBrandCategory_1.ContractID
LEFT JOIN	dbo.msaCommercial ON msaSpot_1.SpotCommericalID = msaCommercial_1.CommercialID
LEFT JOIN	dbo.msaDeal ON msaDeal_1.DealID = msaContract_1.DealID
LEFT JOIN	dbo.msaTraffic ON msaTraffic_1.TrafficID = msaSpot_1.TrafficID
LEFT JOIN	dbo.msaBreakType ON msaTraffic_1.BreakTypeID = msaBreakType_1.BreakTypeID
LEFT JOIN	dbo.msaPlanning ON msaTraffic_1.TrafficPlanningID = msaPlanning_1.PlanningID
LEFT JOIN	dbo.msaPresentation ON msaTraffic_1.TrafficID = msaPresentation_1.TrafficID
LEFT JOIN	dbo.msaSpotPostTX ON msaSpotPostTX_1.SpotID = msaSpot_1.SpotID
LEFT JOIN	dbo.msaTrafficPostTX ON msaSpotPostTX_1.TrafficPostTXID = msaTrafficPostTX_1.TrafficPostTXID
LEFT JOIN	dbo.msaTrafficComponent ON msaTraffic_1.TrafficID = msaTrafficComponent_1.TrafficID
LEFT JOIN	dbo.msaExecutive ON msaContract_1.ContractExecutiveID = msaExecutive_1.ExecutiveID
LEFT JOIN	dbo.msaRefTeam ON msaExecutive_1.ExecutiveTeamID = msaRefTeam_1.TeamID
WHERE		msaOrder_1.OrderID IS NULL

They are identical! Except the WHERE clause...

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 03:29:23

I think it will be possible to just use the first part without the WHERE clause.

SELECT		dbo.msaSpot.SpotCost,
		dbo.msaSpot.Currency,
		dbo.msaSpot.SpotStatus,
		dbo.msaSpot.SpotPosition,
		dbo.msaSpot.SpotDuration,
		dbo.msaContract.ContractNo,
		dbo.msaContract.ContractCampaignNo,
		dbo.msaContract.ContractExecutiveID,
		dbo.msaContract.ContractOrderDate,
		dbo.msaContract.BrandID,
		dbo.msaOrder.OrderLineNo,
		dbo.msaOrder.OrderDayPart,
		dbo.msaOrder.OrderPosition,
		dbo.msaOrder.OrderSpotType,
		dbo.msaOrder.OrderSpotStatus,
		dbo.msaOrderProgramme.ProgrammeID AS OrderProgrammeID,
		dbo.msaOrderChannel.ChannelID AS OrderedChannel,
		dbo.msaOrderDayPart.OrderDayPartStartTime,
		dbo.msaOrderDayPart.OrderDayPartEndTime,
		dbo.msaOrderRegion.RegionID AS OrderedRegion,
		dbo.msaOrderBreak.BreakTypeID AS OrderedBreakID,
		dbo.msaCommercial.CommercialID,
		dbo.msaCommercial.CommercialTitle,
		dbo.msaCommercial.CommercialCopyNo,
		dbo.msaCommercial.CommercialHouseNo,
		dbo.msaDeal.DealNo,
		dbo.msaTraffic.ChannelID,
		dbo.msaTraffic.RegionID,
		dbo.msaTraffic.TrafficDate AS Date,
		dbo.msaTraffic.TrafficBilledTime,
		dbo.msaTraffic.BreakTypeID,
		dbo.msaPlanning.ProgrammeID AS PlanningProgrammeID,
		dbo.msaBreakType.BreakType,
		dbo.msaOrderComponent.ComponentID,
		dbo.msaComponent.ComponentDescription AS OrderedComponentDescription,
		dbo.msaPresentation.PresentationID,
		dbo.msaPresentation.PresentationTime,
		dbo.msaTrafficPostTX.TrafficPostTXBilledTime,
		dbo.msaRefTeam.TeamID,
		dbo.msaRefTeam.TeamName,
		dbo.msaExecutive.ExecutiveName,
		dbo.msaContractBrandCategory.BrandCategoryID,
		dbo.msaSpot.SpotTypeID,
		dbo.msaOrder.OrderID,
		dbo.msaOrderWeek.OrderWeekStartDate AS Date,
		dbo.msaOrderWeek.OrderWeekEndDate,
		dbo.msaContractAgent.AgentID,
		dbo.msaContractAdvertiser.AdvertiserID,
		dbo.msaTrafficPostTX.TrafficPostTXTime,
		dbo.msaTrafficComponent.TrafficComponentDescription
FROM		dbo.msaSpot
LEFT JOIN	dbo.msaOrder ON dbo.msaSpot.OrderID = dbo.msaOrder.OrderID
LEFT JOIN	dbo.msaOrderChannel ON dbo.msaOrder.OrderID = dbo.msaOrderChannel.OrderID
LEFT JOIN	dbo.msaOrderRegion ON dbo.msaOrder.OrderID = dbo.msaOrderRegion.OrderID
LEFT JOIN	dbo.msaOrderProgramme ON dbo.msaOrder.OrderID = dbo.msaOrderProgramme.OrderID
LEFT JOIN	dbo.msaOrderDayPart ON dbo.msaOrder.OrderID = dbo.msaOrderDayPart.OrderID
LEFT JOIN	dbo.msaOrderBreak ON dbo.msaOrder.OrderID = dbo.msaOrderBreak.OrderID
LEFT JOIN	dbo.msaOrderComponent ON dbo.msaOrder.OrderID = dbo.msaOrderComponent.OrderID
LEFT JOIN	dbo.msaOrderWeek ON dbo.msaOrderWeek.OrderWeekID = dbo.msaSpot.OrderWeekID
LEFT JOIN	dbo.msaComponent ON dbo.msaOrderComponent.ComponentID = dbo.msaComponent.ComponentID
LEFT JOIN	dbo.msaContract ON dbo.msaOrder.ContractID = dbo.msaContract.ContractID
LEFT JOIN	dbo.msaContractAdvertiser ON dbo.msaContractAdvertiser.ContractID = dbo.msaContract.ContractID
LEFT JOIN	dbo.msaContractAgent ON dbo.msaContract.ContractID = dbo.msaContractAgent.ContractID
LEFT JOIN	dbo.msaContractBrandCategory ON dbo.msaContract.ContractID = dbo.msaContractBrandCategory.ContractID
LEFT JOIN	dbo.msaCommercial ON dbo.msaSpot.SpotCommericalID = dbo.msaCommercial.CommercialID
LEFT JOIN	dbo.msaDeal ON dbo.msaDeal.DealID = dbo.msaContract.DealID
LEFT JOIN	dbo.msaTraffic ON dbo.msaTraffic.TrafficID = dbo.msaSpot.TrafficID
LEFT JOIN	dbo.msaBreakType ON dbo.msaTraffic.BreakTypeID = dbo.msaBreakType.BreakTypeID
LEFT JOIN	dbo.msaPlanning ON dbo.msaTraffic.TrafficPlanningID = dbo.msaPlanning.PlanningID
LEFT JOIN	dbo.msaPresentation ON dbo.msaTraffic.TrafficID = dbo.msaPresentation.TrafficID
LEFT JOIN	dbo.msaSpotPostTX ON dbo.msaSpotPostTX.SpotID = dbo.msaSpot.SpotID
LEFT JOIN	dbo.msaTrafficPostTX ON dbo.msaSpotPostTX.TrafficPostTXID = dbo.msaTrafficPostTX.TrafficPostTXID
LEFT JOIN	dbo.msaTrafficComponent ON dbo.msaTraffic.TrafficID = dbo.msaTrafficComponent.TrafficID
LEFT JOIN	dbo.msaExecutive ON dbo.msaContract.ContractExecutiveID = dbo.msaExecutive.ExecutiveID
LEFT JOIN	dbo.msaRefTeam ON dbo.msaExecutive.ExecutiveTeamID = dbo.msaRefTeam.TeamID

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 03:30:17

It will cut down the time in almost half.

E 12°55'05.63"
N 56°04'39.26"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-11-21 : 03:49:51

How many rows do you get when doing a simple SELECT COUNT(*) FROM myViewName? If the count is "reasonable" it might be a good idea to create a cache-table of this data instead of having it as a view and then update it once a day or once an hour or whatever. The performance difference will be huge...but you will of course not have 100% up-to-date data...

- Lumbago

neo_6053
Starting Member

24 Posts

Posted - 2008-11-21 : 03:57:37

thx, Peso, for helping me out. :)
actually there is only 1 column that different between the 2 query.
when the OrderID IS NULL , i'll get the TrafficDate as Date and
when the OrderID IS NOT NULL, i'll get the OrderWeekStartDate as Date
but since the UNION make alot of trouble, i think i hv to get rid of it somehow.

=======================
hi, Lumbago
count = 1231263 records.
.NET give me timeout exception when i try to run COUNT in my code .. OTL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 04:02:15

No, you don't. The first SELECT statement determines the column names for all UNION'ed SELECT statement.
Even if you alias a column name in the second statement, it is still returned with same column name from first SELECT at same column position.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 04:05:33

Try this. I have added a new calculated column to the resultset which displays the correct value as Date depending on the former WHERE clause.

SELECT		dbo.msaSpot.SpotCost,
		dbo.msaSpot.Currency,
		dbo.msaSpot.SpotStatus,
		dbo.msaSpot.SpotPosition,
		dbo.msaSpot.SpotDuration,
		dbo.msaContract.ContractNo,
		dbo.msaContract.ContractCampaignNo,
		dbo.msaContract.ContractExecutiveID,
		dbo.msaContract.ContractOrderDate,
		dbo.msaContract.BrandID,
		dbo.msaOrder.OrderLineNo,
		dbo.msaOrder.OrderDayPart,
		dbo.msaOrder.OrderPosition,
		dbo.msaOrder.OrderSpotType,
		dbo.msaOrder.OrderSpotStatus,
		dbo.msaOrderProgramme.ProgrammeID AS OrderProgrammeID,
		dbo.msaOrderChannel.ChannelID AS OrderedChannel,
		dbo.msaOrderDayPart.OrderDayPartStartTime,
		dbo.msaOrderDayPart.OrderDayPartEndTime,
		dbo.msaOrderRegion.RegionID AS OrderedRegion,
		dbo.msaOrderBreak.BreakTypeID AS OrderedBreakID,
		dbo.msaCommercial.CommercialID,
		dbo.msaCommercial.CommercialTitle,
		dbo.msaCommercial.CommercialCopyNo,
		dbo.msaCommercial.CommercialHouseNo,
		dbo.msaDeal.DealNo,
		dbo.msaTraffic.ChannelID,
		dbo.msaTraffic.RegionID,
		dbo.msaTraffic.TrafficDate,
		dbo.msaTraffic.TrafficBilledTime,
		dbo.msaTraffic.BreakTypeID,
		dbo.msaPlanning.ProgrammeID AS PlanningProgrammeID,
		dbo.msaBreakType.BreakType,
		dbo.msaOrderComponent.ComponentID,
		dbo.msaComponent.ComponentDescription AS OrderedComponentDescription,
		dbo.msaPresentation.PresentationID,
		dbo.msaPresentation.PresentationTime,
		dbo.msaTrafficPostTX.TrafficPostTXBilledTime,
		dbo.msaRefTeam.TeamID,
		dbo.msaRefTeam.TeamName,
		dbo.msaExecutive.ExecutiveName,
		dbo.msaContractBrandCategory.BrandCategoryID,
		dbo.msaSpot.SpotTypeID,
		dbo.msaOrder.OrderID,
		dbo.msaOrderWeek.OrderWeekStartDate,
		dbo.msaOrderWeek.OrderWeekEndDate,
		dbo.msaContractAgent.AgentID,
		dbo.msaContractAdvertiser.AdvertiserID,
		dbo.msaTrafficPostTX.TrafficPostTXTime,
		dbo.msaTrafficComponent.TrafficComponentDescription,
		CASE
			WHEN msaOrder.OrderID IS NULL THEN dbo.msaTraffic.TrafficDate
			ELSE dbo.msaOrderWeek.OrderWeekStartDate
		END AS Date
FROM		dbo.msaSpot
LEFT JOIN	dbo.msaOrder ON dbo.msaSpot.OrderID = dbo.msaOrder.OrderID
LEFT JOIN	dbo.msaOrderChannel ON dbo.msaOrder.OrderID = dbo.msaOrderChannel.OrderID
LEFT JOIN	dbo.msaOrderRegion ON dbo.msaOrder.OrderID = dbo.msaOrderRegion.OrderID
LEFT JOIN	dbo.msaOrderProgramme ON dbo.msaOrder.OrderID = dbo.msaOrderProgramme.OrderID
LEFT JOIN	dbo.msaOrderDayPart ON dbo.msaOrder.OrderID = dbo.msaOrderDayPart.OrderID
LEFT JOIN	dbo.msaOrderBreak ON dbo.msaOrder.OrderID = dbo.msaOrderBreak.OrderID
LEFT JOIN	dbo.msaOrderComponent ON dbo.msaOrder.OrderID = dbo.msaOrderComponent.OrderID
LEFT JOIN	dbo.msaOrderWeek ON dbo.msaOrderWeek.OrderWeekID = dbo.msaSpot.OrderWeekID
LEFT JOIN	dbo.msaComponent ON dbo.msaOrderComponent.ComponentID = dbo.msaComponent.ComponentID
LEFT JOIN	dbo.msaContract ON dbo.msaOrder.ContractID = dbo.msaContract.ContractID
LEFT JOIN	dbo.msaContractAdvertiser ON dbo.msaContractAdvertiser.ContractID = dbo.msaContract.ContractID
LEFT JOIN	dbo.msaContractAgent ON dbo.msaContract.ContractID = dbo.msaContractAgent.ContractID
LEFT JOIN	dbo.msaContractBrandCategory ON dbo.msaContract.ContractID = dbo.msaContractBrandCategory.ContractID
LEFT JOIN	dbo.msaCommercial ON dbo.msaSpot.SpotCommericalID = dbo.msaCommercial.CommercialID
LEFT JOIN	dbo.msaDeal ON dbo.msaDeal.DealID = dbo.msaContract.DealID
LEFT JOIN	dbo.msaTraffic ON dbo.msaTraffic.TrafficID = dbo.msaSpot.TrafficID
LEFT JOIN	dbo.msaBreakType ON dbo.msaTraffic.BreakTypeID = dbo.msaBreakType.BreakTypeID
LEFT JOIN	dbo.msaPlanning ON dbo.msaTraffic.TrafficPlanningID = dbo.msaPlanning.PlanningID
LEFT JOIN	dbo.msaPresentation ON dbo.msaTraffic.TrafficID = dbo.msaPresentation.TrafficID
LEFT JOIN	dbo.msaSpotPostTX ON dbo.msaSpotPostTX.SpotID = dbo.msaSpot.SpotID
LEFT JOIN	dbo.msaTrafficPostTX ON dbo.msaSpotPostTX.TrafficPostTXID = dbo.msaTrafficPostTX.TrafficPostTXID
LEFT JOIN	dbo.msaTrafficComponent ON dbo.msaTraffic.TrafficID = dbo.msaTrafficComponent.TrafficID
LEFT JOIN	dbo.msaExecutive ON dbo.msaContract.ContractExecutiveID = dbo.msaExecutive.ExecutiveID
LEFT JOIN	dbo.msaRefTeam ON dbo.msaExecutive.ExecutiveTeamID = dbo.msaRefTeam.TeamID

E 12°55'05.63"
N 56°04'39.26"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-11-21 : 04:19:32

But what is your index situation? Are all the join columns indexed properly? If this view is queried quite frequently I'd *really* consider caching...

- Lumbago

neo_6053
Starting Member

24 Posts

Posted - 2008-11-21 : 04:26:27

COOL, Peso, why i didnt think of.. thx alot.

but still, it take 6+ minutes to run the query although it's abit faster now . :'(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 04:30:49

Have you checked present and missing indexes as Lumbago suggests?

E 12°55'05.63"
N 56°04'39.26"

neo_6053
Starting Member

24 Posts

Posted - 2008-11-21 : 04:41:49

actually i'm quite a newbie in indexes, tuning, performance.. and i'm not the person to create the table.
how can check whether the index exist. =_="

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-11-21 : 06:10:35

Oh...where does one begin...hm...I guess this article will be a good start:
http://www.sqlteam.com/article/sql-server-indexes-the-basics

Then:
In the tables list in management studio you have a + sign in front of every table name. Expand one of them (msaSpot is a good table to start with) and then expand Indexes. If the table has any indexes their names will show up here and you can right-click them and choose properties for each one. If you find the column OrderID as the first column listed in any of the indexes for msaSpot then this table should be indexed allright. Then do the same for all the other columns in your join statements.

- Lumbago

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources