CODEWITHSUNDEEP
sql-serverdelphistored-procedures
JohnG
JohnG

Reputation: 272

Delphi calling SQL Server Stored Procedure slow

I'm stumped. In SSMS, when I execute a Stored Procedure with parameters, the procedure takes 6 seconds. When being executed from a Delphi program (with the same parameters) the procedure takes 26 seconds to run. I ran SQL Profiler and ran Devart's DBMonitor and it only confirms that the time is in fact 26 seconds.

Just to be sure that I'm comparing apples with apples, I clean the environment and the cache before executing in both cases...

The procedure has 7 optional parameters (with default values) all varchar's.

Can anyone guide me to find where the problem can be coming from?

Thanks in advance.

Upvotes: 2

Views: 1082

Answers (1)

JohnG
JohnG

Reputation: 272

Thanks to @Alex K.'s comment +1 which led me to the Slow in application Fast in SSMS I was able to pin point the issue. Although the article is 30 pages - it took 7 pages to understand and solve the issue.

The 2 most common problems found are Parameter sniffing and Different options that are Set that the application sets for running queries.

After copying the parameter values into local variables of the procedure to avoid parameter sniffing, which didn't change the behavior, I looked at different options that are set in the application, such as: ArithAbort, ansi_warnings,concat_null_yields_null, etc.

The problem that we were having in our application, is that the Application running in Delphi sets a bunch of options before starting: ArithAbort off, ansi_warnings off... including concat_null_yields_null OFF

The one that was causing the problem in my case is concat_null_yields_null OFF when running in SSMS (by default) concat_null_yields_null is ON.

I now need to figure out how this option being ON/OFF affects a stored procedure.

Upvotes: 2

Related Questions