April 18, 2012 at 11:35 pm
Jeff Moden (4/18/2012)
Koen Verbeeck (4/18/2012)
Stefan Krzywicki (4/18/2012)
Just complaining for a secondScope for variables in SSIS are utter crap, it'd be better if scope didn't even exist here. Yes, I hear it has been fixed in 2012, but seeing as I won't get to use that until 2016 or 2020, that's cold comfort.
If you install BIDSHelper you can change the scope.
Heh... same holds true if you do it in T-SQL. 😉
Jeff's standard response on any topic SSIS related 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 18, 2012 at 11:37 pm
Revenant (4/18/2012)
Jeff Moden (4/18/2012)
Koen Verbeeck (4/18/2012)
Stefan Krzywicki (4/18/2012)
Just complaining for a secondScope for variables in SSIS are utter crap, it'd be better if scope didn't even exist here. Yes, I hear it has been fixed in 2012, but seeing as I won't get to use that until 2016 or 2020, that's cold comfort.
If you install BIDSHelper you can change the scope.
Heh... same holds true if you do it in T-SQL. 😉
SSIS is the weakest part of SQLS. Fortunately, guys in charge know that.
Care to explain why? Personally, I find it extremely powerful (I may be biased), as you can extend it in every possible way.
In my opinion, SSRS is the weakest link.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 18, 2012 at 11:40 pm
Koen Verbeeck (4/18/2012)
Revenant (4/18/2012)
Jeff Moden (4/18/2012)
Koen Verbeeck (4/18/2012)
Stefan Krzywicki (4/18/2012)
Just complaining for a secondScope for variables in SSIS are utter crap, it'd be better if scope didn't even exist here. Yes, I hear it has been fixed in 2012, but seeing as I won't get to use that until 2016 or 2020, that's cold comfort.
If you install BIDSHelper you can change the scope.
Heh... same holds true if you do it in T-SQL. 😉
SSIS is the weakest part of SQLS. Fortunately, guys in charge know that.
Care to explain why? Personally, I find it extremely powerful (I may be biased), as you can extend it in every possible way.
In my opinion, SSRS is the weakest link.
I would agree with you there. SSRS needs to come a long way still to catch up to SSIS, SSAS, and the Engine.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 19, 2012 at 12:39 am
SQL Kiwi (4/18/2012)
Jeff Moden (4/18/2012)
He's done some awesome testing against some of the new LEAD functionality of SQL Server 2012.An efficient algorithm out-performs an inefficient one? Who knew! Seriously, the code there does not show a good use of LEAD. My concern would be that people form a *general* opinion from that post that the new windowing tools in 2012 do not perform well, and that would be unfortunate.
I submitted two queries (in the comments) that use LEAD to perform that GAP detection that are on par with Jeff and Itziks code as far as performance.
The query Wayne had needed a slight modification and now it runs consistently faster than those two queries on my machine (if 70% of the time qualifies as consistently).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 19, 2012 at 1:31 am
SQLRNNR (4/19/2012)
I submitted two queries (in the comments) that use LEAD to perform that GAP detection that are on par with Jeff and Itziks code as far as performance.The query Wayne had needed a slight modification and now it runs consistently faster than those two queries on my machine (if 70% of the time qualifies as consistently).
Well done! I'll run the code later when I have a SQL Server on hand.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 19, 2012 at 2:28 am
SQL Kiwi (4/18/2012)
Jeff Moden (4/18/2012)
He's done some awesome testing against some of the new LEAD functionality of SQL Server 2012.An efficient algorithm out-performs an inefficient one? Who knew! Seriously, the code there does not show a good use of LEAD. My concern would be that people form a *general* opinion from that post that the new windowing tools in 2012 do not perform well, and that would be unfortunate.
If people read the comments it probably won't do any harm - Jason's two posts pretty well debunk the !LEAD is slow" idea.
Tom
April 19, 2012 at 4:24 am
Stefan Krzywicki (4/18/2012)
SQLRNNR (4/18/2012)
Stefan Krzywicki (4/18/2012)
jcrawf02 (4/18/2012)
Stefan Krzywicki (4/18/2012)
Just complaining for a secondScope for variables in SSIS are utter crap, it'd be better if scope didn't even exist here. Yes, I hear it has been fixed in 2012, but seeing as I won't get to use that until 2016 or 2020, that's cold comfort.
+1
I accidentally created two identically named variables, one at package level and one within a container. Took me FOREVER to figure it out. My mistake, but still, wasn't intentionally changing the scope when I created them, just accidentally clicked on the wrong damn thing before I created the variable.
Yeah, I'm at the point where I make sure every variable is created at the package level. I just had to change 10 variables, all "evaluate as expression" that build on one another because the FlatFile Connection is at package level and can't read anything at a lower scope, though all the file manipulation is done in a Foreach Loop container.
Now I have to go through the entire package and look at the Parameter Mapping 'cause SSIS will throw an error unless I do, even though I'm not changing a damn thing on those screens.
That is one of the most frustrating things with SSIS - opening everything and confirming the settings after a minor change.
Yeah, another fun one I found is in creating XML files. If I change the length of the string specifying the output path, the process fails. There's no fix for it short of changing it back or to somthing the same length or re-creating the item at the control flow level and deleting the old one.
C:\Out\Files\Filename.xml changed to
C:\Out\FilesA\Filname.xml requires a complete re-creation of the step.
I create all variables at the package level simply because the Devs seem to lose them.
I hate the XML Source Transformation. If the XML file gets changed (one column goes from int to bigint, for instance), I can't just change the .xsd file, I have to change out the whole damn Transformation. This goes against our SDLC and requires the package to go through QC testing again. Drives me freaking nuts.
This happened just last month and I had to upgrade 6 packages because the vendor changed a column that pulls for our XML report that we load. :crazy:
April 19, 2012 at 6:26 am
L' Eomot Inversé (4/19/2012)
SQL Kiwi (4/18/2012)
Jeff Moden (4/18/2012)
He's done some awesome testing against some of the new LEAD functionality of SQL Server 2012.An efficient algorithm out-performs an inefficient one? Who knew! Seriously, the code there does not show a good use of LEAD. My concern would be that people form a *general* opinion from that post that the new windowing tools in 2012 do not perform well, and that would be unfortunate.
If people read the comments it probably won't do any harm - Jason's two posts pretty well debunk the !LEAD is slow" idea.
We just started a Proof of Concept using Excel with the PowerPivot addin.
Some of this functionality can be extrememly fast - given you architect to take advantage of some of the changes.
For us, it will take awhile to sort out when to use traditional SSAS or the new tabular version.
And I also see changes might be in order for the base warehouse too.
April 19, 2012 at 6:31 am
I am starting to do guest posts on my writing blog and I'm wondering if any of you Threadzian authors (who have done editing or writing on SQL Server books) would like to cross-post an article about writing / editing over on my blog.
I'm willing to publish previously written posts or new posts. Basically, I'm looking for any How-Tos or advice on technical writing / editing, or even posts about your particular writing process. Things that other people can learn from that doesn't immediately require them to know T-SQL or SQL Server specifically.
FYI: I'm still having problems with my PM inbox, so do not PM me. I won't see it.
April 19, 2012 at 7:32 am
Koen Verbeeck (4/18/2012)
Stefan Krzywicki (4/18/2012)
Koen Verbeeck (4/18/2012)
Stefan Krzywicki (4/18/2012)
Just complaining for a secondScope for variables in SSIS are utter crap, it'd be better if scope didn't even exist here. Yes, I hear it has been fixed in 2012, but seeing as I won't get to use that until 2016 or 2020, that's cold comfort.
If you install BIDSHelper you can change the scope.
I saw something about that in my searches. Have you tried it? Does it integrate with SSMS? Is it trustworthy (from a reliable manufacturer)? I just didn't have the time to do that research and convince those who need convincing that it is worth installing and then do the installation. : -)
And is it free? Probably the biggest block of all.
I surely have tried it. In my SSIS best practices presentation, best practice #1 is "Install BIDSHelper. Now." 😀
It does not integrate with SSMS, as the name says it, it is a plug-in for BIDS (aka Visual Studio). It is trustworthy, I have never had much issues with it, but it can make the editing/opening of packages a bit slower, as it has to highlight certain objects in the designer.
It is free, as it is published on Codeplex. Every client I ever worked with had BIDSHelper installed. Or I installed it for them 🙂
(It's not only for SSIS, but also for SSAS and SSRS)
LOL, SSMS! That shows how distracted I was when I posted. : -) Sorry about that.
I'll seek permission to get it installed. This is just development assistance, right? Or are there tools in it that mean it also needs to be installed on the production server so the packages will run?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
April 19, 2012 at 8:48 am
SQL Kiwi (4/19/2012)
SQLRNNR (4/19/2012)
I submitted two queries (in the comments) that use LEAD to perform that GAP detection that are on par with Jeff and Itziks code as far as performance.The query Wayne had needed a slight modification and now it runs consistently faster than those two queries on my machine (if 70% of the time qualifies as consistently).
Well done! I'll run the code later when I have a SQL Server on hand.
Thanks for double checking my code. Too bad my results were wrong. There has got to be a way of doing this.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 19, 2012 at 9:16 am
SQLRNNR (4/19/2012)
Thanks for double checking my code. Too bad my results were wrong. There has got to be a way of doing this.
I agree, but we might have to wait for any future work that might be done to optimize the (very common) case of LAG and LEAD for the immediately preceding or following row only. As it is, these functions are implemented quite generically as LAST_VALUE with a RANGE 1 PRECEDING clause (you can see this in the query plan if you look closely).
On a separate point, the Itzik and Jeff solutions are optimal when merge is used for the semi-join. If you test them again, add OPTION (MAXDOP 1, MERGE JOIN, LOOP JOIN) to avoid the hash (which spills to tempdb). Parallelism adds little value on the demo (and merge doesn't parallelize very well in general anyway). I tried the following (which produces the optimal plan for me without hints):
SELECT
S.GapStart,
E.GapEnd
FROM
(
SELECT GapStart = gt.N + 1 FROM dbo.GapTest AS gt
WHERE NOT EXISTS (SELECT 1 FROM dbo.GapTest AS gt2 WHERE gt2.N = gt.N + 1)
) AS S
CROSS APPLY
(
SELECT TOP (1)
GapEnd = gt3.N
FROM dbo.GapTest AS gt3
WHERE
gt3.N > S.GapStart
ORDER BY gt3.N
) AS E
ORDER BY
S.GapStart;
I know code on the thread is frowned on but I'm feeling rebellious today. I don't come across gap/islands problems enough to really spend time on this.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 19, 2012 at 9:48 am
SQL Kiwi (4/19/2012)
SQLRNNR (4/19/2012)
Thanks for double checking my code. Too bad my results were wrong. There has got to be a way of doing this.I don't come across gap/islands problems enough to really spend time on this.
Nor do I. This one piqued my interest a little bit so thought I would try working it out.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 19, 2012 at 1:29 pm
And just when I thought we might be seeing light at the end of the tunnel, nope. Just another train heading our way.
Hi, my name is Lynn and I must be a masochist. I can't stop feeding the troll.
April 19, 2012 at 1:33 pm
Stefan Krzywicki (4/19/2012)
LOL, SSMS! That shows how distracted I was when I posted. : -) Sorry about that.I'll seek permission to get it installed. This is just development assistance, right? Or are there tools in it that mean it also needs to be installed on the production server so the packages will run?
It's pure development aid. There is no need to install anything on a server.
Some benefits for SSIS:
* easier deployment (right click on package/project and select deploy)
* ability to change scope of variables
* better expression editor for variables
* ability to reset all GUIDS of a package
* highlights variables and connection managers if they are configured by an expression or package configuration
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 35,566 through 35,580 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply