For lover's of Deadlock art I give you this one
With just around 122 Process ids involved in this one...… https://twitter.com/i/web/status/868092774998040576
So about six weeks ago, my mother’s illness took a turn for the worst, and I took some time off work to take care of her. It was all very rushed because we never expected her condition to worsen so quickly and things were just very messy. I was also pretty stressed at the time and perhaps I didn’...
I started a part-time software development job two months ago. The company I work for has roughly 50% volunteers, but the position I applied for was paid (I still have a copy of the job advertisement).
I asked a question about how I should submit my time sheets to get paid, and they replied, "As...
Well, yes for the amounts, but you will find that in pretty much every jurisdiction forgery is a non trivial crime with jail time attached. — TomTom23 hours ago
You can use either ISNULL() or COALESCE() function:
SELECT
BlogID = B.BlogID,
BlogUserMappingID = COALESCE(BUM.BlogUserMappingID, 0)
-- ...
or the more verbose CASE expression:
-- BlogUserMappingID = CASE WHEN BUM.BlogUserMappingID IS NULL
-- THEN 0 ELSE B...
@ypercubeᵀᴹ Why is tagged as community wiki this answer?
As @ypercube has pointed out on his comments you can use replace() function in ON clause to compare both strings and get the desired result.
select title, path
from t1
inner join t2
on 'path/' || replace(t1.title, ' ', '-') = t2.path;
title | path ...
I'm quite fond of the hole-filling optimization, but my experience has been that it all too often fails to materialize, or other non-MERGE methods turn out to be 'better'.
INSERT tbl_A (col, col2) SELECT col, col2 FROM tbl_B WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE A2.col = tbl_B.col);
considering using MERGE instead
but the top of the MERGE article on BOL says specifically not to do that
Performance Tip: The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics. For example, inserting a row if it does not exist, or updating the row if it does match. When simply updating one table based on the rows of another table, improved performance and scalability can be achieved with basic INSERT, UPDATE, and DELETE statements. For example:
"Better' is just completely dependent on the circumstances. I wrote part 3 because the hole-filling optimization is genuinely an optimization people should consider. It is just as safe under concurrency as other methods btw.
My personal experience has been that I have not been able to use it as successfully as I had hoped, for one reason or another.
Is there a command in PostgreSQL for dropping all views and functions owned by a particular role? I don't understand how all the internal tables are connected together so that I can create such a command.
Is there a command in PostgreSQL for dropping all views and functions owned by a particular role? I'm reading the Postgres docs but having a hard time understanding how all the internal tables are connected together so that I can create such a command.
I've suggested to the OP that they ask about shortening multi-word strings in a new question. Please consider removing your answer from this thread and re-posting it in the new one once it (the new thread) appears. Addressing tangential points raised in comments makes questions messy and is really against the format of this site. Thank you! — Andriy M6 mins ago
The other thing about MERGE is that compute sequence that cannot go parallel.
@JoeObbish Is it really so unrealistic that a (largely) in-memory write and read of 100k rows in an optimized format is a bit cheaper than merge joining 150k and 100k rows?
Don't get me wrong though - it would be awesome if hole-filling was inplemented for INSERT...NOT EXISTS.
I imagine that would be rather more complex than it sounds though.
the sort is 150X more expensive to the query optimizer
INSERT tbl_A WITH (TABLOCK) (col, col2) SELECT col, col2 FROM tbl_B WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE A2.col = tbl_B.col) OPTION (HASH JOIN, MAXDOP 1);
INSERT tbl_A WITH (TABLOCK) (col, col2) SELECT col, col2 FROM tbl_B WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE A2.col = tbl_B.col) OPTION (MAXDOP 1);
I know that the query optimizer won't deduce that the data is already sorted and lower the cost of the sort on that basis
The Sort in that query is costed as a normal sort. Yes it will ideally be completed in memory, but a sort is still quite an expensive operation.
The eager table spool has a low cost because it uses an optimized format that will reside mainly in memory, backed by tempdb. It is an awful lot cheaper than accessing 'real' relations via the normal storage engine interfaces.
Which is not to say that spools can't be slow and expensive - they can - but they're costed low for a reason.
And to preempt, no, spools don't use memory grant. They use regular pages in the buffer pool with optimizations to delay or avoid writing them to the backing store (tempdb) and with greatly reduced logging.
You can use either ISNULL() or COALESCE() function:
SELECT
BlogID = B.BlogID,
BlogUserMappingID = COALESCE(BUM.BlogUserMappingID, 0)
-- ...
or the more verbose CASE expression:
-- BlogUserMappingID = CASE WHEN BUM.BlogUserMappingID IS NULL
-- THEN 0 ELSE B...
As @ypercube has pointed out on his comments you can use replace() function in ON clause to compare both strings and get the desired result.
select title, path
from t1
inner join t2
on 'path/' || replace(t1.title, ' ', '-') = t2.path;
title | path ...
Is there a command in PostgreSQL for dropping all views and functions owned by a particular role? I'm reading the Postgres docs but having a hard time understanding how all the internal tables are connected together so that I can create such a command.
