Slow in the Application, Fast in SSMS

Posts   
 
    
sunnyman
User
Posts: 51
Joined: 21-Feb-2007
# Posted on: 14-May-2014 11:46:30   

Hi, I use llblgen version 2.6 I have a query based on typedview.

The query executed very slow (16 sec) from my program.

I use the profiler and get the exact query generated by llblgen at runtime and run it against db directly and it executes very fast. Also when I execute the same query using .net dataadapter from the same program it executes very fast.

I googled the problem and found these links: http://www.sommarskog.se/query-plan-mysteries.html

The above link suggests that it’s a performance problem related to parameter sniffing and give the following instructions:

1- Try running the query against the db directly using different ARITHABORT options With ARITHABORT OFF : SLOW like when I execute it from my program With ARITHABORT ON : VERY FAST

2- Checking the actual query plan and seeing the difference between compile & runtime parameter value. **With ARITHABORT OFF : **compile & runtime parameter value are different With ARITHABORT ON : compile & runtime parameter value are equal

I try to set DbUtils.SetArithAbortFlag(True / false) but llblgen always set ArithAbort OFF before the query

I found this link from Microsoft that said http://technet.microsoft.com/en-us/library/ms190306.aspx

The default ARITHABORT setting for SQL Server Management Studio is ON. Client applications setting ARITHABORT to OFF can receive different query plans making it difficult to troubleshoot poorly performing queries. That is, the same query can execute fast in management studio but slow in the application. When troubleshooting queries with Management Studio always match the client ARITHABORT setting.

I have never heard about parameter sniffing problem and never face this type of problems with / without llblgen.

The problem suddenly appeared and I don’t know why.

Could you please give me some help?

Thanks.

Walaa avatar
Walaa
Support Team
Posts: 14735
Joined: 21-Aug-2005
# Posted on: 15-May-2014 01:39:08   

Please read the discussion on the following thread: https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=21962

sunnyman
User
Posts: 51
Joined: 21-Feb-2007
# Posted on: 25-May-2014 20:47:13   

Thanks Walaa for your great help. This solves my problem in development server. I will test it in production server in the next few days. But i think it will work.

Sorry for delay, My main HD is OFF. Thank god i have backup