Still in the SQL Server world, but this project is on the last step, so there's a chance I can be back on Oracle in the near future.
A friend emailed me a useful link for equivalent functions between database products. Given that anyone can add/edit entries in there, it should be more up-to-date than my previous resource here (which I still recommend for a read-through).
On the theme of equivalence, I've been looking at the SQL Server 2005 equivalent of an EXPLAIN PLAN.
I've had another issue with poor query performance, but this time with a SELECT.
I would have nosed at the EXPLAIN PLAN in Oracle and the SQL Server version seems to be "SET SHOWPLAN_ALL ON". Rather than doing an EXPLAIN PLAN for a specific query though, once this is set in SQL Server the subsequent queries return the plan rather than the results, until you turn the setting off.
I'm old-school, and like to look at text rather than pretty pictures. A picture being worth a thousand words is only valid when you start with the picture. If you start with a description, a dozen different artists will give a dozen interpretations, and some will be worse than others. With the description, I can make up my own mind what's important. Apparently there is an XML version of the output, but I haven't experimented with that yet, so don't know how readable that gets or can be made.
The query was a simple join between two tables; one was a small load table (a few thousand rows) and the other a much larger 'target' table (several million rows). I expected a scan of the small table with a Nested Loop containing an Index Scan and table access for the target table. It can be useful to start with an expectation of a 'good' plan. If the optimizer comes up with something different, you take a look and see where your assumptions differ from the optimizers, and who is 'right'.
In this case I got pretty much what I expected.
I corrected the join to convert the number in the load table to a varchar, and the query that had taken an hour took just seconds.
A friend emailed me a useful link for equivalent functions between database products. Given that anyone can add/edit entries in there, it should be more up-to-date than my previous resource here (which I still recommend for a read-through).
On the theme of equivalence, I've been looking at the SQL Server 2005 equivalent of an EXPLAIN PLAN.
I've had another issue with poor query performance, but this time with a SELECT.
I would have nosed at the EXPLAIN PLAN in Oracle and the SQL Server version seems to be "SET SHOWPLAN_ALL ON". Rather than doing an EXPLAIN PLAN for a specific query though, once this is set in SQL Server the subsequent queries return the plan rather than the results, until you turn the setting off.
I'm old-school, and like to look at text rather than pretty pictures. A picture being worth a thousand words is only valid when you start with the picture. If you start with a description, a dozen different artists will give a dozen interpretations, and some will be worse than others. With the description, I can make up my own mind what's important. Apparently there is an XML version of the output, but I haven't experimented with that yet, so don't know how readable that gets or can be made.
The query was a simple join between two tables; one was a small load table (a few thousand rows) and the other a much larger 'target' table (several million rows). I expected a scan of the small table with a Nested Loop containing an Index Scan and table access for the target table. It can be useful to start with an expectation of a 'good' plan. If the optimizer comes up with something different, you take a look and see where your assumptions differ from the optimizers, and who is 'right'.
In this case I got pretty much what I expected.
--Filter(WHERE:....
--Parallelism(Gather Streams) .
--Nested Loops(Inner Join, ...
--Nested Loops(Inner Join, ...
--Compute Scalar(...
--Table Scan(OBJECT:LOAD_TABLE....
--Index Seek(OBJECT:TARGET_TABLE_INDEX...
--RID Lookup(OBJECT:TARGET TABLE...
.
I could see (using the same analysis SQL as before) that it was doing a lot more work than I would have expected with that plan. The table scan shouldn't have been responsible, as it was a small table and only done once. The Row lookup on the target shouldn't have been responsible as, if there was an index match, it would have matched only one row. If the index matched lots of rows for this data, it would have been using the wrong index (or the right index in the wrong way).So I took a harder look at the Index Seek Line. It was the right index. It had a 'seek' component and a 'where' component, which I equated to the 'Access predicate' and 'Filter Predicate' in an Oracle plan.
Index Seek(OBJECT:(TARG_TAB),The cause was obvious in this case. The Implicit conversion from a number to a character meant that it only used the two leading columns of the index (which had poor selectivity) and not the third (and subsequent columns) which would have made the index really useful.
SEEK:(TARG_TAB.COL_1=[Expr1008] AND TARG_TAB.COL_2=[Expr1009]),
WHERE:([Expr1010]=TARG_TAB.COL_3 AND
LOAD_TAB.COL_L1=CONVERT_IMPLICIT(numeric(15,0),TARG_TAB.COL_4,0)
AND LOAD_TAB.COL_L2]=CONVERT_IMPLICIT(numeric(15,0),TARG_TAB.COL_5,0))
I corrected the join to convert the number in the load table to a varchar, and the query that had taken an hour took just seconds.
No comments:
Post a Comment