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.