Actually if I don't run the 100k one, the package runs just fine. I cannot test the 100k alone since it needs the whole sequence to be installed before.
Currently I keep investigating this out of "curiosity" since due to some requirements I had to move to Neil's approach using sqlcmd directly (replaces, logging, timeout, etc). - By the way, I found out that the SQL client is a standalone msi which does not need the whole SQL "monster". If there is anything that you can suggest to try to single out the performance degradation, just let me know, I will be glad to try it here, I have plenty of VMs with SQL running on them 8^) -----Original Message----- From: Rob Mensching [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 10, 2008 7:09 PM To: General discussion for Windows Installer XML toolset. Subject: Re: [WiX-users] Very slow performance running SQL Scripts And is it just the last one (100k) that is slow? Or is the 60k slow as well? I hope the 2K are okay. <smile/> -----Original Message----- From: Eitan Behar [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2008 22:30 To: 'General discussion for Windows Installer XML toolset.' Subject: Re: [WiX-users] Very slow performance running SQL Scripts 6 scripts, 4 of them around 2k rows, 1 60k rows, and the last one ...100k rows! We don't create the scripts manually, we load the databases with data, and then use Red Gate to generate the SQL scripts. -----Original Message----- From: Rob Mensching [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 10, 2008 1:00 AM To: General discussion for Windows Installer XML toolset. Subject: Re: [WiX-users] Very slow performance running SQL Scripts How much text (without comments) are in these SQL Scripts again? -----Original Message----- From: Eitan Behar [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 09, 2008 06:58 To: General discussion for Windows Installer XML toolset. Subject: Re: [WiX-users] Very slow performance running SQL Scripts I have done a little research, and while I cannot reach any conclusion, at least this may help to find a lead: 1. Running the scripts using sqlcmd on custom actions (using Neils' code - thanks Neil) gives that the scripts run in less than 10mins (I am running half of the original scripts). This is the partial log: Action start 15:17:09: INSTALL. Action start 15:17:11: CreateDatabase. Action ended 15:17:11: CreateDatabase. Return value 1. MSI (s) (24:C0) [15:19:59:731]: Executing op: ActionStart(Name=VCDRData__22a_dbmVCDRData_Data.sql,,) MSI (s) (24:C0) [15:27:35:370]: Executing op: DatabaseCopy(DatabasePath=C:\WINDOWS\Installer\2528232f.msi,ProductCode={D31FEBAF-1B14-45C8-800E-6F80E658FF94},CabinetStreams=CAB001.cab,,) Action ended 15:27:35: InstallFinalize. 2. Running the same scripts using SqlScripts take around 30 mins (using all the scripts the time increase exponentially) Action start 16:24:20: INSTALL. Action start 16:24:24: CreateDatabase. MSI (s) (48!5C) [16:41:00:894]: PROPERTY CHANGE: Adding ExecuteSqlStrings property. Its value is (...) (very looooonnnng text) Action ended 16:24:24: CreateDatabase. Return value 1. MSI (s) (48!5C) [16:41:00:972]: Doing action: ExecuteSqlStrings MSI (s) (48:94) [16:41:24:916]: Invoking remote custom action. DLL: C:\WINDOWS\Installer\MSI4D5.tmp, Entrypoint: ExecuteSqlStrings and still running.... The database creation process is common for both setups. Therefore, the script handling is my suspect. It seems to be that the loading of the sql scripts into ExecuteSqlStrings (see MSI (s) (48!5C) [16:41:00:894]: PROPERTY CHANGE) is what is taking so long. Hope this helps, Eitan On Mon, Dec 8, 2008 at 9:02 PM, Rob Mensching <[EMAIL PROTECTED]>wrote: > Sounds like it might be a performance problem in the SQL CustomActions. > Can you look in a verbose log file and see what actions are taking the > longest? Breakdown of details will help us target the things that are > actually slow. > > -----Original Message----- > From: Eitan Behar [mailto:[EMAIL PROTECTED] > Sent: Sunday, December 07, 2008 12:21 > To: 'General discussion for Windows Installer XML toolset.' > Subject: Re: [WiX-users] Very slow performance running SQL Scripts > > I haven't tried that, I will check tomorrow morning, but I think that they > will run also fast. I tried the same scripts on Install Shield 12 and they > take around 10 mins to run. > > > > -----Original Message----- > From: Joe Osman [mailto:[EMAIL PROTECTED] > Sent: Sunday, December 07, 2008 10:00 PM > To: General discussion for Windows Installer XML toolset. > Subject: Re: [WiX-users] Very slow performance running SQL Scripts > > Hi Eitan, > How much does it take for your script to run if you use sqlcmd from the > command window? > > Eitan Behar wrote: > > Hi, > > > > I am having a serious performance problem with SQL Scripts, and would > > appreciate some help troubleshooting it. > > > > This is the scenario: Use WIX to create the database, use script (within > the > > component creating the database) changing database properties, use > > scripts on different component to create tables, and fill data, some > scripts > > are pretty big (70k+ rows). > > > > If I do the process from SQL Studio, it takes barely 5 minutes, from WIX > it > > takes more than 1 hour !!!! > > > > Here is the log: > > > > MSI (s) (6C!7C) [13:26:04:695]: Doing action: CreateDatabase > > Action start *13:26*:04: CreateDatabase. > > MSI (s) (6C!7C) [*13:56*:42:069]: PROPERTY CHANGE: Adding > ExecuteSqlStrings > > property. Its value is > > 'MyData_SQL_DB€RDDP-VM29€€master€-2147483648€1€€€01_properties.sql€1€USE > > [master]€Data_properties.sql€1€EXEC dbo.sp_dbcmptlevel > @dbname=N'AuxData', > > @new_cmptlevel=90€AuxData_properties.sql€1€IF (1 = > > FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) > > begin > > EXEC [AuxData].[dbo].[sp_fulltext_database] @action = 'enable' > > end (...) ALTER DATABASE [AuxData] SET AUTO_CLOSE OFF... > > Action ended *13:26:04*: CreateDatabase. Return value 1. > > MSI (s) (6C!7C) [*13:56:42*:132]: Doing action: ExecuteSqlStrings > > Action start 13:56:42: ExecuteSqlStrings. > > Action ended 13:56:42: ExecuteSqlStrings. Return value 1. > > > > Thanks, > > > > Eitan > > > > (BTW, I just installed the latest 3.0.x version, but happened also with > > previous ones) > > > ------------------------------------------------------------------------------ > > SF.Net email is Sponsored by MIX09, March 18-20, 2009 in Las Vegas, > Nevada. > > The future of the web can't happen without you. Join us at MIX09 to help > > pave the way to the Next Web now. Learn more and register at > > > http://ad.doubleclick.net/clk;208669438;13503038;i?http://2009.visitmix.com/ > > _______________________________________________ > > WiX-users mailing list > > WiX-users@lists.sourceforge.net > > https://lists.sourceforge.net/lists/listinfo/wix-users > > > > > > ======================================================================= > This email, including any attachments, is only for the intended > addressee. It is subject to copyright, is confidential and may be > the subject of legal or other privilege, none of which is waived or > lost by reason of this transmission. > If the receiver is not the intended addressee, please accept our > apologies, notify us by return, delete all copies and perform no > other act on the email. > Unfortunately, we cannot warrant that the email has not been > altered or corrupted during transmission. > ======================================================================= > > > > ------------------------------------------------------------------------------ > SF.Net email is Sponsored by MIX09, March 18-20, 2009 in Las Vegas, Nevada. > The future of the web can't happen without you. Join us at MIX09 to help > pave the way to the Next Web now. Learn more and register at > > http://ad.doubleclick.net/clk;208669438;13503038;i?http://2009.visitmix.com/ > _______________________________________________ > WiX-users mailing list > WiX-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/wix-users > > > > ------------------------------------------------------------------------------ > SF.Net email is Sponsored by MIX09, March 18-20, 2009 in Las Vegas, Nevada. > The future of the web can't happen without you. Join us at MIX09 to help > pave the way to the Next Web now. Learn more and register at > > http://ad.doubleclick.net/clk;208669438;13503038;i?http://2009.visitmix.com/ > _______________________________________________ > WiX-users mailing list > WiX-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/wix-users > > ------------------------------------------------------------------------------ > SF.Net email is Sponsored by MIX09, March 18-20, 2009 in Las Vegas, Nevada. > The future of the web can't happen without you. Join us at MIX09 to help > pave the way to the Next Web now. Learn more and register at > > http://ad.doubleclick.net/clk;208669438;13503038;i?http://2009.visitmix.com/ > _______________________________________________ > WiX-users mailing list > WiX-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/wix-users > ------------------------------------------------------------------------------ SF.Net email is Sponsored by MIX09, March 18-20, 2009 in Las Vegas, Nevada. The future of the web can't happen without you. Join us at MIX09 to help pave the way to the Next Web now. Learn more and register at http://ad.doubleclick.net/clk;208669438;13503038;i?http://2009.visitmix.com/ _______________________________________________ WiX-users mailing list WiX-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/wix-users ------------------------------------------------------------------------------ SF.Net email is Sponsored by MIX09, March 18-20, 2009 in Las Vegas, Nevada. The future of the web can't happen without you. Join us at MIX09 to help pave the way to the Next Web now. Learn more and register at http://ad.doubleclick.net/clk;208669438;13503038;i?http://2009.visitmix.com/ _______________________________________________ WiX-users mailing list WiX-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/wix-users ------------------------------------------------------------------------------ SF.Net email is Sponsored by MIX09, March 18-20, 2009 in Las Vegas, Nevada. The future of the web can't happen without you. Join us at MIX09 to help pave the way to the Next Web now. Learn more and register at http://ad.doubleclick.net/clk;208669438;13503038;i?http://2009.visitmix.com/ _______________________________________________ WiX-users mailing list WiX-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/wix-users ------------------------------------------------------------------------------ SF.Net email is Sponsored by MIX09, March 18-20, 2009 in Las Vegas, Nevada. The future of the web can't happen without you. Join us at MIX09 to help pave the way to the Next Web now. Learn more and register at http://ad.doubleclick.net/clk;208669438;13503038;i?http://2009.visitmix.com/ _______________________________________________ WiX-users mailing list WiX-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/wix-users ------------------------------------------------------------------------------ SF.Net email is Sponsored by MIX09, March 18-20, 2009 in Las Vegas, Nevada. The future of the web can't happen without you. Join us at MIX09 to help pave the way to the Next Web now. Learn more and register at http://ad.doubleclick.net/clk;208669438;13503038;i?http://2009.visitmix.com/ _______________________________________________ WiX-users mailing list WiX-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/wix-users
