[dba-SQLServer] Locking Issue

Mark,

My first inclination is to suggest a redesign of the Master table. I
can't imagine how assigning each stock its own column could provide
enough of a performance improvement over just creating a single
properly indexed stock column to identify the stock for the current
row. Then, you would only need to append your individual stock
tables to this single master table. If you need the flattened table
for reporting, this would then be done by running a pivot on the
master table to create a reporting table. I use this approach in one
of my databases.

Ignoring this, I have to assume that the temp table for each stock is
excessively large. Rather than do a whole temp table in a single
update, I would break up the temp tables into smaller updates 
(perhaps using a criteria on time to divide updates into smaller
pieces) to reduce the number of locks that would be needed for each
update.

Francis R Harvey III
WB 303, (301)294-3952
harveyf1 at westat.com


> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com 
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf 
> Of Mark Rider
> Sent: Tuesday, July 06, 2004 11:05 AM
> To: dba-sqlserver at databaseadvisors.com
> Subject: RE: [dba-SQLServer] Locking Issue
> 
> 
> The Update is being done in a SQL statement from VB.Net.  The basic
> procedure is as follows:
> 
> 1) Import the raw Stock data from a text file (DTS Import)
> 2) Loop through the raw data and create a temp table for each Stock,
> including Time, Stock Name and Price
> 3) Create a Master table with a column for each stock and one 
> for the Time
> 4) Update the Master Table to hold the Price for each Time 
> for each Stock
> 
> This gives me a table that I can then manipulate as needed to 
> get the values
> for any given Stock and Time.  All tables are created from 
> within the VB.NET
> program, so I am pretty sure it is not a permissions issue.  
> The actual
> update statement is:
> 
> UPDATE TempMaster  SET
> MarketArray(i) =  tmpi.AveragePrice FROM tmpi , TempMaster
> WHERE tmpi.quoteDate = TempMaster.quoteDate
> 
> The variable "i" is set in the program, and the necessary 
> punctuation has
> been removed for ease of reading. quoteDate is a smalldatetime in all
> tables. MarketArray(i) is the Stock Symbol - varchar(5). 
> AveragePrice id a
> decimal.
> 
> This same program runs fine when I point to the original 
> database - it is
> only on the 'new' DB Server that it has problems.
> 
> Thanks for the help!
<snip>