| Author |
Topic |
|
aspnewbb
Starting Member
31 Posts |
 Posted - 2006-08-28 : 15:57:52
|
| I have a database of a few hundred throusand records each which have a pk, a BARCODE (varchar, can be null, but very very seldom) and some other values like price and descriptionThe website using this database used BARCODES for finding pretty much everything... but as teh site grows it gets slower and slower...I read in numerious post when accessing something like this alot that makes me think I should "make BARCODE" an index for faster retrevail....one problem.. i have no clue where to even start making that field anything...I do have access to the server itself and enterprise manager but I did not create thsi set up.. am just trying to maintain it as best possible |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-28 : 16:15:38
|
| You automatically get an index when you create a primary key constraint. So you've already got an index on BARCODE column. I'm a little confused though as you say that it can have NULLs. Primary keys can not have NULLs, so I'm wondering if you created the PK constraint or you just have one there in theory.Tara Kizer |
 |
|
|
aspnewbb
Starting Member
31 Posts |
Posted - 2006-08-28 : 16:23:56
|
I didnt type that out very well...Its not the PK, the table has a PK, but its not the Barcode though... (because i have like 7 where is the barcode is null or duplicate.)Sorry about that  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-28 : 16:27:59
|
| Then yes you need to add an index to this column. You might want to make it clustered if it's used for practically all queries.We'd need to see DDL and sample queries in order to help you with any performance problems.Tara Kizer |
|
|
|
aspnewbb
Starting Member
31 Posts |
Posted - 2006-08-28 : 16:35:26
|
quote:
Originally posted by tkizer
Then yes you need to add an index to this column. You might want to make it clustered if it's used for practically all queries.
Okay how do I do that tho? (my username tells the truth, Im a newb :)quote:
Originally posted by tkizerWe'd need to see DDL and sample queries in order to help you with any performance problems.Tara Kizer
Most of the queryes are the run of the mill"SELECT * FROM Music WHERE BARCODE=' & whatEverVariable & '"or I type out the columb name if only using 1 or 2 of them |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-28 : 16:44:01
|
| Here's how to created a non-clustered index:CREATE NONCLUSTERED INDEX idx_BARCODE ON Music (BARCODE)You can also create the index via Enterprise Manager, but I prefer Query Analyzer. For EM, right click on the table, go to Manage Indexes...It's possible that your PK is already the clustered one. That might not be what should be clustered though. If almost all of your queries involve BARCODE, then you should make the PK non-clustered and this one clustered.Also, SELECT * is fine for Query Analyzer and ad-hoc type queries. From your front-end application, you should not use * as it's a performance issue.Tara Kizer |
|
|
|
aspnewbb
Starting Member
31 Posts |
Posted - 2006-08-28 : 16:52:53
|
| Thats it?That seems so simple (too simple?).However in EM when I go to Manage INdexes, I dont have a BARCODE in the dropdown (is that cuz it doesnt fit the criteria being a vachar/nullable?)? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-28 : 16:56:37
|
| Try creating the index via Query Analyzer then. Let us know of problems.I'm just not that familiar with how to do it in EM. I just know where it is in there.Tara Kizer |
|
|
|
aspnewbb
Starting Member
31 Posts |
Posted - 2006-08-28 : 16:57:28
|
quote:
Originally posted by tkizer
Try creating the index via Query Analyzer then. Let us know of problems.I'm just not that familiar with how to do it in EM. I just know where it is in there.Tara Kizer
I'll find it, any situation where i should worry about doing this?Thank you very much for yoru help |
|
|
|
aspnewbb
Starting Member
31 Posts |
Posted - 2006-08-28 : 18:08:25
|
| and in what cases do i want to use CLUSTERED over not CLUSTRED? |
|
|
|
aspnewbb
Starting Member
31 Posts |
Posted - 2006-08-28 : 18:09:12
|
| and in what cases do i want to use CLUSTERED over not CLUSTRED? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-08-28 : 18:35:38
|
| I'll have to refer you to SQL Server Books Online to see the differences between the two as I've got to work on production issues. Perhaps someone else will come along to help you out if BOL is not clear.Tara Kizer |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-08-29 : 05:35:32
|
| A clustered index will order the data physically in the table and doing a clustered index seek is the fastest way to access data in sql server. Non clustered indexes are also faster than having no indexes but they have no effect on the physichal storage of data. You should most definetly have barcode as your clustered column...you'll probably see some quite significant performance gains.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
aspnewbb
Starting Member
31 Posts |
Posted - 2006-08-29 : 08:59:22
|
| kool, so should I index ANYTHINg people might search by?or is having too many indexes bad in anyway?its a beefy machine but during peak hours the CPU can get maxed |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-08-29 : 09:12:37
|
| Too many indexes is not a good thing either but it's usually a question of storage and also insert/update/delete frequency. I'd say that if you have a table with few inserts/updates/deletes but many selects it could be allright with many indexes, but tables that are inserted/updated alot should have less indexes. Every insert/delete and some updates will force a change in the index thus creating quite a bit of overhead if you have alot of them. Create indexes that cover 80-90% of your queries at least and focus on the heavy queries first...that's my suggestion.--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
aspnewbb
Starting Member
31 Posts |
Posted - 2006-08-29 : 09:38:20
|
| Sounds like a solid suggestion to me, Thanks a ton |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-01 : 17:10:16
|
| There's an Index Wizard. Whilst I don't think yo should let this rule your life! you might be enlightened by running the Wizard and carefully considering the suggestions it makes.No such think as a free lunch, of course!, but worth checking out the Chef's Recommendations!Kristen |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2006-09-03 : 19:38:52
|
| I'm a bit confused as to why you would definiately want a clustered index here. Clustering is to do with physical ordering of the table. Nothing here suggests that is the case on a barcode (although it might). You certainly want an index for it but whether it's clustered depends on a lot of other things, most of which you haven't revealed. |
|
|
|
|
Indexing