|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 02/11/2001 : 18:50:08
|
Philip writes "This is a two-parter:
If I'm calling a stored procedure from ASP, which of the following procs (entire code not shown) would execute faster, assuming that the fields containing "ID" are primary keys and indexed in the respective tables...
Version 1:
SELECT @currentStepID = currentStepID
FROM users
WHERE userID = @userID
SELECT @currentStepURL = stepURL
FROM steps
WHERE stepID = @currentStepID
Note: @currentStepID is a local variable, @userID is an input parameter, @currentStepURL is an output parameter
Version 2:
SELECT @currentStepURL = s.stepURL
FROM users u
JOIN steps s
ON u.currentStepID = s.stepID
WHERE u.userID = @userID
Note: @userID is an input parameter, @currentStepURL is an output parameter
Part II:
How can I use the SQL Query Analyzer to compare the above? I plugged in both queries and performed an "Execution Plan," but can you provide some more insight on comparing "I/O cost", "CPU cost", "Subtree cost", etc.?
Thanks,
Philip Misiowiec
Webficient LLC" |
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 02/12/2001 : 09:56:22
|
In general terms, the plan that provides the fewest physical reads will perform better, all else being equal. Next would be fewest logical reads. These are the slowest parts of the execution sequence. It's possible to write something that performs better overall with higher readings for either, but it would have fairly small results and tables involved; once the # of rows increases the performance will go down again.
|
 |
|
Stored Procedures: Optimizing Queries
