October 23, 2007 at 5:33 am
Hi,
I have got situation like that presently my huge table contains around 54 millions records and on daily basis it is increasing by around 75k/day.
I have got sever performance issue on this table could anyone guide me on this.
I cant uses partinied table at the moment.
Cheers
Nachi
October 23, 2007 at 6:40 am
What problems specifically are you worried about or experiencing?
54 million rows that are composed of one integer column just isn't going to be an issue. Are you experiecing fragementation of the indexes, excessive I/O on the reads, are the inserts slowing down... Any information to help suggest better approaches would be useful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 23, 2007 at 6:44 am
Thanks for reply.
I my table structure is like :
ID int
Date smalldatetime
Value decimal(18,10)
I have got 2 serious problems they are as follows.
For every insert it will taking more time and for simple select statment with date range it is taking more then 3 mints.
I look forward to hera from you soon
Thanks
Nachi
October 23, 2007 at 6:55 am
What kind of indexes do you have on the table?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 23, 2007 at 6:59 am
Clustered Index
October 23, 2007 at 9:01 am
That's good.
On which columns? Is it the PK? If not the PK, is it unique? What does the query plan that is taking 3 minutes look like and how much data does that query return?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 23, 2007 at 9:18 am
There are FK index of Id,Date and Value is included column as non-clustered index.
and if we querry any date range it taking that much of time.
October 23, 2007 at 10:35 am
I set up a sample using these scripts:
CREATE TABLE BigTable
(
ID INT,
Date SMALLDATETIME,
[Value] decimal(18,10)
)
WITH x AS (SELECT 1000001 AS Id
,GETDATE() AS MyDate
,42.2 AS MyValue
UNION ALL
SELECT
ID + 1
,MyDate
,MyValue
FROM X
WHERE ID < 54000000
)
INSERT INTO [BigTable] (
[ID],
[Date],
[Value]
) SELECT Id
,MyDate
,MyValue
FROM x
OPTION (MAXRECURSION 0);
The insert times didn't degrade over time, but then again, I'm not getting an fragmentation of my index. Are you guys defragging the index on a regular basis?
Any queries you run against this have to use both the ID and the Date or it's going to do a scan. Meaning if you only refer to the date, which is not the leading edge on your index, you're going to get scans. Across this many rows, that'll be bad. You need to either include the id, change the cluster so that the date is the leading edge, or add another index that has the date in it and includes the value column (because you sure don't want lookups in this table).
I ran some queries. I got scans on the data with your key. I added a key (not a quick task on 54,000,000 rows) and it completely changed the query. Instead of 45 seconds, it returned in just under 8 seconds and that was primarily because the data is so dense because of how I did the inserts.
Hopefully this is helpful.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 24, 2007 at 1:28 am
nachikethm@yahoo.com (10/23/2007)
There are FK index of Id,Date and Value is included column as non-clustered index.and if we querry any date range it taking that much of time.
You didn't answer Grant's questions.
Which column is the clustered index?
What does the execution plan of the slow inserts look like (save it as .sqlplan at attach to the post)
Also.
What other indexes do you have on the table, and how fragmented are they?
How much space is free in the data file?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 24, 2007 at 6:18 am
Even though I didn't have all the information, despite the number of rows, it's not a very big table. I did some experimentation. They shouldn't be hitting any issues assuming they've got decent indexes and they're maintaining them.
So, the problem is they've either got bad indexes or they aren't maintained.
Thanks for the help Gail.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 24, 2007 at 6:26 am
Could also be that there's a trigger, or that the foreign and unique constraint checks are taking the time on the insert. They shouldn't but...
That's the main reason I want to see the exec plan.
Nachi, is there an insert trigger on the table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2007 at 2:00 pm
nachikethm@yahoo.com (10/23/2007)
Hi,I have got situation like that presently my huge table contains around 54 millions records and on daily basis it is increasing by around 75k/day.
I have got sever performance issue on this table could anyone guide me on this.
I cant uses partinied table at the moment.
Cheers
Nachi
Please, post DDL of table and indexes.
In adidtion post typical queries
* Noel
October 31, 2007 at 6:52 am
Nachi I definitely agree with the others in that we can't help you without the table structures (including indexes/keys) and sample queries. Also some information on the spread of the dates would help too.
In general I can say that if the majority of your queries are a date range you should consider having the date field be your clustered index. that will offer maximal performance.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 31, 2007 at 6:55 am
You have 3 columns in the table, and 2 are FOREIGN KEY and the other is VALUE?
Put an index on your date field. For whatever reason you are doing a full scan on the table. You said it was a clustered index. I can only see two possible configurations.
If this is a child table, to a PK of ID,date then my suggestion is this.
Create a clustered index on Date (non-unique) and a non-clustered index on ID,Date for the FK reference.
If that doesn't improve the performance nothing will. FYI, 52M is not that huge in todays world.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply