How to enhace the query with voluminous data...

Question

How to enhace the query with voluminous data...

vikastripathi

SSC Rookie

Points: 44
More actions
December 24, 2002 at 8:11 am

#63237

HI ,
I am here describing a situation where i am terribly fixed and gripped
To explain in detail
I have ta table named CDR
The COlumn to mention are :
ID
InvoiceNo
InvoiceID
AccountNoToDebitID
AccountNoToCreditID
CallStartTime
CallDuration
CallingNumber
CalledNumber
DebitCallCharge
CreditCallCharge
PRIMARY : ID colum in identity with Clustered Index
index_name IX_CDR nonclustered, unique located on PRIMARY FeedID, ID, AccountNoToDebitID, ServiceID, AccountNoToCreditID, DestinationGatewayID, CallingNumber, CalledNumber,
IX_CDR1
CallStartTime
constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys No foreign keys reference this table.
No views with schema binding reference this table.
NOW THIS TABLE has inputs almost 1.5 lakhs records everyday.
ANd While generationg gollowing two queries it takes a lot of time
how can i optmise ...The selection is based on date range and updation is also on date range..
The above one composite index mnetioned is necessary at same time
The queries are :
select sum(DebitCallCharge) from CDR
where AccountNoToDebitID = @intAccountNoand CallStartTime >= @dtTemp1 --- 'Nov 01 2002'and CallStartTime <= @dtTemp2 ---'Nov 30 2002'
Update CDR Set InvoiceID = @ID ,InvoiceNo = @strInvoiceNo where AccountNoToDebitID = @intAccountNo and CallStartTime >= @dtTemp1
and CallStartTime <= @dtTemp2
This query doesnt use index when the result set is high..
As told you we have an index on callstarttime also..
Please help me out
Performance have badly hit me .I want to optimise without any hindrance to exisitng indexes
thank you
Regards,
Vikas
VT

VT

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

Most likely what you want to do is change your PK index to non clustered. High selectivity, etc. Not worth having the clustered. I'd move the clustered to the StartTime column. You might want to test adding another index to AccountNoToDebitID or perhaps even add this to the clustered index on starttime as a 2nd column if you do this query a lot.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones