Optimizing query

I have a query where the execution plan is showing a table scan with 32% 
cost.

I have tried to put indexes on pretty much all the fields and still get the 
same result.

The predicate from the Query Analysers execution plan for this table is:

[dbo].[REVIEWER].[EnvironmentID]=[@EnvironmentID] AND
([@NameChoice] IS NULL OR CASE WHEN 
isnull([dbo].[REVIEWER].[FirstName],'')<>'' THEN
([dbo].[REVIEWER].[LastName]+', ')+[dbo].[REVIEWER].[FirstName] ELSE
CASE WHEN isnull([dbo].[REVIEWER].[FirstName],'')=''
THEN [dbo].[REVIEWER].[LastName] ELSE NULL END END
like [@NameChoice]+'%')

And the query is:

 SELECT RID, EnvID, RType, Type, FullType, RName, EmailAddress, 
MemberMissing
 FROM (
   SELECT
     ReviewerID as RID
    ,EnvironmentID as EnvID
    ,@reviewerType as RType
    ,'R' as Type
    ,'Reviewer' as FullType
    ,Case
     When isnull(FirstName, '') <> '' then LastName + ', ' + FirstName
     When isnull(FirstName, '') =  '' then LastName
     End as RName
    ,EmailAddress
    ,'' as MemberMissing
   FROM REVIEWER
   WHERE EnvironmentID = @EnvironmentID AND
      (@TypeChoice IS NULL OR @TypeChoice = @reviewerType)
  ) as a
  Where (@NameChoice IS NULL OR (RName LIKE @NameChoice + '%'))

I have indexes on:

EnvironmentID
ReviewerID
EnvironmentID + ReviewerID
LastName
FirstName

There is no Primary Key - and no clustered index.  The ReviewerID is unique 
(and really should be the Primary Key) but it isn't.

Would it be better to make the indexes unique by changing some of the keys 
to something like:

LastName + ReviewerID

This would make the index a little bigger and not sure if it would help the 
searches or not by much.

But why isn't it using any of the indexes?

Thanks,

Tom 


0
tshad
3/9/2010 7:06:36 PM
sqlserver.programming 1873 articles. 0 followers. Follow

4 Replies
912 Views

Similar Articles

[PageSpeed] 49

I'm guessing that the time is being consumed with the string parsing
that you're doing for each row that is being processed.  Instead of
using the case statement in your where clause, parse out the
@NameChoice into @LastName and @FirstName before the statement by
splitting it at the comma, then do the like comparisons against the
last name and the first name separately in your where clause, which
will take advantage of the indexes on those columns.  When you combine
them, the indexes on those columns are ignored.

Having indexes on multiple fields isn't helping you either as the
optimizer will only select one index to use.  You're better off having
an index on LastName, FirstName (with both fields in the same index)
than one on lastname and another on firstname.  Since it looks like
EnvironmentID is required by your WHERE clause, you could do an index
on EnvironmentID, LastName, FirstName for more speed.  Maybe even
throw ReviewID in there either before the firstname or after it,
depending on which is more likely to be included in the criteria.
That should help.

I think the most important thing to do is to parse out the @NameChoice
into @LastName and @FirstName before your query though.  It will be
only one parse as opposed to one parse per row in your table.

-Eric Isaacs
0
Eric
3/9/2010 7:48:26 PM
tshad (tfs@dslextreme.com) writes:
>  SELECT RID, EnvID, RType, Type, FullType, RName, EmailAddress, 
> MemberMissing
>  FROM (
>    SELECT
>      ReviewerID as RID
>     ,EnvironmentID as EnvID
>     ,@reviewerType as RType
>     ,'R' as Type
>     ,'Reviewer' as FullType
>     ,Case
>      When isnull(FirstName, '') <> '' then LastName + ', ' + FirstName
>      When isnull(FirstName, '') =  '' then LastName
>      End as RName
>     ,EmailAddress
>     ,'' as MemberMissing
>    FROM REVIEWER
>    WHERE EnvironmentID = @EnvironmentID AND
>       (@TypeChoice IS NULL OR @TypeChoice = @reviewerType)
>   ) as a
>   Where (@NameChoice IS NULL OR (RName LIKE @NameChoice + '%'))
> 
> I have indexes on:
> 
> EnvironmentID
> ReviewerID
> EnvironmentID + ReviewerID
> LastName
> FirstName
 
As the query is written, there is not much more you can do about it.
Only the index on EnvironmentID is really useful - and only if it's
selective enough. 

If you want better speed when you have a NameChoice, you would need
to persist the CASE expression and index it. Furthermore you would
need an IF statement to selevt between two different SELECT depending
on input.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
3/9/2010 11:09:32 PM
With a 32% of the total cost, there is probably nothing that you can do 
about it and part of it is because it's not worth it.  As a rule of thumb, 
whenever the optimizer determines that a query will return 10% or more of 
the total number of row of a table, it will choose to do a table scan 
because the alternative will cost more in term of I/O and CPU: looking at a 
table heap through an index is a costly operation and it's simply not worth 
it to do it instead of making of a full table scan if the number of rows to 
be looked at is to great.

The rule is slightly different for a clustered index but the basic principle 
is the same but in this case, the execution plan will indicate a table scan 
even if it's only a partial table scan.  However, you did explicitely 
mention in your post that there were no clustered index on your table.

In this case, the execution plan shows that the table scan is taking 32% of 
the total costs.  Without knowing the total numbers of rows retrieved and in 
the table, it's impossible to say but it's quite possible that a table scan 
is used here simply because the alternative is not worth it because either 
the table is too small or the percentage of retrieved rows to great.

You have EnvironId and (EnvironId + ReviewerID) as two indexes; one of these 
two is probably useless.  The case of the index on the single field ReviewID 
is different because ReviewerID comes in second for the composite index 
(EnvironId + ReviewerID).

If you're really desperate for using an index, one possibility might be to 
try with a index on (EnvironId, LastName, FirstName).  This way, SQL-Server 
can determine is a row will be part of the result by looking exclusively at 
the index without touching the table heap.  However, at 32% of the total 
cost for the table scan, I won't be surprised if even this changes nothing.

-- 
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server 
(French)


"tshad" <tfs@dslextreme.com> wrote in message 
news:%23SNXku7vKHA.1984@TK2MSFTNGP05.phx.gbl...
>I have a query where the execution plan is showing a table scan with 32% 
>cost.
>
> I have tried to put indexes on pretty much all the fields and still get 
> the same result.
>
> The predicate from the Query Analysers execution plan for this table is:
>
> [dbo].[REVIEWER].[EnvironmentID]=[@EnvironmentID] AND
> ([@NameChoice] IS NULL OR CASE WHEN 
> isnull([dbo].[REVIEWER].[FirstName],'')<>'' THEN
> ([dbo].[REVIEWER].[LastName]+', ')+[dbo].[REVIEWER].[FirstName] ELSE
> CASE WHEN isnull([dbo].[REVIEWER].[FirstName],'')=''
> THEN [dbo].[REVIEWER].[LastName] ELSE NULL END END
> like [@NameChoice]+'%')
>
> And the query is:
>
> SELECT RID, EnvID, RType, Type, FullType, RName, EmailAddress, 
> MemberMissing
> FROM (
>   SELECT
>     ReviewerID as RID
>    ,EnvironmentID as EnvID
>    ,@reviewerType as RType
>    ,'R' as Type
>    ,'Reviewer' as FullType
>    ,Case
>     When isnull(FirstName, '') <> '' then LastName + ', ' + FirstName
>     When isnull(FirstName, '') =  '' then LastName
>     End as RName
>    ,EmailAddress
>    ,'' as MemberMissing
>   FROM REVIEWER
>   WHERE EnvironmentID = @EnvironmentID AND
>      (@TypeChoice IS NULL OR @TypeChoice = @reviewerType)
>  ) as a
>  Where (@NameChoice IS NULL OR (RName LIKE @NameChoice + '%'))
>
> I have indexes on:
>
> EnvironmentID
> ReviewerID
> EnvironmentID + ReviewerID
> LastName
> FirstName
>
> There is no Primary Key - and no clustered index.  The ReviewerID is 
> unique (and really should be the Primary Key) but it isn't.
>
> Would it be better to make the indexes unique by changing some of the keys 
> to something like:
>
> LastName + ReviewerID
>
> This would make the index a little bigger and not sure if it would help 
> the searches or not by much.
>
> But why isn't it using any of the indexes?
>
> Thanks,
>
> Tom
> 


0
Sylvain
3/10/2010 3:41:17 AM
Try this article by Gail Shaw

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Dave Ballantyne
http://sqlblogcasts.com/blogs/sqlandthelike/

tshad wrote:
> I have a query where the execution plan is showing a table scan with 32% 
> cost.
> 
> I have tried to put indexes on pretty much all the fields and still get the 
> same result.
> 
> The predicate from the Query Analysers execution plan for this table is:
> 
> [dbo].[REVIEWER].[EnvironmentID]=[@EnvironmentID] AND
> ([@NameChoice] IS NULL OR CASE WHEN 
> isnull([dbo].[REVIEWER].[FirstName],'')<>'' THEN
> ([dbo].[REVIEWER].[LastName]+', ')+[dbo].[REVIEWER].[FirstName] ELSE
> CASE WHEN isnull([dbo].[REVIEWER].[FirstName],'')=''
> THEN [dbo].[REVIEWER].[LastName] ELSE NULL END END
> like [@NameChoice]+'%')
> 
> And the query is:
> 
>  SELECT RID, EnvID, RType, Type, FullType, RName, EmailAddress, 
> MemberMissing
>  FROM (
>    SELECT
>      ReviewerID as RID
>     ,EnvironmentID as EnvID
>     ,@reviewerType as RType
>     ,'R' as Type
>     ,'Reviewer' as FullType
>     ,Case
>      When isnull(FirstName, '') <> '' then LastName + ', ' + FirstName
>      When isnull(FirstName, '') =  '' then LastName
>      End as RName
>     ,EmailAddress
>     ,'' as MemberMissing
>    FROM REVIEWER
>    WHERE EnvironmentID = @EnvironmentID AND
>       (@TypeChoice IS NULL OR @TypeChoice = @reviewerType)
>   ) as a
>   Where (@NameChoice IS NULL OR (RName LIKE @NameChoice + '%'))
> 
> I have indexes on:
> 
> EnvironmentID
> ReviewerID
> EnvironmentID + ReviewerID
> LastName
> FirstName
> 
> There is no Primary Key - and no clustered index.  The ReviewerID is unique 
> (and really should be the Primary Key) but it isn't.
> 
> Would it be better to make the indexes unique by changing some of the keys 
> to something like:
> 
> LastName + ReviewerID
> 
> This would make the index a little bigger and not sure if it would help the 
> searches or not by much.
> 
> But why isn't it using any of the indexes?
> 
> Thanks,
> 
> Tom 
> 
> 
0
Dave
3/10/2010 9:11:35 AM
Reply:

Similar Artilces:

Working Days Query
I was looking to work out which of my reports were late by 60/90 days and someone kindly suggested to use < Date() -60 And < Date() -90 in the criteria. This is great, however i am looking for a way for it to pick up working days and (missing out weekends), is there anyway of doing this? Many thanks If you search the forum there are threads on help for missing out weekends. -- http://www.ready4mainstream.ny911truth.org/index.html "Cathy" wrote: > I was looking to work out which of my reports were late by 60/90 days and > someone kindly suggested to use <...

MS Query and SQL
I am trying to write a query where the following columns are present: Item.Description Item.Price Department.Name Supplier.Name The problem is that when I set up the joins in MS Query, the records that don't have an associated supplier assigned are not returned. When I try to change the join type on Item.SupplieID-Supplier.ID, it says outer joins are not allowed with more than two tables in the query. Any ideas on how I can get it to return all records even if there is no supplier? Thanks, Jason I'm not familiar with MSQuery, but if it will let you insert the SQL command ...

Calculation query, how to avoid extreme values?
Good morning, I have 1 table with a few fields (A, B, C) that I'm using for various calculations. For various reasons are some of the input "weird" and I want to leave that out of the calculation (average). For example, Field A requires > 0 AND < 100, B < 400 Now, if one record doesn't fullfil A requirement I don't want to use it for A's average calculation BUT I want to use it for B's average calculation (if it satisfy B's requirement). This means that if I use the query criteria A: >0 AND <100 it will filter out all other records an...

use a results in a Query to compare with information in a table
This is what I am trying to accomplish: I do a Query on one table that returns a result of the combined time and number of parts produced for that time. Then I get an expression that results in number of parts produced per hour. This is all good but now I need it to compare the “part number” returned in the Query with the “part number” in the Parts table and compare the “parts per hour produced” with the “parts per hour estimated” and return as % efficient. How do I that information from the parts table? Thanks for any help. Access 2007 We're not there, we can't see your...

How do I use same date parameters for different queries and report
I'm having the same problem as Gavelle described below. That is: I've followed the advice and set up a form to have my queries and reports pick the dates from the form. But the queries/reports are still asking me to enter the beginning and ending date parameters for each query/report. Also, when i set up the form it runs the query correctly. But when i try to run the query (or report based off the query) I only get a general parameter box stating Enter Parameter Value "forms!FORMNAME!StartDate and another for the end date. Everything in my query criteria is typed correc...

Date() frustration within query builder
hi.. I have created an application which scans barcoded mail items and injects results into a.n.other table. As part of v2 I'm trying to offer even more usability/funtionality. Basically at the end of the day when the operator produces his/her 'dayend' tasks I want to offer them a button on the main form which, when clicked runs a query which shows a total for today of pieces of mail type "blah" - in this case, there's only a choice of two mail types. goneaways & prizedraws. http://pastebin.com/m2f6bec45 - this works fine when manually entering the date in the...

Can we create multiple pivot tabel views for a single access query?
For an access query there is an option to design pivot table using pivot table view. Now I want to create multiple such views for the same query. Is it possible or do I need to copy this query again into new query to create different pivot table view? A Womand Told Me http://www.awomantoldme.com A query can "remember" the last pivot view that you created, just like the last filter. So, I'll agree that to help solve your problem, create a seperate query, then tailor the pivot view for each instance. Fwiw, I've tried to find where the pivot setup info is stored, but nob...

Multiple Values in a single field with Microsoft Query
Hi Everyone, Is there a way to have multiple values in one cell that will pass into a Microsoft Query. What I'm trying to do is enter multiple numbers (4567, 1234) into cell B3 (they won't be sequential so can use >,< etc.) and get the query to run. I've posted the SQL below for my query. Just wondering if this was possible or not without having to purchase add ins. SELECT VDN_Data.Date, VDN_Data.dvdnVDN, VDN_Data.`VDN Name`, VDN_Data.`Inbound Calls`, VDN_Data.`ACD Calls`, VDN_Data.`Aban Calls` FROM `TeamStatsv1`.VDN_Data VDN_Data WHERE (VDN_Data.Date Between B1 And B2) AND ...

Problems Query with MSQuery
Hell I try to Query from CSV file to Excel worksheet using MSQuery One of the fields contains both numeric and text data but MSQuery return to Excel only the numeric data What can i do to fix the problem The following article on Dick Kusleika's weblog may help (from a post by onedaywhen): http://www.dicks-blog.com/excel/2004/06/external_data_m.html#trackback IZI wrote: > Hello > > I try to Query from CSV file to Excel worksheet using MSQuery. > > One of the fields contains both numeric and text data but MSQuery return to Excel only the numeric data. > > ...

Running an access query
hi, I have a table with multirecords, I would like to put the data in one row, for example, I have a customer who has address 1, address 2 and address 3, each address creates 1 record, how do I run a query that return the customer name and address 1, address 2 and address 3 on one line? -- armele look So your table looks like: Customer Address Bob Bob's Address #1 Bob Bob's Address #2 Bob Bob's Address #3 ???? Are there any other fields in the table? "Armele look" wrote: > hi, &...

ADO: Help build this query in MS Access
Hello I have a very large table in a MS Access database. It has various fields in it. (Eg. Field1, Field2, Field3, Field4, Field5) All I'm trying to do is to run a query that concatenates the values in 3 fields and returns a recordset. In other words, I want a recordset which has only one field and that field contains the values as follows field2+field3+field4. Example: Field1 Field2 Field3 Field4 Field5 1 AA XX PQR 112 2 BB XX STS 234 3 CC XX QQQ 234...

new web query ( import data )
I knoticed that you can download tables into excel from a URL or web site. mutiple tabples can be selected, however when I selected tables from a different part of the URL during the same web query it only downloaded one of the selections not all of them. Is there anyway you can download mutiple tables with out conducting many new web queries? thanks Josh ...

Database Queries/Date Formats
I am importing columns from an Access table using MS Database Query. The date format in the database is YYMMDD, so the query brings the date into the spreadsheet as the same. (ex. 880321). When I attempt to format the cells as a date in Excel, the formatting doesn't work, and the date stays in the same format. Why? And how can I get around it using Excel? Thanks. Dave Check the Access table, is the Date field set to a Date/Time datatype? My guess is not. You can remedy this in Excel or in Access with a date formula. P "daver676" <daver676@yahoo.com> wrote in me...

can't get query to group
here is the sql. can someone help me determine why the query isn't grouping by GoupNumber? The GroupName is the same for the GroupNumber. ID is unique. SELECT Mid([ID],3,2) AS GroupNumber, Accounts.GroupName FROM Accounts GROUP BY Mid([ID],3,2), Accounts.GroupName, Accounts.ID HAVING (((Accounts.ID) Like "01*")); Example ID GroupNumber GroupName 010101 01 red 010102 01 red 010201 02 blue 010301 03 ...

Query combined with formula
Hi, I have made a query, that gets the parameter from my cell A11. There is always only one result, which is written i cell B11. Now i wish to copy that formula from A11 to the area A12:A3000, so it picks up the parameter from the row, where the formula is placed. The result should be, that a query request data based on A11 and spit it out in B11; another query uses A12 and spits out in B12 (...) Now I need it for now in 3000 rows, and that might expande drasticly over time, so how do I easy copy it down? Regular copy/paste only copy the formula or value of the cells, not the queries... ...

Export query from a switchboard
I'm somewhat new to Access. Thanks for the assist in advance. I have a database where I would like to graph the results of a query in EXCEL. The Graph does not appear well within the Chart function of a report. At present I run the query and copy and past into EXCEL and then create my chart. I would like to place a command on my switchboard menu that will export the query to EXCEL. I know if I can create a macro or a VBA module, I can so this from the menu I've created. I have unsuccessfully attempted to create a macro and I don't know how to code in order to do export. I w...

MS Query
Hi thanks for any advice you can offer. Attempting to build a pivot table using external data, connecting to a Microsoft SQL Server. I paste the SQL that I use in Enterprise Mgr directly into MS Query. When I execute the query, the dataset is displayed in MS Query - all 107,000 records. When I ask MS Query to return the data to Excel, I get this message: "Problems Obtaining Data." Help. Thanks, gt Which version of excel are you using? All versions except 2007 have a limit of 65536 rows and cannot present your data that exceeds 100000 rows On Oct 26, 10:07 pm, gtgtgt <g...

Using Option Group to select a range of Queries
Hi there, I'm trying to set a form up to select a range of columns in which I'd like to search for a specific string using presaved queries. I'm thinking of having 10 text boxes, each displaying a single line of the received data. I was then going to have to columns of radio buttons to signify the start and end of the search. The user would then be able to select 'Start button' 3 and 'end button 5', which, after clicking on the 'go' button, would run the 'search column 3', 'search column 4' and 'search column 5' queries. If the Star...

program to make Tables and Queries becomes READ ONLY
Anyone out there knows how to write a code to make the Tables and Queries to be "READ ONLY" in a database, so when we convert that database to a "MDE" mode, then everthing is secured? Your help is much appreciated. No, there isn't. If you use only queries, and have DISTINCT in each of the queries, you won't be able to update the data through the forms. However, you'll still be able to get to the tables by linking to the database. Note that your application should be split into a front-end (containing the queries, forms, reports, macros and modules), li...

help with 2 queries to compare and delete from same source table
(This may sound complicated, please bear with me...) I have a quiz results form that shows the number of times each incorrect answer has been submitted via a query. The query gets 'incorrect score results' from a table called 'tbl_Final_Points_Test'. It uses the unique ID for each answer as a COUNT total which gives me the total number of times each Incorrect answer has been chosen, and it uses the criteria '0' for displaying only incorrect answers. The Problem This all works fine, except as time goes by, the number of each total incorrect entry increases (as it...

"Totals" button in query
While trying to figure out a vacationing coworker's query, I find that he's got a "Total" row in the quey grid, and I discovered that by clicking on what looks like the greek letter sigma on the toolbar, that it toggles this row on/off. Suboptions are Group By, Sum, Avg, etc. but I can't tell from executing the query that they do anything. All his "Total" values are "Group By." Thanks for any insight. A "Totals" query is a Select query that is doing some aggregate calculations on the data selected: Sum/Average/Count the number of rec...

Need help!
Hi all: I am trying to extract some data from an Access query using MS Query while in Excel. The access query will extract the data from the data source without any problems. (I can even cut and past the data from the access query to the excel spreadsheet.) The problem arises when I go to refresh the data in the excel spreadsheet using the MS query. I get the following error message: "[Microsoft][ODBC Microsoft Access Driver] Invalid character value for cast specification on column number 4 (SumofSumofTransValue)" Can anyone shed some light on this problem? Everything wo...

Basic Select Query Duplicating a single end user's records
I am stumped with a particular problem. My table consists of hundreds of records that are directed to their associated end user. This is my simple Select query shown here: "SELECT [Name Tbl].Mgr, [Name Tbl].Name, [Data Tbl].Survey_CNT, FROM Manager INNER JOIN ([Name Tbl] INNER JOIN [Data Tbl] ON [Name Tbl].ID# = [Data Tbl].Name) ON Manager.OM = [Name Tbl].Mgr" Inside the Data Tbl, one Name shows 4 physical records. However, in this simple query it has duplicated one Name's records so now there is 8 for that one particular name. The other Names show a correct m...

sums on a query
Hello, I’m building a query that I would like to sum quantities of a part numbers used on multiple Purchase Orders Example PO Item # qty sum 16 100 2 5 27 100 1 5 39 100 2 5 19 200 2 7 26 200 5 7 Thanks -- JB Use the 'Totals' button in the query designer (see toolbar in query, look for the E-sign) Then group the field you want for instance the 'item' field, just above the criteria field use the arrow for the group by.. hth -- Maurice Ausum "matjcb" wrote: > Hello, > I’m building a query that I would like to sum quantities of a part num...

how to transfer a T-SQL query to a semantec query and create the M
How to transfer lots of T-SQL input from users or files to a semantec query used in Reporting Service of SQL Server 2005 and create the MSDL file accordingly? ...