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

Post New Web Links

WHERE Optimization

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :ASP.Net

I would like to optmize this query

SELECT * FROM table Where field LIKE '%abc%' OR field LIKE '%def%' OR field LIKE '%ghi%'

for something like

WHERE field IN ('%abc%','%def%','%ghi%')

I cant use substring(field) because I cant define a limit for substring.


View Complete Post

More Related Resource Links

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.

VS2010 WDP Optimization


We have moved our projects from VS2008 to VS2010(Ulitmate) but staying with .Net 3.5. In VS2008 we had a web deployment project that would compile the web project to a single dll and emit full debug information, adding the new web deployment project in VS 2010 to do the same the dll is produced along with the pdb but when a dev attempts to debug the variable comes back with "Cannot evaluate expression because the code of the current method is optmized".

In VS2008 we had a targets fix file which had the setting for the optimization but these don't appear to work under 10 (plus the targets file has grown).



Extreme ASP.NET: Search Engine Optimization with ASP.NET 4.0, Visual Studio 2010 and IIS7


In this article, the author takes a look at SEO practices that the reader can apply when using the latest Microsoft Web technologies.

Scott Allen

MSDN Magazine September 2009

Memory Lane: Rediscover the Lost Art of Memory Optimization in Your Managed Code


Managed applications rely on the garbage collector in the .NET Framework to allocate and clean up memory. The little CPU time spent performing garbage collection (GC) is usually a fair trade-off for not having to worry about memory management. But for applications in which CPU time and memory are precious resources, minimizing the time spent garbage collecting can greatly improve application performance and robustness. Find out how to manage memory all over again.

Erik Brown

MSDN Magazine January 2005

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

Try your SQL CLR optimization skills on Phil Factor Speed Phreak challenge

Who can provide a better solution to this problem? http://ask.sqlservercentral.com/questions/17499/phil-factor-speed-phreak-challenge-6-the-stock-exchange-order-book-state-problem Johan Åhlén http://blogical.se/blogs/jahlen

T-SQL: "Premature optimization is the root of all evil in programming"

The above quote is Naom's trademark signature block, caught my attention.Generally sounds true, you should get your T-SQL code "straight" before starting optimizing.  What do you think?Are you under pressure (by supervisor or self) to release T-SQL code quick, declare it production and pass on to the DBA to optimize?Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

SSRS Report Performance & Optimization

Can  any explain how would i Optimize a report performance.. I have few reports that are taking longer time to run. Thank youFM

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.

Optimization level is 0%

Hi all,   I have what appears to be a bug in SSAS 2005 and 2008.  Optimization level is 0% when we add more than a certain number of dimensions to the cube.   I have a cube with 49 dimensions – some with multiple attributes and 22 measures in 1 measure group.   We can add 36 dims with no problem and the aggregations –via the wizard – work fine.  When we add the 37<sup>th</sup>, we get 0%.  If we remove a random dimension it remains at zero, but if we remove the last one added it begins to work again.   It does not seem to matter which dimensions are added or excluded, the number is consistent at 36 dimensions max. (I assume this number would change depending on the complexity of the cube)   Single fact table (view) and dimensions are all views as well.   I have seen other threads noting this situation, but none with a resolution, and none that have seen something with a specific threshold.   Thanks,   --Joe

URL optimization in MVC


How to implement url optimization in MVC?

Currently i m using id for landing pages in page url. But my clients asked

"URL optimized for Search Engine for landing pages - Relevant to title"

How to set this? Please help any one. 

.Net 2.0 errors in Windows XP .NET Runtime Optimization Service & EventType clr20r3


I have a program that runs on windows XP using .net 3.5 framework family. After many reinstalls using the redistributable package, the bootstrapper, and windows updates, when launching the program i get "".exe has encountered a problem and needs to close. Event viewer shows this:

.Net Runtime 2.0 Error

EventType clr20r3, P1 "".exe, P2 2010.1.1012.1, P3 4c6168a1, P4 system.configuration, P5, P6 4889de74, P7 277, P8 14, P9 ioibmurhynrxkw0zxkyrvfn0boyyufow, P10 NIL.

There are also these errors in event viewer, that only showed up after installing using Windows Updates.

.NET Runtime Optimization Service (clr_optimization_v2.0.50727_32) - Service reached limit of transient errors. Will shut down. Last error returned from Service Manager: 0x80029c4a.

I followed the advice in another msdn support request, http://social.msdn.microsoft.com/Forums/en-US/netfxsetup/thread/f56cc6e4-c2bf-490c-b87b-3e22b1e6b935 that suggested running:

%windir%\microsoft.net\framework\v2.0.50727\ngen.exe update

%windir%\microsoft.net\framework\v2.0.50727\ngen.exe executequeuedstatus


%windir%\microsoft.net\framework\v2.0.50727\ngen.exe queue status

and the requested output of the update command returns:

Error loading type library/DLL. (Exception from HRESULT: 0x80029C4A (TYPE_E_CANTLOADLIBRARY))

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"




Suppose i have a table with a data around billion records, how far the below query causes overhead(Performance Issue).

SELECT <>,<> FROM (SELECT <>,<>,<> FROM <> ORDER BY <>,<>)



Santhosh Hiriyanna

Regards, Santhosh Hiriyanna
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