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


Top 5 Contributors of the Month
Kaviya Balasubramanian
Sgraph Infotech
Imran Ghani
Post New Web Links

Optimize

Posted By:      Posted Date: September 09, 2010    Points: 0   Category :Sql Server
 
Hi, I was asked a scenario in a interview. A table has a index on CityName.A query is constructed using the City name in where clause.There can be many customers in that city.How do we optimize that query. Regards Janet


View Complete Post


More Related Resource Links

Optimize WebResource.axd and ScriptResource.axd in asp.net

  
When the handler is requested by the browser it looks for a URL parameter containing the references to the original resource script. They are separated by a comma in the URL parameter. It then retrieves the content of each script using a HTTP request. Then content is then aggregated into a single string which then get's minified. Minifying means that all comments and whitespace is removed from the script.

SQL Server: Uncover Hidden Data to Optimize Application Performance

  

SQL Server 2005 gathers a lot of useful data that you can use to identify areas where database performance can be improved. Here's what you need to improve performance.

Ian Stirk

MSDN Magazine January 2008


Parallel Performance: Optimize Managed Code For Multi-Core Machines

  

While multi-core processors have become increasingly common, most applications still fail to take advantage of multiple cores. Here's an overview of creating multithreaded applications that benefit from these new processors.

Daan Leijen and Judd Hall

MSDN Magazine October 2007


Mobility: Optimize Your Pocket PC Development with the .NET Compact Framework

  

The .NET Compact Framework can be used to write great code and great applications. As long as you take a few things into consideration and are willing to bend a rule or two, you can have your performance cake and eat it too. In this article the authors present some neat tricks to make life as a programmer easier when using the .NET Compact Framework. Later they discuss techniques to increase performance, and decrease both load time and memory footprints. Sample code is provided.

Dave Edson and John Socha-Leialoha

MSDN Magazine December 2004


Design: Inspect and Optimize Your Program's Memory Usage with the .NET Profiler API

  

Developers using .NET often make memory leak tracking a low priority because the common language runtime takes care of garbage collection. What few developers realize, however, is that their objects' lifespans, along with their size and what other objects have been instantiated, all affect how they are cleaned up. Depending on the particular circumstances, these combinations can negatively affect performance, especially over the lifetime of an application. This article presents a way for developers to see memory usage and understand garbage collection using the .NET Profiler API. Along the way, a sample application to demonstrate these principles is built.

Jay Hilyard

MSDN Magazine January 2003


Optimize Calculated Measure containing COUNT EXISTING

  
Hi, My goal is to change the text color of all cells that contain aggregated values. Currently I achieve it like this: I COUNT the members of all attributes of all dimensions. To be multi-select-safe I am using the EXISTING keyword. If the members count of at least one dimension is not 1 than the background color of the cell is changed. CREATE MEMBER CURRENTCUBE .[Measures].[SingleCellSelected]   AS iif ((COUNT (Existing ([Dim1].[Attr1].[ Attr1].MEMBERS ))=1) AND (COUNT (Existing ([Dim1].[Attr2].[Attr2].MEMBERS ))=1) AND (COUNT (Existing ([Dim2].[Attr3].[Attr3].MEMBERS ))=1) AND (COUNT (Existing ([Dim2].[Attr4].[Attr4].MEMBERS ))=1) AND (COUNT (Existing ([Dim3].[Attr5].[Attr5].MEMBERS ))=1),1,0),   VISIBLE = 0  ;    SCOPE ([Measures].AllMembers ); FORE_COLOR (this ) = iif ([Measures].[SingleCellSelected]=1,0,16744448); END SCOPE ; This approach works but it performs badly with attributes with many members. Do you have any idea how to optimize this? Thank you!

proc taking 48 sec to run how to optimize

  
Hi All, i had wrote a dynamic procedure to calculate the values yearly.Now its taking 58 sec to execute and i have to reduce the execution time(3-4 sec).I had used 6 #temp in it and all the table have clusterd index on it.So plaese tell me what i have to ? I am working on sql server 2000. Your help is appreciated.Thank's

Please help me optimize this Overlap query

  
Hi, I have 2 tables: Table1: ls_activated (125,450 records) , screenshot: http://img180.imageshack.us/img180/3282/lsactivated.png Table2: alu (1,194,734 records), screenshot: http://img441.imageshack.us/img441/443/rmskalu.png I want to find how many ALU that are in  a ls_activated record's interval (start-end). basically i want to find the overlapped regions of these two tables (they have to be on the same chromosome) For example: if a ls_activated record has start-end from 3-10, any ALU records that has start-end position that in 1-10 is considered matching. ls_activated:                     3 |---------------------------------------! 10 ALU                              1 |-----------| 5 ALU                                                                          &nbs

looking for optimize way...please help

  
here a below table and the output I am looking for by some isert/update...here I need an extra column in sach a way that MAX order number is set to 1...what are the other way to optimize below query, CREATE TABLE X(step_id varchar(50), step_order int) INSERT INTO X VALUES ('A', 1) INSERT INTO X VALUES ('B', 2) INSERT INTO X VALUES ('C', 2) DECLARE @TBL TABLE (step_id varchar(50), step_order int, is_max BIT) INSERT INTO @TBL SELECT step_id, step_order, 0 AS 'is_max' FROM X DECLARE @MAX_ID INT SELECT @MAX_ID = MAX(step_order) FROM X UPDATE @TBL SET is_max = 1 WHERE step_order = @MAX_ID SELECT * FROM @TBL  

Trying to optimize performance of query having Hash Matches & Agreegate

  

Hi,

I am trying to optimize a stored proc which takes about 20 seconds to execute. I tried building the index and replacing the Table scans with Index seeks and the performance came down to 15 sec which is still bad.

In my execution plan 46% goes for Hash Match Aggregate &

10% for HashMatch (JOINS)

12% sort

32% Index seeks.

Is there a way I can optimize Hash Match Aggregates?

 

My stored proc is union of 3 different views. All the views are Joins of 2 different tables(3*2 =6 tables involved). My base tables already have Indexes defined on the column by which I am joining the tables, I am still not able to get Nested Loop Joins, SQL is executing Hash Match Joins.

Do I need to create Index on views as well to achieve the performance.

 

Thanks in advance

-Jayasree


Trying to optimize performance of query having Hash Matches & Agreegate

  

Hi,

I am trying to optimize a stored proc which takes about 20 seconds to execute. I tried building the index and replacing the Table scans with Index seeks and the performance came down to 15 sec which is still bad.

In my execution plan 46% goes for Hash Match Aggregate &

10% for HashMatch (JOINS)

12% sort

32% Index seeks.

Is there a way I can optimize Hash Match Aggregates?

 

My stored proc is union of 3 different views. All the views are Joins of 2 different tables(3*2 =6 tables involved). My base tables already have Indexes defined on the column by which I am joining the tables, I am still not able to get Nested Loop Joins, SQL is executing Hash Match Joins.

Do I need to create Index on views as well to achieve the performance.

 

Thanks in advance

-Jayasree


Optimize this query

  

Hi can anyone optimize this in query with joins for performing well


SELECT @V_rowCount = COUNT(*)  
FROM ContactDetail C    
WHERE right(C.Value,9) = right(@P_Mobile1,9)      
AND C.CNTYPUID = @V_refMobile1    
AND C.IdentifyingType = 'Address'    
AND C.StatusFlag='A'    
AND C.UID NOT IN (SELECT D.UID     
     FROM ContactDetail D     
     WHERE D.CNTYPUID = @V_refMobile1  
     AND D.IdentifyingType = 'Address'    
     AND (D.IdentifyingUID = @P_AddressUID OR @P_AddressUID IS NULL)    
     AND D.StatusFlag='A'    
      )  


Trying to optimize performance of query having Hash Matches & Agreegate

  

Hi,

I am trying to optimize a stored proc which takes about 20 seconds to execute. I tried building the index and replacing the Table scans with Index seeks and the performance came down to 15 sec which is still bad.

In my execution plan 46% goes for Hash Match Aggregate &

10% for HashMatch (JOINS)

12% sort

32% Index seeks.

Is there a way I can optimize Hash Match Aggregates?

 

My stored proc is union of 3 different views. All the views are Joins of 2 different tables(3*2 =6 tables involved). My base tables already have Indexes defined on the column by which I am joining the tables, I am still not able to get Nested Loop Joins, SQL is executing Hash Match Joins.

Here is my stored proc code. Exec plan is in Excel Spreadsheet form.

https://spreadsheets.google.com/ccc?key=0AtpBDycPp-0_dDZBVHVkdU5QejBmSEE2MGM2bUREQlE&authkey=CN7wqLML&hl=en#gid=0

 

USE [HDIS]
GO
/****** Object: StoredProcedure [dbo].[sp_GetBlockSpecimenInfo] Script Date: 09/23/2010 17:26:11 ******/

By any chance can we able to optimize the below query.. It is running for long time..

  

By any chance, can we optimize the below query..This is running for 2 hrs..As of now, i really don't have any execution plans. 

If i replace "IN" with "Exists", i am saving few minutes. Can someone please optimize the query little more?
SELECT pm.pm_no,
pm.pm_key, 
trunc(pm.last_completion_dt), 
pm.location_name,
loc.unid,
loc.location_desc,
loc.tva_eq_owner_key
FROM 
eam_od_pm_tb pm, 
eam_od_location_tb loc
where 
pm.site_id in ('ALF','BRF', 'COF','CUF','GAF','JOF','JSF','KIF','SHF','WCF', 

how to improve/optimize ssis pacakge data flow task

  

in my SSIS package, i have a Dataflow Task,

it reads data from text file and store into db table, in this data flow task i have only flatfile source and oledb destination nothing else (such as derived columns etc)


is there any way that i can improve the performance, each feeds has 13 columns with 70,000 rows in it


could you please tell me how to optimize this dfd step if exist any

thanks in advabce
asitta


Categories: 
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