Community Forums
Page 1 of 2 12 LastLast
Results 1 to 5 of 8

Thread: SQL Scripts running slow

  1. #1
    Join Date
    May 2007
    Location
    Northern Minnesota
    Posts
    807

    SQL Scripts running slow

    I have a rather long SQL script (>100,000 lines) that runs very slow using the Scripts view. The script can be run in < minute with SQL Management Studio and takes 8-11 minutes when run during install (InstallShield MSI project). Same result for SQL 2008R2 or SQL 2012. I got a log and it appears that it just takes a lot of time to parse the SQL script. For example, the script has a lot of statements like
    IF @CurrentVersion < YYYYMMDD
    BEGIN
    ...
    END
    GO
    And it is even very slow when it is skipping over dates that are not executed. Any ideas are appreciated.

  2. #2
    Join Date
    May 2007
    Location
    Northern Minnesota
    Posts
    807
    I did further testing by running the script with SQLCMD. Both SMS and SQLCMD will run the script in about 10 seconds. It takes 11 minutes to run it in my InstallScript MSI project. I do see a lot of logging going on when I log the install... basically an entry for each GO statement.

    1: Executing SQL at Line 54 >>>
    1: No more results.
    1: Executing SQL at Line 59 >>>
    1: No more results.
    1: Executing SQL at Line 64 >>>
    1: No more results.
    1: Executing SQL at Line 69 >>>
    1: No more results.
    1: Executing SQL at Line 74 >>>
    1: No more results.
    ...

    Most of the time is spent parsing through the 98-99% of this script that is date specific and it even takes 11 minutes when there are no qualifying dates and therefore nothing to execute.
    Last edited by rguggisberg; 12-09-2015 at 03:25 PM.

  3. #3
    Join Date
    Oct 2001
    Location
    Itasca, IL
    Posts
    2,397
    Is the problem reproducible with a Basic MSI project?
    Hidenori Yamanishi - Senior Software Engineer - Flexera Software: InstallShield Team

  4. #4
    Join Date
    May 2007
    Location
    Northern Minnesota
    Posts
    807
    Quote Originally Posted by hidenori View Post
    Is the problem reproducible with a Basic MSI project?
    Thanks for the response hidenori. I don't know. I will try to get a test project set up next week and let you know. It sure would be nice to improve on that 11 minutes

  5. #5
    Join Date
    May 2007
    Location
    Northern Minnesota
    Posts
    807
    Quote Originally Posted by hidenori View Post
    Is the problem reproducible with a Basic MSI project?
    hidenori,
    Thanks for the reply. Sorry for the delay in getting back to you... too many things to do!
    I did set up a Basic MSI project as you suggested and found that the long running script that takes about 10 minutes and 20 seconds in our InstallScript MSI project will run in 1 minute and 50 seconds in the Basic MSI project. Still doesn't match the 10 seconds of SMS and SQLCMD, but would be a huge improvement if we could achieve it. I am anxious to try any suggestions you might have.
    Thanks.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •