| Author |
Topic |
|
abuhassan
105 Posts |
 Posted - 2006-07-28 : 11:42:36
|
| Hi I have a database that stores all the data in one table that is only the data we are required to store hence it didnt require any other tablesthe table has five colunms Orderid, ordercost, orderdate, ordersystem, orderrefit is used in an web application that seraches for all the rest of the information using an orderid and displays the rest of the details the size of the table currently is about 123000 records that is increasing by 20000 every week.i have an archive of the same data that has 7,666,000 records that are also going to be placed into the same database. my question is is that a good idea of doing this?are there any performance issues that i need to be aware of as currently the application runs quite quick? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-28 : 11:50:45
|
| You aren't talking about a lot of data and as you are only accessing via orderid which I assume is indexed then it shouldn't be a problem.==========================================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. |
 |
|
|
abuhassan
105 Posts |
Posted - 2006-07-28 : 11:58:08
|
| Hi i am accessing the data via the orderref? would that make adiifference also do i need to think about performance etc? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-07-28 : 18:57:07
|
| As long as that column is indexed it should be ok.==========================================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. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-29 : 04:25:13
|
| I assume orderref is UNIQUE? If so it will help if you either use a Primary Key on that column, or a unique index (i.e. rather than a normal non-unique index), as it provides an extra hint to SQL Server that there will only be one matching row in the index.Kristen |
|
|
|
abuhassan
105 Posts |
Posted - 2006-11-28 : 12:50:15
|
How do i index a colunm? Do i need to alter my table?http://www.sqlteam.com/item.asp?ItemID=1015I come accross that article i cant seem to figure it out to i have to create a view?the table i have is shown below i am using the order ref to search by my query is as follows: Select * from Orders where orderref = '0009992992' The table currently looks like this:CREATE TABLE [Orders] ( [Orderid] [int] IDENTITY (1, 1) NOT NULL , [ordercost] [varchar] (15) COLLATE Latin1_General_CI_AS NULL , [orderdate] [varchar] (50) COLLATE Latin1_General_CI_AS NULL , [orderref] [varchar] (15) COLLATE Latin1_General_CI_AS NULL , [ordersystem] [varchar] (520) COLLATE Latin1_General_CI_AS NULL , ) GO I have a 8 million records in total. how can i speed up the query it seems to take a longtime currently?is there something other than indexing that i can use to speed it up? |
|
|
|
Heath
Starting Member
3 Posts |
Posted - 2006-11-28 : 13:49:52
|
| OrderRef needs to be indexed Im alot stronger in mysql it would look like this in mysql maybe that would help.ALTER TABLE `Orders` ADD INDEX ( `orderref` ) This shows out to create an indexhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_64l4.asp |
|
|
|
KenW
Constraint Violating Yak Guru
391 Posts |
Posted - 2006-11-28 : 14:20:56
|
abuhasssan,See SQL Server Books on Line, Topic "CREATE INDEX". No ALTER TABLE required.CREATE UNIQUE INDEX index_name ON table ( column ) Ken |
|
|
|
abuhassan
105 Posts |
Posted - 2006-11-30 : 11:14:47
|
| Thanks.....Why does SQL Not Automatically index inorder to speed things up why does it have to be done manually? Are there resource issues i.e. does the indexing increase disk space usage etc? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-30 : 11:24:43
|
| Yes, potential performance issues with INSERT, UPDATE and DELETE if columns are indexed. For a few columns, particularly if they are small, then it is unlikely to be a problem ... but for lots of columns, containing "wide" data, then it will be more of an issue. Uses more disk space too.Also, there is no point having an index if it won't be used; two possible reasons for that: 1) no query is based on that column and 2) the index is not "selective" enough to be used by SQL Server anyway - for example [evenly distributed] Yes/No values in a column.So its up to the Database Designer to choose what is indexed.Kristen |
|
|
|
abuhassan
105 Posts |
Posted - 2006-11-30 : 12:12:54
|
| Hi Do i need do change the select statement that i currently use:Select * from Orders where orderref = '0009992992'in order for the index to take effect?i have approx 7,666,000 records that will be put in the table and thereafter every week 2000 records will be added to the same table?also what do you mean by "2) the index is not "selective" enough to be used by SQL Server anyway - for example [evenly distributed] Yes/No values in a column" i cant seem to understand? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-30 : 12:50:53
|
| "Do i need do change the select statement that i currently use:Select * from Orders where orderref = '0009992992'in order for the index to take effect?"No ... assuming that you have an index on the orderref column.SQL Server can show you the "plan" of how it will handle a given query, and you can use that to decide whether the query is optimal enough, or whether you need to make changes such as adding an index, or phrasing the query differently.There is also an Index Wizard which will make suggestions about indexes that you could add (based on the queries that SQL Server is asked to process)"also what do you mean by "2) the index is not "selective" enough"If you have a column where each value occurs very frequently then SQL Server won't use an index even if there is one - because it is slower than just "scanning" through the whole table. For example, if you have 7,666,000 rows and you have a column where half the rows have a value of "Yes" and the other half "No" then SQL Server won't use an index on that column.Kristen |
|
|
|
abuhassan
105 Posts |
Posted - 2006-12-01 : 06:17:51
|
| Thanks for the clarification ... |
|
|
|
abuhassan
105 Posts |
Posted - 2006-12-04 : 10:55:07
|
| can the following be applied to views rather than tablesCREATE UNIQUE INDEX index_name ON table ( column ) so instead of table index a colunm in a view or do i need to do it on both table and the view that is using the table? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-04 : 11:07:55
|
| You can do it on the VIEW only if you like.If you do in on the TABLE and have a VIEW onto that table it will take advantage of any indexes already on the Table.(In enterprise version Indexes on Views can also be used by underlying queries on the Table itself)Kristen |
|
|
|
abuhassan
105 Posts |
Posted - 2006-12-04 : 11:27:25
|
| Thanks |
|
|
|
|
Database Design - Performance SQL Server 2000