.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

what are Query optimization techniques ?

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :

Hi all,

Can any one tell me what are the Query & SP tuning\optimizing techniques to guide developers.

Also if it would be great if you can provide the real time scenarios with examples & videos it will help me a lot to understand easily. please do the needful.


Thanks in advance.

Yangamuni Prasad M

View Complete Post

More Related Resource Links

MDX Query optimization

Hello Experts, I have a MDX query which first selects those promotions and products valid for a customer and a particular period. Then those sets are used for data extraction from cube. WITH MEMBER   [MEASURES].[Measure_Value] as Sum   ( { [MEASURES].[MeasureA] ,[MEASURES].[MeasureB] ,[MEASURES].[MeasureC] } ) SET   [Products_Subset] as FILTER ( {[Product].[TU Code]. Children} , ([Country].[Code].&[XXX],[Time].[Calendar].[Month].&[7]&[2010] , [Customer].[Hierarchy].[CustomerLevel2].&[00005]&[00004] ,[MEASURES].[Measure_Value])<>0) SET   [Promotion_Subset] as FILTER ( {[Promotion].[Promotion ID]. Children} , ([Country].[Code].&[XXX],[Time].[Calendar].[Month].&[7]&[2010] , [Customer].[Hierarchy].[CustomerLevel2].&[00005]&[00004] ,[MEASURES].[Measure_Value])<>0) MEMBER   [Measures].[Level2 Customer Code] AS   [Customer].[Hierarchy].CurrentMember.Properties("Level2 Customer Code") SELECT   { [Measures].[Planning Customer Code] ,[MEASURES].[MeasureA] ,[MEASURES].[MeasureB] ,[MEASURES].[MeasureC] ,[Measures].[Comments] } ON COLUMNS , FILTER   (( { [Promotion_Subset]} * { [Products_Subset]} * {[Customer].[Hierarchy].[CustomerLevel2].&[00005]&[00004]} * {[Time].[Calendar].[Month].&[7]&[2010]}* {[Country].[Code].&[

SQL query optimization

I have a stored procedure that is taking a long time to run. I have to optimize it. While testing I found that there is a join that is taking a long time : select t1.col1, t1.col2, count(distinct a.personid) e1, count(distinct b.personid) e2 from table1 t1 ...... join messages m with(nolock) on m.messageid = t1.messageid join messagerecipients mr with(nolock) on m.messageid = mr.messageid  left join people a with(nolock) on mr.personid = emp.personid and emp.persontypeid = 2  left join people b with(nolock) on mr.personid = con.personid  and con.persontypeid = 1   The personid in messagerecipients does not have an index. So the join are taking a long time when I find the count in the select statement. I do not intent to modify the production table for now to apply indexes. Is there a way I can optimize my query   Thanks, Reshmi

MS SQL 2000 The query processor ran out of stack space during query optimization.

On an insert statement with an image field I get the following error when the data to go into the image field is over a certain size. Lots of special characters. The query processor ran out of stack space during query optimization. Please help on how to fix.

xml query optimization


following query is taking 3 second at my computer. need to optimize the query

declare @TargetResponseXML xml
set @TargetResponseXML = 

	<prospectsurveyresponses pspt_id="100" vcli_id="321" cmp_id="221">
				<surveyelements id="1">
			<page seq="1">
					<question id="2" seq="2" ismandatory="1" isvertical="0">
						<text>Travel through</text>
							<targetresponses id="4">
								<response type="matrix" id="4" seq="4" jumpto="2" isselected="0">
								<response type="matrix" id="5" seq="5" jumpto="3" isselected="0">
								<response type="matrix"

Insert Column Limit from XML? The query processor ran out of stack space during query optimization e


I get the following error:

Msg 8621, Level 17, State 2, Procedure ProcessFliteTracWorkingXML_FTPax, Line 348
The query processor ran out of stack space during query optimization. Please simplify the query.

I have looked at this KB fix, but we are a newer version then in that article:

Also found this post with a similiar issue, but no solution:

Before I call Microsoft Support I thought I would try here first.

I am trying to run a stored proc that basically needs to parse xml messages coming from an outside datasource.  We are using SQL Server 2005 SP3 (9.00.4053.00 SP3 Standard Edition).  This datasource is an old pervasived database with not the best data model.  So what we wanted to do was pull the raw data as is into a staging table and then convert it to a better data model in our ODS.  There are around 300 columns in this table and I have a generic process that pulls the rows out and converts to XML and saves to a working table (this

MDX Query Optimization Help!


I have the MDX query below that intermitantly takes forever to execute. Sometimes it runs in 10-20 seconds...others, 7-10 MINUTES! I am new to MDX, so I am not sure what I need to do to optimize the execution. The end result is typically only a few hundred rows of data.

Here's the query. I captured this using the Profiler from a SSRS report which is feeding the StrToSet functions with parms selected by the user.

 MEMBER [Measures].[QtyRTS] AS 
   [Measures].[Molding - Cases RTS] = 0
   ,[Measures].[Molding - Quantity RTS]
   ,[Measures].[Molding - Cases RTS]
  ,[Measures].[Molding - Quantity RTS]
  ,[Measures].[Molding - Quantity Made]
  ,[Measures].[Molding - Mach % Std (Schd Hrs)]
  ,[Measures].[Molding - Mach % Std (Run Hrs)]
  ,[Measures].[PID Labor - Labor Efficiency %]
  ,[Measures].[Downtime Hrs]
  ,[Measures].[Scheduled Hrs]
  ,[Measures].[Downtime (% Schd Hrs)]
  ,[Measures].[Run Hrs]
  ,[Measures].[Unknown Availability Hrs]
  ,[Measures].[Scrap - Scrap (% Made)]
  ,[Measures].[DMR - Quantity Rejected]
  ,[Measures].[DMR - After Rework Rejects]
  ,[Measures].[OEE %]
  ,[Measures].[OEE - Availability %]
  ,[Measures].[OEE - Performance Rate %]
  ,[Measures].[OEE - Yield %]
   [Dim Product].[Product - Family Name].MEMBERS
   ,[Dim Resources].[Machine].MEM

Sharepoint Search Query Optimization



need some help on optimizing my query below. I wanted to do here is put more weight on other resulting items besides documents. Are there any improvement / points /performance optimization to the query?

SELECT "PageMap", "Filename", "Title",  "Created","LastModifiedTime","Author","Path", "Contentclass", "Description", "Rank" ,"ContentType","ContactsM","ContentE","IsDocument" FROM Scope()
CONTAINS("PageMap",'"*{0}*"') RANK BY WEIGHT ( 0.90 ) OR
CONTAINS("Title",'"*{0}*"') RANK BY WEIGHT ( 0.20 ) OR
CONTAINS("ContactsM",'"*{0}*"') RANK BY WEIGHT ( 0.20 )OR
CONTAINS("ContentE",'"*{0}*"') RANK BY WEIGHT ( 0.20 )


ContentType='Contacts' OR
ContentType='Content Editor' OR

   FREETEXT("Title",'"*{0}*"') RANK BY WEIGHT(0.20) OR
FREETEXT("FileName",'"*{0}*"') RANK BY WEIGHT(0.20) OR

Query Optimization


Hi i have a SP which will pull the Data  and another SP to organize that Data into proper format to fit my front end application reports.

below is the SP that we use to do formating the resultset form the original SP




Can Some one optimize this SP, i dont to use the while loop coz i have 18 such SP's and i cant do it in all the SP.

Can we get this kind of out put by using a regular query



Query optimization in sql 2005


 Hi ,

        How to optimize sql query in sql server 2005, any idea

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to Encrypt Query String Parameters in ASP.NET

Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Best Coding Techniques To Improve Performance for SharePoint Applications

As more developers write custom code by using the SharePoint Object Model, they encounter common issues that can affect application performance.

The following areas reflect the main issues encountered by developers as they write custom code by using the SharePoint object model:

§ Disposing of SharePoint objects

§ Caching data and objects

§ Writing code that is scalable

Content Query Web Part missing in SharePoint 2010

If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.

SQL Server Optimization

This article describes different ways of improving the performance of SQL Server queries, with a focus on index optimization. The purpose of this document is to describe different ways of improving the performance of SQL Server queries. Most of this document will describe index optimization, with occasional references to particular code snippets. In other words, this document will describe how to achieve the best performance, given the tables and queries to run against.

sql query that don't run



I'm going crazy... i've got a piece of code that don't update the database... Could somebody tel me why it doesn't run??

Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim cmdstring As String = "Update Corsi Set [Contenuto]='" & TxtText.Content & "' Where [ID]='" & Request.QueryString("Id").Replace("'", "") & "'"
        conn = New SqlConnection(SqlAnni.ConnectionString)
        cmd = New SqlCommand(cmdstring, conn)

Need help with a linq to sql query


I want to display some stats in a listview contril.  The table contains a record for every impression and for every click, and every record has a datetime field for the time and date of the impression of click.  I want to be able to display how many impressions and clicks there were in a given day and hour, something like below:

Date              Time                              Impressions    Clicks
8/20/2010                                               450              14            
                     Midnight -1:00am               150               5

How to send an email with information in query string


First I will say I googled (Binged to make Bill happy) for this but could not find anything, perhaps I am not stating what I need correctly;

I have a website that a customer creates orders (for a printing company) and after the order, I would like to email the customer a link to view the status of their order;

I need to send the CustomerID in the link so on the returning page if a CustomerID is detected in the querystring, I "auto login" the client and open the "Dashboard" for the order page for that customer.

Can anyone point me in the right direction as to how to send the link and how to parse the querystring to get the values from the link;

Thank you if you can point me in the right direction.

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend