1. Arnaud Bailly
  2. PowerBuilder
  3. Tuesday, 23 June 2020 10:23 AM UTC

We are trying to understand why our application built with PB2019R2 is significantly slower than the same application built with PB2017, even though in general the application is slow. Analysing the interaction with database using MSSQL Profiled we noticed there is a lot of queries following this pattern:

SET FMTONLY ON select cle_societe, cle_article from article_societe where 1=2 SET FMTONLY OFF
SET TEXTSIZE 32767
declare @p1 int set @p1=67872 exec sp_prepexec @p1 output,N'@P1 numeric(12,0),@P2 numeric(12,0)',N'select tourne_en_prod_soc from article_societe where cle_societe =@P1 and cle_article =@P2 ',2,2000095617 select @p1
SET TEXTSIZE 2147483647
exec sp_unprepare 67872

This pattern is repeated with a lot of different queries over and over and we are having a hard time understanding
where this comes from

Does anyone know where those queries come from? Each of them is usually fast but they add up to account
for 80-90% of the server time

Thanks 

John Fauss Accepted Answer Pending Moderation
  1. Tuesday, 23 June 2020 13:46 PM UTC
  2. PowerBuilder
  3. # 1

Greetings, Arnaud -

I entered keywords "SQL server sp_prepexec" in the duckduckgo.com search engine, and the following URL was the first web page listed:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-prepexec-transact-sql?view=sql-server-ver15

It appears this is a system stored procedure that has something to do with cursors (from scanning the sidebar list on the left side of the Microsoft Docs web page).

You didn't mention what version of SQL Server you are using, or if you have recently upgraded SQL Server. You may need to ask for additional explanation on a SQL Server forum.

Regards, John

Comment
  1. Arnaud Bailly
  2. Tuesday, 23 June 2020 14:12 PM UTC
Thanks for your insights, I admit I did not think of searching this particular query: I searched for set XXX things :(

this makes sense indeed.
  1. Helpful
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.