Better query to get count based on column value

  • I am using SQL server 2008 R2 express edition.

    Out of below 3 method which method is efficient to fetch count  ?

    1.select name,count * from table where name contains 'raj' and P = 1

    select name,count * from table where name contains 'ram' and P = 1

    merge result in code

    vs

    2.select name,count * from table where name contains 'raj' and P = 1

    union all

    select name,count * from table where name contains 'ram' and P = 1

    vs

    3.select name,count * from table where P = 1 group by name

    then sum in datatable (contains name)

     

  • The answer to performance question like this one is often "it depends". For one thing "name contains 'ram'" is not legal SQL syntax, so it depends on what you mean. Do you mean the CONTAINS operator for full-text? Or something else?

    The best to find out is to run a performance test with your actual table and data.

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Okay  Thank You.

  • Most typically:

    select name,count(*) as name_count
    from dbo.table_name
    where name like '%ra[jm]%' and P = 1

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • IT researcher wrote:

    I am using SQL server 2008 R2 express edition.

    Out of below 3 method which method is efficient to fetch count  ?

    1.select name,count * from table where name contains 'raj' and P = 1 select name,count * from table where name contains 'ram' and P = 1 merge result in code

    vs

    2.select name,count * from table where name contains 'raj' and P = 1 union all select name,count * from table where name contains 'ram' and P = 1

    vs

    3.select name,count * from table where P = 1 group by name then sum in datatable (contains name)

    For SQL Server, all 3 of the answers in the original post above are terrible when it comes to SQL Server.  As Erland points out, the CONTAINS keyword is only available if you're using the FULL TEXT SEARCH (FTS for short) option in SQL Server and that doesn't appear to be the case here so, right off the git, none of the answers will actually work in SQL Server.

    I'm not sure where you're getting the questions and answers from but, if it's in a book or some form of online training, you need to get a book that's more SQL Server/T-SQL centric.  If it's on a test or an interview question, you might want to consider a different company because, even if SQL Server did have a CONTAINS keyword outside of the FTS option, none of them have COUNT(*) correctly written, the first two will produce 2 scans of an Index/Heap/Clustered Table, and the 3rd one doesn't even have the same criteria as the others.

    The word CONTAINS infers that the given quoted names could be anywhere in the string and so we'd also need to know what the "name" column actually contains because the equivalent of CONTAINS would be like what Scott posted in the WHERE clause of the code he posted and that has a leading wild card which will make it virtually impossible to do a high performance seek on the code even if there is an index on the "name" column.

    So, to answer your question, the 3rd answer is the closest to being correct for whatever SQL dialect is being use by the people that wrote the question but it's also horribly wrong because neither an extra sum in a data table is required and it also has no filtering criteria.

    Scott's code will work better than all 3 answers but there's probably a much better way even than his code depending on what's actually supposed to be present (first name only, full name, what?) in the name column.

    And, seriously, the code you posted has some serious problems.  If you're trying to learn something, get a better book or find a better website to study from.  If these are questions on a school exam, find a better school.  If these question are on a job interview, get up and leave. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply