Indexing help for update statement

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)

Indexing help for update statement

Author

Topic

Lopaka
Starting Member

48 Posts

Posted - 2007-10-29 : 18:04:51

I use the following two updata statements to rank data in one table. The problem is the table has 22Mil records and it takes 2 hours to run. I have tried, unsuccessfully, to index the proper columns in the wheres. I am confused about indexing, any help would be appreciated...:)

update a
set a.RANK = (select count(*)
from dbo.TestTable
where [NAME] = a.[NAME] and isnull(BASE, 0) >= isnull(a.BASE, 0))
from dbo.TestTable a

update t
set t.RANK = t.RANK - (select count(*)
from dbo.TestTable x
where x.[NAME] = t.[NAME] and
x.RANK = t.RANK and
x.PK < t.PK)
from dbo.TestTable t inner join
(select [NAME], RANK
from dbo.TestTable
group by [NAME], RANK
having count(*) > 1) m on (t.[NAME] = m.[NAME] and
t.RANK = m.RANK)

Robert R. Barnes

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-29 : 19:32:39

I would think a quick build of your new ranks into a temp table, and then an update might work quicker. I haven't seen anyone else respond so here is at least another idea. for the first query anyway. I am not entirely clear on your criteria, but I think the small resulting #Ranks table would produce a faster update using something like this..hopefully you can use this as a hint and modify your actual query.


select  a.[PK],a.[NAME],count(a.[NAME]) as CT
Into #Ranks
from dbo.TestTable a inner join 
			 (Select PK,[NAME],BASE
			   From dbo.TestTable Where BASE is not null) b

		     on a.[PK] = b.[PK] and
			a.[NAME] = b.[NAME]
      WHERE a.Base >= b.Base
Group by [PK],[NAME]


update a
set a.RANK = r.CT
FROM #RANKS r inner join dbo.TestTable a on r.[PK] = a.[PK] and r.[NAME] = a.[NAME]

Kristen
Test

22859 Posts

Posted - 2007-10-30 : 03:10:16

isnull(BASE, 0) >= isnull(a.BASE, 0)

doesn't help because it prevents use of an index on the BASE column. Can you set all NULL values of BASE to 0 so you don't have to use IsNull?

An index on NAME, RANK, PK, BASE should then help.

I haven't looked at whether there is a better overall method.

Kristen

Lopaka
Starting Member

48 Posts

Posted - 2007-11-01 : 01:44:58

Unfortunately, base is a money field and with money Zero and NULL are two different values...:( I thought of that...

Robert R. Barnes

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-11-01 : 04:48:52

Can you split out the null handling into a seperate WHERE or CASE clause i.e. split the query into 2, one dealing with zeros, and one dealing with nulls, so that both parts can overcome the index restricution of the ISNULL?

Kristen
Test

22859 Posts

Posted - 2007-11-01 : 06:48:58

"with money Zero and NULL are two different values"

OK, so Zero-or-NULL in one can match Zero-or-NULL in the other, they don't need to match zero-with-zero and NULL-with-NULL?

I would try multiple separate queries then and UNION ALL the lot together (I'm guessing that there will be around 6 queries).

Could you add a NOT NULL column that stores ZERO when the main column is NULL, otherwise stores the value of the main column?

Kristen

Reporter
Starting Member

48 Posts

Posted - 2007-11-01 : 10:40:44

What is the range of RANK values? Is it 0-10 or 0-22Mil?

Try to index NAME column only and use hint with(index=IX_Name)

It's useful for testing to separate selection statement from update one, I reckon.

Lopaka
Starting Member

48 Posts

Posted - 2007-11-01 : 22:47:24

The rank of the data is base on the name field, 1 to N. The query ran great with only 3M records but after this small subset of data I enclude the rest and boy did it chug...:( That is were i was hoping some here could assist? :)
Here is a small sample of the type of data:

create table #TestTable(
pk int identity(1,1) not null,
[name] char(3) not null,
base money null,
rank int null)

insert into #TestTable([name], base)
selecte 'aaa', 1.0 union
selecte 'aaa', 1.2 union
selecte 'aaa', 1.5 union
selecte 'aaa', 1.1 union
selecte 'aaa', 1.3 union
selecte 'aaa', 1.15 union
selecte 'aaa', 1.22 union
selecte 'aaa', 1.11 union
selecte 'aaa', 1.01 union
selecte 'aaa', null union
selecte 'aaa', null union
selecte 'aaa', null union
selecte 'aaa', 1.33 union
selecte 'aaa', 1.55 union
selecte 'aaa', 1.0 union
selecte 'aaa', 1.0 union
selecte 'bbb', 1.0 union
selecte 'bbb', 1.2 union
selecte 'bbb', 1.5 union
selecte 'bbb', 1.1 union
selecte 'bbb', 1.3 union
selecte 'bbb', 1.15 union
selecte 'bbb', 1.22 union
selecte 'bbb', 1.11 union
selecte 'bbb', 1.01 union
selecte 'bbb', 1.33 union
selecte 'bbb', 1.33 union
selecte 'bbb', 1.55 union
selecte 'bbb', 1.0 union
selecte 'bbb', 1.0 union
selecte 'ccc', null union
selecte 'ccc', null union
selecte 'ccc', 1.33 union
selecte 'ccc', 1.55 union
selecte 'ccc', 1.0 union
selecte 'ccc', 1.0 union
selecte 'ccc', 1.0 union
selecte 'ccc', 1.2 union
selecte 'ccc', 1.5 union
selecte 'ccc', 1.1 union
selecte 'ccc', 1.3 union

Again, Thank you in advance for any assistance... :)
Robert R. Barnes

Robert R. Barnes

Reporter
Starting Member

48 Posts

Posted - 2007-11-02 : 09:27:34

select a.[NAME],a.BASE,RANK = (select count(*)
from #TestTable
where [NAME] = a.[NAME] and isnull(BASE, 0) >= isnull(a.BASE, 0))
from #TestTable a

This is very like - select [name],base from #TestTable order by [name],base

And it's completely like this

select a.pk,a.[NAME],a.BASE,RANK=(select max(pk)-a.pk+1 from #TestTable where [NAME] = a.[NAME])
from #TestTable a

in the context of your example

Lopaka
Starting Member

48 Posts

Posted - 2007-11-02 : 11:48:19

The select statement you provided eliminates duplicates. The duplicates need to be ranked as well...:(
"select a.pk,a.[NAME],a.BASE,RANK=(select max(pk)-a.pk+1 from #TestTable where [NAME] = a.[NAME])
from #TestTable a"

Robert

Robert R. Barnes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources