Index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Index

Author

Topic

Kleber
Yak Posting Veteran

67 Posts

Posted - 2004-05-05 : 15:27:11

Hello

I have a query that takes more than 1 minute to be executed. Looking at the Execution Plan, I saw that SQL Server is using the Clustered Index to answer me.

I ask: Is there a way to force SQL Server to use a specified Index? If 'yes', creatin a new nonclustered index would improve my query?

here is the code:
select O.cliente, O.operacao,O.agencia, O.controle, O.titular,S.bra_situacao,
S.bra_tipo, S.tipo
from tbl_saldos as S

inner join tbl_operacoes as O on
S.operacao = O.operacao and
S.controle = O.controle and
S.agencia = O.agencia

where O.val_nome = 'N' and
O.val_cpf_cgc = 'S' and
O.val_dtanascimento = 'S' and
O.val_cep = 'S' and
O.val_logradouro = 'S' and
S.operacao = 'CCO' and
S.bra_situacao = 'A' and
S.bra_tipo = 'C' and
S.rel_primeirotitular = 'S' and
S.rel_segundotitular = 'N'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-05 : 16:18:58

Yes you can have SQL Server use a specified index. It's called index hints. It isn't recommended though.

So do you have indexes on the JOIN conditions (on both the parent and child tables) plus indexes to support the WHERE clause. Please post what indexes you have in both tables.

Tara

Kleber
Yak Posting Veteran

67 Posts

Posted - 2004-05-05 : 16:27:17

Yes, I have clustered indexes on both tables.
They are
Table tbl_saldos
idx_saldo clustered, unique located on PRIMARY operacao,
controle, agencia

Table tbl_operacoes
operacoes clustered, unique located on PRIMARY operacao, controle, agencia, titular

The execution plan shows that SQL Server is using these two indexes, but, we can't wait 1 minute to see these results.

The tables have about 1000000 lines each.

Tks
Kleber

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-05 : 16:31:21

So you don't have any indexes to support the WHERE?

Tara

Kleber
Yak Posting Veteran

67 Posts

Posted - 2004-05-05 : 16:33:28

This one here
sn_nome nonclustered located on PRIMARY operacao, bra_situacao, bra_tipo, rel_primeirotitular, rel_segundotitular, val_nome, val_cpf_cgc, val_dtanascimento, val_cep, val_logradouro

But the SQL Server, didn't use it!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-05 : 16:37:01

On tbl_operacoes, consider putting a composite index over:
val_nome, val_cpf_cgc, val_dtanascimento, val_cep, val_logradouro

The nonclustered index that you mentioned, is that on tbl_saldos?

How many rows is the query pulling back?

Tara

Kleber
Yak Posting Veteran

67 Posts

Posted - 2004-05-05 : 16:45:38

Yes, it is on Tbl_saldos
This query returns 2 rows only.

But changing condition, it returns 1000 lines or more.

I will take a new index as you said.

Thank you
Kleber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources