Submit My Question - Recent Questions - Recent Answer

T-SQL select performance

Hi. I have in my databases the follow tables:

Cliente: PK = cdCli.
FoneCli: PK = isnFone. One client can have many fones (home, work, moblile, neighbor, father, etc)
I need in a query, get the clients and yours fones home, work and moblile.
I do this now with the follow command:
select Cliente.cdCli, Cliente.nmCli,
(select nuFone from FoneCli where FoneCli.cdCli = Cliente.cdCli and FoneCli.tpCli = 'H') as FoneHome,
(select nuFone from FoneCli where FoneCli.cdCli = Cliente.cdCli and FoneCli.tpCli = 'W') as FoneWork,
(select nuFone from FoneCli where FoneCli.cdCli = Cliente.cdCli and FoneCli.tpCli = 'M') as FoneMob
from Cliente

BUT THE PERFORMANCE FOR THIS COMAND IS BAD.
HOW CAN I REWRITE THIS COMAND WITH A BETTER PERFORMANCE?

Thanks
Alessandro

[810 byte] By [AlessandroCamara] at [2008-2-12]

«« EXE and parameters ?
»» Project in solution explorer

# 1

If possible, create a unique index on the FoneCli with cdCli and tpCli and this should improve the join performance.

TonyGreen at 2007-9-9 >

top of Msdn Tech,SQL Server,Transact-SQL...

# 2

Is there a way that give this information in a ONE SELECT for all fones?

AlessandroCamara at 2007-9-9 >

# 3

Yes, you can rewrite your query like below:

select c.cdCli, c.nmCli, f2.FoneHome, f2.FoneWork, f2.FoneMob
from Cliente as c
left join (
select f1.cdCli,
min(case f1.tpCli when 'H' then f1.nuFone end) as FoneHome,
min(case f1.tpCli when 'W' then f1.nuFone end) as FoneWork,
min(case f1.tpCli when 'M' then f1.nuFone end) as FoneMob
from FoneCli as f1
group by f1.cdCli
) as f2
on f2.cdCli = c.cdCli;

UmachandarJayachandran-MS at 2007-9-9 >

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech