This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
- Revenera Community
- :
- InstallShield
- :
- InstallShield Forum
- :
- SQL Scripts running slow
Subscribe
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Nov 30, 2015
04:37 PM
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.
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.
(7) Replies
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Dec 09, 2015
02:17 PM
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.
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.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Dec 09, 2015
10:16 PM
Is the problem reproducible with a Basic MSI project?
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Dec 10, 2015
04:43 PM
hidenori wrote:
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 🙂
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Dec 21, 2015
09:16 AM
hidenori wrote:
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.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Dec 22, 2015
03:13 PM
Would it be possible for you to send me your SQL script via Private Message so that we can look into this issue further?
Thank you.
Thank you.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Dec 23, 2015
07:38 AM
hidenori,
Thanks for looking into this. I tried to send a PM but get this error:
Also, PM's don't seem to have a place to 'Manage Attachments' as the regular form does???
Thanks again.
Thanks for looking into this. I tried to send a PM but get this error:
The following errors occurred with your submission
hidenori has exceeded their stored private messages quota and cannot accept further messages until they clear some space.
Also, PM's don't seem to have a place to 'Manage Attachments' as the regular form does???
Thanks again.
- Mark as New
- Subscribe
- Mute
- Permalink
- Report Inappropriate Content
‎Jan 08, 2016
11:22 AM
🙂
Hidenori,
Thank you very much for pursuing this to what has to be the best possible solution!
For anyone else reading this,
We received an InstallShield 2015 SP1 hotfix for this issue that resolves the performance issue of the SQL support in InstallScript MSI and Basic MSI projects. "This issue has been tracked under IOJ-1742088 so that the fix will be incorporated into the next release of InstallShield."
We have several long-running scripts. The longest one took about 11 minutes to run. After building with the above hotfix, that script now runs in 8 seconds! Our team and users will be ecstatic to see this.
Thanks again Hidenori.
Hidenori,
Thank you very much for pursuing this to what has to be the best possible solution!
For anyone else reading this,
We received an InstallShield 2015 SP1 hotfix for this issue that resolves the performance issue of the SQL support in InstallScript MSI and Basic MSI projects. "This issue has been tracked under IOJ-1742088 so that the fix will be incorporated into the next release of InstallShield."
We have several long-running scripts. The longest one took about 11 minutes to run. After building with the above hotfix, that script now runs in 8 seconds! Our team and users will be ecstatic to see this.
Thanks again Hidenori.
