Best 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 2008 Forums

Transact-SQL (2008)

Best index

Author

Topic

psfaro
Starting Member

49 Posts

Posted - 2011-11-18 : 04:24:10

Hi

I use the folowing query to update at the same time the "Valor" column of this records

Cod_conta VALOR
6 0
612 0
6121 0
61211 0

UPDATE CCTPOC
set value=100
WHERE '61211' LIKE CAST(COD_CONTA AS varchar(10))+ '%'

I've an Index "cod_conta" but this take too long to Update.

What is the best index or other query to update all the records with one update.

Regards

Pedro

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-18 : 04:49:52

Have a look at the execution plan.
You will see that the index can't be used because there is a function around the indexed column in your WHERE clause.

No, you're never too old to Yak'n'Roll if you're too young to die.

psfaro
Starting Member

49 Posts

Posted - 2011-11-18 : 05:25:43

Hi,

Sorry .
How can put here the Execution plan(is graphical) ?

Update command:
UPDATE CCTPOC
set VDB0=1
where ano='2011'
and '62211' LIKE CAST(COD_CONTA AS varchar(10))+ '%'
and TP_CONTA='G'

Index:

CREATE NONCLUSTERED INDEX [idx_conta] ON [dbo].[CCTPOC]
(
[ano] ASC,
[COD_CONTA] ASC,
[TP_CONTA] ASC,
[cod_ctc] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

psfaro
Starting Member

49 Posts

Posted - 2011-11-18 : 05:29:27

Hi Again,

The execution plan shows that index is Used

Table Update (Cost 1%)
Compute Scalat (Cost 0%)
Top (cost 3%)
Index Seek (Cost 95%)

Any turn around for the query to be executed faster ?

Pedro

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-11-18 : 05:33:54

Index Seek is good.

To give more advice on this is not my skill - sorry.

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-11-18 : 05:44:21

Your index will be used for the ano and tp_conta columns.

However because you are CAST ing the COD_CONTA to a type you can't use any index over that column Whats happening is that the index is seeking to the first two columns and then scanning all possibles below.

Your condition:


and '62211' LIKE CAST(COD_CONTA AS varchar(10))+ '%'

Could be decomposed into


AND [COD_CONTA] IN (6, 62, 622, 6221, 62211)

Which definately would be able to seek all the way.

You could write a table valued function that takes a string and then returns all the possible run lengths you need.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

psfaro
Starting Member

49 Posts

Posted - 2011-11-18 : 05:50:16

Hi

I didnt't show that i use a SP to UPdate .
Here is the complete code.

I think the cast is not necessary , ist returns the same result

ALTER PROCEDURE [dbo].[AcumulaGeral]
@ano Varchar(4),
@conta Varchar(20),
@campo varchar(10),
@valor money

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

UPDATE CCTPOC
set VDB0 =CASE WHEN @campo='VDB0' THEN VDB0 + @valor ELSE VDB0 END,
VDB1 =CASE WHEN @campo='VDB1' THEN VDB1 + @valor ELSE VDB1 END,
VDB2 =CASE WHEN @campo='VDB2' THEN VDB2 + @valor ELSE VDB2 END,
VDB3 =CASE WHEN @campo='VDB3' THEN VDB3 + @valor ELSE VDB3 END,
VDB4 =CASE WHEN @campo='VDB4' THEN VDB4 + @valor ELSE VDB4 END,
VDB5 =CASE WHEN @campo='VDB5' THEN VDB5 + @valor ELSE VDB5 END,
VDB6 =CASE WHEN @campo='VDB6' THEN VDB6 + @valor ELSE VDB6 END,
VDB7 =CASE WHEN @campo='VDB7' THEN VDB7 + @valor ELSE VDB7 END,
VDB8 =CASE WHEN @campo='VDB8' THEN VDB8 + @valor ELSE VDB8 END,
VDB9 =CASE WHEN @campo='VDB9' THEN VDB9 + @valor ELSE VDB9 END,
VDB10=CASE WHEN @campo='VDB10' THEN VDB10 + @valor ELSE VDB10 END,
VDB11=CASE WHEN @campo='VDB11' THEN VDB11 + @valor ELSE VDB11 END,
VDB12=CASE WHEN @campo='VDB12' THEN VDB12 + @valor ELSE VDB12 END,
VDB13=CASE WHEN @campo='VDB13' THEN VDB13 + @valor ELSE VDB13 END,
VDB14=CASE WHEN @campo='VDB14' THEN VDB14 + @valor ELSE VDB14 END,
VDB15=CASE WHEN @campo='VDB15' THEN VDB15 + @valor ELSE VDB15 END,
VCR0 =CASE WHEN @campo='VCR0' THEN VCR0 + @valor ELSE VCR0 END,
VCR1 =CASE WHEN @campo='VCR1' THEN VCR1 + @valor ELSE VCR1 END,
VCR2 =CASE WHEN @campo='VCR2' THEN VCR2 + @valor ELSE VCR2 END,
VCR3 =CASE WHEN @campo='VCR3' THEN VCR3 + @valor ELSE VCR3 END,
VCR4 =CASE WHEN @campo='VCR4' THEN VCR4 + @valor ELSE VCR4 END,
VCR5 =CASE WHEN @campo='VCR5' THEN VCR5 + @valor ELSE VCR5 END,
VCR6 =CASE WHEN @campo='VCR6' THEN VCR6 + @valor ELSE VCR6 END,
VCR7 =CASE WHEN @campo='VCR7' THEN VCR7 + @valor ELSE VCR7 END,
VCR8 =CASE WHEN @campo='VCR8' THEN VCR8 + @valor ELSE VCR8 END,
VCR9 =CASE WHEN @campo='VCR9' THEN VCR9 + @valor ELSE VCR9 END,
VCR10=CASE WHEN @campo='VCR10' THEN VCR10 + @valor ELSE VCR10 END,
VCR11=CASE WHEN @campo='VCR11' THEN VCR11 + @valor ELSE VCR11 END,
VCR12=CASE WHEN @campo='VCR12' THEN VCR12 + @valor ELSE VCR12 END,
VCR13=CASE WHEN @campo='VCR13' THEN VCR13 + @valor ELSE VCR13 END,
VCR14=CASE WHEN @campo='VCR14' THEN VCR14 + @valor ELSE VCR14 END,
VCR15=CASE WHEN @campo='VCR15' THEN VCR15 + @valor ELSE VCR15 END

where ano=@ano
and @conta LIKE COD_CONTA+'%'
and TP_CONTA='G'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources