Link to home
Start Free TrialLog in
Avatar of BartWestphal
BartWestphal

asked on

Multiple Table Updates With One SQL Statement

I have written some basic SQL that works fine, but I know it isn't as efficient as it should be.  When I run the following, I know that it must go through the table (8 million records) once for each statement.  Is there a better way to write this so it only has to go through the table one time?  Also, if I apply an index to the table, would it be better to apply it before or after doing the updates?  (this table is recreated each night, so I have the option of when to set the indexes)

Thanks,  Bart

UPDATE    Items
SET       Ship_To_CUST_NO = CUST_NO
WHERE     (Ship_To_CUST_NO IS NULL)
GO

UPDATE    Items
SET       Ship_To_CUST_EDP_ID = CUST_EDP_ID
WHERE     (Ship_To_CUST_EDP_ID IS NULL)
Avatar of chapmandew
chapmandew
Flag of United States of America image

UPDATE    Items
SET       Ship_To_CUST_NO = CUST_NO,
Ship_To_CUST_EDP_ID = CUST_EDP_ID
WHERE     (Ship_To_CUST_NO IS NULL)
GO

Avatar of kenshaw
kenshaw

In answer to your question about indexes, there is no one right answer.  It depends on which columns are indexed, the type of index, the nature of your data, the frequency and type of access that this table experiences.  However, the short answer is probably that you should just try indexes before and after to see the difference.  Here is some Microsoft documentation on the topic (2000 but basic principles the same in 2005):

Indexes can be dropped, added, and changed without affecting the database schema or application design. Efficient index design is paramount to achieving good performance. For these reasons, you should not hesitate to experiment with different indexes. The Index Tuning Wizard can be used to analyze your queries and suggest the indexes that should be created. For more information, see Index Tuning Wizard.

The query optimizer in Microsoft® SQL Server" 2000 reliably chooses the most effective index in the majority of cases. The overall index design strategy should provide a good selection of indexes to the query optimizer and trust it to make the right decision. This reduces analysis time and results in good performance over a wide variety of situations.

Do not always equate index usage with good performance, and vice-versa. If using an index always produced the best performance, the job of the query optimizer would be simple. In reality, incorrect choice of indexed retrieval can result in less than optimal performance. Therefore, the task of the query optimizer is to select indexed retrieval only when it will improve performance and to avoid indexed retrieval when it will affect performance.

Recommendations for creating indexes include:

* Write queries that update as many rows as possible in a single statement, rather than using multiple queries to update the same rows. By using only one statement, optimized index maintenance can be exploited.

* Use the Index Tuning Wizard to analyze your queries and make index recommendations. For more information, see Index Tuning Wizard.

* Use integer keys for clustered indexes. Additionally, clustered indexes benefit from being created on unique, nonnull, or IDENTITY columns. For more information, see Using Clustered Indexes.

* Create nonclustered indexes on all columns frequently used in queries. This can maximize the use of covered queries. For more information, see Using Nonclustered Indexes.

* The time taken to physically create an index is largely dependent on the disk subsystem. Important factors to consider are:
        * RAID (redundant array of independent disks) level used to store the database and transaction log files.

        * Number of disks in the disk array (if RAID was used).

        * Size of each data row and the number of rows per page. This determines the number of data pages that must be read from disk to create the index.

        * The columns in the index and the data types used. This determines the number of index pages that have to be written to disk.
* Examine column uniqueness. For more information, see Using Unique Indexes.

* Examine data distribution in indexed columns. Often, a long-running query is caused by indexing a column with few unique values, or by performing a join on such a column. This is a fundamental problem with the data and query, and usually cannot be resolved without identifying this situation. For example, a physical telephone directory sorted alphabetically on last name will not expedite locating a person if all people in the city are named Smith or Jones.
ASKER CERTIFIED SOLUTION
Avatar of Christopher Gordon
Christopher Gordon
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of BartWestphal

ASKER

Lowfatspread and gohord,

I didn't know coalesce, but looked it up and I think it or 'gohord's' isnull solution makes sense for my purposes. I'll give them a try and respond accordingly.