gbzhhu
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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)
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.
ASKER
>>You can still avoid casting the field:
Ok. I see. If I remove the leftside cast <= will not work but >= will, right?
Ok. I see. If I remove the leftside cast <= will not work but >= will, right?
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.
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
I have displayed the execution plan - now what info am I looking for? everything says 0% cost
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_promotio nPortal_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_SA P_PROD]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[customerPrice] ([active],[validTo])
INCLUDE ([productSrcRef])
GO
*/
/*
Missing Index Details from SQLQuery15.sql - DBT-DEV-DB-02.pah_promotio
The Query Processor estimates that implementing the following index could improve the query cost by 40.5797%.
*/
/*
USE [pah_promotionPortal_DE_SA
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[customerPrice] ([active],[validTo])
INCLUDE ([productSrcRef])
GO
*/
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
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.
Glad we could help.
ASKER
Thank you both again
ASKER
Cursors are EVIL!
Can't agree more
Can't agree more
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.