Search Results

Search found 106 results on 5 pages for 'predicates'.

Page 3/5 | < Previous Page | 1 2 3 4 5  | Next Page >

• Execution plan warnings–All that glitters is not gold

  - by Dave Ballantyne

  In a previous post, I showed you the new execution plan warnings related to implicit and explicit warnings.  Pretty much as soon as i hit ’post’,  I noticed something rather odd happening. This statement : select top(10) SalesOrderHeader.SalesOrderID, SalesOrderNumberfrom Sales.SalesOrderHeaderjoin Sales.SalesOrderDetail on SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID   Throws the “Type conversion may affect cardinality estimation” warning.     Ive done no such conversion in my statement why would that be ?  Well, SalesOrderNumber is a computed column , “(isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID],0),N'*** ERROR ***'))”,  so thats where the conversion is.   Wait!!! Am i saying that every type conversion will throw the warning ?  Thankfully, no.  It only appears for columns that are used in predicates ,even if the predicate / join condition is fine ,  and the column is indexed ( and/or , presumably has statistics).    Hopefully , this wont lead to to many wild goose chases, but is definitely something to bear in mind.  If you want to see this fixed then upvote my connect item here.

  Read the article

• My new favourite traceflag

  - by Dave Ballantyne

  As we are all aware, there are a number of traceflags.  Some documented, some semi-documented and some completely undocumented.  Here is one that is undocumented that Paul White(b|t) mentioned almost as an aside in one of his excellent blog posts. Much has been written about residual predicates and how a predicate can be pushed into a seek/scan operation.  This is a good thing to happen,  it does save a lot of processing from having to be done.  For the uninitiated though: If we have a simple SELECT statement such as : the process that SQL Server goes through to resolve this is : The index IX_Person_LastName_FirstName_MiddleName is navigated to find the first “Smith” For each “Smith” the middle name is checked for being a null. Two operations!, and the execution plan doesnt fully represent all the work that is being undertaken. As you can see there is only a single seek operation, the work undertaken to resolve the condition “MiddleName is not null” has been pushed into it.  This can be seen in the properties. “Seek predicate” is how the index has been navigated, and “Predicate” is the condition run over every row,  a scan inside a seek!. So the question is:  How many rows have been resolved by the seek and how many by the scan ?  How many rows did the filter remove ? Wouldn’t it be nice if this operation could be split ?  That exactly what traceflag 9130 does. Executing the query: That changes the plan rather dramatically, and should be changing how we think about the index seek itself.  The Filter operator has been added and, unsurprisingly, the condition in this is “MiddleName is not null” So it is now evident that the seek operation found 103 Smiths and 60 of those Smiths had a non-null MiddleName. This traceflag has no place on a production system,  dont even think about it

  Read the article

