Query plan isn't doing an index seek

If you have the following:

SELECT WorkID,VendorName = (SELECT VendorName FROM Vendor WHERE VendorID = 
ws.VendorID)
FROM Work ws

What I get is a Hash Match from 2 index scans.

  |--Compute 
Scalar(DEFINE:(dbo].[VENDOR].[VendorName]=[dbo].[VENDOR].[VendorName]))
       |--Hash Match(Right Outer Join, 
HASH:([dbo].[Z_VENDOR].[VendorID])=([ws].[VendorId]), 
RESIDUAL:([dbo].[VENDOR].[VendorID]=[dbo].[WORK].[VendorId] as 
[ws].[VendorId]))
            |--Clustered Index Scan(OBJECT:([dbo].[VENDOR].[PK_VENDOR]))
            |--Clustered Index Scan(OBJECT:([dbo].[WORK].[PK_Work] AS [ws]))

In both cases, it is doing an Index Scan on both tables even though there 
are 2 clustered indexes on the PK of both tables and there is an index on 
the VendorID column of the WORK table (which I suppose is not being used 
because it is scanning the WORK table then getting the Vendor based on the 
value from WORK table).

But why wouldn't it do a Clustered Index Seek on the Vendor table?

Also, what about the Hash Match - is that a problem?

The Vendor scan costs 1%, the WORK scan costs 54% and the Hash Match costs 
44%.

Thanks,

Tom 


0
tshad
3/29/2010 11:50:01 PM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
783 Views

Similar Articles

[PageSpeed] 40

Well you don't have a WHERE clause so that means it has to touch every row. 
Its far less expensive to scan the index once vs. do all those lookups for 
each row in the table. Add a WHERE clause that limits the rows and you will 
get a different story.

-- 

Andrew J. Kelly   SQL MVP
Solid Quality Mentors

"tshad" <t@dslextreme.com> wrote in message 
news:u8elNq5zKHA.1796@TK2MSFTNGP02.phx.gbl...
> If you have the following:
>
> SELECT WorkID,VendorName = (SELECT VendorName FROM Vendor WHERE VendorID = 
> ws.VendorID)
> FROM Work ws
>
> What I get is a Hash Match from 2 index scans.
>
>  |--Compute 
> Scalar(DEFINE:(dbo].[VENDOR].[VendorName]=[dbo].[VENDOR].[VendorName]))
>       |--Hash Match(Right Outer Join, 
> HASH:([dbo].[Z_VENDOR].[VendorID])=([ws].[VendorId]), 
> RESIDUAL:([dbo].[VENDOR].[VendorID]=[dbo].[WORK].[VendorId] as 
> [ws].[VendorId]))
>            |--Clustered Index Scan(OBJECT:([dbo].[VENDOR].[PK_VENDOR]))
>            |--Clustered Index Scan(OBJECT:([dbo].[WORK].[PK_Work] AS 
> [ws]))
>
> In both cases, it is doing an Index Scan on both tables even though there 
> are 2 clustered indexes on the PK of both tables and there is an index on 
> the VendorID column of the WORK table (which I suppose is not being used 
> because it is scanning the WORK table then getting the Vendor based on the 
> value from WORK table).
>
> But why wouldn't it do a Clustered Index Seek on the Vendor table?
>
> Also, what about the Hash Match - is that a problem?
>
> The Vendor scan costs 1%, the WORK scan costs 54% and the Hash Match costs 
> 44%.
>
> Thanks,
>
> Tom
> 
0
Andrew
3/30/2010 2:37:02 AM
"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message 
news:O0lciH7zKHA.4384@TK2MSFTNGP06.phx.gbl...
> Well you don't have a WHERE clause so that means it has to touch every 
> row. Its far less expensive to scan the index once vs. do all those 
> lookups for each row in the table. Add a WHERE clause that limits the rows 
> and you will get a different story.
>

You're right it did.

I noticed that the Vendor table was always scanned but that was because 
there were only 13 records in it.

When I changed that subquery to another table that had many more records, I 
got an index seek on both tables.

Thanks,

Tom

> -- 
>
> Andrew J. Kelly   SQL MVP
> Solid Quality Mentors
>
> "tshad" <t@dslextreme.com> wrote in message 
> news:u8elNq5zKHA.1796@TK2MSFTNGP02.phx.gbl...
>> If you have the following:
>>
>> SELECT WorkID,VendorName = (SELECT VendorName FROM Vendor WHERE VendorID 
>> = ws.VendorID)
>> FROM Work ws
>>
>> What I get is a Hash Match from 2 index scans.
>>
>>  |--Compute 
>> Scalar(DEFINE:(dbo].[VENDOR].[VendorName]=[dbo].[VENDOR].[VendorName]))
>>       |--Hash Match(Right Outer Join, 
>> HASH:([dbo].[Z_VENDOR].[VendorID])=([ws].[VendorId]), 
>> RESIDUAL:([dbo].[VENDOR].[VendorID]=[dbo].[WORK].[VendorId] as 
>> [ws].[VendorId]))
>>            |--Clustered Index Scan(OBJECT:([dbo].[VENDOR].[PK_VENDOR]))
>>            |--Clustered Index Scan(OBJECT:([dbo].[WORK].[PK_Work] AS 
>> [ws]))
>>
>> In both cases, it is doing an Index Scan on both tables even though there 
>> are 2 clustered indexes on the PK of both tables and there is an index on 
>> the VendorID column of the WORK table (which I suppose is not being used 
>> because it is scanning the WORK table then getting the Vendor based on 
>> the value from WORK table).
>>
>> But why wouldn't it do a Clustered Index Seek on the Vendor table?
>>
>> Also, what about the Hash Match - is that a problem?
>>
>> The Vendor scan costs 1%, the WORK scan costs 54% and the Hash Match 
>> costs 44%.
>>
>> Thanks,
>>
>> Tom
>> 


0
tshad
3/30/2010 5:23:36 AM
Reply:

Similar Artilces:

Xpath query #5
Given following XML: <RESPONSE> <DATA REQUEST_ID=3D"465466189C701F41E040007F01004D25E6B23D14793EB72753CE2387F85A5= 7E5"> <ENTITY name=3D"Q_ARI_KLS"> <ATTRIBUTE name=3D"ID" value=3D"10000307968" /> <ATTRIBUTE name=3D"NOSAUKUMS" value=3D"ABAB=C4=BBEVA" /> </ENTITY> <ENTITY name=3D"Q_ARI_KLS"> <ATTRIBUTE name=3D"ID" value=3D"10000323423" /> <ATTRIBUTE name=3D"NOSAUKUMS" value=3D"CITSKAUTKAS&qu...

How do I create a lesson plan template?
I am new third grade teacher and I'm wanting to create a lesson plan template to fill in each week rather than doing it manually each week. On Sun, 19 Aug 2007, in microsoft.public.excel.charting, Ms. Agner <Ms.Agner@discussions.microsoft.com> said: >I am new third grade teacher and I'm wanting to create a lesson plan template >to fill in each week rather than doing it manually each week. Do it manually once, then set all the cells you would fill to zero, or "XXX" or whatever, and save that. Next week, open the file you saved and fill it in with the new week&...

Button Wizard:RunQuery ignores delete queries?
If I put a button on a form, choose misc, run query, I get a list of queries that does not include any delete queries. Why is that? It is easy enough to get around, but why? Don't have an answer for you, other than to confirm that delete queries are not presented in the list of queries.... ACCESS has been this way for a number of versions, for some reason. -- Ken Snell <MS ACCESS MVP> "Phil Smith" <phil@nhs-inc.com> wrote in message news:OjpW0tg1HHA.1100@TK2MSFTNGP06.phx.gbl... > If I put a button on a form, choose misc, run query, I get a list...

Running queries via forms
is there a good tutorial out there on how to make forms that run queries and reports? I've gotten all turned around on it all. On Thu, 22 Apr 2010 13:53:01 -0700, Dragon <Dragon@discussions.microsoft.com> wrote: >is there a good tutorial out there on how to make forms that run queries and >reports? I've gotten all turned around on it all. Well, forms don't "run" anything. You can use a reference to a form control on a Query, and base a Report on that query. Crystal's tutorial might be a good place to look, this is one of the techniques she d...

MS query #3
I'm bringing over data into Excel using MS Query from another application. One of the fields is a "Note" field. This field comes over including all the formatting. (i.e. "{\rtf1\ansi\ansicpg1252\deff0{\fonttbl{\f0 \fnil\fcharset0 Arial;}{\f1\fnil MS Sans Serif;}} \viewkind4\uc1\pard\lang1033\fs16 Again I say this is just a test.\f1\par }) The technical people at the other application say: "The notes fields are now Info Power Rich Text fields. You will need to use components that reflect them as Rich Text fields. You are apparently using a component that...

Import external data from Access query with Variable
Hello - I have been able to record a macro where I can change the "inventory.Area" from US006 to US007. I would like to be able to make the area a variable and pick up the area code from within a spreadsheet. For example, I am doing reporting for 10 areas and would like to have one template that pulls in the data from Access for one area, saves the file, goes to the next area, pulls in that data from Access and so on. But I cannot figure out how to make the area in the VBcode below a variable. Any help would be appreciated. Sub Macro3() With Selection.QueryT...

Passing parameters to a Web Query
I'm not an Excel expert so bear with me. I want to create a Web Query but I want to pass parameters from the Worksheet itself... Something like this: http://webSite/somepage.asp?startDate=summary!a1&endDate=summary!b1 Essentially, my ASP page needs a start and end date to pass to an SQL stored procedure. Is this even possible and if not, do I have any other options? Took me a while, but I found how to do this. "Raymond D'Anjou" <rdanjou@kindervillexxx.com> wrote in message news:%23R$ohXceKHA.5136@TK2MSFTNGP02.phx.gbl... > I'm not...

uninstall query
Hi All , This is my first msg in this group hope i'll get help n some idea, ok now time for the problem.... i have developed a simple MFC dialog based program which when runs creates few txt file which holds simple numeric data which i use during the operation of the program, now my requirement is ... whenever i uninstall the program all files including the folder / sub folder should get deleted, i am not using any DLL in my program. Isn't that something you should be doing in the uninstaller? -- Vipin Aravind http://www.explorewindows.com/Blogs "PARIJAT" <parijat...

link a txtbox with a query
Hi guys, I am working in an Access Project and I want to connect one textbox I have in the form with the criteria in the query (View) for the row source for a combo box in the same form, what I want is for reduce the amount of records shown in the combo box. I typed this lines in the criteria of the fieldcategory in the query, = [forms]![frmPopAddProductOrRecipe]![txtCategory] but it doesn't recognize it. Hi Orlando The combo box needs to be requeried whenever your text box is changed. Use the After Update event of your text box like... Private Sub txtCategory_AfterUpdate() ...

edit web query
This was working a few days ago and now doesn't. When you try and Edit a Web query the web query box pops up with the correct web address, but you get a message ... "Navigation to the webpage was cancelled". The Web Query does work as it is on automatic refresh in the background and pulls back the correct data, I just can't edit it. I can run IE7 with the same address and it works fine. I also cannot add a new web query as I only ever get the default IE web page displayed. the address is http://uk.finance.yahoo.com/d/quotes.csv?s=DGO.L&f=c1 Any ideas? I suspec...

Access Queries 02-29-08
Is there any way to select the only the top 5 records in a query or table that is sorted by amount. Right click on properties in design view of the query and on top values select top 5. "LL" wrote: > Is there any way to select the only the top 5 records in a query or table > that is sorted by amount. ...

Composite Index Creation Needed SQL 2008?
I have a table with only a simple int pk. I do a merge operation and need to compare several columns in order to know if I need to 'when not matched then insert'. There are 20,000 rows+ in the source table (a temp table) and a few million in the destination table. The table schemata are the same. I know the approx number of unique values in each table, and think I need to create a unique index across all of the columns. Is that true? What is the right syntax for doing that? -- ColumnA: 167 unique values in #temptable -- ColumnB: 1 unique values in #temptable -- ColumnC: 1...

Query Form
I have exported some data from the local MLS system into an Access database and want to run some statistics on the sales information, using a number of different queries which will feed graphs in Excel, etc... I have a form set up for choosing the data which include the city, the development, what kind of property it is (condo, single family residence, etc.) The problem I have is why no selection is made. For example, the query looks for a city selection from a drop-down box; if I choose a city, the query runs perfectly. If I do not choose a city - leaving it blank - the...

Query to add fake records from table
I have a table that contains real data and then I have a table that has dummy data that is needed for another program that I'm sending the file too. I need to figure how to put them in the same colunm in the table. Here is my statement. "Station Name & Item: [DBA_DEVICES]![deviceName] & " " & [DBA_ITEMS]![itemId] NEW [DocFinity Fake Records]![Station Name] & " " & [DocFinity Fake Records]![Item]" Where the 'NEW' is that is where I need to enter my other data is the same colunm. Below is what I'm trying to a...

Timer for Queries?
Does anyone know of a macro or add-on that I could use as a simple timer for queries? I don't like to use the CPU time in the Task Manager because of it's inaccuracy when not receiving full CPU usage. THanks for any suggestions -- cmungs Exactly what are you trying to accomplish? Are you trying to cause a query to run automatically every so often? If so, you will need to use the timer event on a form to do that. -- Lynn Trapp Microsoft MVP (Access) www.ltcomputerdesigns.com "cmungs" <cmungs@discussions.microsoft.com> wrote in message news:88EC7019-045F-4EF...

Tricky XPath query
Hey all, I've attempted to simplify my question (or at least the XML that is part of my example in my question): I have the following XML: <A> <B><C><D> I want to query the data in this node for the element B </D></C><E> I also want to query the data in this node for the element B </E> </B> <B><!--Additional data for another B element--> </B> </A> My idea is to do something like this: --------------------------------------------------- Dim nav As XPathNavigator = doc.CreateNaviga...

Query crashes Access with no errors
Hi, I'm still quite new to Access. I've built a query to pull course details from the database. Unfortunately it seems to be causing problems and a part of it is crashing access giving no errors. I've identified that the following is the problem, but I can't see why. Can you suggest any improvements to stop this happening? This checks 2 fields and depending on the content assigns a duration, which may or may not contain one of those fields. Duration: IIf([Full Course Details]![Weeks_in_Qual]=1,([Full Course Details]![Hours_per_Week] & " Hours"),IIf([Full...

Query Question 11-29-07
my data looks like the following 111 0 01/01/2007 111 0 02/01/2007 222 0 04/01/2007 222 0 05/01/2007 555 1 06/01/2007 666 0 06/01/2007 how can i retrieve the following??? 111 0 01/01/2007 222 0 04/01/2007 555 1 06/01/2007 666 0 06/01/2007 my goal is to retrive distinct cols 1 and 2, and then the earliest date in col 3 for distinct records in cols 1 and 2??? any suggestions??? I tried different ways of using FIRST, but was unsuccessful. Thanks in advance. On Nov 28, 7:08 pm, "Jeff" <findjeffa...@emailias.com>...

Update Queries
I have two tables an Employee table and a Gap Table. Both Tables have the Social Security Field (SSN) in them. I also have a UPI field (unique personal ID) in both tables. In the Gap table the UPI's are null. What I need to know how to do is How do I update the UPI in the GAP table with the UPI in the Employee table when the SSN fields from both tables equal each other. Please Help! Thank you! Marc If you know the SSN, doesn't that mean you (already) know the UPI? If so, why store it twice? By the way, SSNs are not particularly good choices for a Primary Key. They...

query
Because the SQL I've written for a query calls on a database Proc., it can't be displayed graphically. Because of this, it won't let me use paramaters.... any suggestions on how to call a db. proc. and still use parameters? You could try using a question mark, e.g.: SELECT Invoices.InvID, Invoices.CustID, Invoices.InvDate FROM Invoices Invoices WHERE (Invoices.InvDate>?) Tom S wrote: > Because the SQL I've written for a query calls on a database Proc., it can't > be displayed graphically. Because of this, it won't let me use > paramaters.... any sug...

Conditional formatting query
I have 3 columns of data : A, B and C These have numeric price values in them, although one or all could be empty. I want to highlight the lowest value cell in each row that is actually populated. e.g. A1 is empty, B1 is 3.50, C1 is 10.00 - B1 should be highlighted as it is the lowest value A2 is 1.25, B2 is empty, C2 is empty - A1 should be highlighted A3, B3 and C3 are all empty - none should be highlighted. Any ideas how I use conditional formatting to recognise the lowest and highlight it? Hi What was wrong with Biff's suggestion to your last post? Andy. "Stainless&...

Excel Query #3
I want to use an Hanging Indent for the text within a cell. I can do it by spliting that into two different cells but I feel that would be wrong. For example Financial Data for the year ended 2K Please note that the text data should be in the same cell.If any one can help me. Hi can't be done with Excel. You may insert a manual linebreak with ALT+ENTER and insert some spaces but an automatic ident for the second line is not possible with Excel -- Regards Frank Kabel Frankfurt, Germany Felix wrote: > I want to use an Hanging Indent for the text within a > cell. I can do i...

Value in Crosstab Query
Can I show all the data or merge them into one single cell, instead of doing any aggregate functions such as sum and average, in a Crosstab Query? To illustrate my question: StudentID TestID Score (Date) AAA 1 50 (date1) AAA 1 60 (date2) AAA 1 55 (date3) AAA 2 23 (date2) AAA 2 24 (date4) AAA 3 98 (date4) I want to show the data in a Query or Report in this way: Stude...

Any GP-specific risks when adding an index or stat to GP tables?
I've been looking at possibly speeding up some of the GP reports by adding new indexes and/or stats to our GP tables. Are there any special concerns as far as custom indexes/stats with GP tables beyond the usual SQL Server tuning considerations? I have done just that at several client sites and it works great. The concerns are two standard ones: 1. Keep a script used to build the indexes. When you upgrade, update, or rebuild the tables, GP will not re-add your indexes. 2. Too many indexes on a table will slow down database updates. -- Richard L. Whaley Author / Consultant / ...

query returns too few or too many results
I have built a multi-table select query and right now all the relationships between tables are the default "show each record in tblXXX for each record in tblYYY". When I run the query, I get only one record and it is repeated eight times. If I start changing some of the table relationships to INNER JOIN's or OUTER JOIN's, then I start getting the other records, but most of them are repeated at least once. I do not have any additional data connected to these records, so I am not sure why it is being displayed numerous times. I must admit I do not fully understand all about J...