September 7, 2005 at 12:49 pm
I have a table that acts as a journal of transactions for each customer account. It is a large table with over 1 million records.
The structure consists of an AccountId and a LineNumber field acting as a Primary Key and various monetary value fields.
There is a view on this table that does a self join on AccountId and LineNumber = LineNumber -1 and returns the change between journal entries.
The view takes a long time to run so when I saw a stored procedure using this view 4 times I thought that replacing the view with a table that is populated overnight would result in a big boost in performance.
I have ensured that the resulting table has the required primary key but even so the execution time for a duplicate stored procedure (with the sole exception that it uses the table rather than the view) is 150% of the original.
I have tried sp_recompile and the DBCC commands to flush the cache but the performance difference remains.
I am at a loss to explain this behaviour. How can a view be quicker than a table?
Any pointers on this would be gratefully received
September 7, 2005 at 1:09 pm
How are you querying that table??
Can we see the ddl?
September 7, 2005 at 1:10 pm
Its hard to say, and it all depends
Can you present us the view definition?
When you state "The view takes a long time to run" what exactly do you mean?
Please post the query you are using to hit the view.
Also you need to run this query and view the execution plan.
Things to look for are index scans (Bad) Index Seeks (Good)
Index scans can tell you there is a missing index, or your where clause should be reformatted. Unfortanatelly this sort of performance tuning is alot of trial and error.
Post your table definition, the view difinition, the query your using against the view, and the execution plan, and I bet someone can help
September 7, 2005 at 1:58 pm
For confidentiallity reasons I cannot post the actual view or the stored procedure but if you imagine the table is something similar to the following
AccountID INT
LineNo INT
SalesValue MONEY ,
CostValue MONEY ,
OtherValue MONEY
The query within the view is
SELECT Main.AccountID ,
Main.LineNo
COALESCE(Prev.SalesValue - Main.SalesValue,Main.SalesValue) AS ChangeSalesValue ,
COALESCE(Prev.CostValue - Main.CostValue,Main.CostValue) AS ChangeCostValue ,
COALESCE(Prev.OtherValue - Main.OtherValue,Main.OtherValue) AS ChangeOtherValue
FROM dbo.Journal AS Main LEFT JOIN dbo.Journal AS Prev ON
Main.AccountId = Prev.AccountID AND
Main.LineNo = Prev.LineNo-1
Bear in mind that this self join is joining a 1 million record table to itself in the view.
My new table is the results of this query deliberately sorted by AccountID and LineNo and with a Clustered Primary Key on AccountId and LineNo.
Joins to the view/table are always on AccountID and in the majority of cases include LineNo so the Primary Key index should be used in the majority of cases.
Because there are no conditions other than the join referencing the view/table an index scan takes place.
The execution plan for the stored procedure could be used to plug the hole in the New Orleans levvy but the join relevant to the table/view is simple and straight forward in each case.
I started experimenting with a table variable but the memory hit for using them doubled the execution time of the query.
Temporary table was the next worst because of the overhead of generating the temporary table.
Permanent table is the least worst but that is the bit I don't understand!
September 8, 2005 at 10:01 am
The query you have will need to scan the Clutered Index because of the scalar conversion in the second join term. It has to consider every possible row for each AccountID as a candidate for satisfying the Main.LineNo = Prev.LineNo-1 part of the join.
One solution might be to create a computed column (call it PreviousLineNo) that is equal to the LineNo - 1, and place an index on that column. Then the join would look like
FROM dbo.Journal AS Main LEFT JOIN dbo.Journal AS Prev ON
Main.AccountId = Prev.AccountID AND
Main.LineNo = Prev.PreviousLineNo
Now the optimizer might want to use the new index to seek to the correct row. It depends on how many rows you're actually going to use in the result. If you are looking at a single AccountID and LineNo at a time, this may help. If you are looking to get the entire set of AccountID and LineNo combinations, then it is probably not going to help much.
Might be worth a shot, though.
jg
September 8, 2005 at 10:05 am
If I understood correctly, he created a perm table using this query, but query from the perm table, it takes longer than the view even with correct indexes on the new table.
September 8, 2005 at 10:14 am
If the SP doesn't use all of the results of the table, it may very well take longer to create the entire table. The view itself does not give sufficient information about how it is being used.
September 8, 2005 at 10:17 am
"replacing the view with a table that is populated overnight would result in a big boost in performance."
September 8, 2005 at 1:20 pm
Correct!
The performance of the stored procedure using the new table instead of the view is lower than when using the view alone.
It is an absolutely crazy result, especially as the new table is populated in an overnight process.
The only explanation I can think of is that there is something strange going on in the cache due to the view doing a self-join but this is clutching at the last whisp of the last straw.
September 8, 2005 at 1:26 pm
Can you send out the execution plans of the view version and the table version?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply