Having problems with distinct and count

Hi,
Here is the query I am trying to achieve and having syntax issues
Select count(distinct name, number) from results.
To replicate the situation use the following SQL
create table results (name varchar(100), number int)
insert into results values ('test1', 1)
insert into results values ('test1', 1)
insert into results values ('test1', 1)
insert into results values ('test2', 2)
insert into results values ('test2', 2)
insert into results values ('test2', 2)
Basically the return of the query should be 2. I can achieve this by
doing following query
select count(*) from
(select distinct [name], [number] from results) a
but I want to do it one query as the later query is a big hit on the
performance.
On a large sample of data the second query takes around 2 seconds.
Any help would be appreciated.
Thanks
SAIan index on (name, number) might speed it up

hash join / merge join option

can anyone tell me in what situation you should force a hash join or merge
join option in a query?generally speaking, it's usually a best practice to allow sql server's
optimizer to use whichever type of join it deems to be most efficient
instead of forcing a join.|||I know, but sometimes when I tried to force a merge join, it's actually
faster, although I don't know why.
so can you explain why?
"szeying.tan" <szeying.tan@.gmail.com> wrote in message
news:1109792227.328553.193860@.o13g2000cwo.googlegroups.com...
> generally speaking, it's usually a best practice to allow sql server's
> optimizer to use whichever type of join it deems to be most efficient
> instead of forcing a join.
>|||hash joins usually occur when the optimizer cannot find the appropriate
indexes to use to speed up the query.
therefore, when you force an merge join on the query using a particular
column, it will speed up your query. instead of forcing a join hint,
study your query and determine if you can apply any appropriate
indexes. that way, the optimizer will always use the best possible type
of join.
in general, you want your joins to be loop joins, followed by merge
joins and lastly, hash joins.|||I thought loop join is not good but inner join is faster than loop join in
most of the cases.
I think loop join is better than inner join when you call a query which join
tables from a linkedserver
or when a small table joins a big table.
I agree with you that it's bad pratice to force a merge join or hash join.
but as far as merge join or hash join, in case they can't find right
indexes(it happens pretty often actually), I guess they catergorize the
data into small chunks, so it's easier to match the rows between tables.
"szeying.tan" <szeying.tan@.gmail.com> wrote in message
news:1109793218.822840.168320@.z14g2000cwz.googlegroups.com...
> hash joins usually occur when the optimizer cannot find the appropriate
> indexes to use to speed up the query.
> therefore, when you force an merge join on the query using a particular
> column, it will speed up your query. instead of forcing a join hint,
> study your query and determine if you can apply any appropriate
> indexes. that way, the optimizer will always use the best possible type
> of join.
> in general, you want your joins to be loop joins, followed by merge
> joins and lastly, hash joins.
>|||Loop join and inner join are not mutually exclusive. "Inner" controls the
semantics of how you want rows in the two tables to be related; this is
determined by how you actually write your query. If you don't specify the
semantic type of join, the default is INNER JOIN.
"loop|hash|merge" are the algorithms SQL Server can use internally to
process the join. There is no default.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:eBzZ4M2HFHA.3484@.TK2MSFTNGP12.phx.gbl...
>I thought loop join is not good but inner join is faster than loop join in
> most of the cases.
> I think loop join is better than inner join when you call a query which
> join
> tables from a linkedserver
> or when a small table joins a big table.
> I agree with you that it's bad pratice to force a merge join or hash join.
> but as far as merge join or hash join, in case they can't find right
> indexes(it happens pretty often actually), I guess they catergorize the
> data into small chunks, so it's easier to match the rows between tables.
>
> "szeying.tan" <szeying.tan@.gmail.com> wrote in message
> news:1109793218.822840.168320@.z14g2000cwz.googlegroups.com...
>|||Britney
I replied to this in the .server group. Please do not post the same question
independently in multiple groups, so that people do not waste time answering
questions that have already been answered.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uHk$dz1HFHA.3624@.tk2msftngp13.phx.gbl...
> can anyone tell me in what situation you should force a hash join or merge
> join option in a query?
>|||i think you're confusing the type of join (inner, outer, left, right
etc) syntax and the algorithm that sql server uses under the hood to
process a particular join.
sql server optimizer is very smart (99% of the time ;))
if it can't find the right indexes, more often than not. you're not
designing your indexes correctly. if you want some tips on what indexes
should generally be created per your query's design, please let me
know.|||britney,
generally speaking, merge joins are used if both inputs are sorted on the
join predicate (ie, if there are tables 'orders' and 'order_details', joined
on 'orderid' column, and both tables are indexed on that column). if there
are no such indexes, sql server will choose loop join, or decide to sort
(one or both) inputs on fly and use merge join, whichever method it thinks
would perform better for a given situation. iow, it is not a good idea to
force use of either merge or loop join - unless you 'force' merge join by
adding the appropriate indexes.
hth
dean
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:eBzZ4M2HFHA.3484@.TK2MSFTNGP12.phx.gbl...
> I thought loop join is not good but inner join is faster than loop join in
> most of the cases.
> I think loop join is better than inner join when you call a query which
join
> tables from a linkedserver
> or when a small table joins a big table.
> I agree with you that it's bad pratice to force a merge join or hash join.
> but as far as merge join or hash join, in case they can't find right
> indexes(it happens pretty often actually), I guess they catergorize the
> data into small chunks, so it's easier to match the rows between tables.
>
> "szeying.tan" <szeying.tan@.gmail.com> wrote in message
> news:1109793218.822840.168320@.z14g2000cwz.googlegroups.com...
>

Hanging or lockup issue with SQL Server and Terminal Services 2000 on NT 4 Domain Server

Hi all,

Have a situation that my company has never run across before. Client
is running NT4 for the domain server, using terminal services 2000 and
running an application with a SQL Server backend and they are
experiencing locking problems. Once one person gets locked out then
everyone trying to access that tables is also locked out as a result.
It is not specific to a certain User, or module within the
application. It's not a specific time of the day (like when a backup
would be running) and sometimes it's in the middle of the night when
there are actually less Users on the system.

We have 500 customers using this application. Most are using SQL
Server backend, alot of the newer customers are using Terminal
Services, and the number of Users is not accessive as compared to our
other customers. THe only difference is that I do not specifically
know of another client with an NT4 Domain server in the mix.

We actually switched to SQL Server as the recommended back end due to
locking issues using SQLBase because SQL Server is row locking and
SQLBase is page locking. Since making this change we have stopped
seeing the locking for years until now. Is this a SQLServer issue or
issue with the NT Domain server?

Anyone have any ideas?

Thanks
A"ACP" <Akzsurtep@.aol.com> wrote in message
news:f8ce0b90.0409271213.66baade5@.posting.google.c om...
> Hi all,
> Have a situation that my company has never run across before. Client
> is running NT4 for the domain server, using terminal services 2000 and
> running an application with a SQL Server backend and they are
> experiencing locking problems. Once one person gets locked out then
> everyone trying to access that tables is also locked out as a result.
> It is not specific to a certain User, or module within the
> application. It's not a specific time of the day (like when a backup
> would be running) and sometimes it's in the middle of the night when
> there are actually less Users on the system.
> We have 500 customers using this application. Most are using SQL
> Server backend, alot of the newer customers are using Terminal
> Services, and the number of Users is not accessive as compared to our
> other customers. THe only difference is that I do not specifically
> know of another client with an NT4 Domain server in the mix.
> We actually switched to SQL Server as the recommended back end due to
> locking issues using SQLBase because SQL Server is row locking and
> SQLBase is page locking. Since making this change we have stopped
> seeing the locking for years until now. Is this a SQLServer issue or
> issue with the NT Domain server?
> Anyone have any ideas?
> Thanks
> A

It's hard to say what's going on without more information - what version of
MSSQL do you have and which OS is it running on? What is the application and
how does it connect to MSSQL (ODBC, OLE DB)? Also, since it used to work
fine, has anything changed recently such as a new servicepack installation?

As for the locking itself, what do you mean that someone is "locked out"? Do
you mean their connection times out, that they're the victim of a deadlock,
or something else? Have you checked what sp_who2 and sp_lock say about which
connections are blocked and what objects are locked by the blocking
connection(s)? Is there anything unusual in the MSSQL log at the time the
problem happens?

It sounds like one connection locks a table, then doesn't release it
(perhaps an uncommitted transaction - you can check with DBCC OPENTRAN), but
you will have to look into what the connection is actually doing when the
issue occurs.

Simon|||It's definitely a lock on the SQL Server database. The problem is it
is across the board as far as what Users are doing when it happens.
It is grabbing a lock on a table and then locking others out in a
chain reaction. But it's difficult to pinpoint since it's happening
to Users performing different transactions and hitting different
tables in the database. So I'll check using sp_lock and try and
narrow it down a little more. And then I'll look into the option of
using DBCC OPENTRAN if I can narrow it down.

Thanks for the help|||http://www.sommarskog.se/sqlutil/aba_lockinfo.html

This may be of some help to you.

Akzsurtep@.aol.com (ACP) wrote in message news:<f8ce0b90.0409291713.3d7cd6a@.posting.google.com>...
> It's definitely a lock on the SQL Server database. The problem is it
> is across the board as far as what Users are doing when it happens.
> It is grabbing a lock on a table and then locking others out in a
> chain reaction. But it's difficult to pinpoint since it's happening
> to Users performing different transactions and hitting different
> tables in the database. So I'll check using sp_lock and try and
> narrow it down a little more. And then I'll look into the option of
> using DBCC OPENTRAN if I can narrow it down.
> Thanks for the help

Handeling NULL variable

Good afternoon,

I'd like to like how you guys handle a situation like the one I'm going to describe in just a moment. I'm pretty sure I'm not handeling it in the best way, so I'll just try to explain the situation and learn something from the gurus.

Let's imagin I have the following tables:

[Companies]Id [Uniqueidentifier] [PK]Alias [NVarChar]Status [Bit][Campanies_JobTitles]Id [PK]CompanyId [Uniqueidentifier]Alias [NVarChar]Status [Bit]

If I want to know the job titles of company 1 I could easely set the following stored procedure:

SELECT JT.IdAS Id ,JT.AliasAS AliasFROM Companies_JobTitles JTWHERE JT.Status = 1AND JT.CompanyId = @.CompanyIdORDER BY JT.AliasASC;

Now, if the user wants to list the job titles across all the companies I could pass a special value and identify that, as follows:

IF (@.CompanyIdISNOT NULL)BEGIN SELECT JT.IdAS Id ,JT.AliasAS AliasFROM Companies_JobTitles JTWHERE JT.Status = 1AND JT.CompanyId = @.CompanyIdORDER BY JT.AliasASC;ENDELSE BEGIN SELECT JT.IdAS Id ,JT.AliasAS AliasFROM Companies_JobTitles JTWHERE JT.Status = 1ORDER BY JT.AliasASC;END

This works fine. I just find it a bit counter productive to copy paste the query and wrap it into an if statement. So, the bottom line is: how can I do this in a more "professional" way?

Best regards.

Hi,

What I believe, the query is good enough because at certain point you have to check if the company Id exist or not so that if exist you can search it by the id, if not then show all.

Thanks and best regards,

|||

farazsk11:

Hi,

What I believe, the query is good enough because at certain point you have to check if the company Id exist or not so that if exist you can search it by the id, if not then show all.

Thanks and best regards,

I understand what you are saying, although that check was not on the posted code for the sake of simplicity. But, that wouldn't answer the underlying question that is: how can I "reuse" the query so I don't have to repeat it thoughout the procedure? Is there a function that can solve this like ISNULL allows you to handle the values that return as null.

|||

What about this:

SELECTJT.IdAS Id,JT.AliasAS AliasFROM Companies_JobTitles JT
WHERE JT.Status = 1
AND JT.CompanyId =ISNULL(@.CompanyId,JT.CompanyId)ORDER BY JT.Alias

|||

limno:

What about this:

SELECTJT.IdAS Id,JT.AliasAS AliasFROM Companies_JobTitles JT
WHERE JT.Status = 1
AND JT.CompanyId =ISNULL(@.CompanyId,JT.CompanyId)ORDER BY JT.Alias

That was exactly what I was looking for. I didn't know ISNULL could be used in that context.

Thanks a lot limno :)