|
|
You're in the
sql server programming
group:SCD Wizard Question
sql server programming:
I've been playing around with the SSIS SCD Wizard. I noticed that I seem to be getting better performance updating my SCD's with stored procedures. Is there a list of best practices concerning the SCD Wizard or something else I might be missing? Also, I have about 30 Dimension tables, of Types 1 and 2; running the SCD Wizard 30 times seems a bit unruly to update all my Dimension tables. Is there a more efficient method or something else I'm missing? Anyone else encounter this, or am I just missing some key pieces of information? If someone else is having these problems, how did you resolve them? TIA
[quoted text, click to view]
"EMartinez" <emartinez.pr1@gmail.com> wrote in message news:1189297646.036964.226100@22g2000hsm.googlegroups.com... > On Sep 8, 6:41 pm, "Mike C#" <x...@xyz.com> wrote: >> Hi all, >> >> I've been playing around with the SSIS SCD Wizard. I noticed that I seem >> to >> be getting better performance updating my SCD's with stored procedures. >> Is >> there a list of best practices concerning the SCD Wizard or something >> else I >> might be missing? Also, I have about 30 Dimension tables, of Types 1 and >> 2; >> running the SCD Wizard 30 times seems a bit unruly to update all my >> Dimension tables. Is there a more efficient method or something else I'm >> missing? Anyone else encounter this, or am I just missing some key >> pieces >> of information? If someone else is having these problems, how did you >> resolve them? >> >> TIA > > > These links might be helpful. > http://sqljunkies.com/WebLog/tpagel/archive/2005/07/24/16195.aspx > http://msdn2.microsoft.com/en-us/library/ms189681.aspx > Also, keep in mind that the use of stored procedures is always better > than queries since they store their query plans in SQL Server, > thereby, producing faster results. Hope this helps. > > Regards, > > Enrique Martinez > Sr. Software Consultant Thanks for the info, I'm pretty familiar with the benefits of reusing cached query plans (a benefit you can get from parameterized/prepared statements as well). I ran across the pages you posted in my previous searches but I didn't see anything that really addresses my core concerns, however, which are: 1. The SCD Wizard generates individual SQL statements for each insert/update instead of doing it in one batch using a set-based approach. The general idea is that a single set-based statement to update 50,000 rows is significantly faster than 50,000 individual UPDATE statements. 2. The SCD apparently sends one SELECT query per row for lookup purposes also, bypassing any memory-based lookup advantages or basic set-based querying advantages in general. 3. Running the SCD Wizard 30+ times seems like quite a hassle. I also hear that you lose customized outputs if you re-run the wizard to make changes later. I haven't tried this yet, but based on what I've read I'm not too eager go back and redo a bunch of customized outputs via the SCD Wizard interface, over and over again, during development. Finally, I'm told that the SCD Wizard is basically a beginner's tool for people who are unfamiliar with how to load SCD's, and that to get any kind of performance out of it requires quite a bit of custom coding. My question is this: if this is true, then why would anyone use the SCD instead of creating a custom stored procedure from the start? It seems you could get really bogged down with a lot of additional custom coding to work around the SCD Wizard's limitations and to make it work as efficiently as it should... Are there any advantages to using the SCD Wizard as opposed to custom SP's? What method do you all use, and why did you decide to go that way? Thanks in advance.
Hi,
Personally I don't use it. Part of the best pratices... instead of updating the target table, write the updates into another table: mytable_UPD then do a single update statement: update mytable set col1 = mytable_UPD.col1 .... from mytable_UPD where mytable.id = mytable_UPD.id so you'll do only 1 update instead of 50 000. Like Marco says, I'm using the tabledifference in few cases. but if your source data and the destination are on the same server (I'm using a local staging area which is stored on the same server in my case), then you can create views to identify new or updated records. Part of your setup... think SQL 2008... in SQL 2008 there is a new SQL command designed to do inserts, updates and deletes in 1 query. good luck [quoted text, click to view] "Mike C#" <xyz@xyz.com> wrote in message
news:evqddfo8HHA.5360@TK2MSFTNGP03.phx.gbl... > "EMartinez" <emartinez.pr1@gmail.com> wrote in message > news:1189297646.036964.226100@22g2000hsm.googlegroups.com... >> On Sep 8, 6:41 pm, "Mike C#" <x...@xyz.com> wrote: >>> Hi all, >>> >>> I've been playing around with the SSIS SCD Wizard. I noticed that I >>> seem to >>> be getting better performance updating my SCD's with stored procedures. >>> Is >>> there a list of best practices concerning the SCD Wizard or something >>> else I >>> might be missing? Also, I have about 30 Dimension tables, of Types 1 >>> and 2; >>> running the SCD Wizard 30 times seems a bit unruly to update all my >>> Dimension tables. Is there a more efficient method or something else >>> I'm >>> missing? Anyone else encounter this, or am I just missing some key >>> pieces >>> of information? If someone else is having these problems, how did you >>> resolve them? >>> >>> TIA >> >> >> These links might be helpful. >> http://sqljunkies.com/WebLog/tpagel/archive/2005/07/24/16195.aspx >> http://msdn2.microsoft.com/en-us/library/ms189681.aspx >> Also, keep in mind that the use of stored procedures is always better >> than queries since they store their query plans in SQL Server, >> thereby, producing faster results. Hope this helps. >> >> Regards, >> >> Enrique Martinez >> Sr. Software Consultant > > Thanks for the info, I'm pretty familiar with the benefits of reusing > cached query plans (a benefit you can get from parameterized/prepared > statements as well). I ran across the pages you posted in my previous > searches but I didn't see anything that really addresses my core concerns, > however, which are: > > 1. The SCD Wizard generates individual SQL statements for each > insert/update instead of doing it in one batch using a set-based approach. > The general idea is that a single set-based statement to update 50,000 > rows is significantly faster than 50,000 individual UPDATE statements. > > 2. The SCD apparently sends one SELECT query per row for lookup purposes > also, bypassing any memory-based lookup advantages or basic set-based > querying advantages in general. > > 3. Running the SCD Wizard 30+ times seems like quite a hassle. I also > hear that you lose customized outputs if you re-run the wizard to make > changes later. I haven't tried this yet, but based on what I've read I'm > not too eager go back and redo a bunch of customized outputs via the SCD > Wizard interface, over and over again, during development. > > Finally, I'm told that the SCD Wizard is basically a beginner's tool for > people who are unfamiliar with how to load SCD's, and that to get any kind > of performance out of it requires quite a bit of custom coding. My > question is this: if this is true, then why would anyone use the SCD > instead of creating a custom stored procedure from the start? It seems > you could get really bogged down with a lot of additional custom coding to > work around the SCD Wizard's limitations and to make it work as > efficiently as it should... Are there any advantages to using the SCD > Wizard as opposed to custom SP's? What method do you all use, and why did > you decide to go that way? > > Thanks in advance.
[quoted text, click to view]
On Sep 8, 6:41 pm, "Mike C#" <x...@xyz.com> wrote: > Hi all, > > I've been playing around with the SSIS SCD Wizard. I noticed that I seem to > be getting better performance updating my SCD's with stored procedures. Is > there a list of best practices concerning the SCD Wizard or something else I > might be missing? Also, I have about 30 Dimension tables, of Types 1 and 2; > running the SCD Wizard 30 times seems a bit unruly to update all my > Dimension tables. Is there a more efficient method or something else I'm > missing? Anyone else encounter this, or am I just missing some key pieces > of information? If someone else is having these problems, how did you > resolve them? > > TIA These links might be helpful. http://sqljunkies.com/WebLog/tpagel/archive/2005/07/24/16195.aspx http://msdn2.microsoft.com/en-us/library/ms189681.aspx Also, keep in mind that the use of stored procedures is always better than queries since they store their query plans in SQL Server, thereby, producing faster results. Hope this helps. Regards, Enrique Martinez Sr. Software Consultant
[quoted text, click to view]
On Sep 9, 4:19 am, "Mike C#" <x...@xyz.com> wrote: > "EMartinez" <emartinez....@gmail.com> wrote in message > > news:1189297646.036964.226100@22g2000hsm.googlegroups.com... > > > > > > > On Sep 8, 6:41 pm, "Mike C#" <x...@xyz.com> wrote: > >> Hi all, > > >> I've been playing around with the SSIS SCD Wizard. I noticed that I seem > >> to > >> be getting better performance updating my SCD's with stored procedures. > >> Is > >> there a list of best practices concerning the SCD Wizard or something > >> else I > >> might be missing? Also, I have about 30 Dimension tables, of Types 1 and > >> 2; > >> running the SCD Wizard 30 times seems a bit unruly to update all my > >> Dimension tables. Is there a more efficient method or something else I'm > >> missing? Anyone else encounter this, or am I just missing some key > >> pieces > >> of information? If someone else is having these problems, how did you > >> resolve them? > > >> TIA > > > These links might be helpful. > >http://sqljunkies.com/WebLog/tpagel/archive/2005/07/24/16195.aspx > >http://msdn2.microsoft.com/en-us/library/ms189681.aspx > > Also, keep in mind that the use of stored procedures is always better > > than queries since they store their query plans in SQL Server, > > thereby, producing faster results. Hope this helps. > > > Regards, > > > Enrique Martinez > > Sr. Software Consultant > > Thanks for the info, I'm pretty familiar with the benefits of reusing cached > query plans (a benefit you can get from parameterized/prepared statements as > well). I ran across the pages you posted in my previous searches but I > didn't see anything that really addresses my core concerns, however, which > are: > > 1. The SCD Wizard generates individual SQL statements for each > insert/update instead of doing it in one batch using a set-based approach. > The general idea is that a single set-based statement to update 50,000 rows > is significantly faster than 50,000 individual UPDATE statements. > > 2. The SCD apparently sends one SELECT query per row for lookup purposes > also, bypassing any memory-based lookup advantages or basic set-based > querying advantages in general. > > 3. Running the SCD Wizard 30+ times seems like quite a hassle. I also hear > that you lose customized outputs if you re-run the wizard to make changes > later. I haven't tried this yet, but based on what I've read I'm not too > eager go back and redo a bunch of customized outputs via the SCD Wizard > interface, over and over again, during development. > > Finally, I'm told that the SCD Wizard is basically a beginner's tool for > people who are unfamiliar with how to load SCD's, and that to get any kind > of performance out of it requires quite a bit of custom coding. My question > is this: if this is true, then why would anyone use the SCD instead of > creating a custom stored procedure from the start? It seems you could get > really bogged down with a lot of additional custom coding to work around the > SCD Wizard's limitations and to make it work as efficiently as it should... > Are there any advantages to using the SCD Wizard as opposed to custom SP's? > What method do you all use, and why did you decide to go that way? > > Thanks in advance.- Hide quoted text - > > - Show quoted text - I suggest you to take a look at the Table Difference tool. It is a fundamental component if you want to achieve high performance with large volumes of data when you need to mantain a SCD type II (but also type I) dimension. This is the link to the free component and its documentation: http://www.sqlbi.eu/tabledifference.aspx Marco Russo http://www.sqlbi.eu http://sqlblog.com/blogs/marco_russo
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |