How to Avoid Index Seek

I am try to optimize a query where the biggest performance loss results from a table spool (75% of total query).  Here is my query:


Select
      Min(Dss.Dbo.ConvertIntToDate(a.ClaimReceiveDate)) As ClaimReceiveDate,
      Min(Dss.Dbo.ConvertIntToDate(a.APPostingDate)) As APPostingDate,
      Min(      DSS.dbo.ConvertIntToDate(a.CheckDate)) As CheckDate,      
      Convert(Nvarchar(8),a.AdmitDate)+a.FormNbr As ClaimNbr
From
      DwPresentationProd.Dbo.ClaimsDetail  a
            Inner Join DwPresentationProd.Dbo.PayStatus b
                  On a.PayStatusSeqNbr = b.PayStatusSeqNbr
Where
      a.PayStatusSeqNbr <> 0
Group By
       a.AdmitDate,a.FormNbr
TiggamiruAsked:
Who is Participating?
 
ShogunWadeConnect With a Mentor Commented:
In addition to SashP's remark  SQL Server will SCAN if the distribution of the data suggests that other methods will be inefficient.

eg:

Lets say you have an indexed int field in a table will 10,000 rows.   5,000 of the ints are = 1  and the rest are not.

if you did a select * from table1 where myint=1   it is highly likely that a table scan will be conducted.
0
 
ShogunWadeCommented:
what indexes do you have on the tables?  what table is spooling ?
0
 
SashPCommented:
Do indexes exist on the columns that are used in the join

a.PayStatusSeqNbr and b.PayStatusSeqNbr
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
SashPCommented:
I would also be inclined to use

SELECT
    q.ClaimNbr,
    Dss.Dbo.ConvertIntToDate(q.ClaimReceiveDate) ClaimReceiveDate,
    Dss.Dbo.ConvertIntToDate(q.APPostingDate) APPostingDate,
    Dss.Dbo.ConvertIntToDate(q.CheckDate) CheckDate
FROM
(
Select
     Convert(Nvarchar(8),a.AdmitDate)+ a.FormNbr As ClaimNbr,
     Min(a.ClaimReceiveDate) As ClaimReceiveDate,
     Min(a.APPostingDate) As APPostingDate,
     Min(a.CheckDate) As CheckDate
From
     DwPresentationProd.Dbo.ClaimsDetail  a
          Inner Join DwPresentationProd.Dbo.PayStatus b
               On a.PayStatusSeqNbr = b.PayStatusSeqNbr
Where
     a.PayStatusSeqNbr <> 0
Group By
      a.AdmitDate,a.FormNbr
) as q

0
 
SashPCommented:
>Posted to Feedback
>
>I have indexes on PayStatusSeqNbr for these to tables. Your query does 77% of an clustered index SCAN (11,669,436 rows) and 20% of Hash Match/Aggregate.
>My query does 74% of an clustered index SCAN (11,669,436 rows), 12% match/inner join and 14% of Hash Match/Aggregate.
>Which of the Hash Match/Aggegate and Match/Inner Join is less costly?
>And how can I get an index SEEK instead of an index SCAN given the number of rows to access.
>Thanks





0
 
SashPConnect With a Mentor Commented:
How many rows in the source table match a.PayStatusSeqNbr <> 0

select count(*) from DwPresentationProd.Dbo.ClaimsDetail  where a.PayStatusSeqNbr <> 0

my reason for the question is that the database engine will perform a table scan if the indexed field will not exclude enough rows the execution plan will utilise a table scan.
0
All Courses

From novice to tech pro — start learning today.