March 24, 2015 at 4:35 am
Hi,
plz help me
i have table below
CREATE TABLE [dbo].[DR_Test](
[source_item_id] [int] NOT NULL,
[source_line_no] [int] NULL,
[buyer_id] [int] NOT NULL,
[seller_member_id] [int] NULL,
[seller_type] [nvarchar](50) NULL,
[advertiser_id] [int] NULL,
[insertion_order_id] [int] NULL,
[campaign_id] [int] NULL,
[site_id] [int] NULL,
[publisher_id] [int] NULL,
[placement_id] [int] NULL,
[creative_id] [int] NULL,
[inventory_source_id] [int] NULL,
[nvarchar](50) NULL,
[geo_country] [nvarchar](50) NULL,
[payment_type] [nvarchar](50) NULL,
[line_item_id] [int] NULL,
[media_type_id] [int] NULL,
[imp_type] [nvarchar](50) NULL,
[clicks] [int] NULL,
[commissions] [decimal](21, 5) NULL,
[imps] [int] NULL,
[media_cost] [decimal](21, 5) NULL,
[publisher_rpm_publisher_currency] [decimal](21, 5) NULL,
[reseller_revenue] [decimal](21, 5) NULL,
[booked_revenue] [decimal](21, 5) NULL,
[serving_fees] [decimal](21, 5) NULL,
[post_click_convs] [int] NULL,
[post_click_revenue] [decimal](21, 5) NULL,
[post_view_convs] [int] NULL,
[post_view_revenue] [decimal](21, 5) NULL,
[load_date] [datetime] NULL,
[Timezone] [datetime] NULL,
[SQNo] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_SQNO] PRIMARY KEY CLUSTERED
(
[SQNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
the table contains more than 80 million records so when i fetch the data using buyer_id & timezone its taking lot of more than 1 hours or so....& where buyer_id is not unique
so how to fetch the data fast or need to change the structure of the table
please suggest me
Thanks
March 24, 2015 at 4:37 am
Please post the query that you're using and all the indexes on the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 24, 2015 at 5:01 am
we use a SP to get the data
here is the SP
USE [365_AppNexus_ODS]
GO
/****** Object: StoredProcedure [dbo].[GetDeliveryReport] Script Date: 03/24/2015 06:59:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetDeliveryReport]
-- Add the parameters for the stored procedure here
@memberID int,
@AdvertiserIDs nvarchar(max),
@includeCostMetrics bit ,
@QueryAdvertiserIDs varchar(max),
@LineItemIDs nvarchar(max),
-- @include_insertion_order_id bit,
@include_line_item_id bit,
@include_campaign_id bit,
@include_placement_id bit,
@include_creative_id bit,
@Include_geo_country bit,
@include_inventory_source_id bit,
@include_site_id bit,
@include_publisher_id bit,
@Include_advertiser_id bit,
@Include_payment_type bit,
@Include_size bit,
@TimePeriod nvarchar(100) ,
@StartDate datetime,
@EndDate datetime
/**
EXEC [GetDeliveryReport] 364, '68668, 3442, 6076, 224187, 179178', 0, 0, '918565, 918840, 774239, 896246, 864729', 0, 0, 0, 0, 0, 0, 0, 0, 'Daily', '2014-03-01 00:00:00.000', '2014-03-01 00:00:00.000'
**/
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Select statements for procedure here
IF CHARINDEX('-999',@LineItemIDs) > 0
RETURN
IF OBJECT_ID('tempdb.#tmpAdvertisers') IS NOT NULL
DROP TABLE #tmpAdvertisers
create table #tmpAdvertisers
(AdvertiserId varchar(20), AdvertiserName nvarchar(200),member_id int)
-- populate temp tables
if @QueryAdvertiserIDs = '0'
begin
if @AdvertiserIDs = '0'
begin
insert into #tmpAdvertisers (AdvertiserId, AdvertiserName,member_id)
select advertiser_id, advertiser_name,member_id
from AdvertiserMD (nolock)
where member_id = @memberID
AND isReal=1
end
else
begin
insert into #tmpAdvertisers (AdvertiserId, AdvertiserName,member_id)
select advertiser_id, advertiser_name,member_id
from AdvertiserMD (nolock)
where member_id = @memberID
and advertiser_id in (select item from dbo.udfSplit(@AdvertiserIDs, ','))
AND isReal=1
end
end
else
begin
insert into #tmpAdvertisers (AdvertiserId, AdvertiserName,member_id)
select advertiser_id, advertiser_name,member_id
from AdvertiserMD (nolock)
where member_id = @memberID
and advertiser_id in (select item from dbo.udfSplit(@QueryAdvertiserIDs, ','))
AND isReal=1
end
--SELECT * FROM #tmpAdvertisers
IF OBJECT_ID('tempdb.#TempLineItemMDs') IS NOT NULL
DROP TABLE #TempLineItemMDs
CREATE TABLE #TempLineItemMDs
(line_item_id int,name nvarchar(200),member_id int,advertiser_id int )
if @LineItemIDs = '0'
begin
insert into #TempLineItemMDs (line_item_id, name,member_id,advertiser_id)
select l.line_item_id , l.name , l.member_id,l.advertiser_id
from LineItemMD l (nolock)
inner join #tmpAdvertisers ta on ta.AdvertiserId = l.advertiser_id
end
else
begin
insert into #TempLineItemMDs (line_item_id, name,member_id,advertiser_id)
select l.line_item_id , l.name , l.member_id,l.advertiser_id
from LineItemMD l (nolock)
where member_id = @memberID and line_item_id in (select item from dbo.udfSplit(@LineItemIDs, ','))
end
--SELECT * FROM #TempLineItemMDs
IF (@TimePeriod = 'Daily')
BEGIN
select
Mb.MemberName as member_name
,Mb.Member_id as member_id
,DR.seller_member_id
,DR.seller_type
--,AMd.AdvertiserName as advertiser_name
--,DR.advertiser_id
,case when @Include_advertiser_id = 0 then 'ALL' when @Include_advertiser_id = 1 and DR.advertiser_id = 0 then 'Not Set' else ISNULL(AMd.AdvertiserName,'NA') end as advertiser_name
,case when @Include_advertiser_id = 0 then 0 when @Include_advertiser_id = 1 and DR.advertiser_id = 0 then -1 else DR.advertiser_id end as advertiser_id
--,IOMD.inserton_order_name
--,case @include_insertion_order_id when 0 then '0' else DR.insertion_order_id end as insertion_order_id
,case when @Include_line_item_id = 0 then 'ALL' when @Include_line_item_id = 1 and DR.line_item_id = 0 then 'Not Set' else ISNULL(LIMD.name,'NA') end as line_item_name
,case when @Include_line_item_id = 0 then 0 when @Include_line_item_id = 1 and DR.line_item_id = 0 then -1 else DR.line_item_id end as line_item_id
,case when @Include_campaign_id = 0 then 'ALL' when @Include_campaign_id = 1 and DR.campaign_id = 0 then 'Not Set' else ISNULL(CAMD.campaign_name,'NA') end as campaign_name
,case when @Include_campaign_id = 0 then 0 when @Include_campaign_id = 1 and DR.campaign_id = 0 then -1 else DR.campaign_id end as campaign_id
,case when @Include_placement_id = 0 then 'ALL' when @Include_placement_id = 1 and DR.placement_id=0 then 'Not Set' else ISNULL(LUP.placement_name,'NA') end as placement_name
,case when @Include_placement_id = 0 then 0 when @Include_placement_id = 1 and dr.placement_id = 0 then -1 else DR.placement_id end as placement_id
,case when @Include_creative_id= 0 then 'ALL' when @Include_creative_id = 1 and DR.creative_id = 0 then 'Not Set' else ISNULL(CRMD.creative_name,'NA') end as creative_name
,case when @Include_creative_id = 0 then 0 when @Include_creative_id = 1 and DR.creative_id = 0 then -1 else DR.creative_id end as creative_id
,case when @Include_size = 0 then 'ALL' else isnull(DR.Size,'') end as size
,DR.media_type_id
,case when DR.imp_type = 'Blank' then 1 when DR.imp_type = 'PSA' then 2 when DR.imp_type = 'Default Error' then 3 when DR.imp_type = 'Default' then 4 when DR.imp_type = 'Kept' then 5 when DR.imp_type = 'Resold' then 6 when DR.imp_type = 'RTB' then 7 when DR.imp_type = 'PSA Default Error' then 8 when DR.imp_type = 'External Impression' then 9 when DR.imp_type = 'External Click' then 10 when DR.imp_type = 'Insertion' then 11 end as imp_type
,case @Include_geo_country when 0 then 'ALL' else isnull(DR.geo_country,'') end as geo_country
,case @Include_payment_type when 0 then 'ALL' else isnull(DR.payment_type,'') end as payment_type
--,case when @include_inventory_source_id= 0 then 'ALL' when @include_inventory_source_id = 1 and DR.creative_id = 0 then 'Not Set' else ISNULL--(LUI.inventory_source_name,'NA') end as inventory_source_name
,case when @include_inventory_source_id = 0 then 0 when @include_inventory_source_id = 1 and DR.inventory_source_id = 0 then -1 else DR.inventory_source_id end as inventory_source_id
,case when @include_site_id= 0 then 'ALL' when @include_site_id = 1 and DR.site_id = 0 then 'Not Set' else ISNULL(LUS.name,'NA') end as site_name
,case when @include_site_id = 0 then 0 when @include_site_id = 1 and DR.site_id = 0 then -1 else DR.site_id end as site_id
,case when @include_publisher_id= 0 then 'ALL' when @include_publisher_id = 1 and DR.creative_id = 0 then 'Not Set' else ISNULL(LUPB.publisher_name,'NA') end as publisher_name
,case when @include_publisher_id = 0 then 0 when @include_publisher_id = 1 and DR.publisher_id = 0 then -1 else DR.publisher_id end as publisher_id
--,replace(convert(varchar(12), DR.[day], 106), ' ', '-') as created_on Changed for the Time Zone and The Day Light Saving tacke into Account
,replace(convert(varchar(12), DR.Timezone, 106), ' ', '-') as [date]--created_on
,SUM(DR.clicks) as clicks
,SUM(DR.imps) as imps
, case when SUM(DR.imps) =0 then NULL else cast(cast(SUM(DR.clicks) as decimal(21,3) )/cast( SUM(DR.imps) as decimal(21,3) ) as decimal(21,3)) end as ctr
,SUM(DR.post_click_convs) as post_click_convs
,case when SUM(DR.imps) =0 then NULL else cast(cast(SUM(DR.post_click_convs) as decimal(21,3) ) / cast ( SUM(DR.imps) as decimal(21,3) ) as decimal(21,3)) end as post_click_convs_rate
,SUM(DR.post_view_convs) as post_view_convs
,case when SUM(DR.imps) =0 then NULL else cast(cast ( SUM(DR.post_view_convs) as decimal(21,3) ) / cast ( SUM(DR.imps) as decimal(21,3) ) as decimal(21,3)) end as post_view_convs_rate
,(SUM(DR.post_click_convs) + SUM(DR.post_view_convs)) as total_convs
,case when SUM(DR.imps) =0 then NULL else cast(cast((SUM(DR.post_click_convs) + SUM(DR.post_view_convs))as decimal(21,3)) / SUM(DR.imps) as decimal(21,3)) end as convs_rate
,case @includeCostMetrics when 0 then null else cast((SUM(DR.media_cost)) as decimal(21,3)) end as cost
,case @includeCostMetrics when 0 then null else case when SUM(DR.imps) =0 then NULL else cast(((SUM(DR.media_cost) / SUM(DR.imps)) * 1000) as decimal(21,3)) end end as cpm --
,case @includeCostMetrics when 0 then null else cast(SUM(DR.commissions) as decimal(21,3)) end as commissions --
,case @includeCostMetrics when 0 then null else cast(SUM(DR.publisher_rpm_publisher_currency) as decimal(21,3)) end as publisher_rpm_publisher_currency
,case @includeCostMetrics when 0 then null else cast(SUM(DR.reseller_revenue) as decimal(21,3)) end as reseller_revenue
,case @includeCostMetrics when 0 then null else cast(SUM(DR.booked_revenue) as decimal(21,3)) end as booked_revenue
,case @includeCostMetrics when 0 then null else cast(SUM(DR.serving_fees)as decimal(21,3)) end as serving_fees
,case @includeCostMetrics when 0 then null else cast(SUM(DR.post_click_revenue) as decimal(21,3)) end as post_click_revenue
,case @includeCostMetrics when 0 then null else cast(SUM(DR.post_view_revenue) as decimal(21,3)) end as post_view_revenue
,case @includeCostMetrics when 0 then null else cast((SUM(DR.booked_revenue) - SUM(DR.media_cost))as decimal(21,3)) end as profit -- AS per QA Sehet Booked Revenue - COST
--,case @includeCostMetrics when 0 then null else case when SUM(DR.post_click_convs) = 0 and SUM(DR.post_click_convs) =0 then NULL else cast(((SUM(DR.media_cost)/(SUM(DR.post_click_convs))+SUM(DR.post_view_convs))) as decimal(21,3)) end end as cost_ecpa
--,case @includeCostMetrics when 0 then null else case when (SUM(DR.post_click_convs) + SUM(DR.post_click_convs)) =0 then 0 else cast(((SUM(DR.media_cost)/(SUM(DR.post_click_convs))+SUM(DR.post_view_convs))) as decimal(21,3)) end end as cost_ecpa
,case @includeCostMetrics when 0 then null else case when (SUM(DR.post_click_convs) + SUM(DR.post_view_convs)) =0 then 0 else cast((SUM(DR.media_cost)/(SUM(DR.post_click_convs)+SUM(DR.post_view_convs))) as decimal(21,3)) end end as cost_ecpa
,case @includeCostMetrics when 0 then null else case when SUM(DR.clicks) = 0 then 0 else cast((SUM(DR.media_cost)/SUM(DR.clicks)) as decimal(21,3)) end end as cost_ecpc
,case @include_creative_id when 0 then null else CM.ad_server end as adserver_name
,case @include_creative_id when 0 then null else CM.placement_id end as adserver_placement_id
from
DeliveryReport DR (nolock) inner join
Member Mb on Mb.Member_id = DR.buyer_id
inner join #tmpAdvertisers AMD on AMD.AdvertiserId = DR.advertiser_id and AMD.member_id = DR.buyer_id
inner join #TempLineItemMDs LIMD on LIMD.member_id = DR.buyer_id and LIMD.advertiser_id = DR.advertiser_id and LIMD.line_item_id = DR.line_item_id
inner join CampaignMD CAMD (nolock) on CAMD.member_id = DR.buyer_id and CAMD.advertiser_id = DR.advertiser_id and CAMD.campaign_id = DR.campaign_id
inner join CreativeMD CRMD (nolock) on CRMD.member_id = DR.buyer_id and CRMD.advertiser_id = DR.advertiser_id and CRMD.creative_id = DR.creative_id
left join LookUp_Placements LUP (nolock) on lup.member_id = DR.buyer_id and LUP.placement_id = DR.placement_id
--inner join LookUp_InventorySource LUI on LUI.member_id = DR.buyer_id and LUI.inventory_source_id = DR.inventory_source_id
left join LookUp_Site LUS (nolock) on LUS.member_id = DR.buyer_id and LUS.site_id = DR.site_id and LUS.publisher_id = DR.publisher_id
left join LookUp_Publisher LUPB (nolock) on LUPB.member_id = DR.buyer_id and DR.publisher_id = LUPB.publisher_id
left join Creative_Mapped cm on DR.buyer_id = cm.member_id and DR.advertiser_id = cm.advertiser_id and DR.creative_id = cm.creative_id
where
--DR.[day] between @StartDate and @EndDateas created_on Changed for the Time Zone and The Day Light Saving tacke into Account
DR.Timezone between @StartDate and @EndDate
and DR.buyer_id = @memberID
group by
Mb.Member_id
,Mb.MemberName
,DR.seller_member_id
,DR.seller_type
--,AMd.AdvertiserName
--,dr.advertiser_id
,case when @Include_advertiser_id = 0 then 'ALL' when @Include_advertiser_id = 1 and DR.advertiser_id = 0 then 'Not Set' else ISNULL(AMd.AdvertiserName,'NA') end
,case when @Include_advertiser_id = 0 then 0 when @Include_advertiser_id = 1 and DR.advertiser_id = 0 then -1 else DR.advertiser_id end
--,IOMD.inserton_order_name
--,case @include_insertion_order_id when 0 then 0 else DR.insertion_order_id end
,case when @Include_line_item_id = 0 then 'ALL' when @Include_line_item_id = 1 and DR.line_item_id = 0 then 'Not Set' else ISNULL(LIMD.name,'NA') end
,case when @Include_line_item_id = 0 then 0 when @Include_line_item_id = 1 and DR.line_item_id = 0 then -1 else DR.line_item_id end
,case when @Include_campaign_id = 0 then 'ALL' when @Include_campaign_id = 1 and DR.campaign_id = 0 then 'Not Set' else ISNULL(CAMD.campaign_name,'NA') end
,case when @Include_campaign_id = 0 then 0 when @Include_campaign_id = 1 and DR.campaign_id = 0 then -1 else DR.campaign_id end
,case when @Include_placement_id = 0 then 'ALL' when @Include_placement_id = 1 and DR.placement_id=0 then 'Not Set' else ISNULL(LUP.placement_name,'NA') end
,case when @Include_placement_id = 0 then 0 when @Include_placement_id = 1 and dr.placement_id = 0 then -1 else DR.placement_id end
,case when @Include_creative_id= 0 then 'ALL' when @Include_creative_id = 1 and DR.creative_id = 0 then 'Not Set' else ISNULL(CRMD.creative_name,'NA') end
,case when @Include_creative_id = 0 then 0 when @Include_creative_id = 1 and DR.creative_id = 0 then -1 else DR.creative_id end
,case when @Include_size = 0 then 'ALL' else isnull(DR.Size,'') end
,DR.media_type_id
,case when DR.imp_type = 'Blank' then 1 when DR.imp_type = 'PSA' then 2 when DR.imp_type = 'Default Error' then 3 when DR.imp_type = 'Default' then 4 when DR.imp_type = 'Kept' then 5 when DR.imp_type = 'Resold' then 6 when DR.imp_type = 'RTB' then 7 when DR.imp_type = 'PSA Default Error' then 8 when DR.imp_type = 'External Impression' then 9 when DR.imp_type = 'External Click' then 10 when DR.imp_type = 'Insertion' then 11 end
,case @Include_geo_country when 0 then 'ALL' else isnull(DR.geo_country,'') end
,case @Include_payment_type when 0 then 'ALL' else isnull(DR.payment_type,'') end
--,case when @include_inventory_source_id= 0 then 'ALL' when @include_inventory_source_id = 1 and DR.creative_id = 0 then 'Not Set' else ISNULL(LUI.inventory_source_name,'NA') end
,case when @include_inventory_source_id = 0 then 0 when @include_inventory_source_id = 1 and DR.inventory_source_id = 0 then -1 else DR.inventory_source_id end
,case when @include_site_id= 0 then 'ALL' when @include_site_id = 1 and DR.site_id = 0 then 'Not Set' else ISNULL(LUS.name,'NA') end
,case when @include_site_id = 0 then 0 when @include_site_id = 1 and DR.site_id = 0 then -1 else DR.site_id end
,case when @include_publisher_id= 0 then 'ALL' when @include_publisher_id = 1 and DR.creative_id = 0 then 'Not Set' else ISNULL(LUPB.publisher_name,'NA') end
,case when @include_publisher_id = 0 then 0 when @include_publisher_id = 1 and DR.publisher_id = 0 then -1 else DR.publisher_id end
--,DR.[day] as created_on Changed for the Time Zone and The Day Light Saving tacke into Account
,DR.Timezone
,case @include_creative_id when 0 then null else CM.ad_server end
,case @include_creative_id when 0 then null else CM.placement_id end
END
DROP TABLE #tmpAdvertisers
DROP TABLE #TempLineItemMDs
END
GO
Index:
/****** Object: Index [CDX_DeliveryReport] Script Date: 03/24/2015 06:56:32 ******/
CREATE UNIQUE CLUSTERED INDEX [CDX_DeliveryReport] ON [dbo].[DeliveryReport]
(
[buyer_id] ASC,
[advertiser_id] ASC,
[insertion_order_id] ASC,
[campaign_id] ASC,
[publisher_id] ASC,
[placement_id] ASC,
[creative_id] ASC,
[inventory_source_id] ASC,
[site_id] ASC,
[payment_type] ASC,
[geo_country] ASC,
ASC,
[line_item_id] ASC,
[media_type_id] ASC,
[imp_type] ASC,
[Timezone] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
please help
Thanks
March 24, 2015 at 11:12 am
Could you please post the execution plan?
Maybe adding a few indexes at those temp tables it can help, the aggregation is what is killing you
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply