1

i have a performance issue with my query and i need to rewrite it to have my result in better time. If you can help me with an idea?

My query is the following:

select 
    dc.Country,
    COUNT(DISTINCT fvcd.IdAppInstance) AS Visitors
FROM 
    dbo.Fact_VisitorsCountryDetails fvcd
    INNER JOIN dbo.Dim_Country dc ON dc.Id = fvcd.IdCountry
WHERE
    fvcd.IdDate BETWEEN @IdMinDate AND @IdMaxDate 
    AND fvcd.IdPartnerid = @IdPartnerId
GROUP BY
    dc.Country
ORDER BY
    Visitors DESC;

My DDL is:

CREATE TABLE [dbo].[Dim_Country](
    [Id] [bigint] NOT NULL,
    [Country] [varchar](24) NULL,
 CONSTRAINT [PK_Dim_Country_Id] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Fact_VisitorsCountryDetails](
    [IdDate] [bigint] NULL,
    [IdPartnerId] [bigint] NULL,
    [IdCountry] [bigint] NULL,
    [IdAppinstance] [bigint] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Fact_VisitorsCountryDetails]  WITH NOCHECK ADD  CONSTRAINT [FK_Fact_VisitorsCountryDetails_Dim_Country_Id] FOREIGN KEY([IdCountry])
REFERENCES [dbo].[Dim_Country] ([Id])
GO

ALTER TABLE [dbo].[Fact_VisitorsCountryDetails] CHECK CONSTRAINT [FK_Fact_VisitorsCountryDetails_Dim_Country_Id]
GO

ALTER TABLE [dbo].[Fact_VisitorsCountryDetails]  WITH NOCHECK ADD  CONSTRAINT [FK_Fact_VisitorsCountryDetails_Dim_Date_Id] FOREIGN KEY([IdDate])
REFERENCES [dbo].[Dim_Date] ([Id])
GO

ALTER TABLE [dbo].[Fact_VisitorsCountryDetails] CHECK CONSTRAINT [FK_Fact_VisitorsCountryDetails_Dim_Date_Id]
GO

ALTER TABLE [dbo].[Fact_VisitorsCountryDetails]  WITH NOCHECK ADD  CONSTRAINT [FK_Fact_VisitorsCountryDetails_Dim_PartnerId_Id] FOREIGN KEY([IdPartnerId])
REFERENCES [dbo].[Dim_PartnerId] ([Id])
GO

ALTER TABLE [dbo].[Fact_VisitorsCountryDetails] CHECK CONSTRAINT [FK_Fact_VisitorsCountryDetails_Dim_PartnerId_Id]
GO
0

DISTINCT is always a huge overhead. You might try to move the aggregation into a Derived Table (of course this might only help if the optimizer is not smart enough to do that automatically)

select dc.Country,
  fvcd.Visitors
from 
 (
   select 
       fvcd.IdCountry,
       COUNT(DISTINCT fvcd.IdAppInstance) AS Visitors
   FROM 
       dbo.Fact_VisitorsCountryDetails fvcd
   WHERE
       fvcd.IdDate BETWEEN @IdMinDate AND @IdMaxDate 
       AND fvcd.IdPartnerid = @IdPartnerId
   GROUP BY
       fvcd.IdCountry
 ) fvcd
INNER JOIN dbo.Dim_Country dc ON dc.Id = fvcd.IdCountry   
ORDER BY
    Visitors DESC;
  • 2
    I think SQL Server's optimizer is smart enough to do similar transformations (if it can't do the specific, it's probably because a UNIQUE constraint on Dim_Country (Country) is missing and thus your rewriting is not strictly equivalent to the original query.) – ypercubeᵀᴹ Oct 2 '15 at 12:25
  • For example, if there are 2 countries with different ID but same name or both with NULL in their name (I have no idea what that would mean but the design allows it), the original query will produce 1 row for them but yours 2 rows and different counts (Visitors). – ypercubeᵀᴹ Oct 2 '15 at 12:55
  • @ypercube: Of course you're right, but there's low probability for multiple ids for the same country. But you never know, there's also BIGINTs for country & date id (in fact I've seen it for gender, too :-) – dnoeth Oct 2 '15 at 17:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.