December 2, 2004 at 12:54 am
Hi,
I am trying to use the results from a select statement to update a table. I want to be able to do it faster just using sql server and not ASP.Right now I am doing it using ASP in the following way:
rsSalessql = "SELECT SeqNumberTemp.ProductNameFull, (CASE WHEN OrderTotal > 0 THEN (SalesTotalTemp.OrderTotal * SeqNumberTemp.margin) ELSE SeqNumberTemp.margin END) AS Total FROM SalesTotalTemp RIGHT OUTER JOIN SeqNumberTemp ON SalesTotalTemp.ProductNameFull2 = SeqNumberTemp.ProductNameFull ORDER BY Total desc"
Set rsSales = SessionData.Execute(rsSalessql)
seq = 0
While not rsSales.EOF
seq = seq + 6
sql = "update Products SET salesseq = '" & seq &"' WHERE Manufacturer + ' ' + ProductName = '" & rsSales("ProductNameFull") & "'"
SessionData.Execute(sql)
rsSales.MoveNext
Wend
Can you please so that I can make this work faster just using sql server.
December 3, 2004 at 5:42 am
This should give your the same result as the code you posted by a single call to a stored proc. Not sure why you are calculating the total column, and you may want to rethink your DB schema by adding a ProductID value to your Products table and utilize that column in your Sequence tables for sales. multi-column named lookups are surely putting a strain on your SQL Server.
-Mike
--- ASP.Net code
Dim Ret As Integer = ExecuteNonQuery( "ProductSalesSeq_Update " & _productName )
--- Stored procedure
CREATE PROCEDURE ProductsSalesSeq_Update (
@ProductFullName varchar(50) = ''
)
AS
-- internal variable declarations
DECLARE @salesSeq int
DECLARE @tempTable Table (
uid INT IDENTITY (1,1) NOT NULL,
ProductNameFull varchar(50),
TotalValue int
)
DECALRE @currRec int
DECLARE @returnval int
-- default variable values
SET @returnval = 1
SET @salesSeq = 0
SET @currRec = 0
-- populate variable table
INSERT INTO @tempTable
( ProductNameFull,
TotalValue
)
SELECT
SeqNumberTemp.ProductNameFull,
(CASE WHEN OrderTotal > 0
THEN (SalesTotalTemp.OrderTotal * SeqNumberTemp.margin)
ELSE SeqNumberTemp.margin END) AS Total
FROM SalesTotalTemp
RIGHT OUTER JOIN SeqNumberTemp
ON SalesTotalTemp.ProductNameFull2 = SeqNumberTemp.ProductNameFull
WHERE
ProductFullName = @ProductFullName
ORDER BY Total DESC
-- update your salesseq value here with the initial value
SELECT @currRec = ISNULL(Min(uid), 0),
@salesSeq = @salesSeq + 6
FROM @tempTable
WHERE uid > @currRec
WHILE @currRec > 0
BEGIN
-- sequence through all records
UPDATE Products
SET
salesseq = @salesSeq
WHERE
Manufacturer + ' ' + ProductName = @ProductFullName
-- get next record
SELECT @currRec = ISNULL(Min(uid), 0),
@salesSeq = @salesSeq + 6
FROM @tempTable
WHERE uid > @currRec
-- update return_value for NonQueryExecute calls
SET @returnval = 0
END
RETURN @returnval
GO
December 3, 2004 at 6:31 am
I already gave you an answer for this question at this adress
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=148386
also read Yoda's answer as he completes mine very well (talking about not inserting presentation data when not necessary).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply