November 23, 2011 at 8:27 am
1) this query goes WAY beyond what you can expect help for on a free forum where people donate their time to help others.
2) looks like some Business Objects crap 🙂
3) You can make a plan guide for this monster to force the query type you know is most efficient. That should work, assuming the plan guide can match up with this beast when it runs. And any little change from the app would make it miss.
4) The reason it isn't getting an optimal plan is likely complexity. The more things you throw in a single query the lower the probability that estimates (assuming good stats) will play out correctly. There are also numerous things in the query that will affect the optimizer's ability to get good estimates - but given that it is generated code not much you can do there either. Well, if it IS BO, Cognose or some other tool there are things that can be done on the metadata/report side to help improve generated queries. But it takes a VERY knowledgeable person to be able to do those things.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 23, 2011 at 8:46 am
kyadav (11/21/2011)
Query cannot be modified as it is being generated from third patry tool.Regards,
Virendra
Even though it is generated from a 3rd party tool, the tool is likely pulling in what it was told - drag and drop style tool?
The tool that generated this query - can you get in and look at what it is that caused this query to be generated? E.G. in Cognos, you can visually go in and determine what tables were added to the query. The same can be said of Crystal Reports. Other ORM tools do the same sort of thing. Take a look inside the tool and see what you can do to optimize the query requirements.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply