Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old February 22nd, 2005, 09:41 AM
Registered User
Join Date: Feb 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to BinFrog
Default indexing performance question

I was wondering if anyone knew the answer this simple, yet search engine deficient, question.

Suppose I create a table called sw_Person_Update. The table has maybe 250,000 rows including swPersonID, swLastName, swEmailAddress. The table was created with some info I got from a select statement on the contact table which we'll call sw_Contact. That table has well over 2 million rows. Also, sw_Contact has a clustered index on it's primary key, swContactID. sw_Person_Update has no indexes.

My question is: how much will a clustered index be helpful on sw_Person_Update's swPersonID field if I write an update as follows:

Update sw_Contact
set sw_contact.swEmailAddress = sw_Person_Update.swEmailAddress
from sw_Contact
join sw_Person_Update on swPersonID = swContactID

Will the lack of an index on our ad-hoc table create performance issues? I know that if we didn't have an index on the sw_Contact table we would have major time issues. But does the joined table matter? Doesn't it just have to go row by row in sw_Person_Update anyway? I know it's good practice to always have a clustered index on tables, but suppose I were to forget to add one (which recently happened), does anyone know what kind of a performance hit the database would take?

Because in my mind I see the update taking about the same amount of time either way. Because the table being updated has an index, SQL Server can jump right to the record it needs to update while going through the Update statement. But our ad-hoc table still needs to go row by row, regardless of whether or not it is indexed. Do you see what I mean? What does it matter if my ad-hoc table is stored in SQL as "1, 2, 3, 4, 5" or "202, 9991, 107453, 70712"?

Am I way off on this?


Old February 23rd, 2005, 11:47 PM
Friend of Wrox
Join Date: Nov 2003
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc

In cases of updates that have specific criteria or that return result sets not in order, having no index do not pose a problem. For updates that return ordered result sets, extra processing is needed to search the index pages that are not in order. And as far as time is concerned , if it takes a long time it is a performance issue.

You can also run your query with and without a cluster indexing and view the execution plan to measure how your updates did.

Jaime E. Maccou

Similar Threads
Thread Thread Starter Forum Replies Last Post
Simple Indexing Question Pavesa SQL Server 2000 5 April 10th, 2007 07:19 AM
query performance question kBusby Oracle 3 February 14th, 2005 04:42 PM
Indexing XML in relational database - performance cooold XML 1 November 13th, 2003 03:49 AM
performance question.. gbianchi Pro VB 6 6 October 8th, 2003 11:21 AM
Performance Question Kenny Alligood VB Databases Basics 2 August 11th, 2003 08:54 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.