February 27, 2021 at 8:18 am
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)
February 27, 2021 at 9:49 am
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]
February 27, 2021 at 10:56 am
Okay Thank You.
February 27, 2021 at 5:47 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
February 27, 2021 at 6:33 pm
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
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply