DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(ID) FROM Table1)
DECLARE @I INT
SET @I = 1
WHILE (@I 0
BEGIN
INSERT INTO Table2 (base, part) VALUES (@word, SUBSTRING(@word,1,@len))
SET @len=@len-1
END
SET @I = @I + 1
END
Can you explain what are you trying to do?
Try defining some index in Table1 and Table2. The index may accelerate the query execution.
@Beatriz Fernandez Reuter
I created index, but not in the field varchar.
I have a list of words ex:
work
take
different
important
...
Query reads words from this list (first column) and creates new forms (second column)
BaseForm CreatedForm
--------------------------
work work
work wor
work wo
work w
take take
take tak
take ta
take t
different different
different differen
different differe
different differ
different diffe
different diff
different di
different d
Try to save data and log files in different disks and rebuild index ,update statistics
This is a nested loop of inserts, so the asymptotic complexity is O(RowCount * len). If RowCount is large and the average len is large it will be a large number of INSERTs. Look at the execution plan to see what is going on in detail. I guess that it is compiled into a nested loop of INSERTS without any chance of an optimization.
Also check the transaction behavior. Is every INSERT a separate transaction or can it be grouped into one big transaction. The latter may be a lot faster.
Dear Vehbi,
I agree with Josef to the asymptotic complexity explanation that is related to the nested loop in your code. Correct me if I am wrong, but as far as I can see you are trying to retrieve all records from Table 1 that are not Null and the string 'word' is contained. Considering that the column name in Table 1 is 'words' and the string we are looking for is 'word' why don't you use SELECT as follows:
SELECT words FROM Table 1 WHERE words = 'word' AND words IS NOT NULL;
Then you can insert the results into Table 2 with INSERT INTO.
- Badges
- Science topic
- Similar topics
- Mathematical Sciences
- Graphs