PDA

View Full Version : SQL Scripts running slow



rguggisberg
11-30-2015, 05:37 PM
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.

rguggisberg
12-09-2015, 03: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.

hidenori
12-09-2015, 11:16 PM
Is the problem reproducible with a Basic MSI project?

rguggisberg
12-10-2015, 05:43 PM
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 :)

rguggisberg
12-21-2015, 10:16 AM
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.

hidenori
12-22-2015, 04: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.

rguggisberg
12-23-2015, 08:38 AM
hidenori,
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.

rguggisberg
01-08-2016, 12:22 PM
:)
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.