We've run into this. Luckily the one largest SQL script is also the last one we need to execute so we removed it from the installer and just have customers run that one manually using SQL Management Studio. About 34 seconds to run through there while the installer takes an hour and a half to run the same script.
-----Original Message----- From: Rob Mensching [mailto:r...@robmensching.com] Sent: Thursday, January 23, 2014 4:39 PM To: General discussion about the WiX toolset. Subject: Re: [WiX-users] SqlScript with large schema setup Code was originally written sometime around 2001. It hasn't changed really since then. -----Original Message----- From: Levi Wilson [mailto:l...@leviwilson.com] Sent: Thursday, January 23, 2014 2:31 PM To: General discussion about the WiX toolset. Subject: Re: [WiX-users] SqlScript with large schema setup I'm actually looking at it now :-) I can live with the initial, since that's only going to happen the first time you run our installer and our schema is not there. On an update (doing major upgrades) we are going to have migration scripts run. Those should not be near what this initial one is. Looking at the code (I just have 3.7, but it doesn't sounds like it's changed in awhile) I'm not seeing any tests for any of the parsing in scasqlstr.cpp, which intimidates me :-) It's doing a LOT, but mostly looks like it is just eating comments and removing "GO" statements to execute each command individually. I may fall into the group of living with it ;-) On Thu, Jan 23, 2014 at 4:47 PM, Rob Mensching <r...@robmensching.com> wrote: > I assume most people live with the perf since no one has ever bothered > to fix the bug. > > -----Original Message----- > From: Levi Wilson [mailto:l...@leviwilson.com] > Sent: Thursday, January 23, 2014 12:30 PM > To: General discussion about the WiX toolset. > Subject: Re: [WiX-users] SqlScript with large schema setup > > What are other ways that people have gotten around it? It doesn't seem > desirable to execute `sqlcmd` from a CA. > > > On Thu, Jan 23, 2014 at 2:26 PM, Rob Mensching <r...@robmensching.com> > wrote: > > > You'll want to think about security with that second option. I don't > > think the MSI property is the issue. I think it's the SQLCA code > > itself that is slow parsing. IIRC, there is a (very old) bug about > > the perf problem. Maybe you'd like to tackle it? > > > > -----Original Message----- > > From: Levi Wilson [mailto:l...@leviwilson.com] > > Sent: Thursday, January 23, 2014 10:41 AM > > To: wix-users@lists.sourceforge.net > > Subject: Re: [WiX-users] SqlScript with large schema setup > > > > Sorry I didn't mention this before, but the database is already > > going to exist. Our customers create this beforehand. The installer > > will just be running the SQL script for the initial schema to create > > all of our tables, triggers, sprocs, etc. > > > > When looking at the installer log, I see two lines that jump out at me: > > > > MSI (s) (C4!BC) [11:44:00:998]: Closing MSIHANDLE (45) of type > > 790540 for thread 12732 MSI (s) (C4!BC) [11:50:33:616]: PROPERTY CHANGE: > > Adding ExecuteSqlStrings property. Its value is '**********'. > > > > So it looks like it takes 6 minutes to take the 144k lines of my SQL > > script that is stored in a <binary /> to create the > > ExecuteSqlStrings property. > > Seems like I'm holding it wrong :-) > > > > Would a better option be to create a CA that reads the SQL out of a > > binary record and execute it myself? Rather than having the > > intermediate step of creating an MSI property? > > > > Levi > > > > > > > > > Why not right click on the DB, tasks and generate in MS? What > > > version of SQL? > > > > > > > > > On Thu, Jan 23, 2014 at 10:52 AM, Levi Wilson <levi@...> wrote: > > > > I am using a <SqlScript /> to create our database schema. I > > > > exported the schema using RedGate. The file is 10MB (I know, I > know...). > > > > Everything works as expected, but during the installation the > > > > "Configuring SQL > > > Server" > > > > takes forever. > > > > > > > > I know, 10MB file with 144k lines of SQL to parse is a lot...is > > > > there a better alternative to getting this initial script created? > > > > > > > > Thanks in advance, > > > > > > > > Levi > > > > > > > > > > > ------------------------------------------------------------------ > > > -- > > > -- > > > -------- > > > > CenturyLink Cloud: The Leader in Enterprise Cloud Services. > > > > Learn Why More Businesses Are Choosing CenturyLink Cloud For > > > > Critical Workloads, Development Environments & Everything In Between. > > > > Get a Quote or Start a Free Trial Today. > > > > > > > > > > > http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg. > > > clktrk > > > > _______________________________________________ > > > > WiX-users mailing list > > > > WiX-users@... > > > > https://lists.sourceforge.net/lists/listinfo/wix-users > > > > > > > -- > > > "They may forget what you said but they will never forget how you > > > made them feel." -- Anonymous > > > > -------------------------------------------------------------------- > > -- > > -------- CenturyLink Cloud: The Leader in Enterprise Cloud Services. > > Learn Why More Businesses Are Choosing CenturyLink Cloud For > > Critical Workloads, Development Environments & Everything In Between. > > Get a Quote or Start a Free Trial Today. > > > > http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg. > > clktrk _______________________________________________ > > WiX-users mailing list > > WiX-users@lists.sourceforge.net > > https://lists.sourceforge.net/lists/listinfo/wix-users > > > > > > > > -------------------------------------------------------------------- > > -- > > -------- CenturyLink Cloud: The Leader in Enterprise Cloud Services. > > Learn Why More Businesses Are Choosing CenturyLink Cloud For > > Critical Workloads, Development Environments & Everything In Between. > > Get a Quote or Start a Free Trial Today. > > > > http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg. > > clktrk _______________________________________________ > > WiX-users mailing list > > WiX-users@lists.sourceforge.net > > https://lists.sourceforge.net/lists/listinfo/wix-users > > > > ---------------------------------------------------------------------- > -------- CenturyLink Cloud: The Leader in Enterprise Cloud Services. > Learn Why More Businesses Are Choosing CenturyLink Cloud For Critical > Workloads, Development Environments & Everything In Between. > Get a Quote or Start a Free Trial Today. > > http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg. > clktrk _______________________________________________ > WiX-users mailing list > WiX-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/wix-users > > > > ---------------------------------------------------------------------- > -------- CenturyLink Cloud: The Leader in Enterprise Cloud Services. > Learn Why More Businesses Are Choosing CenturyLink Cloud For Critical > Workloads, Development Environments & Everything In Between. > Get a Quote or Start a Free Trial Today. > > http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg. > clktrk _______________________________________________ > WiX-users mailing list > WiX-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/wix-users > ------------------------------------------------------------------------------ CenturyLink Cloud: The Leader in Enterprise Cloud Services. Learn Why More Businesses Are Choosing CenturyLink Cloud For Critical Workloads, Development Environments & Everything In Between. Get a Quote or Start a Free Trial Today. http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk _______________________________________________ WiX-users mailing list WiX-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/wix-users ------------------------------------------------------------------------------ CenturyLink Cloud: The Leader in Enterprise Cloud Services. Learn Why More Businesses Are Choosing CenturyLink Cloud For Critical Workloads, Development Environments & Everything In Between. Get a Quote or Start a Free Trial Today. http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk _______________________________________________ WiX-users mailing list WiX-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/wix-users ------------------------------------------------------------------------------ CenturyLink Cloud: The Leader in Enterprise Cloud Services. Learn Why More Businesses Are Choosing CenturyLink Cloud For Critical Workloads, Development Environments & Everything In Between. Get a Quote or Start a Free Trial Today. http://pubads.g.doubleclick.net/gampad/clk?id=119420431&iu=/4140/ostg.clktrk _______________________________________________ WiX-users mailing list WiX-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/wix-users
