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)
 Huge UNION query taking more time to execute

Author  Topic 

raghav_sai2002
Starting Member

18 Posts
Posted - 2009-10-27 : 04:45:49
HI,

i have a query in which i need to perform 5 unions as i have to join to different tables in each union.

the problem i am facing is in a our testing server, when i actually execute the parts of the individual select statements it execute like in 3 second each, but when i execute it as a set using union it took more than 3:50mins to complete, but this will definitely cause a timeout exception on the client side.


so i need to optimize it, please help me out on this one..

here is the complete code generated all this is generated using the dynamic sql...

SELECT ProgramID, CampaignID, CampaignName, [DivisionID], DivisionName
, [KeywordID], [Keyword], [Match Type], [Keyword Status]
, [AdGroupId], [AdGroup]
, [ChannelCampaignID], [Channel Campiagn]
, [Bid rule name], [Bid rule]
, [Bid rule ID]
, [Bid rule goal]
, Convert(decimal(10,2),[Max CPC threshold]) AS [Max CPC threshold]
, [Range]
, [Bid rule goal range]
, [ChannelID] , [Channel], [Sub ChannelID] , [Sub Channel]
,[Look back method], [Look back filter], [Conversion tag filter]
,[Day parting]
,( ((right('0' + rtrim(day([Bid rule start date])), 2)
+ '/' + right('0' + rtrim(month([Bid rule start date])), 2)
+ '/' + right( rtrim(year([Bid rule start date])),2))
+ '-' +
(right('0' + rtrim(day([Bid rule end date])), 2)
+ '/' + right('0' + rtrim(month([Bid rule end date])), 2)
+ '/' + right( rtrim(year([Bid rule end date])),2)) )) AS [Bid Rule Start End Date]
, [Bid rule status]
, [Bid rule start date]
, [Bid rule end date] FROM (

SELECT keywords.ProgramID as ProgramID
, substring(keywords.ProgramID,9, len(keywords.ProgramID)) as CampaignID
, LTRIM(RTRIM(keywords.KeywordId)) as KeywordID
, keywords.KeywordName as Keyword
, keywords.MatchType as [Match Type]
, ( CASE keywords.Status
WHEN 'Online' THEN 'Enabled'
WHEN 'Offline' THEN 'Paused'
WHEN '0' THEN 'Paused'
WHEN '2' THEN 'Enabled'
WHEN 'Active' THEN 'Enabled'
WHEN 'In Active' THEN 'Paused'
ELSE keywords.status END )
as [Keyword Status]
, d.campaignname as CampaignName
, brands.BrandID as [DivisionID]
, brands.BrandName as DivisionName
, LTRIM(RTRIM(categories.CategoryID)) as [AdGroupId]
, categories.CategoryName as [AdGroup]
, categories.ChannelCampID as [ChannelCampaignID]
, apiInfo.ChannelCampName as [Channel Campiagn]
, NULL as [Bid rule ID]
, NULL as [Bid rule name]
, NULL as [Bid rule]
, b.ChannelID as [ChannelID]
, b.ChannelName as [Channel]
, c.SubChannelID as [Sub ChannelID]
, c.SubChannelName as [Sub Channel]
, NULL as [Max CPC threshold]
, NULL AS [Range]
, NULL AS [Bid rule goal]
, NULL as [Bid rule goal range]
, NULL as [Look back method]
, NULL AS [Look back filter]
, NULL as [Conversion tag filter]
, NULL as [Day parting]
, NULL as [Bid rule start date]
, NULL as [Bid rule end date]
, NULL as [Bid rule status]

FROM tblChannelKeywords keywords WITH (NOLOCK)
INNER JOIN tblchannels b WITH (NOLOCK)
ON keywords.channelid = b.channelid
INNER JOIN tblsubchannels c WITH (NOLOCK)
ON keywords.subchannelid = c.subchannelid
AND keywords.channelid = c.channelid
INNER JOIN tblCampaigns d WITH (NOLOCK)
ON d.CampaignID = SUBSTRING(keywords.ProgramID, 9, LEN(keywords.ProgramID))
AND d.AccountID = LEFT(keywords.ProgramID, 5)
AND d.BrandID = LEFT(SUBSTRING(keywords.ProgramID, 6,LEN(keywords.ProgramID)), 3)
INNER JOIN tblbrands brands WITH (NOLOCK)
ON LEFT(SUBSTRING(keywords.ProgramID, 6,LEN(keywords.ProgramID)), 3) = brands.BrandID
AND brands.AccountID = LEFT(keywords.ProgramID, 5)
LEFT JOIN tblChannelCategories categories WITH (NOLOCK)
ON LTRIM(RTRIM(keywords.CategoryId)) = LTRIM(RTRIM(categories.CategoryId))
AND keywords.ProgramID = categories.ProgramID
AND keywords.ChannelID = categories.ChannelID
AND keywords.SubChannelID = categories.SubChannelID
AND keywords.ChannelCampID = categories.ChannelCampID
LEFT JOIN tblApiInfo apiInfo WITH (NOLOCK)
ON categories.ChannelCampID = apiInfo.ChannelCampID
AND categories.ProgramID = apiInfo.ProgramID

WHERE LEFT(keywords.ProgramID, 5) = 90659
AND keywords.ProgramID IN ( '90659407412' )
AND LTRIM(RTRIM(keywords.KeywordId)) NOT IN ( SELECT LTRIM(RTRIM(a.KeywordID)) FROM tblBidB2PRulesDetails a
INNER JOIN tblCampaigns b
ON a.CampaignID = b.CampaignID AND b.CampaignTypeID = 1
WHERE a.KeywordID IS NOT NULL AND a.AccountID = 90659
UNION
SELECT LTRIM(RTRIM(a.KeywordID)) FROM tblBidCPARulesDetails a
INNER JOIN tblCampaigns b
ON a.CampaignID = b.CampaignID AND b.CampaignTypeID = 1
WHERE a.KeywordID IS NOT NULL AND a.AccountID = 90659
UNION
SELECT LTRIM(RTRIM(a.KeywordID)) FROM tblBidCPCRulesDetails a
INNER JOIN tblCampaigns b
ON a.CampaignID = b.CampaignID AND b.CampaignTypeID = 1
WHERE a.KeywordID IS NOT NULL AND a.AccountID = 90659
UNION
SELECT LTRIM(RTRIM(a.KeywordID)) FROM tblBidCOSRulesDetails a
INNER JOIN tblCampaigns b
ON a.CampaignID = b.CampaignID AND b.CampaignTypeID = 1
WHERE a.KeywordID IS NOT NULL AND a.AccountID = 90659
UNION
SELECT LTRIM(RTRIM(a.KeywordID)) FROM tblBidROASRulesDetails a
INNER JOIN tblCampaigns b
ON a.CampaignID = b.CampaignID AND b.CampaignTypeID = 1
WHERE a.KeywordID IS NOT NULL AND a.AccountID = 90659 )
UNION
SELECT keywords.ProgramID as ProgramID
, substring(keywords.ProgramID,9, len(keywords.ProgramID)) as CampaignID
, LTRIM(RTRIM(keywords.KeywordId)) as KeywordID
, keywords.KeywordName as Keyword
, keywords.MatchType as [Match Type]
, ( CASE keywords.Status
WHEN 'Online' THEN 'Enabled'
WHEN 'Offline' THEN 'Paused'
WHEN '0' THEN 'Paused'
WHEN '2' THEN 'Enabled'
WHEN 'Active' THEN 'Enabled'
WHEN 'In Active' THEN 'Paused'
ELSE keywords.status END )
as [Keyword Status]
, d.campaignname as CampaignName
, brands.BrandID as [DivisionID]
, brands.BrandName as DivisionName
, LTRIM(RTRIM(categories.CategoryID)) as [AdGroupId]
, categories.CategoryName as [AdGroup]
, categories.ChannelCampID as [ChannelCampaignID]
, apiInfo.ChannelCampName as [Channel Campiagn]
, g.RuleID as [Bid rule ID]
, g.RuleName as [Bid rule name]
, i.RuleType as [Bid rule]
, b.ChannelID as [ChannelID]
, b.ChannelName as [Channel]
, c.SubChannelID as [Sub ChannelID]
, c.SubChannelName as [Sub Channel]
, g.ThresholdMaxCPC as [Max CPC threshold]
, (CASE WHEN g.RuleID IS NULL THEN NULL
ELSE g.GoalRange END ) AS [Range]

, CAST( g.DesiredPosition AS DECIMAL(18,2)) as [Bid rule goal]
, ISNULL((ISNULL(CAST(g.DesiredPosition AS VARCHAR(100)),'') + '( +/-' + ISNULL(CAST(g.GoalRange as VARCHAR(100)),'') + ')' ),'') as [Bid rule goal range]
, (l.LookBackName + '('
+ ( CASE WHEN l.LookBackName = 'Clicks' THEN CAST( CONVERT(decimal(10,0),g.LookBackValue) AS VARCHAR(100))
WHEN l.LookBackName = 'Conversions' THEN CAST( CONVERT(decimal(10,0),g.LookBackValue) AS VARCHAR(100))
ELSE ( '$' + CAST(CONVERT(decimal(10,2),g.LookBackValue) AS VARCHAR(100))) END) + ')'
) as [Look back method]
, ( CASE WHEN g.RuleID IS NULL THEN NULL
ELSE ISNULL(( SELECT COUNT(LookBackID) from tblBidLookBackDetails
Where ruleid = g.RuleID and ruletype = 'B2P'),0) END )
AS [Look back filter]
, ( CASE WHEN g.RuleID IS NULL THEN NULL
ELSE ISNULL(( SELECT (LEN(conv.RuleLevelValue) -
LEN(REPLACE(conv.RuleLevelValue, ';', '')))
FROM tblBidRuleLevels conv WHERE conv.RuleID = g.RuleID
AND conv.RuleLevelID = 6
AND conv.RuleType = i.RuleType ),0) END )
as [Conversion tag filter]
,( CASE WHEN g.RuleID IS NULL THEN NULL
ELSE ISNULL(( SELECT COUNT(b.Id) from tblBidDayRules a
INNER JOIN tblBidDayActionDetails b
ON a.RuleID = b.RuleId
Where Parentruleid = g.RuleID and ParentRuleType = i.RuleType ),0) END )
as [Day parting]
, g.StartDate as [Bid rule start date]
, g.EndDate as [Bid rule end date]
, (CASE WHEN g.RuleID IS NULL THEN NULL
WHEN f.Status = 1 THEN 'Enabled'
ELSE 'Paused' END ) as [Bid rule status]

FROM tblChannelKeywords keywords WITH (NOLOCK)
INNER JOIN tblchannels b WITH (NOLOCK)
ON keywords.channelid = b.channelid
INNER JOIN tblsubchannels c WITH (NOLOCK)
ON keywords.subchannelid = c.subchannelid
AND keywords.channelid = c.channelid
INNER JOIN tblCampaigns d WITH (NOLOCK)
ON d.CampaignID = SUBSTRING(keywords.ProgramID, 9, LEN(keywords.ProgramID))
AND d.AccountID = LEFT(keywords.ProgramID, 5)
AND d.BrandID = LEFT(SUBSTRING(keywords.ProgramID, 6,LEN(keywords.ProgramID)), 3)
INNER JOIN tblbrands brands WITH (NOLOCK)
ON LEFT(SUBSTRING(keywords.ProgramID, 6,LEN(keywords.ProgramID)), 3) = brands.BrandID
AND brands.AccountID = LEFT(keywords.ProgramID, 5)
LEFT JOIN tblChannelCategories categories WITH (NOLOCK)
ON LTRIM(RTRIM(keywords.CategoryId)) = LTRIM(RTRIM(categories.CategoryId))
AND keywords.ProgramID = categories.ProgramID
AND keywords.ChannelID = categories.ChannelID
AND keywords.SubChannelID = categories.SubChannelID
AND keywords.ChannelCampID = categories.ChannelCampID
LEFT JOIN tblApiInfo apiInfo WITH (NOLOCK)
ON categories.ChannelCampID = apiInfo.ChannelCampID
AND categories.ProgramID = apiInfo.ProgramID

INNER JOIN tblBidB2PRulesDetails f with (NOLOCK)
ON LTRIM(RTRIM(keywords.KeywordID)) = LTRIM(RTRIM(f.KeywordID))
AND f.CampaignID = d.CampaignID
AND keywords.MatchType = f.MatchType
AND categories.ChannelCampID = f.ChannelCampaignID
AND LTRIM(RTRIM(f.AdGroupID)) = LTRIM(RTRIM(categories.CategoryID))
AND f.DivisionID = brands.BrandID AND RuleLevelID = 4
AND d.CampaignTypeID = 1 AND f.AccountID = LEFT(LTRIM(RTRIM(keywords.ProgramID)), 5)
INNER JOIN tblBidB2PRules g with (NOLOCK)
ON f.RuleID = g.RuleID
AND g.Keyword = 1 AND g.IsDeleted = 0
LEFT JOIN tblBidLookBackDetails h with (NOLOCK)
ON g.RuleID = h.RuleID AND h.RuleType = 'B2P'
LEFT JOIN tblBidLookBacks l with (NOLOCK)
ON g.LookBackID = l.LookBackID
INNER JOIN tblBidRuleLevels i WITH (NOLOCK)
ON g.RuleID = i.RuleID
AND i.RuleType = 'B2P' AND i.RuleLevelID = 4
LEFT JOIN tblBidDayRules j WITH (NOLOCK)
ON g.RuleID = j.ParentRuleID AND j.IsDeleted = 0 AND j.ParentRuleType = 'B2P'
LEFT JOIN tblBidDayRulesDetails k with (NOLOCK)
ON j.RuleID = k.RuleID
AND LTRIM(RTRIM(keywords.KeywordID)) = LTRIM(RTRIM(k.KeywordID))
AND k.AccountID = LEFT(keywords.ProgramID, 5)
WHERE LEFT(keywords.ProgramID, 5) = 90659
AND keywords.ProgramID IN ( '90659407412' )
UNION
SELECT keywords.ProgramID as ProgramID
, substring(keywords.ProgramID,9, len(keywords.ProgramID)) as CampaignID
, LTRIM(RTRIM(keywords.KeywordId)) as KeywordID
, keywords.KeywordName as Keyword
, keywords.MatchType as [Match Type]
, ( CASE keywords.Status
WHEN 'Online' THEN 'Enabled'
WHEN 'Offline' THEN 'Paused'
WHEN '0' THEN 'Paused'
WHEN '2' THEN 'Enabled'
WHEN 'Active' THEN 'Enabled'
WHEN 'In Active' THEN 'Paused'
ELSE keywords.status END )
as [Keyword Status]
, d.campaignname as CampaignName
, brands.BrandID as [DivisionID]
, brands.BrandName as DivisionName
, LTRIM(RTRIM(categories.CategoryID)) as [AdGroupId]
, categories.CategoryName as [AdGroup]
, categories.ChannelCampID as [ChannelCampaignID]
, apiInfo.ChannelCampName as [Channel Campiagn]
, g.RuleID as [Bid rule ID]
, g.RuleName as [Bid rule name]
, i.RuleType as [Bid rule]
, b.ChannelID as [ChannelID]
, b.ChannelName as [Channel]
, c.SubChannelID as [Sub ChannelID]
, c.SubChannelName as [Sub Channel]
, g.ThresholdMaxCPC as [Max CPC threshold]
, (CASE WHEN g.RuleID IS NULL THEN NULL
ELSE g.GoalRange END ) AS [Range]

, CAST(g.DesiredCOS AS DECIMAL(18,2)) as [Bid rule goal]
, ISNULL(('$' + ISNULL(CAST(g.DesiredCOS AS VARCHAR(100)),'') + '( +/-' + '$' + ISNULL(CAST(g.GoalRange AS VARCHAR(100)),'') + ')' ),'') as [Bid rule goal range]
, (CASE WHEN i.RuleType = 'B2P' THEN NULL
ELSE l.LookBackName + '('
+ ( CASE WHEN l.LookBackName = 'Clicks' THEN CAST( CONVERT(decimal(10,0),g.LookBackValue) AS VARCHAR(100))
WHEN l.LookBackName = 'Conversions' THEN CAST( CONVERT(decimal(10,0),g.LookBackValue) AS VARCHAR(100))
ELSE ( '$' + CAST(CONVERT(decimal(10,2),g.LookBackValue) AS VARCHAR(100))) END) + ')'
END) as [Look back method]
, ( CASE WHEN g.RuleID IS NULL THEN NULL
ELSE ISNULL(( SELECT COUNT(LookBackID) from tblBidLookBackDetails
Where ruleid = g.RuleID and ruletype = i.RuleType),0) END )
AS [Look back filter]
, ( CASE WHEN g.RuleID IS NULL THEN NULL
ELSE ISNULL(( SELECT (LEN(conv.RuleLevelValue) -
LEN(REPLACE(conv.RuleLevelValue, ';', '')))
FROM tblBidRuleLevels conv WHERE conv.RuleID = g.RuleID
AND conv.RuleLevelID = 6
AND conv.RuleType = i.RuleType ),0) END )
as [Conversion tag filter]
,( CASE WHEN g.RuleID IS NULL THEN NULL
ELSE ISNULL(( SELECT COUNT(b.Id) from tblBidDayRules a
INNER JOIN tblBidDayActionDetails b
ON a.RuleID = b.RuleId
Where Parentruleid = g.RuleID and ParentRuleType = i.RuleType ),0) END )
as [Day parting]
, g.StartDate as [Bid rule start date]
, g.EndDate as [Bid rule end date]
, (CASE WHEN g.RuleID IS NULL THEN NULL
WHEN f.Status = 1 THEN 'Enabled'
ELSE 'Paused' END ) as [Bid rule status]

FROM tblChannelKeywords keywords WITH (NOLOCK)
INNER JOIN tblchannels b WITH (NOLOCK)
ON keywords.channelid = b.channelid
INNER JOIN tblsubchannels c WITH (NOLOCK)
ON keywords.subchannelid = c.subchannelid
AND keywords.channelid = c.channelid
INNER JOIN tblCampaigns d WITH (NOLOCK)
ON d.CampaignID = SUBSTRING(keywords.ProgramID, 9, LEN(keywords.ProgramID))
AND d.AccountID = LEFT(keywords.ProgramID, 5)
AND d.BrandID = LEFT(SUBSTRING(keywords.ProgramID, 6,LEN(keywords.ProgramID)), 3)
INNER JOIN tblbrands brands WITH (NOLOCK)
ON LEFT(SUBSTRING(keywords.ProgramID, 6,LEN(keywords.ProgramID)), 3) = brands.BrandID
AND brands.AccountID = LEFT(keywords.ProgramID, 5)
LEFT JOIN tblChannelCategories categories WITH (NOLOCK)
ON LTRIM(RTRIM(keywords.CategoryId)) = LTRIM(RTRIM(categories.CategoryId))
AND keywords.ProgramID = categories.ProgramID
AND keywords.ChannelID = categories.ChannelID
AND keywords.SubChannelID = categories.SubChannelID
AND keywords.ChannelCampID = categories.ChannelCampID
LEFT JOIN tblApiInfo apiInfo WITH (NOLOCK)
ON categories.ChannelCampID = apiInfo.ChannelCampID
AND categories.ProgramID = apiInfo.ProgramID

INNER JOIN tblBidCOSRulesDetails f with (NOLOCK)
ON LTRIM(RTRIM(keywords.KeywordID)) = LTRIM(RTRIM(f.KeywordID))
AND f.CampaignID = d.CampaignID AND keywords.MatchType = f.MatchType
AND categories.ChannelCampID = f.ChannelCampaignID
AND LTRIM(RTRIM(f.AdGroupID)) = LTRIM(RTRIM(categories.CategoryID))
AND f.DivisionID = brands.BrandID AND RuleLevelID = 4
AND d.CampaignTypeID = 1 AND f.AccountID = LEFT(keywords.ProgramID, 5)
INNER JOIN tblBidCOSRules g with (NOLOCK)
ON f.RuleID = g.RuleID
AND g.Keyword = 1 AND g.IsDeleted = 0
LEFT JOIN tblBidLookBackDetails h with (NOLOCK)
ON g.RuleID = h.RuleID AND h.RuleType = 'COS'
LEFT JOIN tblBidLookBacks l with (NOLOCK)
ON g.LookBackID = l.LookBackID
INNER JOIN tblBidRuleLevels i WITH (NOLOCK)
ON g.RuleID = i.RuleID
AND i.RuleType = 'COS' AND i.RuleLevelID = 4
LEFT JOIN tblBidDayRules j WITH (NOLOCK)
ON g.RuleID = j.ParentRuleID AND j.IsDeleted = 0 AND j.ParentRuleType = 'COS'
LEFT JOIN tblBidDayRulesDetails k with (NOLOCK)
ON j.RuleID = k.RuleID
AND LTRIM(RTRIM(keywords.KeywordID)) = LTRIM(RTRIM(k.KeywordID)) AND k.AccountID = LEFT(keywords.ProgramID, 5)
WHERE LEFT(keywords.ProgramID, 5) = 90659
AND keywords.ProgramID IN ( '90659407412' )
UNION
SELECT keywords.ProgramID as ProgramID
, substring(keywords.ProgramID,9, len(keywords.ProgramID)) as CampaignID
, LTRIM(RTRIM(keywords.KeywordId)) as KeywordID
, keywords.KeywordName as Keyword
, keywords.MatchType as [Match Type]
, ( CASE keywords.Status
WHEN 'Online' THEN 'Enabled'
WHEN 'Offline' THEN 'Paused'
WHEN '0' THEN 'Paused'
WHEN '2' THEN 'Enabled'
WHEN 'Active' THEN 'Enabled'
WHEN 'In Active' THEN 'Paused'
ELSE keywords.status END )
as [Keyword Status]
, d.campaignname as CampaignName
, brands.BrandID as [DivisionID]
, brands.BrandName as DivisionName
, LTRIM(RTRIM(categories.CategoryID)) as [AdGroupId]
, categories.CategoryName as [AdGroup]
, categories.ChannelCampID as [ChannelCampaignID]
, apiInfo.ChannelCampName as [Channel Campiagn]
, g.RuleID as [Bid rule ID]
, g.RuleName as [Bid rule name]
, i.RuleType as [Bid rule]
, b.ChannelID as [ChannelID]
, b.ChannelName as [Channel]
, c.SubChannelID as [Sub ChannelID]
, c.SubChannelName as [Sub Channel]
, g.ThresholdMaxCPC as [Max CPC threshold]
, (CASE WHEN g.RuleID IS NULL THEN NULL
ELSE g.GoalRange END ) AS [Range]

, CAST( g.DesiredCPA AS DECIMAL(18,2)) as [Bid rule goal]
, ISNULL(( '$' + CAST(g.DesiredCPA AS VARCHAR(100)) + '( +/-' + '$' + CAST(g.GoalRange AS VARCHAR(100)) + ')' ),'') as [Bid rule goal range]
, (CASE WHEN i.RuleType = 'B2P' THEN NULL
ELSE l.LookBackName + '('
+ ( CASE WHEN l.LookBackName = 'Clicks' THEN CAST( CONVERT(decimal(10,0),g.LookBackValue) AS VARCHAR(100))
WHEN l.LookBackName = 'Conversions' THEN CAST( CONVERT(decimal(10,0),g.LookBackValue) AS VARCHAR(100))
ELSE ( '$' + CAST(CONVERT(decimal(10,2),g.LookBackValue) AS VARCHAR(100))) END) + ')'
END) as [Look back method]
, ( CASE WHEN g.RuleID IS NULL THEN NULL
ELSE ISNULL(( SELECT COUNT(LookBackID) from tblBidLookBackDetails
Where ruleid = g.RuleID and ruletype = i.RuleType),0) END )
AS [Look back filter]
, ( CASE WHEN g.RuleID IS NULL THEN NULL
ELSE ISNULL(( SELECT (LEN(conv.RuleLevelValue) -
LEN(REPLACE(conv.RuleLevelValue, ';', '')))
FROM tblBidRuleLevels conv WHERE conv.RuleID = g.RuleID
AND conv.RuleLevelID = 6
AND conv.RuleType = i.RuleType ),0) END )
as [Conversion tag filter]
,( CASE WHEN g.RuleID IS NULL THEN NULL
ELSE ISNULL(( SELECT COUNT(b.Id) from tblBidDayRules a
INNER JOIN tblBidDayActionDetails b
ON a.RuleID = b.RuleId
Where Parentruleid = g.RuleID and ParentRuleType = i.RuleType ),0) END )
as [Day parting]
, g.StartDate as [Bid rule start date]
, g.EndDate as [Bid rule end date]
, (CASE WHEN g.RuleID IS NULL THEN NULL
WHEN f.Status = 1 THEN 'Enabled'
ELSE 'Paused' END ) as [Bid rule status]

FROM tblChannelKeywords keywords WITH (NOLOCK)
INNER JOIN tblchannels b WITH (NOLOCK)
ON keywords.channelid = b.channelid
INNER JOIN tblsubchannels c WITH (NOLOCK)
ON keywords.subchannelid = c.subchannelid
AND keywords.channelid = c.channelid
INNER JOIN tblCampaigns d WITH (NOLOCK)
ON d.CampaignID = SUBSTRING(keywords.ProgramID, 9, LEN(keywords.ProgramID))
AND d.AccountID = LEFT(keywords.ProgramID, 5)
AND d.BrandID = LEFT(SUBSTRING(keywords.ProgramID, 6,LEN(keywords.ProgramID)), 3)
INNER JOIN tblbrands brands WITH (NOLOCK)
ON LEFT(SUBSTRING(keywords.ProgramID, 6,LEN(keywords.ProgramID)), 3) = brands.BrandID
AND brands.AccountID = LEFT(keywords.ProgramID, 5)
LEFT JOIN tblChannelCategories categories WITH (NOLOCK)
ON LTRIM(RTRIM(keywords.CategoryId)) = LTRIM(RTRIM(categories.CategoryId))
AND keywords.ProgramID = categories.ProgramID
AND keywords.ChannelID = categories.ChannelID
AND keywords.SubChannelID = categories.SubChannelID
AND keywords.ChannelCampID = categories.ChannelCampID
LEFT JOIN tblApiInfo apiInfo WITH (NOLOCK)
ON categories.ChannelCampID = apiInfo.ChannelCampID
AND categories.ProgramID = apiInfo.ProgramID

INNER JOIN tblBidCPARulesDetails f with (NOLOCK)
ON LTRIM(RTRIM(keywords.KeywordID)) = LTRIM(RTRIM(f.KeywordID))
AND f.CampaignID = d.CampaignID AND keywords.MatchType = f.MatchType
AND categories.ChannelCampID = f.ChannelCampaignID
AND LTRIM(RTRIM(f.AdGroupID)) = LTRIM(RTRIM(categories.CategoryID))
AND f.DivisionID = brands.BrandID AND RuleLevelID = 4
AND d.CampaignTypeID = 1 AND f.AccountID = LEFT(keywords.ProgramID, 5)
INNER JOIN tblBidCPARules g with (NOLOCK)
ON f.RuleID = g.RuleID
AND g.Keyword = 1 AND g.IsDeleted = 0
LEFT JOIN tblBidLookBackDetails h with (NOLOCK)
ON g.RuleID = h.RuleID AND h.RuleType = 'CPA'
LEFT JOIN tblBidLookBacks l with (NOLOCK)
ON g.LookBackID = l.LookBackID
INNER JOIN tblBidRuleLevels i WITH (NOLOCK)
ON g.RuleID = i.RuleID
AND i.RuleType = 'CPA' AND i.RuleLevelID = 4
LEFT JOIN tblBidDayRules j WITH (NOLOCK)
ON g.RuleID = j.ParentRuleID AND j.IsDeleted = 0 AND j.ParentRuleType = 'CPA'
LEFT JOIN tblBidDayRulesDetails k with (NOLOCK)
ON j.RuleID = k.RuleID
AND LTRIM(RTRIM(keywords.KeywordID)) = LTRIM(RTRIM(k.KeywordID)) AND k.AccountID = LEFT(keywords.ProgramID, 5)
WHERE LEFT(keywords.ProgramID, 5) = 90659
AND keywords.ProgramID IN ( '90659407412' )
UNION
SELECT keywords.ProgramID as ProgramID
, substring(keywords.ProgramID,9, len(keywords.ProgramID)) as CampaignID
, LTRIM(RTRIM(keywords.KeywordId)) as KeywordID
, keywords.KeywordName as Keyword
, keywords.MatchType as [Match Type]
, ( CASE keywords.Status
WHEN 'Online' THEN 'Enabled'
WHEN 'Offline' THEN 'Paused'
WHEN '0' THEN 'Paused'
WHEN '2' THEN 'Enabled'
WHEN 'Active' THEN 'Enabled'
WHEN 'In Active' THEN 'Paused'
ELSE keywords.status END )
as [Keyword Status]
, d.campaignname as CampaignName
, brands.BrandID as [DivisionID]
, brands.BrandName as DivisionName
, LTRIM(RTRIM(categories.CategoryID)) as [AdGroupId]
, categories.CategoryName as [AdGroup]
, categories.ChannelCampID as [ChannelCampaignID]
, apiInfo.ChannelCampName as [Channel Campiagn]
, g.RuleID as [Bid rule ID]
, g.RuleName as [Bid rule name]
, i.RuleType as [Bid rule]
, b.ChannelID as [ChannelID]
, b.ChannelName as [Channel]
, c.SubChannelID as [Sub ChannelID]
, c.SubChannelName as [Sub Channel]
, g.ThresholdMaxCPC as [Max CPC threshold]
, (CASE WHEN g.RuleID IS NULL THEN NULL
ELSE g.GoalRange END ) AS [Range]

, CAST( g.DesiredCPC AS DECIMAL(18,2)) as [Bid rule goal]
, ISNULL(( '$' + CAST(g.DesiredCPC AS VARCHAR(100)) + '( +/-' + '$' + CAST(g.GoalRange AS VARCHAR(100)) + ')' ),'') as [Bid rule goal range]
, (CASE WHEN i.RuleType = 'B2P' THEN NULL
ELSE l.LookBackName + '('
+ ( CASE WHEN l.LookBackName = 'Clicks' THEN CAST( CONVERT(decimal(10,0),g.LookBackValue) AS VARCHAR(100))
WHEN l.LookBackName = 'Conversions' THEN CAST( CONVERT(decimal(10,0),g.LookBackValue) AS VARCHAR(100))
ELSE ( '$' + CAST(CONVERT(decimal(10,2),g.LookBackValue) AS VARCHAR(100))) END) + ')'
END) as [Look back method]
, ( CASE WHEN g.RuleID IS NULL THEN NULL
ELSE ISNULL(( SELECT COUNT(LookBackID) from tblBidLookBackDetails
Where ruleid = g.RuleID and ruletype = i.RuleType),0) END )
AS [Look back filter]
, ( CASE WHEN g.RuleID IS NULL THEN NULL
ELSE ISNULL(( SELECT (LEN(conv.RuleLevelValue) -
LEN(REPLACE(conv.RuleLevelValue, ';', '')))
FROM tblBidRuleLevels conv WHERE conv.RuleID = g.RuleID
AND conv.RuleLevelID = 6
AND conv.RuleType = i.RuleType ),0) END )
as [Conversion tag filter]
,( CASE WHEN g.RuleID IS NULL THEN NULL
ELSE ISNULL(( SELECT COUNT(b.Id) from tblBidDayRules a
INNER JOIN tblBidDayActionDetails b
ON a.RuleID = b.RuleId
Where Parentruleid = g.RuleID and ParentRuleType = i.RuleType ),0) END )
as [Day parting]
, g.StartDate as [Bid rule start date]
, g.EndDate as [Bid rule end date]
, (CASE WHEN g.RuleID IS NULL THEN NULL
WHEN f.Status = 1 THEN 'Enabled'
ELSE 'Paused' END ) as [Bid rule status]

FROM tblChannelKeywords keywords WITH (NOLOCK)
INNER JOIN tblchannels b WITH (NOLOCK)
ON keywords.channelid = b.channelid
INNER JOIN tblsubchannels c WITH (NOLOCK)
ON keywords.subchannelid = c.subchannelid
AND keywords.channelid = c.channelid
INNER JOIN tblCampaigns d WITH (NOLOCK)
ON d.CampaignID = SUBSTRING(keywords.ProgramID, 9, LEN(keywords.ProgramID))
AND d.AccountID = LEFT(keywords.ProgramID, 5)
AND d.BrandID = LEFT(SUBSTRING(keywords.ProgramID, 6,LEN(keywords.ProgramID)), 3)
INNER JOIN tblbrands brands WITH (NOLOCK)
ON LEFT(SUBSTRING(keywords.ProgramID, 6,LEN(keywords.ProgramID)), 3) = brands.BrandID
AND brands.AccountID = LEFT(keywords.ProgramID, 5)
LEFT JOIN tblChannelCategories categories WITH (NOLOCK)
ON LTRIM(RTRIM(keywords.CategoryId)) = LTRIM(RTRIM(categories.CategoryId))
AND keywords.ProgramID = categories.ProgramID
AND keywords.ChannelID = categories.ChannelID
AND keywords.SubChannelID = categories.SubChannelID
AND keywords.ChannelCampID = categories.ChannelCampID
LEFT JOIN tblApiInfo apiInfo WITH (NOLOCK)
ON categories.ChannelCampID = apiInfo.ChannelCampID
AND categories.ProgramID = apiInfo.ProgramID

INNER JOIN tblBidCPCRulesDetails f with (NOLOCK)
ON LTRIM(RTRIM(keywords.KeywordID)) = LTRIM(RTRIM(f.KeywordID))
AND f.CampaignID = d.CampaignID AND keywords.MatchType = f.MatchType
AND categories.ChannelCampID = f.ChannelCampaignID
AND LTRIM(RTRIM(f.AdGroupID)) = LTRIM(RTRIM(categories.CategoryID))
AND f.DivisionID = brands.BrandID AND RuleLevelID = 4
AND d.CampaignTypeID = 1 AND f.AccountID = LEFT(keywords.ProgramID, 5)
INNER JOIN tblBidCPCRules g with (NOLOCK)
ON f.RuleID = g.RuleID
AND g.Keyword = 1 AND g.IsDeleted = 0
LEFT JOIN tblBidLookBackDetails h with (NOLOCK)
ON g.RuleID = h.RuleID AND h.RuleType = 'CPC'
LEFT JOIN tblBidLookBacks l with (NOLOCK)
ON g.LookBackID = l.LookBackID
INNER JOIN tblBidRuleLevels i WITH (NOLOCK)
ON g.RuleID = i.RuleID
AND i.RuleType = 'CPC' AND i.RuleLevelID = 4
LEFT JOIN tblBidDayRules j WITH (NOLOCK)
ON g.RuleID = j.ParentRuleID AND j.IsDeleted = 0 AND j.ParentRuleType = 'CPC'
LEFT JOIN tblBidDayRulesDetails k with (NOLOCK)
ON j.RuleID = k.RuleID
AND LTRIM(RTRIM(keywords.KeywordID)) = LTRIM(RTRIM(k.KeywordID)) AND k.AccountID = LEFT(keywords.ProgramID, 5)
WHERE LEFT(keywords.ProgramID, 5) = 90659
AND keywords.ProgramID IN ( '90659407412' )
UNION
SELECT keywords.ProgramID as ProgramID
, substring(keywords.ProgramID,9, len(keywords.ProgramID)) as CampaignID
, LTRIM(RTRIM(keywords.KeywordId)) as KeywordID
, keywords.KeywordName as Keyword
, keywords.MatchType as [Match Type]
, ( CASE keywords.Status
WHEN 'Online' THEN 'Enabled'
WHEN 'Offline' THEN 'Paused'
WHEN '0' THEN 'Paused'
WHEN '2' THEN 'Enabled'
WHEN 'Active' THEN 'Enabled'
WHEN 'In Active' THEN 'Paused'
ELSE keywords.status END )
as [Keyword Status]
, d.campaignname as CampaignName
, brands.BrandID as [DivisionID]
, brands.BrandName as DivisionName
, LTRIM(RTRIM(categories.CategoryID)) as [AdGroupId]
, categories.CategoryName as [AdGroup]
, categories.ChannelCampID as [ChannelCampaignID]
, apiInfo.ChannelCampName as [Channel Campiagn]
, g.RuleID as [Bid rule ID]
, g.RuleName as [Bid rule name]
, i.RuleType as [Bid rule]
, b.ChannelID as [ChannelID]
, b.ChannelName as [Channel]
, c.SubChannelID as [Sub ChannelID]
, c.SubChannelName as [Sub Channel]
, g.ThresholdMaxCPC as [Max CPC threshold]
, (CASE WHEN g.RuleID IS NULL THEN NULL
ELSE g.GoalRange END ) AS [Range]

, CAST ( g.DesiredROAS AS DECIMAL(18,2)) as [Bid rule goal]
, ISNULL(( CAST(g.DesiredROAS AS VARCHAR(100)) + '%' + '( +/-' + CAST(g.GoalRange AS VARCHAR(100)) + '%' + ')' ),'') as [Bid rule goal range]
, (CASE WHEN i.RuleType = 'B2P' THEN NULL
ELSE l.LookBackName + '('
+ ( CASE WHEN l.LookBackName = 'Clicks' THEN CAST( CONVERT(decimal(10,0),g.LookBackValue) AS VARCHAR(100))
WHEN l.LookBackName = 'Conversions' THEN CAST( CONVERT(decimal(10,0),g.LookBackValue) AS VARCHAR(100))
ELSE ( '$' + CAST(CONVERT(decimal(10,2),g.LookBackValue) AS VARCHAR(100))) END) + ')'
END) as [Look back method]
, ( CASE WHEN g.RuleID IS NULL THEN NULL
ELSE ISNULL(( SELECT COUNT(LookBackID) from tblBidLookBackDetails
Where ruleid = g.RuleID and ruletype = i.RuleType),0) END )
AS [Look back filter]
, ( CASE WHEN g.RuleID IS NULL THEN NULL
ELSE ISNULL(( SELECT (LEN(conv.RuleLevelValue) -
LEN(REPLACE(conv.RuleLevelValue, ';', '')))
FROM tblBidRuleLevels conv WHERE conv.RuleID = g.RuleID
AND conv.RuleLevelID = 6
AND conv.RuleType = i.RuleType ),0) END )
as [Conversion tag filter]
,( CASE WHEN g.RuleID IS NULL THEN NULL
ELSE ISNULL(( SELECT COUNT(b.Id) from tblBidDayRules a
INNER JOIN tblBidDayActionDetails b
ON a.RuleID = b.RuleId
Where Parentruleid = g.RuleID and ParentRuleType = i.RuleType ),0) END )
as [Day parting]
, g.StartDate as [Bid rule start date]
, g.EndDate as [Bid rule end date]
, (CASE WHEN g.RuleID IS NULL THEN NULL
WHEN f.Status = 1 THEN 'Enabled'
ELSE 'Paused' END ) as [Bid rule status]

FROM tblChannelKeywords keywords WITH (NOLOCK)
INNER JOIN tblchannels b WITH (NOLOCK)
ON keywords.channelid = b.channelid
INNER JOIN tblsubchannels c WITH (NOLOCK)
ON keywords.subchannelid = c.subchannelid
AND keywords.channelid = c.channelid
INNER JOIN tblCampaigns d WITH (NOLOCK)
ON d.CampaignID = SUBSTRING(keywords.ProgramID, 9, LEN(keywords.ProgramID))
AND d.AccountID = LEFT(keywords.ProgramID, 5)
AND d.BrandID = LEFT(SUBSTRING(keywords.ProgramID, 6,LEN(keywords.ProgramID)), 3)
INNER JOIN tblbrands brands WITH (NOLOCK)
ON LEFT(SUBSTRING(keywords.ProgramID, 6,LEN(keywords.ProgramID)), 3) = brands.BrandID
AND brands.AccountID = LEFT(keywords.ProgramID, 5)
LEFT JOIN tblChannelCategories categories WITH (NOLOCK)
ON LTRIM(RTRIM(keywords.CategoryId)) = LTRIM(RTRIM(categories.CategoryId))
AND keywords.ProgramID = categories.ProgramID
AND keywords.ChannelID = categories.ChannelID
AND keywords.SubChannelID = categories.SubChannelID
AND keywords.ChannelCampID = categories.ChannelCampID
LEFT JOIN tblApiInfo apiInfo WITH (NOLOCK)
ON categories.ChannelCampID = apiInfo.ChannelCampID
AND categories.ProgramID = apiInfo.ProgramID

INNER JOIN tblBidROASRulesDetails f with (NOLOCK)
ON LTRIM(RTRIM(keywords.KeywordID)) = LTRIM(RTRIM(f.KeywordID))
AND f.CampaignID = d.CampaignID AND keywords.MatchType = f.MatchType
AND categories.ChannelCampID = f.ChannelCampaignID
AND LTRIM(RTRIM(f.AdGroupID)) = LTRIM(RTRIM(categories.CategoryID))
AND f.DivisionID = brands.BrandID AND RuleLevelID = 4
AND d.CampaignTypeID = 1 AND f.AccountID = LEFT(keywords.ProgramID, 5)
INNER JOIN tblBidROASRules g with (NOLOCK)
ON f.RuleID = g.RuleID
AND g.Keyword = 1 AND g.IsDeleted = 0
LEFT JOIN tblBidLookBackDetails h with (NOLOCK)
ON g.RuleID = h.RuleID AND h.RuleType = 'ROAS'
LEFT JOIN tblBidLookBacks l with (NOLOCK)
ON g.LookBackID = l.LookBackID
INNER JOIN tblBidRuleLevels i WITH (NOLOCK)
ON g.RuleID = i.RuleID
AND i.RuleType = 'ROAS' AND i.RuleLevelID = 4
LEFT JOIN tblBidDayRules j WITH (NOLOCK)
ON g.RuleID = j.ParentRuleID AND j.IsDeleted = 0 AND j.ParentRuleType = 'ROAS'
LEFT JOIN tblBidDayRulesDetails k with (NOLOCK)
ON j.RuleID = k.RuleID
AND LTRIM(RTRIM(keywords.KeywordID)) = LTRIM(RTRIM(k.KeywordID)) AND k.AccountID = LEFT(keywords.ProgramID, 5)
WHERE LEFT(keywords.ProgramID, 5) = 90659
AND keywords.ProgramID IN ( '90659407412' )
) a
GROUP BY ProgramID, CampaignID, CampaignName, [DivisionID], DivisionName
, [KeywordID], [Keyword], [Match Type], [Keyword Status]
, [AdGroupId], [AdGroup]
, [ChannelCampaignID], [Channel Campiagn]
, [Bid rule name], [Bid rule]
, [Bid rule ID]
, [Bid rule goal]
, Convert(decimal(10,2),[Max CPC threshold])
, [Range]
, [Bid rule goal range]
, [ChannelID] , [Channel], [Sub ChannelID] , [Sub Channel]
,[Look back method], [Look back filter], [Conversion tag filter]
,[Day parting]
,( ((right('0' + rtrim(day([Bid rule start date])), 2)
+ '/' + right('0' + rtrim(month([Bid rule start date])), 2)
+ '/' + right( rtrim(year([Bid rule start date])),2))
+ '-' +
(right('0' + rtrim(day([Bid rule end date])), 2)
+ '/' + right('0' + rtrim(month([Bid rule end date])), 2)
+ '/' + right( rtrim(year([Bid rule end date])),2)) ))
, [Bid rule status]
, [Bid rule start date]
, [Bid rule end date]

ORDER BY [Keyword] asc

-----------------------------------

the table tblChannelKeywords, tblChannelCategories , tblApiInfo tables has got millions of records.

at max we fetch abt 25000 reocrds from the db at the moment.....

please help me out on this one.

sanoj_av
Posting Yak Master

118 Posts
Posted - 2009-10-27 : 05:05:08
Have you looked in to the Query execution plan to know what is happening inside and which part is taking more time and resources?
Go to Top of Page

raghav_sai2002
Starting Member

18 Posts
Posted - 2009-10-28 : 03:39:52
Hi,
Thank you for the reply, i had a look at the execution plan in the local server on the same query. it only take like 4 seconds on the local server ( as the data is limited only to 5k), but our testing server is very slow .. and it doesnt event finish after 4mins and the amount of data is like 20k records.


as per the execution plan ( on the local environment).. i can understand is that the max time taken is for the scanning of the clustered index in each select which is about 16% and then the sort operation about 1% each.

basically the clustered index scan is taking longer time.. as the index is placed on the ID which is and identity column. but the where condition is on the programid which is varchar(11), so it is taking time to rearrange data and then sort and then filter it. and this is happening for all the 5 unions in the query.



this is what it says

PREDICATE

CONVERT_IMPLICIT(int,SUbSTRING([tblChannelKeywords].ProgramID AS [Keywords].[ProgramID],(1), (5)),0) = (88357)

OBJECT
[tblChannelKeywords].[PK_tblChannelKeyWords][Keywords]



please let me know how to improve the performance of the query.

does it help if i use views of some sort for the joins.

Thank you for your help
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-10-28 : 11:45:48
scary. this is what I would suggest.

1. Do you have appropriate indexes on the fields you are joining on
2. What is this: keywords.ProgramID IN ( '90659407412' ) Why are you doing that instead of
keywords.ProgramID = '90659407412' Is there a time when it might be more than one value as in
keywords.ProgramID IN ( '90659407412',''90659407413', '90659407414' )
3. You need lots of views where you do all the CONVERT CAST + - /
4. this is bad:
AND LTRIM(RTRIM(keywords.KeywordID)) = LTRIM(RTRIM(k.KeywordID)) AND k.AccountID = LEFT(keywords.ProgramID, 5) this will eat you alive doing joins on this type of formatted field values. You might have no control over the database, but you can do this in views . So in your view you do SELECT LTRIM(RTRIM(keywords.KeywordID)) AS KeyworkID then in your huge query here you will say
vw_keywords.KeywordID = k.KeywordID
it will be clean.




<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

denis_the_thief
Aged Yak Warrior

596 Posts
Posted - 2009-10-28 : 12:17:20
- views won't likely help - likely be the same time - because sql server makes 1 execution path per query, it will just do a text replace of your view name with the query associated with the view

- change Union to Union All, union all is generally quicker, may be significantly quicker or might only be slightly quicker

- run and time pieces of your SQL, which may give some insight as to what exactly is making it slow

- you could also try, if you can separate the query into smaller fast pieces, insert these smaller queries separately into a temp table, then return a final query on the temp table
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-10-28 : 12:20:53
yes I agree with denis on the views, temp table will help you a lot. and if you use indexes on your temp tables, then it will speed things.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

denis_the_thief
Aged Yak Warrior

596 Posts
Posted - 2009-10-28 : 12:33:36
quote:
Originally posted by yosiasz

and if you use indexes on your temp tables, then it will speed things.


Agree, if your Temp Tables get joined in a select. I've seen some slow Stored Procs, reason being that the Temp Tables weren't indexed and were used with joins.

And if the where is on the ProgramID and ProgramID has no index, an index on ProgramID could help.
Go to Top of Page

raghav_sai2002
Starting Member

18 Posts
Posted - 2009-10-29 : 00:32:18
Hi,

Thank you for the replies....

and regarding the questions
1. the indexes are on the primary keys... which is ID Column in the db and is not the one i am using for the join. this is the way they have developed it before, i know it will be better if we had the index on the programId it would have been helpful, but programid isn't unique all the time, so thats why our dba just made and identity column as a primary key and then placed an index over it and this is consuming more time.

2. i have to use something like keywords.ProgramID IN ( '90659407412') because the there could be a situation when it could be like keywords.ProgramID IN ( '90659407412',''90659407413', '90659407414' )
3. i will try to avoid the converts and cast wherever i can and i will only use when it is absolutely necessary.
4. and coming to this LTRIM(RTRIM(keywords.KeywordID)) = LTRIM(RTRIM(k.KeywordID))
AND k.AccountID = LEFT(keywords.ProgramID, 5), it was the only way i can do that at that moment, because we have to use yahoo and Google services to retrieve some data, and this data .. especially the keyword bit is retrieved and stored with lots of trailing spaces... like "90659407412 ", so when i was trying to join it in the normal way... i wasnt getting all the records.

i will definitely try to create some views and try to improve performance...

if it doesn't work then i would prefer to create a temp table, but our dba wont accept this for some reason.. so i will look into table variables or create tables with unique ids and drop them on exit.


but i have got one doubt, the same query would work on the same server, on the same number of tables
when the number of records retrieved by the filter condition ( keywords.ProgramID IN ( '90659407412',''90659407413', '90659407414' ) ) are less, but it won't work if the number of records are more..., is it something to do with the union... like the first select in the union has to store records in the memory, and then it has to fetch the next select records and store them in the memory and so on.. so is it something to do with the memory...


Thank you
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-10-29 : 10:09:06
Your dba won't accept temp tables? Let me talk to him, put him on the phone so I can reach through the phone and smack him. What kind of joke is that? Anyways, so they don't have index on programid and they do not want you to create temp table so you have flexibility to create indexes. Go with the table creation and drop at end option then. You will definitely see performance improvement. When you retrieve the data form yahoo and Google, is it dumped in a staging table? Do you have control over that? If so do not save it with trailing blanks then you can avoid the trim cast convert stuff. I cannot confirm your doubt. My approach would be to break it down. Create table with index dump data into it. Create next table with index dump data into it. Then at the end do all the unions and see what happens. Then at end drop tables.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

denis_the_thief
Aged Yak Warrior

596 Posts
Posted - 2009-10-29 : 10:32:10
quote:
Originally posted by raghav_sai2002

Hi,

Thank you for the replies....

and regarding the questions
1. the indexes are on the primary keys...



I think part of the confusion here is over what is an index. There are different kinds, they can be unique but they can also be non-unique! A Primary Key is a type of index where yes, there can only be one per table. A clustered index is also one per table. However non-unique indexes (which are for the purpose of performance enhancement) you can have as many as you wish per table (or a limit of something like 1024 maybe?). So in this case, you can keep the primary Key as is. And add a non-unique key on ProgramID. If you suggest that to the DBA and the DBA gives you a hard time, try to find proof by timing it before and after on the test machine, if it improves performance, the DBA would be crazy to not allow it.

In terms of a DBA saying no temp tables, that is unfortunate. If a feature were always bad, then why does it exist! But on the other hand, I can see where the DBA is coming from, I've seen overuse of Temp Tables, quite often when an Application Developer is bringing that kind of Application Programming mentality to stored procedures and a mess of Temp tables is the result. So I would experiment with the Temp Tables and if you get a performance improvement and can prove to the DBA that the Temp Tables are responsible for the performance improvement, your DBA would be crazy not to go along with it.
Go to Top of Page

raghav_sai2002
Starting Member

18 Posts
Posted - 2009-10-30 : 05:11:01
Hi,

Thank you,

your suggestions helped me out a lot...

i followed these steps to improve the performance of the query

1. i created an index on programid, keywordid, and matchtype...(leaving the arguments to my PM and DBA, whether to create a view in the production or not )... this did reduced the time by 2 minutes. but still had to reduce that by 3mins, so that it can comes under 30secs.

2. as my DBA strictly said... that he dont want temp tables, i decided to go with table variables, i have read and article in the forum, that table variables can be a bit slow when it comes to storing large amounts of data. but i hope it wont be that bad for a maximum of 25000 records at anytime.

once i created the index on the table,used a table variable and instead of using a union, i inserted, every select result set into the table variable, then the query actually executed in (6secs ..... which used to be like 6mins)

so it solved my problem... i still could try to improve the performance, by removing all those cast and converts, but i can live with this at the moment.

3. and coming to the data which is being retrieved from Google or yahoo or any other channel, i observed that in our service they are not performing a trim action, i am sure in couple of months i will get a chance to upgrade the service, then as the service is totally under my control, i will trim it and save it into the db.

thank you all for your help.
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-10-30 : 14:22:50
glad to hear that!
what Denis says is very true..because I used to be an Application Developer "..is bringing that kind of Application Programming mentality to stored procedures and a mess of Temp tables is the result" Overuse or lack of best practice implementation can happen regardless if one is application developer or sql developer. also what do you have those other fields you need to do trimming on, varchar or nvarchar?

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

denis_the_thief
Aged Yak Warrior

596 Posts
Posted - 2009-11-02 : 09:06:35
Thank you for sharing the happy news, Raghav.

So your DBA won't accept Temporary Tables but will accept Table Variables. Sounds like half your challenge was dealing with the politics. I've seen the same thing view using Views. Views are VERY often misunderstood, then the Department says: No Views.

I actually haven't used Table Variables myself. But I looked it up a little and read they are best suited for few rows. So perhaps Temp. Tables would have been the better solution. But in any event, I guess your bottom line was the huge speed improvment that you already achieved.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site