August 31, 2011 at 3:12 am
I have code attached which looks at transactions in the AAACCTRA table, manipulates the data, writes it to the Transaction_Temp table, and then at the end writes the results to the transaction table.
The AAACCTRA table is not indexed and gets populated every day with data. The file structures are also attached. Is there any way of making this code run faster?
Currently it processes roughly 700 000 new transactions in an hour but am trying to cut down the time.
August 31, 2011 at 7:39 am
First, why insert twice? That's two times the labor and work for SQL Server.
Second, have you looked at the execution plans? That will tell you how things are behaving and places where you can possible improve performance.
Third, you need to know that the NOLOCK hint is not a "RUN FASTER" switch in SQL Server. It can lead to missing and extra data. Don't use it if you can't justify it, and no, an increase in performance is not a good reason if the data returned is incorrect.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 31, 2011 at 1:02 pm
I would check if the following section of the code is one of the performance killers:
CASE WHEN A.TransactionType in ('I', 'D', 'DYS', 'E') AND LEN(RTRIM(A.Reference)) = 4 THEN
(SELECT TOP 1 ISNULL(SfeCode, RTRIM(A.Reference)) FROM UniverseR.dbo.SFE (NOLOCK)
WHERE SFECode = RIGHT(RTRIM(A.Reference),3)) ELSE A.Reference END
Other than that I second Grants comments. Especially, but not limited to, the NOLOCK issue...
August 31, 2011 at 5:59 pm
I would look at the functions and either convert them to iTVF or eliminate them. In fact, I would get rid of all of the functions on the select into the temp table and move them to the select from the temp table.
All of those user defined functions is going to kill the performance - and you really don't need to RTRIM any columns. You can eliminate the RTRIM within the LEN function by using DATALENGTH instead.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 6, 2011 at 2:28 am
Thanks for all the suggestions. I will have a look at what you highlighted and give feedback.
September 7, 2011 at 5:26 am
Hello tendayit and all,
I see two estrange points (to me) in your code, lets me explain:
First, your table TRANSACTION_TEMP has a clustered index but, being a temp table, you need this index for nothing, it only adds extra work. So I think you should eliminate this index (if not the entire table).
Second, I don't see any DELETE on table AAACCTRA!, instead you filter rows using a JOIN with the TRANSACTION table to avoid process twice the same records. I suppose you empty this AAACCTRA table in another process.
This JOIN to filter rows should add cost to your process. Actually you should need an index in TRANSACTION table using the [TransactionId] column, but I don't see it in your DDL statements.
Maybe you can code your process like this?:
DELETE FROM UniverseR.dbo.AAACCTRA
OUTPUT ... INTO UniverseW.dbo.[Transaction]
This way you should eliminate both the TRANSACTION_TEMP table and the JOIN to filter.
Regards,
Francesc
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply