powerbuilder sql server performance issues

[mulakhail]

We converted our pb app that was using sql anywhere 11.5 to
use sql server 2008 and since our migration although the app
works but performance that was pretty good on sql anywhere
has degraded. We were hoping that sql server being a better
dbms will give us at least the same if not better
performance. Has this been discussed before here and is
there a link that will help me with our performance issue.

Here are some info that will help as for as the version and
dbparm string is concerned:

PB version 12.5 build 2511
sql server 2008
snc: SQLNCLI10
DBPARM: DBParm =
"Database='',DelimitIdentifier=1,RecheckRows=1,Provider='SQLNCLI10'
,Block=1000,DisableBind=0"

any help will be much appreciated

 

[mbalent]

Have you done any diagnostics such as tracing the database? There are many things which could be in play here (like indexes or your use of cursors, etc).

Matt

"Nature forges everything on the anvil of time"

http://www.anvil-of-time.com

 

[mbalent]

You may wish to revisit your 'BLOCK=100' parameter. It's my understanding this relates to how the SQLNCLI driver handles subquery results of more than one row. (Try Block=1).

Matt

"Nature forges everything on the anvil of time"

http://www.anvil-of-time.com

 

[mulakhail]

Matt: I will look into block parameter again, I thought it had to do with the query results being returned in each block and higher the block size more records are returned in each block. But I could have misread it I will check it out again. However intially I did not even have block parameter set and after I set it it did not make any difference. We are also running sql profiler in ms sql server to pinpoint queries that may be a problem. I wanted to see besides analyzing individual queries if there is something eles that we could do to improve performance. thanks for your help and I will keep updating this thread with my results.