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

Top 5 Contributors of the Month
Post New Web Links

How to tell SQL Server to skip the part after AND?

Posted By:      Posted Date: October 04, 2010    Points: 0   Category :Sql Server

Hi everybody!

I have a question regarding optimizations. Concider the following WHERE-clause:

WHERE [Filter A]
AND [Filter B]

Suppose filter B is really heavy to execute. Is it possible for me to ensure that the SQL Server engine skips filter B if filter A returns false? After all, if filter A returns false, the value of filter B doesn't matter anymore because the result of the AND operation will be false anyway. I thought that SQL Server would already keep this in mind but it seems I was wrong. Here is some sample code that demonstrates this:

SET @var = 1
	SELECT 1 AS [Value]
) A
WHERE @var = 1
AND A.Value = (SELECT @var)
	SELECT 1 AS [Value]
) A
WHERE @var = 2
AND A.Value = (SELECT @var)
	SELECT 1 AS [Value]
) A
WHERE @var = 2

If you include the execution plan, you'll notice that the first 2 queries are equally fast, and that the 3rd is the fastest one. This is logic because the 3rd one has one less WHERE-operation to execute. However, the 2nd query should (so I thought) be faster than the 1st one because its first filter returns false. I would have expected that t

View Complete Post

More Related Resource Links

Test Run: Configuration Testing With Virtual Server, Part 2


Because Virtual Server is built upon a set of COM modules, you can automate the creation and testing of virtual machines. Here we use Windows PowerShell to run the tests.

Dr. James McCaffrey and Paul Despe

MSDN Magazine December 2008

Test Run: Configuration Testing With Virtual Server, Part 1


This time James McCaffrey sets up a virtual environment to use for configuration testing to introduce you to software configuration testing with Microsoft Virtual Server

Dr. James McCaffrey

MSDN Magazine September 2008

Service Station: An XML Guru's Guide to BizTalk Server 2004, Part I


Who would have believed that XML, such a seemingly trivial technology, could revolutionize an industry? It may have seemed like a long shot in the beginning, but the XML aficionados saw something special and pragmatic right away-a sort of duct tape for the world's information systems.

Aaron Skonnard

MSDN Magazine November 2005

Pass the server variable to search core result web part


I'd like to custom the search core result web part to filter results based on the log on user name.

It can easily be done in Dataview web part.

However the search core web part doesn't accept "<parameterbining>" to pass in the server variable [logon_user]. I can't figure out how to use <xsl:param> to get this server variable either.


Any ideas? Thanks a lot!

If you get confused, listen to the music play...

Customizing the Content Query Web Part in SharePoint Server 2007

Walk through how to customize the Content Query Web Part in SharePoint Server 2007 to query content across multiple sites in a site collection and display the results in any way that XSL can support. Learn how to get similar results when customizing the CQWP does not meet your needs.

Real World Branding with SharePoint Server 2007 Publishing Sites, Part 1

Learn the essential concepts needed to create an engaging user interface design in SharePoint Server 2007 publishing sites.

SQL Server SKIP statement with VB6

Hi All, I am trying to perform a complex query against a SQL Database in VB6 using ADO. Basically this is what I am  looking for; Select all records from the table, ordered by ImageDateTime DESC and grouped by key1, then I want to skip the first xx records of each group. Trying to figure out this syntax is making me OLD :) Any help would be appreciated.  

Can the default member for the sql server analysis filter web part be a mdx expression or not

I have designed a dashboard , some parametrized reports ( on date and time) in my case with a cube as datasource, added an analysis services 2005 filter, connected it to the reports. The issue concerns the default value for the filter . I f I choose [Date Local].[Calendar Hierarchy].[All] it works well. But the business requirement is to use the last day where there have been registered a measurement which in my case looks like the following mdx expression: Tail( Filter ([Date Local].[Date Key].[Date Key].Members , ( ( [Advisor Organisation].[Advisor organisation].[Contact Center].&[Denmark]&[Kundelinjen], [Measures].[Calls Offered] , [Date Local].[DK Holiday].&[False] , [Date Local].[Week Day].&[True] ) > 0 ) ), 1).Item(0).Item(0) The expression is all right as I use it in my report as an mdx filter expression. But when I use apply it I get an invalid dafault member value. So the question is : Does the filter support an mdx expression ? If yes, is there another syntax or change in order to make it work ? Thank you a lot for your answer.

Error rendering "*************" web part: [The remote server returned an error: (400) Bad Request.]

Hi There I am implementing a WCF based solution and all was working fine until I started to process larger quantities of data. The Solution worked well in DEV where we added large quantities of daata when we restored the site to QA we got this error . Error rendering Councillors Online web part: [The remote server returned an error: (400) Bad Request.] Can anyone propose a solution to this problem? Thank you My WCF web.config looks as follows : ======================================================== <system.serviceModel>  <bindings>   <webHttpBinding>   </webHttpBinding>  </bindings>  <behaviors>   <serviceBehaviors>    <behavior name="serviceBehavior" >     <serviceMetadata httpGetEnabled="true" />    </behavior>   </serviceBehaviors>   <endpointBehaviors>    <behavior name="web">     <webHttp/>    </behavior>   </endpointBehaviors>  </behaviors>  <services>   <service name="CouncillorsOnline.Service.CouncilMasterDataService" behaviorConfiguration=

Slow performance when searching in "any part of field" of a SQL Server database using a Microsoft Ac

We have just migrated our back end database from Access to MS SQL Server 2008 R2. We have noticed better performance on our searches - unless we select to search in "any part of field" in Access (with Access' built-in search function - we use a mix of Access 2003 and 2007). This takes nearly 20 seconds to find the result, whereas before the migration it was taking 5-7 seconds (compared to instant results we now experience when searching for whole fields). The main fields we search are not large - usually just two words. Obviously it is better to search for a whole or start of a field, but this is not always possible. The contractor that assisted us in this project has told us that this "is just the way SQL works with Access". Is this really true? I find it hard to believe two MS products would have such a big issue between them. I presumed using SQL with an Access front end would be a common setup, which is why I thought this must be a problem with our setup. Is there any way to fix this speed issue?

How to access server/share resources from an internal/intranet Web Part?

We are a team of native-client/webforms developers who have been tasked with developing an internal intranet SharePoint system for our team. We want a WebPart that displays sets of files on internal shares, allows selection of subset(s), and then downloads the files to the local machine. Basically it's a builds/test-artifacts/documents "getter" scenario. We created less elaborate web sites that do somethihng like this (as proof of concept.) However, no matter what we do with our WebPart, it will not allow access to non-local(host) shares, files, etc. The WebPart CAN access said shares/files when the entire thing - client (IE session) and debug[ger/gee] etc - are run on a single dev box. However, when we attempt to run the client on a different machine accessing the dev box (in a server role), the code cannot access the network resources (for example, File.Exists() and Directory.Exists() fail.) How can we make this work?

SQL Server Analysis Services Filter web part.

Hi all In a NTLM farm with 1 SharePoint and 1 SQL server, I am using clamis based authentication for Excel Services app in SharePoint Server 2010. While all Excel documents work, I get error on "SQL Server Analysis Services Filter" web part. The filter works when I am logged on the sharepoint server and open the Intranet to use it, but doesn't work if I reach the Intranet by not logging on the SharePoint server. The error I get is on the filter web part: "An error occured while retrieving filter values (The connection either timed out or was lost). Anyone else that have had the same problem? Any ideas to troubleshoot? :) Regards

Issue - A significant part of sql server memory has been paged out.


Hello Experts,

I am running a huge SSRS 2008 report and it gets rendered sometimes and sometimes it gives me an error,

"The report execution has expired or cannot be found. (rsExecutionNotFound)”.

When I see SQL Server log after this issue comes I see following message there;

"A significant part of sql server memory has been paged out. This may result in performance degradation. Duration: 300 seconds. Working set(KB): 97672, memory utilization 42%"

Please help me to know what is the issue and how to fix it.


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