| Author |
Topic |
|
fmardani
Constraint Violating Yak Guru
433 Posts |
 Posted - 2004-08-12 : 08:58:18
|
| Hi,I would like to optimize a table which has about 50 Million records and increasing daily.I was thinking of putting indexes in it.I would also like to use the execution plan to see if it can suggest anything. What is the best way to take advantage of the execution plan, etc.Thanks |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-08-12 : 09:12:33
|
| "I was thinking of putting indexes in it."...think a lot more...it's a MAJOR must....both for performance and referential integrity..."execution plan"....remove/minimise index/table scans....they are real bad news.post sample code + ddl of the tables involved in the queries....and we should get you moving faster. |
 |
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2004-08-12 : 10:07:08
|
| what is ddl please? |
|
|
|
n/a
deleted
35 Posts |
Posted - 2004-08-12 : 10:09:06
|
| You might also want to look into horizontally partitioning the table and read up on Federated Databases. These require some programing, but can GREATLY increase speed of the database.HTHPaul |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-12 : 10:09:14
|
| ddl is table schema (structure)Go with the flow & have fun! Else fight the flow :) |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-12 : 10:10:30
|
| ddl is table schema (structure)Go with the flow & have fun! Else fight the flow :) |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-12 : 10:12:17
|
| fmardani, post a create table statement and some inserts to give us some sample data. ddl would be the create table. dml would be the insert statements.data definitiondata modificationWhat is this table used for? Do you mean it doesn't have ANY indexes on it, or you just want to research what would be the optimum indexes.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2004-08-12 : 10:43:45
|
| There are two tables that are joined together.T_Headers --> contains nID (PK) clustered, tDateofData (datetime) non-clusteredThis table is joined to T_Gcus --> contains fields --> nID (PK) clustered, HeaderID non-clustered,10 more fields...T_Headers is joined to T_Gcus via nID, i.eselect h.tDateOfData, g.*from T_GcusA as g inner join T_Headers as h on g.HeaderID = h.nIDwhere h.tDateOfData = '31 Dec 2003' and g.AccountNumber = 'x123456' and g.CusipNumber = 'P5432'what indexes and which fields in which tables do I put them?Currently I have placed indexes in these tables as described on top.Thanks guys |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-08-12 : 10:51:09
|
| speed will be improved with indices that support some or all of the the columns referenced in the joins + where conditions.also....it is good SQL programming practice NOT to use the code format "select * from table"...instead...being particular with a style of "select col1, col2, col3, etc from table"...may enable indices to be used. |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2004-08-12 : 10:58:05
|
| Andrew,Thanks for the input but my main query is where to place the indexes correctly based on my query. And also if it is any use, how can I benefit from execution plan? |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-12 : 11:00:21
|
| So, basically, you are missing indexes on these columns:h.tDateOfData = '31 Dec 2003' andg.AccountNumber = 'x123456' andg.CusipNumber = 'P5432'You need to determine if these are used a lot. If so, and the increased performance in selects would outweigh the decrease in insert, update, and delete actions, then put indexes on them. On a fifty million row table, I would think this query would be incredibly slow without indexes on these columns.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2004-08-12 : 11:08:09
|
| Yes derrik, I already have non-clustered indexes on the three mentioned columns. |
|
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2004-08-12 : 11:23:54
|
| Hi guys,It seems that I can not get the query any quicker than the way it is now (Which is kind of acceptable).So thanks for your inputs ;-) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-13 : 08:12:16
|
| I presume you ahve indexes on g.HeaderID and h.nID (the later is probably a PK anyway)You could try making a composite index of h.tDateOfData + h.nID as SQL might be able to "cover" the use of the T_Headers table from the indexPerhaps horizontally partitioning your data would help.Seeing the Query and the Query Plan would help make suggestions.Kristen |
|
|
|
|
Execution Plan