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.
| Author |
Topic |
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
 Posted - 2008-01-11 : 02:55:13
|
| Hi Experts,We have a table which is really huge. Approximatly 156 million record or little more than it. Fetching rows or doing any operation used to takes lot of time.So we thought of partioning the table. Therefore we moved all old records from the huge table to another database with a different name but same table name. All records older than year 2006 were moved to the new histroy database created.And then we created a view to fetch records from the 2 tables (Old one and the new history table). But surprisingly the performance degraded badly. Stored procedures accessing the huge bulky table were faster than the partioning setup. In older setup it used to return the result sets in 4-5 seconds but in the new setup it takes more than 3 mins.Can anybody help here?Thanks in advanceRegardsSachinDon't sit back because of failure. It will come back to check if you still available. -- Binu |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-01-11 : 05:40:33
|
| Depends on the query being used.If the two tables are on the same drive then pobably you won't do any better than decent indexes.If you have just crated a view and haven't implemented partitioning you'll probably find that it's table scanning.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2008-01-11 : 06:12:32
|
| Hello NR,Thanks for instant reply.The both the tables are on different drives. And the execution plan shows index seek.Please tell me what cud be the possible reason.RegardsSachinDon't sit back because of failure. It will come back to check if you still available. -- Binu |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-01-11 : 06:51:55
|
| It must be doing nore than just an index seek (is it in a nested loop?).Compare the execution lpan from the old and new structures and see what the difference is.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2008-01-14 : 01:55:37
|
| Hi NR,I checked the execution plan and it does go for an index seek.Please help.Thanks in advance.RegardssachinDon't sit back because of failure. It will come back to check if you still available. -- Binu |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-01-14 : 02:24:55
|
| Just a single seek or in a loop?How many rows does it return? Have a look at the disk i/o too.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2008-01-14 : 02:44:12
|
| Just one record. What counters shud I add to measure the I/O?regardssachinDon't sit back because of failure. It will come back to check if you still available. -- Binu |
|
|
|
sachinsamuel
Constraint Violating Yak Guru
383 Posts |
Posted - 2008-01-14 : 02:44:13
|
| Just one record. What counters shud I add to measure the I/O?regardssachinDon't sit back because of failure. It will come back to check if you still available. -- Binu |
|
|
|
|
|
|
Does Partitioning Helps - Always?