What is the alter native for union

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

General SQL Server Forums

New to SQL Server Programming

What is the alter native for union

Author

Topic

Haja Mohideen
Starting Member

12 Posts

Posted - 2014-12-02 : 08:27:34

I written one stored procedure that is taking more execution time. Nearly 10 minutes for just less than 1000 rows. I need any alternative method that is instead of doing UNION for two tables try with other like joins or some other method

query is like below,
-------------------
SELECT *
FROM Invoice

UNION

SELECT *
FROM Invoicebak
--------------------

Above two table are CTE. Normally union is giving slow performance.
and is there any other changes i need to do in my stored procedure to improve speed is most appreciation.
-- Full stored procedure is given below,
USE [EDE2_APR14]
GO
/****** Object: StoredProcedure [dbo].[SPEDE2_PARTNERDETAILS] Script Date: 12/02/2014 18:31:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SPEDE2_PARTNERDETAILS] --'10/01/2013','10/03/2014','','','',''
(
@fromdate DATE
,@todate DATE
,@strStockistCode VARCHAR(20)
,@stateID INT
,@cityID INT
,@strCityCode VARCHAR(8000)
)
AS
BEGIN
DECLARE @from_date_1 DATE
DECLARE @to_date_1 DATE

SET @from_date_1 = Cast(@Fromdate AS DATE)
SET @to_date_1 = Cast(@Todate AS DATE)

IF @strStockistCode = ''
BEGIN
SET @strStockistCode = NULL
END

IF @stateID = 0
BEGIN
SET @stateID = NULL
END

IF @cityID = 0
BEGIN
SET @cityID = NULL
END

DECLARE @CityCode INT
DECLARE @delimiter CHAR(1)
DECLARE @start INT
,@end INT
DECLARE @City TABLE (CityID INT)

SET @delimiter = ','

SELECT @start = 1
,@end = CHARINDEX(@delimiter, @strCityCode)

WHILE @start < LEN(@strCityCode) + 1
BEGIN
IF @end = 0
SET @end = LEN(@strCityCode) + 1

INSERT INTO @City (CityID)
VALUES (SUBSTRING(@strCityCode, @start, @end - @start))

SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @strCityCode, @start)
END;

--kill 55
WITH Invoice (
StockListCode
,StockListName
,PartnerCode
,PartnerName
,PartnerCity
,ApprovalStatus
,TransactionStatus
,DATE
)
AS (
SELECT USR1.UserName
,USR1.NAME
,USR.UserName
,USR.NAME
,MCH.CityName
,CASE REL.Approved
WHEN 0
THEN 'NO'
ELSE 'YES'
END
,CASE
WHEN REL.RelateID IS NULL
THEN 'NO'
ELSE 'YES'
END
,INV.ReceivedDate
FROM RELATIONSHIPS REL
LEFT JOIN USERS USR ON USR.ID = REL.RelateID
INNER JOIN USERS USR1 ON USR1.ID = REL.MyID
AND USR1.ROLE = 'FD73E3ED-7021-4069-8D6F-819CF695C27C' --StockList
INNER JOIN INVOICES INV ON INV.SellerID = USR1.ID
INNER JOIN MASTERBRAND MAS ON MAS.UNIFORMPRODUCTCODE = INV.UPC
AND INV.UPC <> ''
--LEFT JOIN @City CT ON CT.CityID = USR.CityID
INNER JOIN AWACSCITYMASTER MCH ON MCH.CityID = USR.CityID
INNER JOIN AWACSDistrictMaster DM ON DM.District_Code_Id = MCH.District_Id
INNER JOIN AWACSStateMaster SMH ON SMH.StateID = DM.State_Id
WHERE REL.CreatedDate >= @from_date_1
AND REL.CreatedDate <= @to_date_1
AND INV.ReceivedDate >= @from_date_1
AND INV.ReceivedDate<= @to_date_1
AND USR1.UserName = COALESCE(@strStockistCode, USR1.UserName)
AND SMH.StateID = COALESCE(@stateID, SMH.StateID)
AND MCH.CityID = COALESCE(@cityID, MCH.CityID)
)
,Invoicebak (
StockListCode
,StockListName
,PartnerCode
,PartnerName
,PartnerCity
,ApprovalStatus
,TransactionStatus
,DATE
)
AS (
SELECT USR1.UserName
,USR1.NAME
,USR.UserName
,USR.NAME
,MCH.CityName
,CASE REL.Approved
WHEN 0
THEN 'NO'
ELSE 'YES'
END
,CASE
WHEN REL.RelateID IS NULL
THEN 'NO'
ELSE 'YES'
END
,INV.ReceivedDate
FROM RELATIONSHIPS REL
LEFT JOIN USERS USR ON USR.ID = REL.RelateID
INNER JOIN USERS USR1 ON USR1.ID = REL.MyID
AND USR1.ROLE = 'FD73E3ED-7021-4069-8D6F-819CF695C27C' --StockList
INNER JOIN Invoices_Bak INV ON INV.SellerID = USR1.ID
INNER JOIN MASTERBRAND MAS ON MAS.UNIFORMPRODUCTCODE = INV.UPC
AND INV.UPC <> ''
--LEFT JOIN @City CT ON CT.CityID = USR.CityID
INNER JOIN AWACSCITYMASTER MCH ON MCH.CityID = USR.CityID
INNER JOIN AWACSDistrictMaster DM ON DM.District_Code_Id = MCH.District_Id
INNER JOIN AWACSStateMaster SMH ON SMH.StateID = DM.State_Id
WHERE REL.CreatedDate >= @from_date_1
AND REL.CreatedDate <= @to_date_1
AND INV.ReceivedDate >= @from_date_1
AND INV.ReceivedDate<= @to_date_1
AND USR1.UserName = COALESCE(@strStockistCode, USR1.UserName)
AND SMH.StateID = COALESCE(@stateID, SMH.StateID)
AND MCH.CityID = COALESCE(@cityID, MCH.CityID)
)

SELECT *
FROM Invoice

UNION

SELECT *
FROM Invoicebak

--AND INV.ReceivedDate >= '11/08/2013'
--AND INV.ReceivedDate<= '11/05/2014'
--GROUP BY USR.UserName,USR.Name,USR1.UserName,USR1.Name,MCH.CityName,REL.Approved,INV.ReceivedDate,REL.RelateID
--ORDER BY USR1.Name
END

-- exec [dbo].[SPEDE2_PARTNERDETAILS] '10/01/2013','10/03/2014','','','',''

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-02 : 08:41:22

Your problem is not in the UNION, it's in the JOINs. Examine the execution plan. Check that all the columns referred to in the JOIN and WHERE predicates are indexed.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-12-02 : 19:25:19

One small idea: If the two tables being UNION'd together have distinct data, you should use UNION ALL. It saves the overhead of sorting and removing duplicates.
One small idea: For your date ranges (e.g., INV.ReceivedDate), use the BETWEEN operator in lieu of the <= and >= operators; it's often much quicker

No amount of belief makes something a fact. -James Randi

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-02 : 19:27:00

Run this and post the IO output (not the result set output):

SET STATISTICS IO ON
exec [dbo].[SPEDE2_PARTNERDETAILS] '10/01/2013','10/03/2014','','','',''

The output will lead you to which tables to focus on first.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Haja Mohideen
Starting Member

12 Posts

Posted - 2014-12-04 : 06:53:29

Dear tkizer,
Thank you so much for your response, below is IO output,

(80287 row(s) affected)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'INVOICES'. Scan count 2, logical reads 6396, physical reads 4, read-ahead reads 62, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'RELATIONSHIPS'. Scan count 2, logical reads 2046, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'USERS'. Scan count 4, logical reads 3394, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'DRL_MasterCity_hdr'. Scan count 2, logical reads 26, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'DRL_MasterDistrict_hdr'. Scan count 2, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'DRL_StateMaster_hdr'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Note: As per above tips i added indexes, so it is giving good improvement from 3 minutes to 10 seconds.

Is there any more changes i need to apply on stored procedure then kindly share your valuable idea's

Nothing is impossible

Haja Mohideen
Starting Member

12 Posts

Posted - 2014-12-04 : 06:57:40

quote:
Originally posted by tkizer

Run this and post the IO output (not the result set output):

SET STATISTICS IO ON
exec [dbo].[SPEDE2_PARTNERDETAILS] '10/01/2013','10/03/2014','','','',''

The output will lead you to which tables to focus on first.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Nothing is impossible

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-04 : 08:40:13

Try changing UNION to UNION ALL, unless you have duplicate rows that you want to preserve

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-04 : 11:57:14

The stats IO output doesn't look too bad. It's not perfect but isn't too bad. Could you post the execution plan in XML format?

The USERS table would be the first place I'd start. You might need to adjust an index by making it a covering index.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-04 : 11:58:52

quote:
Originally posted by gbritton

Try changing UNION to UNION ALL, unless you have duplicate rows that you want to eliminate

Haja Mohideen
Starting Member

12 Posts

Posted - 2015-01-02 : 01:02:05

Dear all
Thank you so much

Nothing is impossible

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources