Link to home
Start Free TrialLog in
Avatar of gbzhhu
gbzhhuFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Query performance SQL Server

HI all,
I have the stored procedure below (sql server 2008).  It works fine.  It returns the whole result broken into 10 sets.  I need this for later zipping each set in a web service.

It takes 3 and half minutes to return data that is about 600,000 rows so each set is about 60,000.  What can do to to speed it up.  I know the cursor doesn't help but I know no other way of returning the data the way I want.

Help much appreciated

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[ipadClient_getCustomerPriceFullSync]
	 @accountManagerId int
AS
BEGIN

	DECLARE @noOfBlocks INT, @recordsPerBlock INT, @customerPriceCount INT, @isFullCustomersAM BIT
	SET @noOfBlocks = 10

	DECLARE @AmCustomerPrices TABLE(
    id INT NOT NULL,
    customerRef NVARCHAR(50),
    productRef NVARCHAR(50),
    validFrom DATE,
	validTo DATE,
	price DECIMAL(10, 2),
	active BIT,
	modifiedDate DATETIME);

	SELECT @isFullCustomersAM = CASE WHEN roleRef = 3 THEN 0 ELSE 1 END FROM [dbo].[accountManager] WHERE id = @accountManagerId

	IF @isFullCustomersAM = 0
	BEGIN
		SELECT @customerPriceCount = COUNT(*) FROM dbo.accountManagerCustomer amc
		INNER JOIN dbo.customerPrice cp ON amc.customerSrcRef = cp.customerSrcRef
		LEFT JOIN vwListPrice pr on cp.productSrcRef = pr.productSrcRef
		WHERE amc.accountManagerref = @accountManagerId AND CAST(cp.validTo AS DATE) >= CAST(GETDATE() AS DATE) AND cp.active = 1

		SET @recordsPerBlock = CEILING((CONVERT(DECIMAL(16,2),@customerPriceCount)/@noOfBlocks))

		INSERT INTO @AmCustomerPrices
		SELECT DISTINCT
			cp.id AS id
			,cp.customerSrcRef AS customerRef
			,cp.productSrcRef AS productRef 
			,CONVERT(DATE,cp.validFrom) AS validFrom
			,CONVERT(DATE,cp.validTo) AS validTo
			,CONVERT(DECIMAL(10, 2), ((cp.rate/100) * pr.rate) + pr.rate) AS price
			,cp.active AS active
			,cp.modifiedDate AS modifiedDate
		FROM dbo.accountManagerCustomer amc
		INNER JOIN dbo.customerPrice cp ON amc.customerSrcRef = cp.customerSrcRef
		LEFT JOIN vwListPrice pr on cp.productSrcRef = pr.productSrcRef
		WHERE amc.accountManagerref = @accountManagerId 
			AND amc.enabled = 1 AND amc.deleted=0
			AND CAST(cp.validTo AS DATE) >= CAST(GETDATE() AS DATE) AND cp.active = 1
		order by cp.id
	END
	ELSE
	BEGIN
		SELECT @customerPriceCount = COUNT(*) FROM dbo.customerPrice cp
		LEFT JOIN vwListPrice pr on cp.productSrcRef = pr.productSrcRef
		WHERE CAST(cp.validTo AS DATE) >= CAST(GETDATE() AS DATE) AND cp.active = 1

		SET @recordsPerBlock = CEILING((CONVERT(DECIMAL(16,2),@customerPriceCount)/@noOfBlocks))

		INSERT INTO @AmCustomerPrices
		SELECT DISTINCT
			cp.id AS id
			,cp.customerSrcRef AS customerRef
			,cp.productSrcRef AS productRef 
			,CONVERT(DATE,cp.validFrom) AS validFrom
			,CONVERT(DATE,cp.validTo) AS validTo
			,CONVERT(DECIMAL(10, 2), ((cp.rate/100) * pr.rate) + pr.rate) AS price
			,cp.active AS active
			,cp.modifiedDate AS modifiedDate
		FROM dbo.customerPrice cp
		LEFT JOIN vwListPrice pr on cp.productSrcRef = pr.productSrcRef
		WHERE CAST(cp.validTo AS DATE) >= CAST(GETDATE() AS DATE) AND cp.active = 1
		order by cp.id
	END

	DECLARE @blockCount INT
	SET @blockCount = 0 

	WHILE @blockCount<@noOfBlocks and @customerPriceCount > 0
	BEGIN
	
		SELECT 1 AS Tag
				,NULL AS Parent
				,id AS [cp!1!id]
				,customerRef AS [cp!1!cr]
				,productRef AS [cp!1!pr]
				,CONVERT(DATE, validFrom) AS [cp!1!vf]
				,CONVERT(DATE, validTo) AS [cp!1!vt]
				,price AS [cp!1!p]
				,active AS [cp!1!en]
				,modifiedDate AS [cp!1!md]
			FROM @AmCustomerPrices cp
			ORDER BY [cp!1!cr],[cp!1!pr]
			OFFSET @blockCount*@recordsPerBlock ROWS 
			FETCH NEXT @recordsPerBlock ROWS ONLY 
		FOR XML EXPLICIT, ROOT('cps')

		SET @blockCount = @blockCount + 1
	END

	-- last sync server date  
	SELECT 1 AS Tag
			,NULL AS Parent
			,GETDATE() AS [d!1!date]
			,@blockCount AS [d!1!blk]
			,@customerPriceCount AS [d!1!rows]
			ORDER BY [d!1!date]
	FOR XML EXPLICIT, ROOT('sd')

	RETURN
    
END

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Russ Suter
Russ Suter

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Russ Suter
Russ Suter

@Vitor
Casting may have some use here. GETDATE() returns a DATETIME object. If cp.validTo is also a DATETIME datatype then there may be issues with the TIME part getting in the way. Casting as DATE strips the time information thereby giving you a full day. It's not always the most efficient method but it has its uses.

@gbzhhu
If you need a DATE datatype but also need time information stored you might consider using a computed column that you can index in your table. We use a similar method to compute the month and year which is very useful when we need monthly data.
Avatar of gbzhhu

ASKER

Many thanks to you all guys.

The CAST part .... I am using it to remove the time part as Russ said but I need the time data in the column later.  So I am temporarily removing time

I am gong to rewrite it as set based using Ross's method and come back

Cheers
You can still avoid casting the field:
cp.validTo >= CAST(GETDATE() AS DATE)
Vitor is right. Removing the cast from the left side of the operand should improve performance and will still give you the same result.
Avatar of gbzhhu

ASKER

>>You can still avoid casting the field:

Ok.  I see. If I remove the leftside cast <= will not work but >= will,  right?
Avatar of gbzhhu

ASKER

Yep, got you both.  Thanks
Yes, just because any date with time will be always bigger than a date that has 00:00:00.000 as default time. Avoid using functions on table fields that are used in the WHERE clause so any existing index can be used.
Avatar of gbzhhu

ASKER

Well I am very pleased to report that query has gone down from 3 and half minutes to 27 seconds :-)  Wow!

I have displayed the execution plan - now what info am I looking for?  everything says 0% cost
Avatar of gbzhhu

ASKER

I included the actual execution plan and managed to get one missing index and sql tells me this is what is needed.  Are these tools accurate? am I better off creating the index?

/*
Missing Index Details from SQLQuery15.sql - DBT-DEV-DB-02.pah_promotionPortal_DE_SAP_PROD (HOSTED\administrator (74))
The Query Processor estimates that implementing the following index could improve the query cost by 40.5797%.
*/

/*
USE [pah_promotionPortal_DE_SAP_PROD]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[customerPrice] ([active],[validTo])
INCLUDE ([productSrcRef])
GO
*/
Avatar of gbzhhu

ASKER

Implemented the suggested index and it made it 1 second slower and stayed the same sometimes!!

I am very happy with what I have so will delete it

Thank you both
The Display Estimated Execution Plan is pretty good but not infallible. Sometimes you're better off not creating the index it recommends. I suspect getting rid of the cursor is what really did the trick. I have been spending months rewriting cursor-based stored procedures to set-based ones. There hasn't yet been a case when I wasn't able to gain a fairly significant performance advantage. Cursors are EVIL!

Glad we could help.
Avatar of gbzhhu

ASKER

Thank you both again
Avatar of gbzhhu

ASKER

Cursors are EVIL!

Can't agree more