Question about indexes - 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 2005 Forums

Transact-SQL (2005)

Question about indexes

Author

Topic

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-10-13 : 10:07:25

I wrote a query that will return records that meets certain conditions. The query is touching only two tables however one of these tables have several million rows. When I run this with the 'with(nolock)', it takes almost 3 minutes to run. However if I specify a certain index 'with(index(ix_recorddate_code) nolock)', then i get the results in less than 10 seconds. Here is the code:

declare @StartDate as datetime
declare @EndDate as datetime
declare @percentage as decimal(9,2)

set @StartDate = '9/1/2008'
set @EndDate = '10/1/2008'
set @percentage = .05

select
wa.OfficeNumber,
wa.EntityCode,
wa.TotalDueNow,
sum(case when tAR.ArCode =3 and tar.ARType <> 8 then tar.ARAmount else 0 end) as DiscountAmt,
cast(cast(sum(case when tAR.ArCode = 3 and tar.ARType <> 8 then tar.ARAmount else 0 end) as decimal(9,2))*1.0 /
wa.TotalDueNow as decimal(9,2)) as Percentage
from tAR with( index(ix_recorddate_arcode)nolock)
inner join Ageing_Summary wa with(nolock)
on tAR.OfficeNumber = wa.OfficeNumber
where
ArRecordDate >= @StartDate
and ArRecordDate < @EndDate
and wa.AgeingDate>=dateadd(d,-2,getdate())
group by wa.entitycode, wa.OfficeNumber,wa.TotalDueNow
having
sum(case when tAR.ArCode =3 and tar.ARType <> 8 then tar.ARAmount else 0 end) > 0
and cast(sum(case when tAR.ArCode =3 and tar.ARType <> 8 then tar.ARAmount else 0 end) as decimal(9,2)) /
wa.TotalDueNow > @percentage
order by wa.EntityCode

So my question is when do you specify indexes in your query? I was told that it is not good practice to do this. However, the optimizer seems to not pick up the best index that was available in this huge table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 10:10:11

what all columns does ix_recorddate_code include?

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-10-13 : 10:21:02

Just the recorddate and code columns in ascending order.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 10:28:25

quote:
Originally posted by andros30

Just the recorddate and code columns in ascending order.

what did execution plan showed in the first case (without expplicit specifying index)? was it using clustered index,if any, present on table?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 11:17:02

Add an extra WHERE clause to only calculate those records that match the pivoting

declare @StartDate as datetime,
	@EndDate as datetime,
	@percentage as decimal(9, 2)

select	@StartDate = '9/1/2008',
	@EndDate = '10/1/2008',
	@percentage = 0.05

select		wa.OfficeNumber, 
		wa.EntityCode,
		wa.TotalDueNow,
		sum(tar.ARAmount) as DiscountAmt,
		sum(1.0E * tar.ARAmount) / wa.TotalDueNow as Percentage
from		tAR with (index(ix_recorddate_arcode), nolock)
inner join	Ageing_Summary as wa with(nolock) on tAR.OfficeNumber = wa.OfficeNumber 
where		ArRecordDate >= @StartDate 
		and ArRecordDate < @EndDate 
		and wa.AgeingDate >= dateadd(d, -2, getdate())
		and tAR.ArCode = 3
		and tar.ARType <> 8
group by	wa.entitycode,
		wa.OfficeNumber,
		wa.TotalDueNow
having		sum(tar.ARAmount) > 0
		and sum(1.0E * tar.ARAmount) / wa.TotalDueNow > @percentage
order by	wa.EntityCode

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 11:19:18

Also, which of the two tables has "million" records?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 11:25:04

[code]declare @StartDate as datetime,
@EndDate as datetime,
@percentage as decimal(9, 2)

select @StartDate = '9/1/2008',
@EndDate = '10/1/2008',
@percentage = 0.05

select wa.OfficeNumber,
wa.EntityCode,
wa.TotalDueNow,
sum(tar.ARAmount) as DiscountAmt,
sum(1.0E * tar.ARAmount) / wa.TotalDueNow as Percentage
from (
SELECT OfficeNumber,
SUM(ARAmount) AS ARAmount
FROM tAR with (index(ix_recorddate_arcode), nolock)
WHERE ArRecordDate >= @StartDate
and ArRecordDate < @EndDate
and tAR.ArCode = 3
and tar.ARType <> 8
GROUP BY OfficeNumber
) AS tAR
inner join (
SELECT entitycode,
OfficeNumber,
TotalDueNow
FROM Ageing_Summary with(nolock)
where AgeingDate >= dateadd(d, -2, getdate())
) AS wa on tAR.OfficeNumber = wa.OfficeNumber
group by wa.entitycode,
wa.OfficeNumber,
wa.TotalDueNow
having sum(tar.ARAmount) > 0
and sum(1.0E * tar.ARAmount) / wa.TotalDueNow > @percentage
order by wa.EntityCode[/code]

E 12°55'05.63"
N 56°04'39.26"

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-10-13 : 12:45:41

Thank you both for the replies. Visakh, prior to specifying an index, the execution plan showed that it was using a clustered index. The tAR table is the table that has millions of records because it contains all the financial details that we need to reference.

Peso, the recommendation of adding the where clause actually cut the time down to 4 seconds. And your alternate suggestion also returned the same results. I just need to format the Percentage column to display only 2 digits after the decimal.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 12:49:50

quote:
Originally posted by andros30

Thank you both for the replies. Visakh, prior to specifying an index, the execution plan showed that it was using a clustered index. The tAR table is the table that has millions of records because it contains all the financial details that we need to reference.

Peso, the recommendation of adding the where clause actually cut the time down to 4 seconds. And your alternate suggestion also returned the same results. I just need to format the Percentage column to display only 2 digits after the decimal.

use round() function for that

http://doc.ddart.net/mssql/sql70/ra-rz_20.htm

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-10-13 : 13:08:22

AWESOME!!! Thanks a mil.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 14:21:15

Did my second suggestion also run in 4 seconds?

E 12°55'05.63"
N 56°04'39.26"

andros30
Yak Posting Veteran

80 Posts