- if # TAB row data, or according to the ID dealing with more complex logic, speed is slow, have superior can provide other SQL logic of writing or optimize the wording?
If object_id (' tempdb for.. # temp ') is not null
The begin
Drop table # temp
End
If object_id (' tempdb for.. # TAB ') is not null
The begin
Drop table # TAB
End
The create table # TAB
(
Col varchar (Max),
Col2 varchar (Max),
Col3 varchar (Max),
Col4 varchar (Max)
)
- test of new simulation data
Insert into # TAB (col, col2, col3 col4) values
(' A ', 'A', 'A', 'A'), (' A ', 'A', 'A', 'A'), (' A ', 'A', 'A', 'A'), (' A ', 'A', 'A', 'A'), (' A ', 'A', 'A', 'A'),
(' A ', 'A', 'A', 'A'), (' A ', 'A', 'A', 'A'), (' A ', 'A', 'A', 'A'), (' A ', 'A', 'A', 'A'), (' A ', 'A', 'A', 'A'),
(' A ', 'A', 'A', 'A'), (' A ', 'A', 'A', 'A'), (' A ', 'A', 'A', 'A'), (' A ', 'A', 'A', 'A'), (' A ', 'A', 'A', 'A'),
(' A ', 'A', 'A', 'A'), (' A ', 'A', 'A', 'A'), (' A ', 'A', 'A', 'A'), (' A ', 'A', 'A', 'A'), (' A ', 'A', 'A', 'A'),
(' A ', 'A', 'A', 'A'), (' A ', 'A', 'A', 'A'), (' A ', 'A', 'A', 'A'), (' A ', 'A', 'A', 'A'), (' A ', 'A', 'A', 'A')
-- -- --
The create table # temp
(
GetID uniqueidentifier,
Col varchar (Max),
Col2 varchar (Max),
Col3 varchar (Max),
Col4 varchar (Max)
)
Insert into # temp select newID (), * from # TAB

- -
While the Exists (select GetID from # temp where GetID is not null)
The begin
Declare @ GetID uniqueidentifier='00000000-0000-0000-0000-000000000000
Select the top 1 @ GetID=GetID from # temp where GetID is not null
-- -- -- -- -- - to deal with complex logic by ID here
Update # temp set col2='B' where GetID=@ GetID
-- -- -- -- -- --
Update # temp set GetID=null where GetID=@ GetID
End

- output processing result
Select * from # temp

CodePudding user response:

The thinking of you this is the front desk programmer, is a powerful database can be batch, you now the code or the front desk of that kind of traverse, no efficiency,

You do not directly?
Update # temp set col2='B'
Where GetID is not null

Update # temp set GetID=null
Where GetID is null

CodePudding user response:

Mainly according to the incoming temporary table to handle complex logic, the update # temp set col2='B' where GetID=@ GetID is a simple example, because when the time comes to do logic,
In the select top 1 @ GetID=GetID from # temp where GetID is not null to define a variable, you can always get some columns, logic, and then do or say what are the common methods can deal with complex logic of writing

CodePudding user response:

Go as far as possible index depends on the search condition, the search conditions

CodePudding user response:

What you posted there is nothing can be optimized, or are not much can also optimization,
Key or complex logic, you say you don't have that part of the post,

Do you want to post the complete, the somebody else to help you optimize,