• Basics of Join Factorization

  - by Hong Su

  We continue our series on optimizer transformations with a post that describes the Join Factorization transformation. The Join Factorization transformation was introduced in Oracle 11g Release 2 and applies to UNION ALL queries. Union all queries are commonly used in database applications, especially in data integration applications. In many scenarios the branches in a UNION All query share a common processing, i.e, refer to the same tables. In the current Oracle execution strategy, each branch of a UNION ALL query is evaluated independently, which leads to repetitive processing, including data access and join. The join factorization transformation offers an opportunity to share the common computations across the UNION ALL branches. Currently, join factorization only factorizes common references to base tables only, i.e, not views. Consider a simple example of query Q1. Q1:    select t1.c1, t2.c2    from t1, t2, t3    where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c2 = 2 and t2.c2 = t3.c2   union all    select t1.c1, t2.c2    from t1, t2, t4    where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c3 = t4.c3; Table t1 appears in both the branches. As does the filter predicates on t1 (t1.c1 > 1) and the join predicates involving t1 (t1.c1 = t2.c1). Nevertheless, without any transformation, the scan (and the filtering) on t1 has to be done twice, once per branch. Such a query may benefit from join factorization which can transform Q1 into Q2 as follows: Q2:    select t1.c1, VW_JF_1.item_2    from t1, (select t2.c1 item_1, t2.c2 item_2                   from t2, t3                    where t2.c2 = t3.c2 and t2.c2 = 2                                  union all                   select t2.c1 item_1, t2.c2 item_2                   from t2, t4                    where t2.c3 = t4.c3) VW_JF_1    where t1.c1 = VW_JF_1.item_1 and t1.c1 > 1; In Q2, t1 is "factorized" and thus the table scan and the filtering on t1 is done only once (it's shared). If t1 is large, then avoiding one extra scan of t1 can lead to a huge performance improvement. Another benefit of join factorization is that it can open up more join orders. Let's look at query Q3. Q3:    select *    from t5, (select t1.c1, t2.c2                  from t1, t2, t3                  where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c2 = 2 and t2.c2 = t3.c2                 union all                  select t1.c1, t2.c2                  from t1, t2, t4                  where t1.c1 = t2.c1 and t1.c1 > 1 and t2.c3 = t4.c3) V;   where t5.c1 = V.c1 In Q3, view V is same as Q1. Before join factorization, t1, t2 and t3 must be joined first before they can be joined with t5. But if join factorization factorizes t1 from view V, t1 can then be joined with t5. This opens up new join orders. That being said, join factorization imposes certain join orders. For example, in Q2, t2 and t3 appear in the first branch of the UNION ALL query in view VW_JF_1. T2 must be joined with t3 before it can be joined with t1 which is outside of the VW_JF_1 view. The imposed join order may not necessarily be the best join order. For this reason, join factorization is performed under cost-based transformation framework; this means that we cost the plans with and without join factorization and choose the cheapest plan. Note that if the branches in UNION ALL have DISTINCT clauses, join factorization is not valid. For example, Q4 is NOT semantically equivalent to Q5.   Q4:     select distinct t1.*      from t1, t2      where t1.c1 = t2.c1  union all      select distinct t1.*      from t1, t2      where t1.c1 = t2.c1 Q5:    select distinct t1.*     from t1, (select t2.c1 item_1                   from t2                union all                   select t2.c1 item_1                  from t2) VW_JF_1     where t1.c1 = VW_JF_1.item_1 Q4 might return more rows than Q5. Q5's results are guaranteed to be duplicate free because of the DISTINCT key word at the top level while Q4's results might contain duplicates.   The examples given so far involve inner joins only. Join factorization is also supported in outer join, anti join and semi join. But only the right tables of outer join, anti join and semi joins can be factorized. It is not semantically correct to factorize the left table of outer join, anti join or semi join. For example, Q6 is NOT semantically equivalent to Q7. Q6:     select t1.c1, t2.c2    from t1, t2    where t1.c1 = t2.c1(+) and t2.c2 (+) = 2  union all    select t1.c1, t2.c2    from t1, t2      where t1.c1 = t2.c1(+) and t2.c2 (+) = 3 Q7:     select t1.c1, VW_JF_1.item_2    from t1, (select t2.c1 item_1, t2.c2 item_2                  from t2                  where t2.c2 = 2                union all                  select t2.c1 item_1, t2.c2 item_2                  from t2                                                                                                    where t2.c2 = 3) VW_JF_1       where t1.c1 = VW_JF_1.item_1(+)                                                                  However, the right side of an outer join can be factorized. For example, join factorization can transform Q8 to Q9 by factorizing t2, which is the right table of an outer join. Q8:    select t1.c2, t2.c2    from t1, t2      where t1.c1 = t2.c1 (+) and t1.c1 = 1 union all    select t1.c2, t2.c2    from t1, t2    where t1.c1 = t2.c1(+) and t1.c1 = 2 Q9:   select VW_JF_1.item_2, t2.c2   from t2,             (select t1.c1 item_1, t1.c2 item_2            from t1            where t1.c1 = 1           union all            select t1.c1 item_1, t1.c2 item_2            from t1            where t1.c1 = 2) VW_JF_1   where VW_JF_1.item_1 = t2.c1(+) All of the examples in this blog show factorizing a single table from two branches. This is just for ease of illustration. Join factorization can factorize multiple tables and from more than two UNION ALL branches.  SummaryJoin factorization is a cost-based transformation. It can factorize common computations from branches in a UNION ALL query which can lead to huge performance improvement. 

  Read the article

• In JPA 2, using a CriteriaQuery, how to count results

  - by seanizer

  I am rather new to JPA 2 and it's CriteriaBuilder / CriteriaQuery API: http://java.sun.com/javaee/6/docs/api/javax/persistence/criteria/CriteriaQuery.html http://java.sun.com/javaee/6/docs/tutorial/doc/gjivm.html I would like to count the results of a CriteriaQuery without actually retrieving them. Is that possible, I did not find any such method, the only way would be to do this: CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery<MyEntity> cq = cb .createQuery(MyEntityclass); // initialize predicates here return entityManager.createQuery(cq).getResultList().size(); And that can't be the proper way to do it... Is there a solution?

  Read the article

• zf2 \Zend\Db\Sql\Sql using predicate in where condition

  - by RomanKonz

  i really don't get the point how to use predicates in zend framework 2. this is what i got: $sql->select() ->columns(array('GroupedColum' ,'minValue' => new Expression('min(ValueColumn)'))) ->from('ValueTable') ->group('GroupedColum') ->order('minValue') ->order('GroupedColum') ->limit(10); this is working fine now i want to apply somethin like that: $predicate = new Zend\Db\Sql\Predicate\Predicate(); $sql->where($predicate->greaterThan('filterColumn','20); this is what i tried, it throws no error, but it is not working :-( This is what i expect as SQL: select GroupedColum , min(ValueColumn) as minValue from ValueTable where filterColumn > 20 group by GroupedColum order by minValue GroupedColum limit 10;

  Read the article

• Using local classes with STL algorithms

  - by David Rodríguez - dribeas

  I have always wondered why you cannot use locally defined classes as predicates to STL algorithms. In the question: Approaching STL algorithms, lambda, local classes and other approaches, BubbaT mentions says that 'Since the C++ standard forbids local types to be used as arguments' Example code: int main() { int array[] = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }; std::vector<int> v( array, array+10 ); struct pair : public std::unary_function<int,bool> { bool operator()( int x ) { return !( x % 2 ); } }; std::remove_if( v.begin(), v.end(), pair() ); // error } Does anyone know where in the standard is the restriction? What is the rationale for disallowing local types?

  Read the article

• Lambda Expressions and Stored Procedures

  - by Jason Summers

  Hi Everyone. I'm trying to mimic the LINQ Where extension method for my ADO.NET DAL methods. Bascially, my aim is to have a single method that I can call. Such as: Product p = Dal.GetProduct(x => x.ProductId == 32); Product p2 = Dal.GetProduct(x => x.ProductName.Contains("Soap")); I then want to dissect those Predicates and send the filter options to parameters in an ADO.NET Stored Procedure call. Any comments greatly appreciated.

  Read the article

• A Question About the Expressive Power of Higher-Order Logical Reasoning Formalisms.

  - by Bubba88

  Hi! I do not really know if this is scientifically proven, but I've read in a book (It was a relatively modern AI book by Peter Norvig) that secon-order logical programming could be more expressive than existing first-order languages. The question is: Is it statistically/symbolically proven that higher-order predicate logics exceed first-order predicates in their expressive power? Or they just bring the modularity/convenience/maintainability to your knowledge bases? Additionally: If there is some kind of firm direction in which I could go seeking more expressive power than I have (I mean exactly the descriptive potential of the symbols I write in given semantics/syntax) - then I would be glad to hear just almost everything :) Thank you.

  Read the article

• Problem with ranking of search results in SharePoint 2007 if using the CONTAINS predicate

  - by mythicdawn

  While writing a front-end for the SharePoint Search web service for work, I did some quick testing with the MOSS Search Tool to make sure things were working right under the hood. What I found was that queries composed only of CONTAINS predicates (FREETEXT ones were fine) would have a rank of 1000 for any results that were returned. According to the documentation (http://msdn.microsoft.com/en-us/library/ms544086.aspx): "If the query returns a document because a non–full-text predicate evaluates to TRUE for that document, the rank value is calculated as 1000." Given that the behaviour I am seeing seems to contradict the documentation, is it the case that all queries that use only the CONTAINS predicate will produce ranking like this?

  Read the article

• [Cocoa] Temporarily disabled NSArrayController filterPredicate, or consult ManagedObjectContext?

  - by ndg

  I have an NSArrayController which is bound to a class in my Managed Object Context. During runtime the NSArrayController can have a number of different filter predicates applied. At certain intervals, I want to iterate through my NSArrayController's contents regardless of the filter predicate applied to it. To do this, I set the filterPredicate to nil and then reinstate it after having iterated through my array. This seems to work, but I'm wondering if it's best practice? Should I instead be polling my Managed Object Context manually? NSPredicate *predicate = nil; predicate = [myArrayController filterPredicate]; [myArrayController setFilterPredicate:nil]; for(MyManagedObject *object in [myArrayController arrangedObjects]) { // ... } [myArrayController setFilterPredicate:predicate];

  Read the article

• NSPredicateEditorRowTemplate for date comparison

  - by Dave DeLong

  I'm building an NSPredicateEditor, and I want the ability to do advanced date comparison. I realize that I can build an NSPredicateEditorRowTemplate with a rightExpressionType of NSDateAttributeType, but the predicates I want to build need to be much more advanced than that. For example, I need to basic comparison like: dateKeypath < aDate dateKeypath <= aDate dateKeypath = aDate dateKeypath != aDate dateKeypath > aDate dateKeypath >= aDate These basic comparisons are quite easy to achieve, and I have these working. However, I also need to do comparisons like: dateKeypath isInTheLast n days (or weeks, months, years) dateKeypath isNotInTheLast n days (or weeks, months, years) dateKeypath between aDate and anotherDate How can I achieve these sorts of comparisons? I understand that I'll need to create a custom NSPredicateEditorRowTemplate, but I haven't found any clear documentation on how to achieve something like this. EDIT Bonus points are available for also knowing how to make these comparisons a full date-time (year-month-day-hour-minute-second) comparison (as NSDateAttributeType only has year-month-day granularity).

  Read the article

• Why is the 'if' statement considered evil?

  - by Vadim

  I just came from Simple Design and Testing Conference. In one of the session we were talking about evil keywords in programming languages. Corey Haines, who proposed the subject, was convinced that if statement is absolute evil. His alternative was to create functions with predicates. Can you please explain to me why if is evil. I understand that you can write very ugly code abusing if. But I don't believe that it's that bad.

  Read the article

• E4X in ActionScript

  - by Brian Genisio

  Hey all, Looking at the E4X implementation in ActionScript, it occurs to me that they had to figure out how to do three things that I am not sure can be done within ActionScript regularly: Properties/Getters prefixed with @: var myAttribute = xmlPerson.@name; Nameless functions for filtering: xmlData.person.(/* predicate */) lambda syntax for predicates: xmlData.person.(@name == "Brian") So here is my question: Are these just one-off capabilities (much like Vector.<>) they put in just for E4X, therefore keeping out of reach for us? Or do we, as ActionScript developers, have access to these features? More specifically, I'd love to get access to the expression tree of that lambda predicate for my own code (not tied to the XML classes in any way). I figured out that this is called the "filter operator"... but I am not sure how to harness it. Not sure I can... since ActionScript does not allow for operator overloading :(

  Read the article

• Temporarily disabled NSArrayController filterPredicate, or consult ManagedObjectContext?

  - by ndg

  I have an NSArrayController which is bound to a class in my Managed Object Context. During runtime the NSArrayController can have a number of different filter predicates applied. At certain intervals, I want to iterate through my NSArrayController's contents regardless of the filter predicate applied to it. To do this, I set the filterPredicate to nil and then reinstate it after having iterated through my array. This seems to work, but I'm wondering if it's best practice? Should I instead be polling my Managed Object Context manually? NSPredicate *predicate = nil; predicate = [myArrayController filterPredicate]; [myArrayController setFilterPredicate:nil]; for(MyManagedObject *object in [myArrayController arrangedObjects]) { // ... } [myArrayController setFilterPredicate:predicate];

  Read the article

• Counting Sublist Elements in Prolog

  - by idea_

  How can I count nested list elements in prolog? I have the following predicates defined, which will count a nested list as one element: length([ ], 0). length([H|T],N) :- length(T,M), N is M+1. Usage: ?- length([a,b,c],Out). Out = 3 This works, but I would like to count nested elements as well i.e. length([a,b,[c,d,e],f],Output). ?- length([a,b,[c,d,e],f],Output). Output = 6

  Read the article

• Getting unique items from NSMutableArray

  - by Gidogeek

  Hi Guys, I have a question about Objective-C today involving NSMutableArray. Coming from a .net/c# background I'm having some trouble working with these things. Let's say I have an object called "Song" My song has 3 properties: Title Artist Genre I have a NSMutableArray or NSArray wich holds all my Song objects. How would I go about trying to 'query' my array to get a new array with only (Unique) Artists or Genre's. Where as in .net you would write a simple LINQ query with a DISTINCT clause, how would one solve this in Objective-C ? I'm guessing with predicates but am struggling to find a solution. Thanks in advance.

  Read the article

• plld Prolog C++

  - by H.J. Miri

  I have a large Prolog program with lots of predicates. I need to connect to this Prolog code from C++ (VS2008) to obtain certain query results. So I am not trying to embed Prolog in C++ as a logicasl engine, but for my C++ program to connect to my Prolog code, consult (compile) it, obtain query results, and pass them back to C++. Running the following command at the VS2008 Command Prompt generates so many errors: plld -o myprog.exe mycpp.cpp mypl.pl Is there any way I can get my C++ program to consult my Prolog program, by including a command or makefile, etc...? I am aware that if you use VS2008, you are better off not using plld, so I am trying to include everything in one master C++ program, then press F5 to build and compile, and then call Prolog, then C++, and so on... Cheers,

  Read the article

• Composite-like pattern and SRP violation

  - by jimmy_keen

  Recently I've noticed myself implementing pattern similar to the one described below. Starting with interface: public interface IUserProvider { User GetUser(UserData data); } GetUser method's pure job is to somehow return user (that would be an operation speaking in composite terms). There might be many implementations of IUserProvider, which all do the same thing - return user basing on input data. It doesn't really matter, as they are only leaves in composite terms and that's fairly simple. Now, my leaves are used by one own them all composite class, which at the moment follows this implementation: public interface IUserProviderComposite : IUserProvider { void RegisterProvider(Predicate<UserData> predicate, IUserProvider provider); } public class UserProviderComposite : IUserProviderComposite { public User GetUser(SomeUserData data) ... public void RegisterProvider(Predicate<UserData> predicate, IUserProvider provider) ... } Idea behind UserProviderComposite is simple. You register providers, and this class acts as a reusable entry-point. When calling GetUser, it will use whatever registered provider matches predicate for requested user data (if that helps, it stores key-value map of predicates and providers internally). Now, what confuses me is whether RegisterProvider method (brings to mind composite's add operation) should be a part of that class. It kind of expands its responsibilities from providing user to also managing providers collection. As far as my understanding goes, this violates Single Responsibility Principle... or am I wrong here? I thought about extracting register part into separate entity and inject it to the composite. As long as it looks decent on paper (in terms of SRP), it feels bit awkward because: I would be essentially injecting Dictionary (or other key-value map) ...or silly wrapper around it, doing nothing more than adding entires This won't be following composite anymore (as add won't be part of composite) What exactly is the presented pattern called? Composite felt natural to compare it with, but I realize it's not exactly the one however nothing else rings any bells. Which approach would you take - stick with SRP or stick with "composite"/pattern? Or is the design here flawed and given the problem this can be done in a better way?

  Read the article

• What Precalculus knowledge is required before learning Discrete Math Computer Science topics?

  - by Ein Doofus

  Below I've listed the chapters from a Precalculus book as well as the author recommended Computer Science chapters from a Discrete Mathematics book. Although these chapters are from two specific books on these subjects I believe the topics are generally the same between any Precalc or Discrete Math book. What Precalculus topics should one know before starting these Discrete Math Computer Science topics?: Discrete Mathematics CS Chapters 1.1 Propositional Logic 1.2 Propositional Equivalences 1.3 Predicates and Quantifiers 1.4 Nested Quantifiers 1.5 Rules of Inference 1.6 Introduction to Proofs 1.7 Proof Methods and Strategy 2.1 Sets 2.2 Set Operations 2.3 Functions 2.4 Sequences and Summations 3.1 Algorithms 3.2 The Growths of Functions 3.3 Complexity of Algorithms 3.4 The Integers and Division 3.5 Primes and Greatest Common Divisors 3.6 Integers and Algorithms 3.8 Matrices 4.1 Mathematical Induction 4.2 Strong Induction and Well-Ordering 4.3 Recursive Definitions and Structural Induction 4.4 Recursive Algorithms 4.5 Program Correctness 5.1 The Basics of Counting 5.2 The Pigeonhole Principle 5.3 Permutations and Combinations 5.6 Generating Permutations and Combinations 6.1 An Introduction to Discrete Probability 6.4 Expected Value and Variance 7.1 Recurrence Relations 7.3 Divide-and-Conquer Algorithms and Recurrence Relations 7.5 Inclusion-Exclusion 8.1 Relations and Their Properties 8.2 n-ary Relations and Their Applications 8.3 Representing Relations 8.5 Equivalence Relations 9.1 Graphs and Graph Models 9.2 Graph Terminology and Special Types of Graphs 9.3 Representing Graphs and Graph Isomorphism 9.4 Connectivity 9.5 Euler and Hamilton Ptahs 10.1 Introduction to Trees 10.2 Application of Trees 10.3 Tree Traversal 11.1 Boolean Functions 11.2 Representing Boolean Functions 11.3 Logic Gates 11.4 Minimization of Circuits 12.1 Language and Grammars 12.2 Finite-State Machines with Output 12.3 Finite-State Machines with No Output 12.4 Language Recognition 12.5 Turing Machines Precalculus Chapters R.1 The Real-Number System R.2 Integer Exponents, Scientific Notation, and Order of Operations R.3 Addition, Subtraction, and Multiplication of Polynomials R.4 Factoring R.5 Rational Expressions R.6 Radical Notation and Rational Exponents R.7 The Basics of Equation Solving 1.1 Functions, Graphs, Graphers 1.2 Linear Functions, Slope, and Applications 1.3 Modeling: Data Analysis, Curve Fitting, and Linear Regression 1.4 More on Functions 1.5 Symmetry and Transformations 1.6 Variation and Applications 1.7 Distance, Midpoints, and Circles 2.1 Zeros of Linear Functions and Models 2.2 The Complex Numbers 2.3 Zeros of Quadratic Functions and Models 2.4 Analyzing Graphs of Quadratic Functions 2.5 Modeling: Data Analysis, Curve Fitting, and Quadratic Regression 2.6 Zeros and More Equation Solving 2.7 Solving Inequalities 3.1 Polynomial Functions and Modeling 3.2 Polynomial Division; The Remainder and Factor Theorems 3.3 Theorems about Zeros of Polynomial Functions 3.4 Rational Functions 3.5 Polynomial and Rational Inequalities 4.1 Composite and Inverse Functions 4.2 Exponential Functions and Graphs 4.3 Logarithmic Functions and Graphs 4.4 Properties of Logarithmic Functions 4.5 Solving Exponential and Logarithmic Equations 4.6 Applications and Models: Growth and Decay 5.1 Systems of Equations in Two Variables 5.2 System of Equations in Three Variables 5.3 Matrices and Systems of Equations 5.4 Matrix Operations 5.5 Inverses of Matrices 5.6 System of Inequalities and Linear Programming 5.7 Partial Fractions 6.1 The Parabola 6.2 The Circle and Ellipse 6.3 The Hyperbola 6.4 Nonlinear Systems of Equations

  Read the article

• More SQL Smells

  - by Nick Harrison

  Let's continue exploring some of the SQL Smells from Phil's list. He has been putting together. Datatype mis-matches in predicates that rely on implicit conversion.(Plamen Ratchev) This is a great example poking holes in the whole theory of "If it works it's not broken" Queries will this probably will generally work and give the correct response. In fact, without careful analysis, you probably may be completely oblivious that there is even a problem. This subtle little problem will needlessly complicate queries and slow them down regardless of the indexes applied. Consider this example: CREATE TABLE [dbo].[Page](     [PageId] [int] IDENTITY(1,1) NOT NULL,     [Title] [varchar](75) NOT NULL,     [Sequence] [int] NOT NULL,     [ThemeId] [int] NOT NULL,     [CustomCss] [text] NOT NULL,     [CustomScript] [text] NOT NULL,     [PageGroupId] [int] NOT NULL;  CREATE PROCEDURE PageSelectBySequence ( @sequenceMin smallint , @sequenceMax smallint ) AS BEGIN SELECT [PageId] , [Title] , [Sequence] , [ThemeId] , [CustomCss] , [CustomScript] , [PageGroupId] FROM [CMS].[dbo].[Page] WHERE Sequence BETWEEN @sequenceMin AND @SequenceMax END  Note that the Sequence column is defined as int while the sequence parameter is defined as a small int. The problem is that the database may have to do a lot of type conversions to evaluate the query. In some cases, this may even negate the indexes that you have in place. Using Correlated subqueries instead of a join   (Dave_Levy/ Plamen Ratchev) There are two main problems here. The first is a little subjective, since this is a non-standard way of expressing the query, it is harder to understand. The other problem is much more objective and potentially problematic. You are taking much of the control away from the optimizer. Written properly, such a query may well out perform a corresponding query written with traditional joins. More likely than not, performance will degrade. Whenever you assume that you know better than the optimizer, you will most likely be wrong. This is the fundmental problem with any hint. Consider a query like this:  SELECT Page.Title , Page.Sequence , Page.ThemeId , Page.CustomCss , Page.CustomScript , PageEffectParams.Name , PageEffectParams.Value , ( SELECT EffectName FROM dbo.Effect WHERE EffectId = dbo.PageEffects.EffectId ) AS EffectName FROM Page INNER JOIN PageEffect ON Page.PageId = PageEffects.PageId INNER JOIN PageEffectParam ON PageEffects.PageEffectId = PageEffectParams.PageEffectId  This can and should be written as:  SELECT Page.Title , Page.Sequence , Page.ThemeId , Page.CustomCss , Page.CustomScript , PageEffectParams.Name , PageEffectParams.Value , EffectName FROM Page INNER JOIN PageEffect ON Page.PageId = PageEffects.PageId INNER JOIN PageEffectParam ON PageEffects.PageEffectId = PageEffectParams.PageEffectId INNER JOIN dbo.Effect ON dbo.Effects.EffectId = dbo.PageEffects.EffectId  The correlated query may just as easily show up in the where clause. It's not a good idea in the select clause or the where clause. Few or No comments. This one is a bit more complicated and controversial. All comments are not created equal. Some comments are helpful and need to be included. Other comments are not necessary and may indicate a problem. I tend to follow the rule of thumb that comments that explain why are good. Comments that explain how are bad. Many people may be shocked to hear the idea of a bad comment, but hear me out. If a comment is needed to explain what is going on or how it works, the logic is too complex and needs to be simplified. Comments that explain why are good. Comments may explain why the sql is needed are good. Comments that explain where the sql is used are good. Comments that explain how tables are related should not be needed if the sql is well written. If they are needed, you need to consider reworking the sql or simplify your data model. Use of functions in a WHERE clause. (Anil Das) Calling a function in the where clause will often negate the indexing strategy. The function will be called for every record considered. This will often a force a full table scan on the tables affected. Calling a function will not guarantee that there is a full table scan, but there is a good chance that it will. If you find that you often need to write queries using a particular function, you may need to add a column to the table that has the function already applied.

  Read the article

• SQL SERVER – Puzzle #1 – Querying Pattern Ranges and Wild Cards

  - by Pinal Dave

  Note: Read at the end of the blog post how you can get five Joes 2 Pros Book #1 and a surprise gift. I have been blogging for almost 7 years and every other day I receive questions about Querying Pattern Ranges. The most common way to solve the problem is to use Wild Cards. However, not everyone knows how to use wild card properly. SQL Queries 2012 Joes 2 Pros Volume 1 – The SQL Queries 2012 Hands-On Tutorial for Beginners Book On Amazon | Book On Flipkart Learn SQL Server get all the five parts combo kit Kit on Amazon | Kit on Flipkart Many people know wildcards are great for finding patterns in character data. There are also some special sequences with wildcards that can give you even more power. This series from SQL Queries 2012 Joes 2 Pros® Volume 1 will show you some of these cool tricks. All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the SQL 2012 series Volume 1 in the file SQLQueries2012Vol1Chapter2.2Setup.sql. If you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs” Querying Pattern Ranges The % wildcard character represents any number of characters of any length. Let’s find all first names that end in the letter ‘A’. By using the percentage ‘%’ sign with the letter ‘A’, we achieve this goal using the code sample below: SELECT * FROM Employee WHERE FirstName LIKE '%A' To find all FirstName values beginning with the letters ‘A’ or ‘B’ we can use two predicates in our WHERE clause, by separating them with the OR statement. Finding names beginning with an ‘A’ or ‘B’ is easy and this works fine until we want a larger range of letters as in the example below for ‘A’ thru ‘K’: SELECT * FROM Employee WHERE FirstName LIKE 'A%' OR FirstName LIKE 'B%' OR FirstName LIKE 'C%' OR FirstName LIKE 'D%' OR FirstName LIKE 'E%' OR FirstName LIKE 'F%' OR FirstName LIKE 'G%' OR FirstName LIKE 'H%' OR FirstName LIKE 'I%' OR FirstName LIKE 'J%' OR FirstName LIKE 'K%' The previous query does find FirstName values beginning with the letters ‘A’ thru ‘K’. However, when a query requires a large range of letters, the LIKE operator has an even better option. Since the first letter of the FirstName field can be ‘A’, ‘B’, ‘C’, ‘D’, ‘E’, ‘F’, ‘G’, ‘H’, ‘I’, ‘J’ or ‘K’, simply list all these choices inside a set of square brackets followed by the ‘%’ wildcard, as in the example below: SELECT * FROM Employee WHERE FirstName LIKE '[ABCDEFGHIJK]%' A more elegant example of this technique recognizes that all these letters are in a continuous range, so we really only need to list the first and last letter of the range inside the square brackets, followed by the ‘%’ wildcard allowing for any number of characters after the first letter in the range. Note: A predicate that uses a range will not work with the ‘=’ operator (equals sign). It will neither raise an error, nor produce a result set. --Bad query (will not error or return any records) SELECT * FROM Employee WHERE FirstName = '[A-K]%' Question: You want to find all first names that start with the letters A-M in your Customer table and end with the letter Z. Which SQL code would you use? a. SELECT * FROM Customer WHERE FirstName LIKE 'm%z' b. SELECT * FROM Customer WHERE FirstName LIKE 'a-m%z' c. SELECT * FROM Customer WHERE FirstName LIKE 'a-m%z' d. SELECT * FROM Customer WHERE FirstName LIKE '[a-m]%z' e. SELECT * FROM Customer WHERE FirstName LIKE '[a-m]z%' f. SELECT * FROM Customer WHERE FirstName LIKE '[a-m]%z' g. SELECT * FROM Customer WHERE FirstName LIKE '[a-m]z%' Contest Leave a valid answer before June 18, 2013 in the comment section. 5 winners will be selected from all the valid answers and will receive Joes 2 Pros Book #1. 1 Lucky person will get a surprise gift from Joes 2 Pros. The contest is open for all the countries where Amazon ships the book (USA, UK, Canada, India and many others). Special Note: Read all the options before you provide valid answer as there is a small trick hidden in answers. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: Joes 2 Pros, PostADay, SQL, SQL Authority, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• When row estimation goes wrong

  - by Dave Ballantyne

  Whilst working at a client site, I hit upon one of those issues that you are not sure if that this is something entirely new or a bug or a gap in your knowledge. The client had a large query that needed optimizing.  The query itself looked pretty good, no udfs, UNION ALL were used rather than UNION, most of the predicates were sargable other than one or two minor ones.  There were a few extra joins that could be eradicated and having fixed up the query I then started to dive into the plan. I could see all manor of spills in the hash joins and the sort operations,  these are caused when SQL Server has not reserved enough memory and has to write to tempdb.  A VERY expensive operation that is generally avoidable.  These, however, are a symptom of a bad row estimation somewhere else, and when that bad estimation is combined with other estimation errors, chaos can ensue. Working my way back down the plan, I found the cause, and the more I thought about it the more i came convinced that the optimizer could be making a much more intelligent choice. First step is to reproduce and I was able to simplify the query down a single join between two tables, Product and ProductStatus,  from a business point of view, quite fundamental, find the status of particular products to show if ‘active’ ,’inactive’ or whatever. The query itself couldn’t be any simpler The estimated plan looked like this: Ignore the “!” warning which is a missing index, but notice that Products has 27,984 rows and the join outputs 14,000. The actual plan shows how bad that estimation of 14,000 is : So every row in Products has a corresponding row in ProductStatus.  This is unsurprising, in fact it is guaranteed,  there is a trusted FK relationship between the two columns.  There is no way that the actual output of the join can be different from the input. The optimizer is already partly aware of the foreign key meta data, and that can be seen in the simplifiction stage. If we drop the Description column from the query: the join to ProductStatus is optimized out. It serves no purpose to the query, there is no data required from the table and the optimizer knows that the FK will guarantee that a matching row will exist so it has been removed. Surely the same should be applied to the row estimations in the initial example, right ?  If you think so, please upvote this connect item. So what are our options in fixing this error ? Simply changing the join to a left join will cause the optimizer to think that we could allow the rows not to exist. or a subselect would also work However, this is a client site, Im not able to change each and every query where this join takes place but there is a more global switch that will fix this error,  TraceFlag 2301. This is described as, perhaps loosely, “Enable advanced decision support optimizations”. We can test this on the original query in isolation by using the “QueryTraceOn” option and lo and behold our estimated plan now has the ‘correct’ estimation. Many thanks goes to Paul White (b|t) for his help and keeping me sane through this

  Read the article

• search all paths and the shortest path for a graph - Prolog

  - by prologian

  Hi , I have a problem in my code with turbo prolog wich search all paths and the shortest path for a graph between 2 nodes the problem that i have is to test if the node is on the list or not exactly in the clause of member and this is my code : /* 1 ---- b ---- 3 --- | --- --- | ----- a |5 d --- | ----- --- | --- 2 --- | --- 4 -- c -- for example we have for b--->c ([b,c],5) , ([b,a,c],3) and ([b,d,c],7) : possible paths. ([b,a,c],3) : the shortest path. */ DOMAINS list=Symbol * PREDICATES distance(Symbol,Symbol) path1(Symbol,Symbol,list,integer) path(Symbol,Symbol,list,list,integer) distance(Symbol,list,integer) member(Symbol,list) shortest(Symbol,Symbol,list,integer) CLAUSES distance(a,b,1). distance(a,c,2). distance(b,d,3). distance(c,d,4). distance(b,c,5). distance(b,a,1). distance(c,a,2). distance(d,b,3). distance(d,c,4). distance(c,b,5). member(X, [Y|T]) :- X = Y; member(X, T). absent(X,L) :-member(X, L),!,fail. absent(_,_). /*find all paths*/ path1(X, Y, L, C):- path(X, Y, L, I, C). path(X, X, [X], I, C) :- absent(X, I). path(X, Y, [X|R], I, C) :- distance(X, Z, A) , absent(Z, I), path(Z, Y, R, [X|I] ,C1) , C=C1+A . /*to find the shortest path*/ shortest(X, Y, L, C):-path(X, Y, L, C),path(X, Y, L1, C1),C<C1.

  Read the article

• Fluent interface design and code smell

  - by Jiho Han

  public class StepClause { public NamedStepClause Action1() {} public NamedStepClause Action2() {} } public class NamedStepClause : StepClause { public StepClause Step(string name) {} } Basically, I want to be able to do something like this: var workflow = new Workflow().Configure() .Action1() .Step("abc").Action2() .Action2() .Step("def").Action1(); So, some "steps" are named and some are not. The thing I do not like is that the StepClause has knowledge of its derived class NamedStepClause. I tried a couple of things to make this sit better with me. I tried to move things out to interfaces but then the problem just moved from the concrete to the interfaces - INamedStepClause still need to derive from IStepClause and IStepClause needs to return INamedStepClause to be able to call Step(). I could also make Step() part of a completely separate type. Then we do not have this problem and we'd have: var workflow = new Workflow().Configure() .Step().Action1() .Step("abc").Action2() .Step().Action2() .Step("def").Action1(); Which is ok but I'd like to make the step-naming optional if possible. I found this other post on SO here which looks interesting and promising. What are your opinions? I'd think the original solution is completely unacceptable or is it? By the way, those action methods will take predicates and functors and I don't think I want to take an additional parameter for naming the step there. The point of it all is, for me, is to only define these action methods in one place and one place only. So the solutions from the referenced link using generics and extension methods seem to be the best approaches so far.

  Read the article

• flushing database cache in SWI-Prolog

  - by JPro

  We are using swi-prolog to run our testcases. Whenever the test starts, I am opening the connection to MYSQL database and storing the Name of the Test hat is being done and then closing the DB. These tests run for about 2 days continuously. After the tests are done, the results basically gets stored in folder in the server. There is a predicate in another prolog file that is called to update the results to the MYSQL database. The code is simple, I use odbc library and just call odbc_* predicates to connect and update the mysql by issuing direct queries. The actual problem is : If I try to call the Predicate from the same Prolog window, where the test just got completed, I get an error as updating to the DB server. Although I do not get any error in the connection. If I close the session of that prolog with halt and closing all the open prolog windows , then open an other complete new instance of Prolog and run the predicate the update goes well. I have a feeling that there is some connection reference to the MySQL DB in Prolog database. Is there any way to clear the database in prolog so that I can run the same predicate without closing any existing prolog windows? Any ideas appreciated. Thanks.

  Read the article

< Previous Page | 1 2 3 4 5  | Next Page >