Questions? Feedback?powered byOlark live chat software
Bug 2771

PB 2017 Performance on SQL 2017 Enterprise compared to SQL 2017 Standard Edition 20 June, 2019

Veerender Khanna
30 May, 2019
Product: PowerBuilder Category: Database
Version: 2017 R3 Publishing: Public
Status: Verifying Priority: P3
Classification: Resolution:
Veerender Khanna 20 June, 2019
Hi Ken,

Thank you. We continue to work with Microsoft to further investigate this slowness.

regards
Veeren
Ken Guo 20 June, 2019
Comparison Result

Hi Veeren,

I can’t find PB would call this SP anywhere. 
BTW, please try running the following SQL, not on PB IDE, nor while any PB application is running, directly on SQL Server Management Studio. Then you will see SQL Server 2017 EE is much slower. See attached image for the comparison of the results. 

DECLARE @dt_start datetime, @dt_end datetime
DECLARE @n_sum int
DECLARE @c_receiptkey char(10)
SET @n_sum = 1000
SET @dt_start = getdate()
WHILE (@n_sum <=2000)
BEGIN
       SET @c_receiptkey ='888000' + CAST(@n_sum AS char(4))
       EXEC nsp_performance_insert 'appeon',@c_receiptkey
       SET @n_sum=@n_sum+1
END 
SET @dt_end = getdate()
SELECT @dt_start,@dt_end

The test above has nothing to do with PB. So you will see SQL Server 2017 EE has a slow performance than SQL Server 2017 DE. Therefore, I suggest that you request SQL Server Support to continue to analyze for the cause. 

Regards,
Ken
Veerender Khanna 19 June, 2019
Hello Ken,

We reached out to Microsoft and below feedback was provided:

Microsoft engineer has come back that when on SQL 2017 running through application, there is a calling of sp ‘sp_describe_undeclared_parameters’

This Stored Procedure is the most expensive statement in our testing and MS suspect that is the reason why the test result is worst in SQL 2017 compared to SQL 2008. However our testing program didn’t call this sp explicitly.


Can you help to check if PowerBuilder or MS SQL Native Client 2012 calls this SP implicitly?

regards
Veeren
Veerender Khanna 18 June, 2019
HI Ken,

Thanks verifying, we will reach out to Microsoft Support team to raise this issue. 
As mentioned we are using SQL Server Enterprise 2017 and not 2019. However the behavior is quite similar.

Thank you.

regards
Veeren
Ken Guo 18 June, 2019
Hi Veerender,

I did a comparison test with your test case and SQL Server 2019 Enterprise Edition (EE) is indeed slower than SQL Server 2019 Developer Edition (DE).
                   SQL Server 2019 DE      SQL Server 2019 EE
Run Test 1 button:         19s                    32s
Run Test 2 button:         24s                    49s
Run Test 3 button:         23s                    47s
Run Test 3 button:         26s                    51s
Note: loop number is set to 10000.

When I ran the code in Test 1 button, I checked in SQL Server Profile, the execution time on the database side on SQL Server 2019 EE is twice as long as SQL Server 2019 DE.
If I don’t use PB, but rather run the following SQL directly on SQL Server Management Studio, it also takes more than twice on SQL Server 2019 EE than on DE version:
declare @p2 int
set @p2=500000
exec dbo.nsp_performance_call N'Test1',@p2 output
select @p2

Therefore, this issue is not related to PB itself. I suggest that you consult SQL Server 2019 EE’s support team. 

Regards,
Ken
Veerender Khanna 17 June, 2019
FG TEST_Update 20190617_01.zip (16KB)

Hi Ken,

We use the enclosed sample PB 2017 Code to test, you try to see if you can leverage to perform testing.
This is to help simulation fo the issue at your side.
-------------
1. Button ‘Test1’: call nsp_performance_call.sql to insert and select 500,000 records
2. Button ‘Test2’: loop the number of time, call nsp_performance_insert with count as the parameter, commit after each call of sp
3. Button ‘Test3’: loop the number of time, perform the below 2 SQL in each loop and commit at each loop by PB
Insert Into ERRLOG(LOGDATE,USERID,ERRORID,SYSTEMSTATE,MODULE,ERRORTEXT)
Values(GETDATE(),'',0,NULL,'Test3','SQL 2017 TEST:'+:ls_receiptkey)
Select ShipTOKey into :ls_ShipTOKey  From Orders Where Externorderkey = :ls_receiptkey
4. Button ‘Test4’: loop the number of time, perform the insert SQL from step 3 in each loop and commit at each loop by PB
---------------

regards
Veeren
Ken Guo 12 June, 2019
Hi Veerender,

I will set up a similar environment locally and I will update you if I can reproduce the issue. 

Regards,
Ken
Veerender Khanna 11 June, 2019
Hi Ken,

Please see my response below to some of your queries starting with  >>>

regards
Veeren
Veerender Khanna 11 June, 2019
Hi Veerender,

I am sorry that what I said previously was not accurate. PB 2017 R3 supports SQL Server 2017.
Since we don’t have SQL Server 2017 Enterprise Edition locally, I can’t reproduce your issue.

>>> Glad to hear that Appeon officially support SQL Server 2017 Enterprise Edition.

Can you confirm the following again:
1. Open SQL Profile and check if the time taken for executing the SQLs is normal.

2. We suggest that you use OLEDB and ADO.Net to connect to the database, then do some quick test and see if there is any issue, so we can determine if the issue is related to SQL Native Client.

>>> We did test ADO.NET and there is no improvement.  As mentioned earlier We use SQL Native Client 2012.

3. Do you have this issue if you use an old version of PB like PB 12.6?

>>> We never test using older PB version. However as I mentioned PB2017 R3 on SQL Server 2017 Standard Edition the speed is OK.

4. If possible, we suggest that you re-install the Standard Edition and see if the issue disappears? And then if you re-install Enterprise Edition again, do you always get this issue?

>>> We did this exercise and the application is still slow on SQL Server 2017 Enterprise Edition.


Regards,
Ken
Ken Guo 11 June, 2019
Hi Veerender,

I am sorry that what I said previously was not accurate. PB 2017 R3 supports SQL Server 2017.
Since we don’t have SQL Server 2017 Enterprise Edition locally, I can’t reproduce your issue.

Can you confirm the following again:
1. Open SQL Profile and check if the time taken for executing the SQLs is normal.
2. We suggest that you use OLEDB and ADO.Net to connect to the database, then do some quick test and see if there is any issue, so we can determine if the issue is related to SQL Native Client.
3. Do you have this issue if you use an old version of PB like PB 12.6?
4. If possible, we suggest that you re-install the Standard Edition and see if the issue disappears? And then if you re-install Enterprise Edition again, do you always get this issue?

Regards,
Ken
Veerender Khanna 02 June, 2019
Hi  Ken,

What SQL Server version is officially supported by PB2017 R3? Not aware that PB 2017 R3 does not officially support SQL Server 2017. We are able to migrate our application to SQL Server 2017 however as I mentioned the performance of SQL Server 2017 Enterprise Edition with always ON is slower than Standard Edition.

In addition, 

Is it slow to perform other functions, like the Retrieve and Update function in datawindow?  
>>> Yes it is slower

Is it also slow if you create a small application?
>>> Yes it is slower on Enterprise and Faster on Standard Edition with the same driver to SQL Server.

Is there still a problem if you connect to the database using ADO.Net DBMS?
>>> Not tried this option, we can try this option however afraid this may require extensive testing at our end to ensure all the data is stored correctly
Ken Guo 30 May, 2019
Hi Veerender,

PB 2017 R3 does not officially support SQL Server 2017.
Thanks for your feedback on the issue using SQL Server 2017.
In order to confirm whether it is related to SQL Enterprise Edition, I suggest that you uninstall it and then reinstall SQL Standard Edition, and verify if it works.  

In addition, 
Is it slow to perform other functions, like the Retrieve and Update function in datawindow? 
Is it also slow if you create a small application?
Is there still a problem if you connect to the database using ADO.Net DBMS?

Regards,
Ken
Veerender Khanna 30 May, 2019
*Phenomenon:

It is slower when we run PB 2017 R3 version with SQL 2017 Enterprise compared to SQL 2017 Standard Edition.

We used exactly the same Server with Windows 2016, installed Standard Edition 1st, then re-install with Enterprise Edition and found our PB Client Server application which does Import/Export from DB perform slower compared to SQL 2017 Standard Edition.

We are going for Enterprise Edition due to Always ON feature with SQL Reporting. We use SQL Native Client 2012.


*Reproduce Steps:


Remarks:

We realize stuff running in SQL server is performing better (like Stored Procedure call etc) however calls from PB application to DB is performing slower. Is it due to the SQL Native Client 2012?

Kindly help to advise how we can overcome this and improve the performance to match with SQL 2017 Standard Edition.
OS:
Windows Server 2016 
Platform:
64-bit 
Database Type:
Microsoft SQL Server 
Database Version:
Enterprise 2017 & Standard 2017