Indexing a table with 25 million records..

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)

Indexing a table with 25 million records..

Author

Topic

rejimani
Starting Member

1 Post

Posted - 2009-02-23 : 23:58:13

H i,

I have a table with 25 million records. Sample format is given below.

SELECT TOP 5 Id, Call_Time, Duration, Rate, Charge, Calling_Nop, Called_Nop, Tier
FROM EWSD
----------------------------------------------------------------------------------
463935 2009-01-30 00:28:17.000 10 11.4 1.9 MTN VIS MOB
463936 2009-01-30 00:28:20.000 10 11.4 1.9 MTN VIS MOB
463937 2009-01-30 00:27:07.000 87 11.4 16.53 MTN VIS MOB
463938 2009-01-30 00:28:34.000 1 11.4 0.19 MTN VIS MOB
463939 2009-01-30 00:28:08.000 30 11.4 5.7 ETS VIS MOB
----------------------------------------------------------------------------------

Table have clustered index (primary key) on ID column and non-clustered indexes on Call_Date and Call_Time columns. Aproximately 3-400,000 records are added to this table every day.

I have a query executed from a stored procedure which takes 5 minutes to complete.

SELECT
Call_Date,
Count(ID) AS CallCount,
sum(Duration) AS Duration
FROM
EWSD
WHERE
Call_Time BETWEEN '02/01/2009 00:00:01' AND '02/23/2009 10:10:00'
AND Called_Nop='MTN'
GROUP BY
Call_Date

Explain plan shows it is using the non-clustered index on call_time column.

Any suggestions to improve the performance of this query execution?

Reji Mani

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-24 : 09:17:44

Are you Defragging index since you have such high volume of insert everyday? Also update stats.

net205
Starting Member

3 Posts

Posted - 2009-02-24 : 12:30:40

I also want to know

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-24 : 12:32:25

quote:
Originally posted by net205

I also want to know

what you want to know? I want to know millions of things

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-02-24 : 12:35:04

Is Call_Date and Call_Time two different columns? I dont see them in your table schema.

quote:
non-clustered indexes on Call_Date and Call_Time columns.

Nathan Skerl

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-24 : 12:38:19

quote:
Originally posted by nathans

Is Call_Date and Call_Time two different columns? I dont see them in your table schema.

quote:
non-clustered indexes on Call_Date and Call_Time columns.

Nathan Skerl

Yes they are included in the query.

nathans
Aged Yak Warrior

938 Posts

Posted - 2009-02-24 : 12:49:17

oops, missed the select on top of the sample data.

Hard to say without seeing the execution plan, but it may prove beneficial to create a covering index on (Call_Time, Called_Nop) or perhaps the query is being blocked by the frequent inserts? Have you tried to run using (NOLOCK) hint? How many rows meet the Call_Time filter? How selective is Called_Nop? The best way for you to tune this query is to try many things and evaluate the stats/plan of each one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources