« first day (1948 days earlier)      last day (2910 days later) » 

Paul White
Paul White
1:33 AM
2
A: Transposing hierachical data from one VARCHAR to two INTs

wBobAs your data looks a bit like hierarchyId I thought about using that. Initial versions did not scale well up to 1 million rows, however a few indexing choices on the main temp table have helped. However issues could also be due to my test data, so can you tell me a bit more about your hierarchy...

 
 
2 hours later…
swasheck
swasheck
3:35 AM
@billinkc is there any smarter way to upgrade packages and connections than to open the solution in a newer version of SSDT and change every. single. connection. to get rid of the god-forsaken error?
> Severity Code Description Project File Line Suppression State
Error Error loading : The connection "{BB37BF72-81AF-4EDB-BEC7-221AC45F7896}" is not found. This error is thrown by Connections collection when the specific connection element is not found.
 
 
3 hours later…
Paul White
Paul White
6:45 AM
@wBob I added an answer to that termination reason question, but I fear it may be a wasted effort.
 
wBob
wBob
7:06 AM
@PaulWhite I saw, nice. They might reasonably direct their efforts towards plan warnings.
 
Paul White
Paul White
@wBob Maybe. There is all kinds of useful information in plans. My main concern is the OP is using the wrong tool to find the plans that require attention in the first place.
A plan can have as many warnings as it likes and I still won't care if it performs acceptably well every time it executes. There are always bigger fish to fry.
Where "not care" = because I'll never see it.
Naturally, when someone presents me with a specific query to tune, I'll tune it to death regardless :)
3
 
wBob
wBob
7:55 AM
@PaulWhite lol, natch'
@PaulWhite Although I may need to explain to my boss today why I've spent several hours on hierarchyId query instead of my actual work : )
 
 
1 hour later…
wBob
wBob
9:23 AM
Drive-by downvote on "Queries without Good Enough Plan Found", wonder why...
 
ypercubeᵀᴹ
ypercubeᵀᴹ
@wBob downvotes? You mean on the deleted answer of RLF?
 
Marian
Marian
9:48 AM
@wBob You sure?
 
Mark Sinkinson
Mark Sinkinson
@PaulWhite Yeah, I didn't think so. Was just reading through a guide to performance tuning for a third-party app. Pretty much everything else they wrote is incorrect.
 
Paul White
Paul White
@MarkSinkinson There's a lot of it about :)
 
ypercubeᵀᴹ
ypercubeᵀᴹ
@MarkSinkinson They are probably authoritah subscribers
5
 
 
2 hours later…
Tom V
Tom V
12:28 PM
The humor is in the codez
2
 
wBob
wBob
12:48 PM
@Marian Yes had a minus 10 this morning; not to worry : )
 
Tom V
Tom V
@wBob I missed that question/answer apparently ;)
 
wBob
wBob
1:09 PM
@TomV Thanks Tom. I was thinking of extending with a SQL example of how to find these, but then there are many available via that internet thingy.
 
Marian
Marian
@TomV :)) where is that?
 
billinkc
billinkc
1:40 PM
@swasheck sorry, what's the backstory of what's happening?
 
 
1 hour later…
Tom V
Tom V
2:50 PM
@Marian Pinal
 
Max Vernon
Max Vernon
> Penal: Of, relating to, or prescribing punishment, as for breaking the law.
I know it's not the same spelling, but still
 
Tom V
Tom V
I only pick on the guy because as long as I side with the bullys I'm not the one being picked on
4
:p
 
swasheck
swasheck
3:11 PM
@billinkc packages created in SSDT/VS 2010 ... now we'd like to move to SSDT/VS 2015 and all of the connection manager GUIDs break in that process. is there a way to not break them, other than opening each task and re-selecting the connection?
 
Marian
Marian
@TomV oh, that
 
billinkc
billinkc
@swasheck Let me see if I can gin up a repro
 
swasheck
swasheck
bathtub gin?
 
swasheck
swasheck
3:34 PM
@TomV samesame, but different
 
A_V
A_V
Hey
There's the OEM tool for oracle database that can look up an expensive query/job on your database
and then it gives advices on what you could do to make it faster such as exec plan profiles and indexes
Does an equivalent tool exist for sqlserver ?
 
swasheck
swasheck
your brain
kidding. i use sql sentry products
plan explorer is free and can be helpful
there's also the query plan itself which suggests missing indexes (need you think through this, though because just because an index is good for that query doesnt mean it'd help your workload overall)
 
A_V
A_V
good, my brain could be enough if I was the one who designed this quite complicated application. Thing is I did not and I have no way to find who did so asking them what it's for might be complicated, let alone make any helpful fixes
those sqlsentry products
are they licensed by db or by user ?
nevermind I just read the part that says that
 
swasheck
swasheck
3:54 PM
sry
 
 
2 hours later…
unseen_damage
unseen_damage
5:50 PM
Hey. Real quick question. does postgres support having transactions inside of stored procedures?
 
Andriy M
Andriy M
6:26 PM
@unseen_damage This answer appears to imply that it does:
3
A: Committing Records into the table while executing a postgreql Function

GoatWalkerThis can be done using dblink. I showed an example with one insert being committed you will need to add your while loop logic and commit every loop. You can http://www.postgresql.org/docs/9.3/static/contrib-dblink-connect.html CREATE OR REPLACE FUNCTION log_the_dancing(ip_dance_entry text) RET...

 
ypercubeᵀᴹ
ypercubeᵀᴹ
7:05 PM
@unseen_damage @AndriyM No, that example does not have any BEGIN TRANSACTION inside a function
13
Q: PostgreSQL cannot begin/end transactions in PL/pgSQL

TimI am seeking clarification of how to ensure an atomic transaction in a plpgsql function, and where the isolation level is set for this particular change to the database. In the plpgsql function shown below, I want to make sure that BOTH the deletion AND the insertion succeed. I am getting an ...

transactions runtime-error plpgsql postgresql-9.2 read-committed
 
Andriy M
Andriy M
Yeah, that was careless of me
 
ypercubeᵀᴹ
ypercubeᵀᴹ
26
Q: are postgres functions transactional?

DónalIs a postgres function such as the following automatically transactional? CREATE OR REPLACE FUNCTION refresh_materialized_view(name) RETURNS integer AS $BODY$ DECLARE _table_name ALIAS FOR $1; _entry materialized_views%ROWTYPE; _result INT; BEGIN EXECUTE 'TRUNC...

postgresql transactions
@AndriyM The indentation in that code was careless
 
Andriy M
Andriy M
Easy to deceive someone like me, yes
Serves me right, though. Shouldn't have tried to help where I know next to nothing
 
ypercubeᵀᴹ
ypercubeᵀᴹ
However:
@unseen_damage check all the above links.
You can ROLLBACK TO SAVEPOINT to effectively roll back a part of a transaction.
Haha, I got a nice answer badge. Didn't think I would, since it's CW
10
A: T-SQL : Is string a Palindrome?

ypercubeᵀᴹThe REVERSE() method "improved", i.e. reversing only half of the string: SELECT CASE WHEN RIGHT(@string, LEN(@string)/2) = REVERSE(LEFT(@string, LEN(@string)/2)) THEN 1 ELSE 0 END AS Palindrome;

 
Max Vernon
Max Vernon
7:24 PM
@ypercubeᵀᴹ I love that you only reverse the last half of the string.
most people dont' realize that's all it takes.
 
ypercubeᵀᴹ
ypercubeᵀᴹ
@MaxVernon that's why it's "improved"
 
Max Vernon
Max Vernon
that's what I figured!
 
ypercubeᵀᴹ
ypercubeᵀᴹ
I was tempted to add another, recursive CTE answer.
 
unseen_damage
unseen_damage
7:42 PM
@ypercubeᵀᴹ, so essentially I can do a multi table update transaction inside a postgres "stored procedure"/ "function"?
 
ypercubeᵀᴹ
ypercubeᵀᴹ
8:10 PM
@unseen_damage You can do an update yes, inside a function.
You can always run it inside a transaction with something like : 
BEGIN TRANSACTION;
--- some statements ;
--- SELECT your_function( <parameters> ) ;
-- more statements ;
COMMIT ;
 
unseen_damage
unseen_damage
8:38 PM
I was thinking more along the lines of:
CREATE FUNCTION myFunct (IN var text...) RETURNS boolean AS $$
BEGIN TRANSACTION
UPDATE table 1.....
UPDATE table 2.......
UPDATE table 3..........
COMMIT;
END;
$$ LANGUAGE plpgsql
@ypercubeᵀᴹ then I could call the function, and not have to write all the transaction sequences in the app layer
 

« first day (1948 days earlier)      last day (2910 days later) »