• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • MSS long execution time due to MS_SQLSERVER_SYNTAX in pbodbpbodb.ini file

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (4)

Resolved MSS long execution time due to MS_SQLSERVER_SYNTAX in pbodbpbodb.ini file

  1. Advice
0
Votes
Undo
  1. Mark Malin
    Mark Malin
  2. PowerBuilder
  3. Tuesday, 28 June 2022 11:46 AM UTC

Hi All.

We've noticed that the query below, that appears in the pdobd.ini file (both in PB17 and PB21)

SelectDefault='select y.name, x.text from syscomments x, syscolumns y where x.id = y.cdefault and y.id = OBJECT_ID(''&TableOwner.&TableName'')'

The above query execution takes a long time to complete. 

Execution samples were around 5 minutes.

The issue observed on SQL Server 2014.

We've considered that perhaps the x.id = y.cdefault condition might be incorrect, as the records returned always amounted to zero.

Changing the condition from x.id = y.cdefault to x.id = y.id resulted in less than a second execution time.

Although, according to documentation, messing with the ini file is not recommended.

 

Any advise regarding this issue and maybe how can it be resolved?

 

 

 

 

 

 

 

  1. PowerBuilder
  2. SQL Server
Changing nested report sub report dataobject is sl...
Printer is not defaulting to workstation default p...
Responses (4)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 29 June 2022 14:42 PM UTC
  2. PowerBuilder
  3. # 1

Hi Mark ... No, the SNC (original MSS driver replacement) has now been replaced with the new Microsoft SS native driver standard ... "MSO".  ;-)

Comment
There are no comments made yet.
Mark Malin
Mark Malin Accepted Answer Pending Moderation
  1. Wednesday, 29 June 2022 04:57 AM UTC
  2. PowerBuilder
  3. # 2

As far as we understand. SQL native client is deprecated.

Isn't there a benefit to disable this rule altogether? 

As there is no output returned and it is being run against all our database tables which is much time consuming

Comment
mike S
  1. mike S
  2. Wednesday, 29 June 2022 16:33 PM UTC
My guess is that this is in the IDE where you are seeing slowness? You never said what the process is that was slow for you.



The PB ide database tool uses this code to get the defaults when you look at the column definition. I believe this is the only time it would run that sql script - I can't see any other reason to get the database defaults. it is very doubtful that the drivers are making it slow. are you using a schema and do you have have the PB default schema using that value? Did you DBA look at the slowness?



In any case, the IdE is used as a lightweight generic database tool by many developers. It isn't great at what it does.

IMO it is extra technical baggage that appeon has to deal with when there are plenty of database tools such as sql enterprise manager that are free, as well as low cost ones that work loads better than PB at this.



appeon should simply drop support for this aspect of PB and remove it from the IDE.



  1. Helpful
Roland Smith
  1. Roland Smith
  2. Wednesday, 29 June 2022 17:06 PM UTC
SQL Native Client was deprecated but then Microsoft brought it back. PowerBuilder 2019-R3 and higher have a new native driver MSO that is specific to the new SQL Native Client. The native driver SNC only works with the older deprecated versions of SQL Native Client.
  1. Helpful
Roland Smith
  1. Roland Smith
  2. Wednesday, 29 June 2022 17:51 PM UTC
Also, the ini file is only used for ODBC connections. MSO would bypass it entirely.
  1. Helpful
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Tuesday, 28 June 2022 12:25 PM UTC
  2. PowerBuilder
  3. # 3

SelectDefault : the sql is correct.

it gets the defaults set on columns for the given table.

you can change it up a bit to see whether you have any defaults on any column on any tables:

select o.name as tablename, y.name as columname, x.text as columndefault
from syscomments x
join syscolumns y on x.id = y.cdefault
join sysobjects o on y.id = o.id

 

 

 

 

Comment
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Tuesday, 28 June 2022 11:58 AM UTC
  2. PowerBuilder
  3. # 4

Hi Mark;

  I suspect that the performance issue is on the DBMS server side. I would use another SS utility to test the SQL syntax. My guess is that you'll see the same behaviour.

  You might want to also test your SQL using a SS native driver vs ODBC (which is "middle ware").

Regards ... Chris 

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.