speed up query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

speed up query

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2003-01-29 : 13:26:56

I created a stored proc (SP) that creates a temp table and does insert and lots of updates on 12 fields per record. When there are hundreds of records, so it does 12*number of records updates. I checked the execution plan and the SP does a table scan on the temp table (Cost 79% to 100%). How can I speed up this stored procedure?

rkc01
Starting Member

43 Posts

Posted - 2003-01-29 : 13:54:58

Try creating indexes on the columns. It would be helpful to see the table structure and sp.

-Rob

PeterG
Posting Yak Master

156 Posts

Posted - 2003-01-29 : 14:09:56

how do i create an index on a column of a temp table?

PeterG
Posting Yak Master

156 Posts

Posted - 2003-01-29 : 14:17:14

Here's my SP:

CREATE PROCEDURE dbo.SelectStatementCycleDataPGG
@txtAccountNumber as nvarchar(16), --account number
@dteStartDate as datetime, --cycle date
@lengths varchar(75), --max lengths in a delimited string
@startpos varchar(75), --start positions in a delimited string
@separator varchar(2) --delimiter

AS
SET NOCOUNT ON
DECLARE @sql varchar(4000), @counter int, @numOfTrans int, @CostCenter varchar(80), @index int, @newlengths varchar(80)
DECLARE @separator_length int, @length_value varchar(80), @separator_start int, @start_value varchar(80), @newstartpos varchar(80)

-- append a separator at the end of the array
SET @lengths = @lengths + @separator
SET @newlengths = @lengths
SET @startpos = @startpos + @separator
SET @newstartpos = @startpos
SET @counter = 0 --costcode number as txtCostCode1-12
SET @index = 0 --index value of records in the temp table

--create tenmp table
Create table #tempTransaction
(lngIndex int IDENTITY,
txtAccountNum varchar(30),
txtCompanyNum varchar(20),
dtePostDate datetime,
dteCycleDate datetime,
dteTransacDate datetime,
txtMerchantName varchar(35),
txtSourceCurr varchar(12),
txtBillingCurr varchar(14),
curForeignAmnt money,
txtSic varchar(16),
curTransacAmnt money,
txtTransacCode varchar(12),
txtMerchantCity varchar(40),
txtMerchantProv varchar(40),
txtMemoFlag varchar(13),
lngParent int,
txtCostCenter varchar(90),
curOrigAmnt money,
curGst money,
lngSourceId int,
curPst money,
blnForeignEx bit,
curNetAmnt money,
blnSent bit,
blnMatched bit,
txtComments varchar(265),
txtCostCode1 varchar(80),
txtCostCode2 varchar(80),
txtCostCode3 varchar(80),
txtCostCode4 varchar(80),
txtCostCode5 varchar(80),
txtCostCode6 varchar(80),
txtCostCode7 varchar(80),
txtCostCode8 varchar(80),
txtCostCode9 varchar(80),
txtCostCode10 varchar(80),
txtCostCode11 varchar(80),
txtCostCode12 varchar(80)
)

--populate the temp table
Insert Into #tempTransaction
(txtAccountNum, txtCompanyNum, dtePostDate, dteCycleDate, dteTransacDate, txtMerchantName, txtSourceCurr, txtBillingCurr, curForeignAmnt, txtSic, curTransacAmnt,
txtTransacCode, txtMerchantCity, txtMerchantProv, txtMemoFlag, lngParent, txtCostCenter, curOrigAmnt, curGst, lngSourceId, curPst, blnForeignEx, curNetAmnt,
blnSent, blnMatched, txtComments)
SELECT T.txtAccountNum,
T.txtCompanyNum, T.dtePostDate, T.dteCycleDate, T.dteTransacDate, T.txtMerchantName, T.txtSourceCurr, T.txtBillingCurr, T.curForeignAmnt, T.txtSic, T.curTransacAmnt,
T.txtTransacCode, T.txtMerchantCity, T.txtMerchantProv, T.txtMemoFlag, T.lngParent, T.txtCostCenter, T.curOrigAmnt, T.curGst, T.lngSourceId, T.curPst, T.blnForeignEx, T.curNetAmnt,
T.blnSent, T.blnMatched, T.txtComments
FROM Transactions T
WHERE (T.txtAccountNum = @txtAccountNumber) AND
(T.dteCycleDate = @dteStartDate)

--get total number of records
SELECT @numOfTrans = (Select MAX(lngIndex) FROM #tempTransaction)

--loop until all rows have been updated
while @numOfTrans < > @index
BEGIN
--increment the record index
SET @index = @index + 1
--grab costcenter string
SELECT @CostCenter = txtCostCenter FROM #tempTransaction where lngIndex = @index
--loop while there is a member element in he delimited string
while patindex('%' + @separator + '%' , @lengths) <> 0
BEGIN
select @separator_length = patindex('%' + @separator + '%' , @lengths)
select @separator_start = patindex('%' + @separator + '%' , @startpos)

--get the member element
select @length_value = left(@lengths , @separator_length - 1)
select @start_value = left(@startpos , @separator_start - 1)

--increment cost code field counter
SET @counter = @counter + 1

--parse into cost codes
SET @sql = 'Update #tempTransaction
Set txtCostCode' + CAST(@counter as varchar(2)) + ' = substring(''' + @CostCenter + ''',' + @start_value + ',' + @length_value + ')
where lngIndex= ' + CAST(@index as varchar(2))
exec (@sql)

--remove member element from the array
select @lengths = stuff(@lengths, 1, @separator_length, '')
select @startpos = stuff(@startpos, 1, @separator_start, '')
END
SET @counter = 0 --reset costcode field counter
--reset the dlimited strings to their original values
SET @lengths = @newlengths
SET @startpos = @newstartpos
END

Select * from #tempTransaction
Drop table #tempTransaction
SET NOCOUNT OFF
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-01-29 : 14:17:50

Same as you would on a normal table, except the create statement would be in your stored procedure.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-29 : 14:30:38

Can you describe, in english, what your stored proc is doing? I'm positive we can re-write this to not just a WHILE loop, which is part of what is slowing you down. Probably w/o the temp table as well.

If you can clearly tell me what the goal is, I'll take a stab at it using set-based operations (i.e., doing everything at once) if possible.

- Jeff

PeterG
Posting Yak Master

156 Posts

Posted - 2003-02-07 : 18:32:18

I got so busy doing other projects that I forgot to check the site.

Anyways, here is what I was doing.

Created a temp table
I selected some of the fields from the Transactions table, that I would be returning to my asp page.
In my while clause, I grabbed the txtCostCenter (80-char string) from the temp table and parsed it using substring (the length and starting points are in a CSV value passed to the SP). The parsed strings are then stored (updated) in the txtCostCode[1-12] fields in the temp table.
The fields I earlier selected into the temp table plus the txtCostCodes are then returned as one recordset to the calling asp page.

I hope this is clear enough, but if not let me know. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources