SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Indexing
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

aspnewbb
Starting Member

31 Posts

Posted - 08/28/2006 :  15:57:52  Show Profile
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 description

The 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


Edited by - aspnewbb on 08/28/2006 15:58:24

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 08/28/2006 :  16:15:38  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

aspnewbb
Starting Member

31 Posts

Posted - 08/28/2006 :  16:23:56  Show Profile
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

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 08/28/2006 :  16:27:59  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

aspnewbb
Starting Member

31 Posts

Posted - 08/28/2006 :  16:35:26  Show Profile
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 tkizer
We'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

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 08/28/2006 :  16:44:01  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

aspnewbb
Starting Member

31 Posts

Posted - 08/28/2006 :  16:52:53  Show Profile
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?)?




Edited by - aspnewbb on 08/28/2006 16:56:36
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 08/28/2006 :  16:56:37  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

aspnewbb
Starting Member

31 Posts

Posted - 08/28/2006 :  16:57:28  Show Profile
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

Go to Top of Page

aspnewbb
Starting Member

31 Posts

Posted - 08/28/2006 :  18:08:25  Show Profile
and in what cases do i want to use CLUSTERED over not CLUSTRED?
Go to Top of Page

aspnewbb
Starting Member

31 Posts

Posted - 08/28/2006 :  18:09:12  Show Profile
and in what cases do i want to use CLUSTERED over not CLUSTRED?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35953 Posts

Posted - 08/28/2006 :  18:35:38  Show Profile  Visit tkizer's Homepage
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
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 08/29/2006 :  05:35:32  Show Profile
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"
Go to Top of Page

aspnewbb
Starting Member

31 Posts

Posted - 08/29/2006 :  08:59:22  Show Profile
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
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 08/29/2006 :  09:12:37  Show Profile
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"
Go to Top of Page

aspnewbb
Starting Member

31 Posts

Posted - 08/29/2006 :  09:38:20  Show Profile
Sounds like a solid suggestion to me, Thanks a ton
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/01/2006 :  17:10:16  Show Profile
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
Go to Top of Page

LoztInSpace
Aged Yak Warrior

938 Posts

Posted - 09/03/2006 :  19:38:52  Show Profile
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000