optimize slow 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)

optimize slow query

Author

Topic

ThePrisoner
Starting Member

18 Posts

Posted - 2007-09-11 : 07:10:58

hello

the following query took 2 hours to run, it returns 1305 rows,the database tuning advisor didn't have any suggestions, so I'm looking for ideas to speed up this query.
I use it as a base for a Reporting Services report.
thanks in advance for any suggestions !

declare @TimePeriodDesc     varchar(255)
declare @TotalMarket        varchar(255)
declare @datasetname        varchar(255)
declare @VisibleProducts    varchar(500)
declare @DoctorSubTerritory varchar(255)
set @visibleproducts    = 'Total Retail,PPI Total, -Lansoprazole, -Other PPI Total,Generics Total,Eurogenerics Total,Docpharma Total,Sandoz Total,Merck-Generics Total,  - Omeprazole MCK,  - Elisa 35  MCK,  - Simvastatine MCK,  - Prareduct - MCK,  - Paroxetine  MCK,  - Other Merck Products'
set @TimePeriodDesc     = 'r3month 200706'
set @TotalMarket        = 'total retail'
set @datasetname        = 'hdmckq02'
set @DoctorSubTerritory = 'rn1'

SELECT  xpospecialty.specialtydesc,
        a.doctorsubterritory      ,
        a.doctorbrick             ,
        a.doctorgroup             ,
        xpomarket.productdesc     ,
        b.totalmarketrank         ,
        c.prod1ranktotal          ,
        d.prod2ranktotal          ,
        SUM
        (
                CASE
                        WHEN (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
                        THEN xpomeasures.rxvalues
                        ELSE 0
                END
        ) AS totalrxvalues,
        COUNT
        (
                CASE
                        WHEN (xpoproductsize.prodsizedesc     <> 'Non -')
                            AND (XpoMarketSize.MarketSizeDesc <> 'Non - MKT')
                            AND (xpotimeperiod.timeperioddesc  = @TimePeriodDesc)
                        THEN 1
                        ELSE NULL
                END
        ) AS rxrcount,
        COUNT
        (
                CASE
                        WHEN (xpoproductsize.prodsizedesc     = 'High')
                            AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
                        THEN 1
                        ELSE NULL
                END
        ) AS [high],
        COUNT
        (
                CASE
                        WHEN (xpoproductsize.prodsizedesc     = 'Medium')
                            AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
                        THEN 1
                        ELSE NULL
                END
        ) AS [medium],
        COUNT
        (
                CASE
                        WHEN (xpoproductsize.prodsizedesc     = 'Low')
                            AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
                        THEN 1
                        ELSE NULL
                END
        ) AS [low],
        COUNT
        (
                CASE
                        WHEN (xpoproductsize.prodsizedesc     = 'Very Low')
                            AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
                        THEN 1
                        ELSE NULL
                END
        ) AS [very low],
        COUNT
        (
                CASE
                        WHEN (xpoproductsize.prodsizedesc     = 'Very High')
                            AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
                        THEN 1
                        ELSE NULL
                END
        ) AS [very high],
        (SELECT SUM(xpomeasures.rxvalues) AS totalrxvalues
        FROM    xpotimeperiod
        INNER JOIN xpomeasures
        ON      xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
        INNER JOIN xpomarket
        ON      xpomeasures.xpomarketid = xpomarket.xpomarketid
        INNER JOIN xpogeography
        ON      xpomeasures.geographyid       = xpogeography.geographyid
        WHERE (xpomarket.productdesc          = @TotalMarket)
            AND (xpomeasures.datasetname      = @DatasetName)
            AND (xpogeography.doctorgroup     = a.doctorgroup)
            AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
        ) AS rxvaluesdoctorgroup
FROM    xpotimeperiod
INNER JOIN xpomeasures
ON      xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
INNER JOIN XpoMarketSize
ON      XpoMeasures.XpoMarketSizeId = XpoMarketSize.XpoMarketSizeId
INNER JOIN xpoproductsize
ON      xpomeasures.xpoproductsizeid = xpoproductsize.xpoprodsizeid
INNER JOIN xpomarket
ON      xpomeasures.xpomarketid = xpomarket.xpomarketid
INNER JOIN xpogeography a
ON      xpomeasures.geographyid = a.geographyid
INNER JOIN xpospecialty
ON      xpomeasures.xpospecialtyid = xpospecialty.xpospecialtyid
INNER JOIN dbo.Fn_mvparamsorted (@VisibleProducts,',' )
ON      xpomarket.productdesc = fn_mvparamsorted.parame
INNER JOIN
        (SELECT xpogeography.doctorgroup,
                Row_number() OVER(ORDER BY Round(SUM(xpomeasures.rxvalues),0) DESC) AS 'TotalMarketRank'
        FROM    xpogeography
        INNER JOIN xpomeasures
        ON      xpogeography.geographyid = xpomeasures.geographyid
        INNER JOIN xpomarket
        ON      xpomeasures.xpomarketid = xpomarket.xpomarketid
        INNER JOIN xpotimeperiod
        ON      xpomeasures.timeperiodid         = xpotimeperiod.timeperiodid
        WHERE (xpomeasures.datasetname           = @DatasetName)
            AND (xpogeography.doctorsubterritory = @DoctorSubTerritory)
            AND (xpotimeperiod.timeperioddesc    = @TimePeriodDesc)
            AND (xpomarket.productdesc           = @TotalMarket)
        GROUP BY xpogeography.doctorgroup
        )
        b
ON      a.doctorgroup = b.doctorgroup
INNER JOIN
        (SELECT xpogeography.doctorgroup,
                Row_number() OVER(ORDER BY Round(SUM(xpomeasures.rxvalues),0) DESC) AS 'Prod1RankTotal'
        FROM    xpogeography
        INNER JOIN xpomeasures
        ON      xpogeography.geographyid = xpomeasures.geographyid
        INNER JOIN xpomarket
        ON      xpomeasures.xpomarketid = xpomarket.xpomarketid
        INNER JOIN xpotimeperiod
        ON      xpomeasures.timeperiodid         = xpotimeperiod.timeperiodid
        WHERE (xpomeasures.datasetname           = @DatasetName)
            AND (xpogeography.doctorsubterritory = @DoctorSubTerritory)
            AND (xpotimeperiod.timeperioddesc    = @TimePeriodDesc)
            AND (xpomarket.productdesc           = 'Merck-Generics Total')
        GROUP BY xpogeography.doctorgroup
        )
        c
ON      a.doctorgroup = c.doctorgroup
INNER JOIN
        (SELECT xpogeography.doctorgroup,
                Row_number() OVER(ORDER BY Round(SUM(xpomeasures.rxvalues),0) DESC) AS 'Prod2RankTotal'
        FROM    xpogeography
        INNER JOIN xpomeasures
        ON      xpogeography.geographyid = xpomeasures.geographyid
        INNER JOIN xpomarket
        ON      xpomeasures.xpomarketid = xpomarket.xpomarketid
        INNER JOIN xpotimeperiod
        ON      xpomeasures.timeperiodid         = xpotimeperiod.timeperiodid
        WHERE (xpomeasures.datasetname           = @DatasetName)
            AND (xpogeography.doctorsubterritory = @DoctorSubTerritory)
            AND (xpotimeperiod.timeperioddesc    = @TimePeriodDesc)
            AND (xpomarket.productdesc           = 'PPI Total')
        GROUP BY xpogeography.doctorgroup
        )
        d
ON      a.doctorgroup                 = d.doctorgroup
WHERE (xpomeasures.datasetname        = @DatasetName)
    AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
    AND (a.doctorsubterritory         = @DoctorSubTerritory)
GROUP BY xpospecialty.specialtydesc,
        a.doctorsubterritory       ,
        a.doctorbrick              ,
        a.doctorbrick              ,
        a.doctorgroup              ,
        fn_mvparamsorted.parame    ,
        fn_mvparamsorted.sortid    ,
        xpomarket.productdesc      ,
        b.totalmarketrank          ,
        c.prod1ranktotal           ,
        d.prod2ranktotal
ORDER BY b.totalmarketrank,
        a.doctorgroup     ,
        fn_mvparamsorted.sortid

Kristen
Test

22859 Posts

Posted - 2007-09-11 : 07:33:30

I wonder if you could combine those 3 inner sub queries into one, to a temporary table, and then join that in?

The tables are being repeated a lot, might it be possible to pre-query and aggregate some data, which could then be used to avoid repeated joining of the tables?

I presume you have indexes on all the Joined columns?

I would insist on "dbo." prefix for all named tables, in case it helps the query plan get cached.

Kristen

ThePrisoner
Starting Member

18 Posts

Posted - 2007-09-11 : 08:24:52

thanks for your suggestions, I fixed the problem by adding a few missing where clauses !

(EDIT : in fact no, it just went faster the second time, i suppose the results were cached)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-11 : 08:31:21

Kristen means something like this

declare @TimePeriodDesc     varchar(255),
	@TotalMarket        varchar(255),
	@datasetname        varchar(255),
	@VisibleProducts    varchar(500),
	@DoctorSubTerritory varchar(255)

select	@visibleproducts    = 'Total Retail,PPI Total, -Lansoprazole, -Other PPI Total,Generics Total,Eurogenerics Total,Docpharma Total,Sandoz Total,Merck-Generics Total,  - Omeprazole MCK,  - Elisa 35  MCK,  - Simvastatine MCK,  - Prareduct - MCK,  - Paroxetine  MCK,  - Other Merck Products',
	@TimePeriodDesc     = 'r3month 200706',
	@TotalMarket        = 'total retail',
	@datasetname        = 'hdmckq02',
	@DoctorSubTerritory = 'rn1'

SELECT		xpospecialty.specialtydesc,
		a.doctorsubterritory,
		a.doctorbrick,
		a.doctorgroup,
		xpomarket.productdesc,
		s.totalmarketrank,
		s.prod1ranktotal,
		s.prod2ranktotal,
		SUM(CASE WHEN xpotimeperiod.timeperioddesc = @TimePeriodDesc THEN xpomeasures.rxvalues ELSE 0 END) AS totalrxvalues,
		COUNT(CASE WHEN xpoproductsize.prodsizedesc <> 'Non -' AND XpoMarketSize.MarketSizeDesc <> 'Non - MKT' AND xpotimeperiod.timeperioddesc = @TimePeriodDesc THEN 1 END) AS rxrcount,
	        COUNT(CASE WHEN xpoproductsize.prodsizedesc = 'High' AND xpotimeperiod.timeperioddesc = @TimePeriodDesc THEN 1 END) AS [high],
	        COUNT(CASE WHEN xpoproductsize.prodsizedesc = 'Medium' AND xpotimeperiod.timeperioddesc = @TimePeriodDesc THEN 1 END) AS [medium],
	        COUNT(CASE WHEN xpoproductsize.prodsizedesc = 'Low' AND xpotimeperiod.timeperioddesc = @TimePeriodDesc THEN 1 END) AS [low],
	        COUNT(CASE WHEN xpoproductsize.prodsizedesc = 'Very Low' AND xpotimeperiod.timeperioddesc = @TimePeriodDesc THEN 1 END) AS [very low],
	        COUNT(CASE WHEN xpoproductsize.prodsizedesc = 'Very High' AND xpotimeperiod.timeperioddesc = @TimePeriodDesc THEN 1 END) AS [very high],
		ISNULL(s.totalrxvalues, 0) as rxvaluesdoctorgroup
FROM		xpotimeperiod
INNER JOIN	xpomeasures ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
INNER JOIN	XpoMarketSize ON XpoMeasures.XpoMarketSizeId = XpoMarketSize.XpoMarketSizeId
INNER JOIN	xpoproductsize ON xpomeasures.xpoproductsizeid = xpoproductsize.xpoprodsizeid
INNER JOIN	xpomarket ON xpomeasures.xpomarketid = xpomarket.xpomarketid
INNER JOIN	xpogeography as a ON xpomeasures.geographyid = a.geographyid
INNER JOIN	xpospecialty ON xpomeasures.xpospecialtyid = xpospecialty.xpospecialtyid
INNER JOIN	dbo.Fn_mvparamsorted(@VisibleProducts,',' ) ON xpomarket.productdesc = fn_mvparamsorted.parame
LEFT JOIN	(
			SELECT		xpogeography.doctorgroup,
					SUM(CASE WHEN xpomarket.productdesc = @TotalMarket THEN xpomeasures.rxvalues ELSE 0 END) AS totalrxvalues,
					ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = @TotalMarket THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS TotalMarketRank,
					ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = 'PPI Total' AND xpogeography.doctorsubterritory = @DoctorSubTerritory THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS 'Prod2RankTotal',
					ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = 'Merck-Generics Total' AND xpogeography.doctorsubterritory = @DoctorSubTerritory THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS 'Prod1RankTotal'
			FROM		xpotimeperiod
			INNER JOIN	xpomeasures ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
			INNER JOIN	xpomarket ON xpomeasures.xpomarketid = xpomarket.xpomarketid
			INNER JOIN	xpogeography ON xpomeasures.geographyid = xpogeography.geographyid
			WHERE		xpomeasures.datasetname = @DatasetName
					AND xpotimeperiod.timeperioddesc = @TimePeriodDesc
			GROUP BY	xpogeography.doctorgroup
		) AS s ON s.doctorgroup = a.doctorgroup
WHERE		xpomeasures.datasetname = @DatasetName
		AND xpotimeperiod.timeperioddesc = @TimePeriodDesc
		AND a.doctorsubterritory = @DoctorSubTerritory
GROUP BY	xpospecialty.specialtydesc,
		a.doctorsubterritory,
		a.doctorbrick,
		a.doctorbrick,
		a.doctorgroup,
		fn_mvparamsorted.parame,
		fn_mvparamsorted.sortid,
		xpomarket.productdesc,
		s.totalmarketrank,
		s.prod1ranktotal,
		s.prod2ranktotal
ORDER BY	s.totalmarketrank,
		a.doctorgroup,
		fn_mvparamsorted.sortid

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-11 : 10:02:15

"i suppose the results were cached"

The Query Plan should be cached. For a large query that could take a significant amount of time to regenerate.

In addition the data read from disk might well ALL be in memory (or some/most of it), which would help performance on repeat running.

Kristen

ThePrisoner
Starting Member

18 Posts

Posted - 2007-09-12 : 03:06:54

the query still takes the same amount of time.

thanks for your suggestions. I'll have to figure out some other way to speed up the results.

ThePrisoner
Starting Member

18 Posts

Posted - 2007-09-14 : 04:19:27

quote:
Originally posted by ThePrisoner

the query still takes the same amount of time.

thanks for your suggestions. I'll have to figure out some other way to speed up the results.

with the use of temp tables ,I reduced the run time to 20 minutes

Kristen
Test

22859 Posts

Posted - 2007-09-14 : 05:37:54

What was it before?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-14 : 07:19:13