T-SQL (SS2K8) :: Using Union ALL Or Union Kills Performance On Stored Proc
Jun 12, 2014
SQL Server 2008 r2...
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS (
SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum
FROM[UT_Pole] A
LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
I hope I can express what I need to know.Here it goes. I am planning on writing a single stored procedure to include two select statements and followed by a third like so:Select * etcUNION ALLSelect * etcGOSelect something elseMy question is this: does this return one record set (returning all records from the three Select statements) or two, the first being the result from the UNION and the second from the select following the GO. I will need to access the data returned from the resultant UNION and from the select after the Go, so I wonder if I'll be dealing with a single dataset or two. I hope this made sense.
I get a red squiggle below the first IF statement. Can't figure out the correct syntax.
UNION ALL IF ((SELECT Flexpool FROM Huizen WHERE IDHuis = @LOCATIECODE ) = 'WAAR') SELECTM.LOCATIECODE, M.AfdelingZPT, DATEPART(ISO_WEEK, M.Begindatum) wk, M.DienstGroep, NULL Leeg, NULL Leeg, SUM(M.Hrs) AS Uren FROMDRPHrsManual M
---Selection results SELECT L.Segment , l.email, l.Addr1 , l.City ,l.ST, l.code, s.Fname , s.LName into #expectedresult FROM #LIST L JOIN #Subject S ON l.Segment =s.Segment
--Now here what i am trying to get, union the below two record to the above "selection Results" for only segment ='VEC_BAL' or segment = 'PPC_BAL'
--2 records SELECT l.segment , l.email , Addr1, City, ST, 999 as Code,'' fname, ''lname from #LIST l where email ='mmm@m.com' SELECT l.segment , l.email , Addr1, City, ST, 888 as Code, '' fname, ''lname from #LIST l where email ='lrk@m.com'
--- expected results please execute below block ( per each segment 2 records will insert if there is segment ='VEC_BAL' or 'PPC_BAL' ). If there is no Vec_Bal or PPC BAL then no additional inserts, if there is only VEC_BAL no PPC_BAL then only 2 records insert SELECT L.Segment , l.email, l.Addr1 , l.City ,l.ST, l.code, s.Fname , s.LName into #expctresults1 FROM #LIST L JOIN #Subject S ON l.Segment =s.Segment union
I have a view which uses UNION of two tables. First table has a 1.5 Million records and the second one has 40,000 records. When I query the view with a column (that is indexed in both tables) in the where clause, it's taking taking 3 Minutes to give the result. The column is of DateTime data Type. Any ideas as to how to improve the query performance ???
Right now, a client of mine has a T-SQL statement that does thefollowing:1) Create a temp table.2) Populate temp table with data from one table using an INSERTstatement.3) Populate temp table with data from another table using an INSERTstatement.4) SELECT from temp table.Would it be more efficient to simply SELECT from table1 then UNIONtable 2? The simply wants to see the result set and does not need tore-SELECT from the temp table.
Currently I have a standard query with a join to several tables. There are two additional tables MAS_CTB and MAS_STB. I would like to do a union between those two tables to get FIELDVALUE which will exist in either the CTB table or the STB table and then have that value be returned with the results of the original query.
I can of course write a UNION from the main query to the CTB and then to the STB table, however it's about 80 dummy fields I would have to replicate in the union which is why I was wondering if there was a more simple way.
Main Query: SELECT Field1, Field2...Field80 From Table1 Join Table2
Union Portion: Select FieldValue <------ From MAS_CTB UNION Select FieldValue <-----Return to main query above From MAS_STB
I have a Union All transformation with 4 inputs and one output when I debug the package the sum of the different inputs rows does not match the row count in output.
I don't understand, I've used the Union All transform many times and I've never seen this.
I'm fairly new to MSSQL 7.0, and am having a problem. I would like to create a Stored Procedure that builds a Union of the result sets from two Stored Procedures. In reality, it is two calls to a single stored procedure, with different parameters.
I can't find any documented method to actually perform any operations on the result set of a stored procedure. Is there any way to get a SELECT statement to return the results of a Stored Procedure call?
I have a stored procedure using UNION joins on three SQL queries. Sadly, I'm only now learning how to use Stored Procedures, so if this is a really dumb question, please forgive me. I'm not used to big UNION statements like this either... usually I'm just programming websites to serve information out pretty simply :) I need to return one result set, sorted by date... one complete result per day. eg: 5/15/2007 | XX | XX | XX | XX | XX | XX |5/16/2007 | XX | XX | XX | XX | XX | XX |5/17/2007 | XX | XX | XX | XX | XX | XX | Currently, when I run the query, I'm getting three separate date values for each date... eg:5/15/2007 | XX | XX | 00 | 00 | 00 | 00 |5/15/2007 | 00 | 00 | XX | XX | 00 | 00 |5/15/2007 | 00 | 00 | 00 | 00 | XX | XX |5/16/2007 | XX | XX | 00 | 00 | 00 | 00 |5/16/2007 | 00 | 00 | XX | XX | 00 | 00 |5/16/2007 | 00 | 00 | 00 | 00 | XX | XX |etc How do I fix this? I've looked through my query ad naseum and don't see anything that sets me off as "wrong". Here is the stored procedure if you can help. I'd really really love the help!
C R E A T E P R O C E D U R E sp_ApptActivityDate (@strWHERE as varchar(500), @strWHERECANCELED as varchar(500)) as exec ('SELECT [date] AS Date, SUM(length) AS TotalSlots, COUNT(cast(substring(appointUniqueKey, 1, 1) AS decimal)) AS TotalAppts, SUM(length * 5) / 60 AS TotalSlotHours, 0 AS TotalActiveSlots, 0 AS TotalActiveAppts, 0 AS TotalActiveSlotHours, 0 AS totalCancelSlots, 0 AS TotalCancelAppts, 0 AS TotalCancelSlotHoursFROM dbo.vw_ALL_ApptActivity ' + @strWHERE + ' UNIONSELECT [date] as DATE, 0 AS TotalSlots, 0 AS TotalAppts, 0 AS TotalSlotHours, SUM(length) AS TotalActiveSlots, COUNT(cast(substring(appointuniquekey, 1, 1) AS decimal)) AS TotalActiveAppts, SUM(length * 5) / 60 AS TotalActiveSlotHours, 0 AS totalCancelSlots, 0 AS TotalCancelAppts, 0 AS TotalCancelSlotHoursFROM dbo.vw_Active_ApptActivity' + @strWHERE + ' UNIONSELECT [date] as DATE, 0 AS TotalSlots, 0 AS TotalAppts, 0 AS TotalSlotHours, 0 AS TotalActiveSlots, 0 AS TotalActiveAppts, 0 AS TotalActiveSlotHours, SUM(length) AS totalCancelSlots, COUNT(cast(substring(AppointUniqueKey, 1, 1) AS decimal)) AS TotalCancelAppts, SUM(length * 5) / 60 AS TotalCancelSlotHoursFROM dbo.vw_CANCELED_ApptActivity ' + @strWHERECANCELED + ' ORDER BY dbo.vw_ALL_ApptActivity.[Date] ' )GO
I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.
I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501 SELECT 1,2,3,4,'CurrentSetting' FROM TableA ta WHERE tblid = @tblid UNION SELECT 1,2,3,4,'PreviosSetting' FROM Tableb tb WHERE tblid = @tblid
I have a stored proc which is doing a search from a few tables by doing an inner join. When I enter a new search string, the proc takes some time to get the results. Whereas when I run this again after a few times, it gives results immediately. I know it is due to proc cache but still is there any way to sor this out.
Another problem is that, if a enter a string which is not available in the database itself then it takes a very long time. But takes relatively less time if the string exists in the database.
I just found out that I can do an ORDER BY clause on entire records set retrieve from a query that combines several sub queries with UNION from different tables with the same structure... so this is great to know, BTW, is this a new feature of MSSQL 2K ? I don't recall being able to do this in MSSQL 7 or 6.5.
Anyway, the main question is, can I use the TOP command in a query that has UNION in it?? Meaning, there are two queries (or more) from two tables (or more) and I need to fetch the top 10 records by an ORDER BY clause from the combined results, when I try to add each sub query TOP 10 the results are not correct at all, when I try to add TOP 10 only to the first query hoping that the analyzer will refer to the whole query, it's selecting TOP 10 from the first query and combines it with all the records from the others...
So, can anyone help? I hope the problem is understood.
I am trying to join to different queries into one table ( I accomplished this)
Next I need to ADD or SUM the results of 2 rows to form a single row.
As you can see in the query below, I run 2 separate queries and use ' ' as a place holder for the UNION to work. I get duplicate rows, one with a value and the other with a '0'. I want to have a single row.
Any help is greatly appreciated!
(SELECT v_gs_supportedpackages.ProdID0 as 'Product Name', v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Site', Count(ProdID0) as '# copies installed', '' as '# legitimate copies installed'
FROM v_R_System SYS, v_GS_Workstation_Status HWSCAN, v_gs_SupportedPackages inner join v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID
WHERE SYS.ResourceId = HWSCAN.ResourceId AND SYS.ResourceId = v_gs_SupportedPackages.ResourceId AND v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)
(SELECT v_gs_supportedpackages.ProdID0 as 'Product Name', v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Site', '' as '# copies installed', Count(ProdID0) as '# legitimate copies installed'
FROM v_R_System SYS, v_GS_Workstation_Status HWSCAN, v_gs_SupportedPackages inner join v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID
WHERE SYS.ResourceId = HWSCAN.ResourceId AND SYS.ResourceId = v_gs_SupportedPackages.ResourceId AND v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60) AND DateDiff(Day,HWSCAN.LastHWScan,GetDate()) <= '20'
By default, the UNION operator removes duplicate rows from the result set. If you use ALL, all rows are included in the results and duplicates are not removed.
Why is it assumed that one would want the duplicates removed by default? Isn't that what SELECT DISINCT is for?
I was wondering if there is a way I could write this query as one query using union instead of 3 different queries?
SELECT columna as column,count(ID) as Applications FROM tablea GROUP columna
SELECT columna as column2, count(ID) as Approved FROM tablesa where substring(APP_DATE,1,6) >'200304' and in 'Approved') GROUP BY columna
SELECT columna as column3, count(ID) as Booked, sum(AMT) as amt, sum(AMT)/count(ID) as lavg from tablea where substring(APP_DATE,1,6) >'200304' and STATUS in('book') group by columna
I am doing a UNION of 8 views(with 3 million rows in each view approx). I am sure that there is NO DUPLICATE data between the views. SO specifying a UNION ALL would be better than specifying a UNION in this case?
Thanks!
Prakash.P The secret to creativity is knowing how to hide your sources!
Hi All I want to do the following report and I want the 2nd select statement to appear at the bottom to display the totals
SELECT Item,B.Mar, B.Apr, B.May, B.Jun,B.Jul, B.Aug, B.Sep, B.Oct, B.Nov, B.Dec, B.Jan FROM fncWineSales(2007) AS A UNION SELECT 'Total',SUM(B.Mar), SUM(B.Apr), SUM(B.May), SUM(B.Jun),SUM(B.Jul), SUM(B.Aug), SUM(B.Sep), SUM(B.Oct), SUM(B.Nov), SUM(B.Dec), SUM(B.Jan) FROM dbo.fncWineSales (2007) AS B
Hello,Bear with me (not had much sleep last night), pls see following ddl,dml and comments for what is desired, I don't have a problem gettingdesired result(s), however, I'm wondering if there's another (better)solution than UNION operator in this case. TIA.-- DDLCREATE TABLE #TMP (col varchar(10));-- DMLinsert into #TMPvalues('A124');insert into #TMPvalues('A127');insert into #TMPvalues('A12728');insert into #TMPvalues('A17282');insert into #TMPvalues('BCD');insert into #TMPvalues('BCD');insert into #TMPvalues('CDSS');insert into #TMPvalues('DS');insert into #TMPvalues('YUUEI');-- goal: get one row with col data starting with 'A' and distict rowsfor the restselect top 1 colfrom #TMPwhere col LIKE 'A%'UNIONselect distinct colfrom #TMPwhere col NOT LIKE 'A%'
Hi,I can return results of a union easily enough, but I wish to sum the recordsfirst. Is this possible in a single SQL statement? Or do I have to useseparate ones to sum up after the union?Cheers,Chris
Of the two queries below, the first returns the desired result, the union of the select-except statments. Why doesn't the second query return the same result as the first? Is this a mistake/bug on Microsofts side?
When any changes have been made to the underlying table structure, the Union all reports error and does not automatically correct the error. I then have to delete it and recreate it. Is this a bug?
