September 14, 2005 at 4:13 pm
Hi,
I was wondering if anyone can explain to me how creating views and indexes in SQL Server 2000 works and also how to use it in my php code in order to improve performance. Imight sound stupid by asking this question but i have no clue how to do this :S I know views are used in sql 2000 to prevent the execution of a certian select statement over and over but how would I use it in my code? I already created a view in Entreprise Manager but other than that im clueless.
THanks a lot
September 14, 2005 at 4:29 pm
Hey Shoom,
That's a BIG topic for this little forum box :-).
If your concern is performance, then indexing can help; views propbably not. Views are a method to present the data in the database in an alternate format to make it easier to work with. They mostly do not (broad generalization) speed things up. Technically it's more complicated when you hit stuff like indexed views, but it seems like you are not to that point yet.
So indexing. Indexes basically pre-sort the data in your tables in different orders, storing the sort in a tree. The idea is that if you are looking up a person in a table of people by last name, then the machine can find the person faster if it has the info sorted by last name than not.
Indexes are created "on" columns and sort the data by the column content.
A table can have zero or one Clustered Indexes, which actually determines the physical ordering of the data, and many non-clustered indexes, which the machine can use to locate records by other ordering. (Non-clustered indexes store the location or key of the records in the actual table, sorted in the order of the index.)
Start with Books On Line (BOL) topic Creating an Index...
September 14, 2005 at 4:45 pm
thank you for the quick response.
So regarding the indexes. The tables that get accessed the most on my server, I right click on it, went to All Tasks, Manage INdexes and i added a new index choose the most common fields in that table. So i should just do the same for all the most accessed tables in my database? does it sort automatically or should I restart the server or something? I sound like such a noob speaking like this but I have no cluse about SQL Performance and need to improve the speed of the site asap LOL..
thanks again for your help
September 14, 2005 at 4:57 pm
Well, kinda. Do some reading on this, really. This format will not allow you to get all the information you need to do the job well.
Basically, you want to create indexes on the columns that
a. Are often used to search for data in the table -- either because they are in the WHERE clauses of queries, or in Join criteria
AND
b. Contain content that is not very repetitive. For example, an index on a column that has only two values (yes, no) is worthless because it only divides the records into two large groups. A column worth indexing has to have higher selectivity.
If you don't like BOL, at least read the articles at
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply