December 20, 2001 at 12:37 am
Hi:
I'm new to SQL/T-SQL. Migrating an Access application. I've scanned/read a few books but haven't found the information I'm looking for.
I have a simple task to pre-process the contents of a table. It looks at ALL records in the table (usually 50 or so records, 200 would probably be a high water mark). The table comes from an outside source and is imported into the database prior to the pre-processing.
Specifically, one part of that task is to update a field (RELATION) based on the value of two other fields (EVALUTION and CHANNEL)
Using ACCESS, the SQL statement used is:
"UPDATE TempNew SET RELATION = iif(EVALUATION=TRUE,'EVALUATION',iif(trim(Channel)='DIRECT','CUSTOMER',iif(trim(Channel)='DISTRIBUTOR','DISTRIBUTOR','UNKNOWN')))"
which translates to:
If EVALUATION = TRUE
RELATION = 'EVALUATION'
If EVALUATION = FALSE then
If CHANNEL = 'DIRECT'
RELATION = 'CUSTOMER
Else
If CHANNEL = 'DISTRIBUTOR'
RELATION = 'DISTRIBUTOR'
Else
RELATION = 'UNKNOWN'
I can come up with a couple of ways to do this in T-SQL, but I'm trying to establish the 'right' way to do it.
For instance, I've tested the following as a stored procedure:
Alter Procedure StoredProcedure1
As
UPDATE TempNew
SET Relation 'EVALUATION'
WHERE Evaluation = 1
UPDATE TempNew
SET Relation = 'CUSTOMER'
WHERE Evaluation = 0 AND CHANNEL='DIRECT'
UPDATE TempNew
SET Relation = CHANNEL
WHERE Evaluation = 0 AND
CHANNEL='DISTRIBUTOR'
UPDATE TempNew
ISNULL(Relation,'UNKNOWN')
return
But, while it works, it seems terribly inefficient. That appears to be four separate queries and therefore four passes through the data. However, no row is returned or updated more than once. But maybe it's not four separate queries/passes. Maybe SQL Server gets the entire statement, evaluates it and processes it in a single pass. Or maybe four separate queries/passes where there's no overlap IS efficient.
I've read that SQL is oriented toward 'set processing' vs 'row processing', but I haven't found an explanation of what the goal of a 'set processing' approach is when writing a SQL statement.
I'm not sure what qualifies as 'efficient' for set processing, what my goal is when attempting to write a SQL UPDATE statement.
Any examples I've seen have been very simplistic and not much help.
If anyone can recommend a good book on the subject, or some other reference, I'd be more than happy to look into that.
I understand that a cursor can be used for 'row processing', but this doesn't seem like a 'row processing' issue. Besides, I can use ADO to handle row processing on a single result set.
Thanks for whatever help you can provide.
JK
December 20, 2001 at 1:13 am
that WILL be treated as four different queries ...
this should do it in one
UPDATE TempNew
SET Relation = CASE Evaluation
WHEN 1 THEN 'EVALUATION'
WHEN 0 THEN
CASE CHANNEL WHEN 'DIRECT' THEN 'CUSTOMER'
WHEN 'DISTRIBUTOR' THEN 'DISTRIBUTOR'
ELSE 'UNKNOWN'
END
END
set based processing would mean that you would leave all the recursions/iterations for the SQL Query engine .. let it do all the work .. in one word try and avoid cursors
December 20, 2001 at 1:41 am
Well, that makes perfect sense! Thank you. I don't know why I missed the application of CASE here. There's a very different approach when using T-SQL than what I've been used to.
Again, thanks.
JK
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply