July 31, 2009 at 5:30 am
Hi All,
We have table 'Accounts' with 148952 records. Table structure is :
AccountId INT - primary key
AccountName NVARCHAR(120)
AccountTypeId INT
CreatedBy INT
CreatedDate DATETIME
There is clustured index on AccountId column whereas non-clustured index is
CREATE NONCLUSTERED INDEX [IX_AccountId] ON [dbo].[Accounts]
(
[AccountId] ASC
)
INCLUDE ( [AccountName],[AccountTypeId])
There is another table with Opportunities with 199910 records - in which this accountId is used as foreign key.
In my query, I am joining these two primary tables (Opportunities and Accounts) along with 2-3 other small tables.
If I select any column from Accounts table (e.g. accounname) in select query - response times are double than what it is without accountname column. Note that I have joins to accounts table in both cases (with/without column).
I am attaching query and two execution plans (with accountname is select list and commented accountname). I think it looks like a improper indexing. But can you please suggest what could be the problem.
If I do the join of Opportunities table to some other table instead of 'Accounts' with nearly same number of records, it works fine and response times good.
Query WITHOUT accountname in select list takes 7 secs while the one WITH accountname takes 13 secs to run.
Please let me know what could be the reasons.
July 31, 2009 at 7:53 am
Is there an index in Opportunities on the AccountID? Are your indexes defragmented and the statistics up to date? How selective is the data within the Account table?
Based on what you're showing us, I wouldn't expect quite this result.
You're returning 100000 rows. Is this for an ETL process? If not, users don't need that much data and won't use that much data. Unless it's an ETL process you should look to apply more and better filters.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
July 31, 2009 at 7:55 am
By looking at the execution plans.
SQL has worked out that when you are not returning any columns from the accounts table. That this table is not actually need in the query at all and as a result is not used.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 1, 2009 at 4:29 am
Grant Fritchey (7/31/2009)
yes, there is index on Opportunities tab for AccountId column. Also there is no index fragmentation and statistics are uptodate.
Based on what you're showing us, I wouldn't expect quite this result.
You're returning 100000 rows. Is this for an ETL process? If not, users don't need that much data and won't use that much data. Unless it's an ETL process you should look to apply more and better filters.
Actually for security purpose, we are using some table valued UDF functions at root level which returns the accessible data. These UDFs are used instead of direct access to table. For ex. instead of using Opportunities table in direct queries - we have UDFs which returns required data. So for high level users - this UDF returns more than 100000 rows.. The query I posted earlier is part of UDF function.
August 1, 2009 at 4:53 am
While I was trying different things on query, I noticed interesting thing in execution plan. If I use CTE in my query joins, execution plan shows sort operator with cost 40%. CTE is does not have any reference to Opportunies/Accounts table. But still sorting is done on AccountId column of Opportunities table.
When I use physical table instead of CTE, there is no sort operation and query runs in 7 secs while it takes 13-14 secs in other case.
I do not understand why SQL server is doing sort with CTE in join. I am attaching query with execution plans in both cases. Please help me to understand this and how sort could be eliminated here.
Thanks in advance.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply