November 26, 2023 at 1:58 pm
Could you please help?
My Union all sql query consist of three individual queries that runs quickly on its own. However when they are in a union all statement it runs up to a point then kind of hangs and then resume after a while. It runs for a long time. The first two queries in the union runs quickly but when I union it to third one it runs slower for a long time before completion. I have checked indexes on the the third query and applied missing indexes. I am using SQL Server 2016.
Your help is appreciated.
Query and estimated plan attached.
November 26, 2023 at 2:00 pm
select
'Securities' as PositionGroup ,
'' Product,
cob,
InstrumentRef,
Company,
PartyRef,
Position,
PositionDT,
BalanceCD,
ColumnCD,
AccInt,
InstrumentGrp,
CAST( AccountCode1 AS VARCHAR(50)) AS AccountCode1,
DebtAmt1,
CreditAmt1,
CAST( AccountCode2 AS VARCHAR(50)) AS AccountCode2,
DebtAmt2,
CreditAmt2,
Accrual_Lifecycle_Event,
PTD_Accrued_Interest_TB,
MidPrice_Close,
BidPrice,
OfferPrice,
PalpType,
DenomCCY,
Position_Notional,
Position_Cost,
MarketValue,
UnrealPnL,
RealPnL,
Daily_Position_Mvt,
Daily_UnrealPnL,
Daily_PnL,
AveragePrice,
BookDescription,
MarketPrice,
MidFX_Close,
Market_Value_Base,
Daily_PnL_Base,
Daily_MarketPrice_Mvt,
'' as LateTradeInd
from [SSASTabular].[vWStockPosition_Securities]
union all
select 'Derivative- Futures' as PositionGroup,
product,
cob,
InstrumentRef,
Company,
PartyRef,
Position,
PositionDT,
BalanceCD,
ColumnCD,
AccInt,
InstrumentGrp,
CAST(NULL AS VARCHAR(50)) AS AccountCode1,
DebtAmt1,
CreditAmt1,
--NULL AccountCode2,
CAST( AccountCode2 AS VARCHAR(50)) AS AccountCode2,
DebtAmt2,
CreditAmt2,
NULL Accrual_Lifecycle_Event,
PTD_Accrued_Interest_TB,
MidPrice_Close,
BidPrice,
OfferPrice,
PalpType,
DenomCCY,
Position_Notional,
Position_Cost,
MarketValue,
UnrealPnL,
RealPnL,
Daily_Position_Mvt,
Daily_UnrealPnL,
Daily_PnL,
AveragePrice,
NULL BookDescription,
MarketPrice,
MidFX_Close,
Market_Value_Base,
Daily_PnL_Base,
Daily_MarketPrice_Mvt,
LateTradeInd
from SSASTabular.vWStockPosition_Futures
union all
select
'CDS' as PositionGroup,
product,
cob,
InstrumentRef,
Company,
PartyRef,
Position,
PositionDT,
BalanceCD,
ColumnCD,
AccInt,
InstrumentGrp,
AccountCode1,
DebtAmt1,
CreditAmt1,
AccountCode2,
DebtAmt2,
CreditAmt2,
Accrual_Lifecycle_Event,
PTD_Accrued_Interest_TB,
MidPrice_Close,
BidPrice,
OfferPrice,
PalpType,
DenomCCY,
Position_Notional,
Position_Cost,
MarketValue,
UnrealPnL,
RealPnL,
Daily_Position_Mvt,
Daily_UnrealPnL,
Daily_PnL,
AveragePrice,
BookDescription,
MarketPrice,
MidFX_Close,
Market_Value_Base,
Daily_PnL_Base,
Daily_MarketPrice_Mvt,
LateTradeInd
from SSASTabular.vWStockPosition_CDS
Please find above the script.
November 26, 2023 at 2:01 pm
This was removed by the editor as SPAM
November 26, 2023 at 3:13 pm
Your query is probably trying to access all the tables at the same time and on a not very powerful machine with a slow disk it might be slowing it down. If they run quickly independently just insert the results of each one into a temporary table, then query the temp table:
DROP TABLE IF EXISTS #TempTable;
-- Create the temporary table using the first SELECT query
SELECT
'Securities' AS PositionGroup,
'' AS Product,
cob,
InstrumentRef,
Company,
PartyRef,
Position,
PositionDT,
BalanceCD,
ColumnCD,
AccInt,
InstrumentGrp,
CAST(AccountCode1 AS VARCHAR(50)) AS AccountCode1,
DebtAmt1,
CreditAmt1,
CAST(AccountCode2 AS VARCHAR(50)) AS AccountCode2,
DebtAmt2,
CreditAmt2,
Accrual_Lifecycle_Event,
PTD_Accrued_Interest_TB,
MidPrice_Close,
BidPrice,
OfferPrice,
PalpType,
DenomCCY,
Position_Notional,
Position_Cost,
MarketValue,
UnrealPnL,
RealPnL,
Daily_Position_Mvt,
Daily_UnrealPnL,
Daily_PnL,
AveragePrice,
BookDescription,
MarketPrice,
MidFX_Close,
Market_Value_Base,
Daily_PnL_Base,
Daily_MarketPrice_Mvt,
'' AS LateTradeInd
INTO #TempTable
FROM [SSASTabular].[vWStockPosition_Securities];
-- Insert into the temporary table using the second SELECT query
INSERT INTO #TempTable
SELECT
'Derivative- Futures' AS PositionGroup,
product,
cob,
InstrumentRef,
Company,
PartyRef,
Position,
PositionDT,
BalanceCD,
ColumnCD,
AccInt,
InstrumentGrp,
CAST(NULL AS VARCHAR(50)) AS AccountCode1,
DebtAmt1,
CreditAmt1,
CAST(AccountCode2 AS VARCHAR(50)) AS AccountCode2,
DebtAmt2,
CreditAmt2,
NULL AS Accrual_Lifecycle_Event,
PTD_Accrued_Interest_TB,
MidPrice_Close,
BidPrice,
OfferPrice,
PalpType,
DenomCCY,
Position_Notional,
Position_Cost,
MarketValue,
UnrealPnL,
RealPnL,
Daily_Position_Mvt,
Daily_UnrealPnL,
Daily_PnL,
AveragePrice,
NULL AS BookDescription,
MarketPrice,
MidFX_Close,
Market_Value_Base,
Daily_PnL_Base,
Daily_MarketPrice_Mvt,
LateTradeInd
FROM SSASTabular.vWStockPosition_Futures;
-- Insert into the temporary table using the third SELECT query
INSERT INTO #TempTable
SELECT
'CDS' AS PositionGroup,
product,
cob,
InstrumentRef,
Company,
PartyRef,
Position,
PositionDT,
BalanceCD,
ColumnCD,
AccInt,
InstrumentGrp,
AccountCode1,
DebtAmt1,
CreditAmt1,
AccountCode2,
DebtAmt2,
CreditAmt2,
Accrual_Lifecycle_Event,
PTD_Accrued_Interest_TB,
MidPrice_Close,
BidPrice,
OfferPrice,
PalpType,
DenomCCY,
Position_Notional,
Position_Cost,
MarketValue,
UnrealPnL,
RealPnL,
Daily_Position_Mvt,
Daily_UnrealPnL,
Daily_PnL,
AveragePrice,
BookDescription,
MarketPrice,
MidFX_Close,
Market_Value_Base,
Daily_PnL_Base,
Daily_MarketPrice_Mvt,
LateTradeInd
FROM SSASTabular.vWStockPosition_CDS;
-- Select from the temporary table
SELECT *
FROM #TempTable;
November 26, 2023 at 6:42 pm
This was removed by the editor as SPAM
November 26, 2023 at 7:48 pm
Why aren't you just using 3 inividual inserts ?
Especially when those perform way better.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 27, 2023 at 6:13 am
Check the 3 underlying views. If they're all pulling from the same tables, it might be worth while to crib some of the code and write a separate query, especially since you're copying all the rows offered by all 3 views.
If not, then the 3 separate queries will keep you out of the woods at the expense of not getting "Minimal Logging" in the 2nd 2 inserts.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 27, 2023 at 7:04 am
It sounds like combining those three queries into one with UNION ALL is causing a slowdown. Even though the first two are fast, the third one's making things sluggish. You've checked indexes and added some that were missing, which is good. One thing you might want to do is to check if the third query's results are much larger than the first two. Sometimes combining big results can slow things down. Also, looking at the estimated plan might reveal more about what's causing the delay
November 27, 2023 at 5:08 pm
One thing I would point out is adding indexes is something you should do with caution. I know Bedeencion40 said that adding missing indexes was good, but it isn't always good. Each index you add may help your specific query and can help SELECT statements, it will hurt INSERT, UPDATE, and DELETE performance as well as eat up more disk space. My guess on the delay is that you have some self-blocking going on and the query MAY have gone parallel when it needs to be single threaded. MAY not hurt to try running it single threaded (MAXDOP 1 query hint)? I've seen that help query performance before where I had some self-blocking happening.
I would also recommend that you look at the existing indexes and see if you have any unused indexes or indexes that are overlapping and you may want to work on tweaking some of those. Mind you, I strongly encourage you to make all changes on a test system so you can gauge the impact before you accidentally hose production. ALMOST everyone hoses production at some point, but we try our best not to let that happen.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 27, 2023 at 5:41 pm
Check the 3 underlying views. If they're all pulling from the same tables, it might be worth while to crib some of the code and write a separate query, especially since you're copying all the rows offered by all 3 views.
If not, then the 3 separate queries will keep you out of the woods at the expense of not getting "Minimal Logging" in the 2nd 2 inserts.
Hi Jeff,
Have you written an article on minimal logging?
November 27, 2023 at 7:01 pm
Jeff Moden wrote:Check the 3 underlying views. If they're all pulling from the same tables, it might be worth while to crib some of the code and write a separate query, especially since you're copying all the rows offered by all 3 views.
If not, then the 3 separate queries will keep you out of the woods at the expense of not getting "Minimal Logging" in the 2nd 2 inserts.
Hi Jeff,
Have you written an article on minimal logging?
Funny that you should bring that up. To make a longer story short, I was challenged to write what he said was basically an impossible thing to do without blowing out the log file. I've seriously proved him wrong with code and have started to work on a "Minimal Logging" article. If you're in the process of writing one, I'll wait.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 27, 2023 at 7:58 pm
Jonathan AC Roberts wrote:Jeff Moden wrote:Check the 3 underlying views. If they're all pulling from the same tables, it might be worth while to crib some of the code and write a separate query, especially since you're copying all the rows offered by all 3 views.
If not, then the 3 separate queries will keep you out of the woods at the expense of not getting "Minimal Logging" in the 2nd 2 inserts.
Hi Jeff,
Have you written an article on minimal logging?
Funny that you should bring that up. To make a longer story short, I was challenged to write what he said was basically an impossible thing to do without blowing out the log file. I've seriously proved him wrong with code and have started to work on a "Minimal Logging" article. If you're in the process of writing one, I'll wait.
I haven't and not in the process of writing one either. But I'm looking forward to reading yours once written. You often tell people to use minimal logging but don't tell them how to so an article would be really useful.
My only knowledge is you use TABLOCK or TABLOCKX, but I'm sure there is more to it than that.
INSERT INTO #TempTable WITH (TABLOCK)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply