How can I speed up a clustered index scan on 1.6 million rows?

Question

I have a table with over 1.6 million records. (Create statement below.) I join to it in a query, using the Primary key, and the query takes 4 seconds, with 61% of that used on a Clustered index scan on this table.

Here is a sample of the query:

DECLARE     @pintDueBy              DATETIME = '1/1/1900',
    @pintCounty             INT = 0

SET @pintDueBy = N'10/30/14'
SET @pintCounty = 42043

SELECT 
ISNULL(Eqp.ELOFLNUM,0) AS ELOFLNUM
,ISNULL(Eqp.ELERPNUM, 0) AS ELERPNUM
,ISNULL(CASE
            WHEN Eqp.ELERPNUM IS NULL OR Eqp.ELERPNUM = 0 THEN Eqp.ELOFLNUM
            ELSE Eqp.ELERPNUM
        END, 0) AS OwnFLNUM
,ISNULL(RpOwn.T_AS400_ELVPOWN00_ID,0) AS RespOwn_ID
,ISNULL(Eqp.ELBLDGCD,0) AS ELBLDGCD
,ISNULL(Eqp.ELEEQNO,0) AS ELEEQNO 
,ISNULL(Eqp.ELPERMTNO,0) AS ELPERMTNO
,ISNULL(Eqp.ELEPERMID,'') AS ELEPERMID
,ISNULL(ELEYCEXP,0) AS ELEYCEXP
,ISNULL(ELEMCEXP,0) AS ELEMCEXP
, BOISdbo.UDF_PARSE_DATE_FIELDS(NULL,ELEYCEXP,ELEMCEXP,99) AS ELECertExp
, BOISdbo.UDF_PARSE_DATE_FIELDS(NULL,LastInsp.ELDTINSYY,LastInsp.ELDTINSMM,LastInsp.ELDTINSDD) AS LastSeen
, ISNULL(LastInsp.ELERSLT1, '') AS LastSeenResult
,Eqp.T_AS400_ELVPEQP00_ID AS EqpID 
,ISNULL(Eqp.ELEECOCD_ID,0) AS ELEECOCD_ID
,ISNULL(Eqp.ELEEORIG_ID,0) AS ELEEORIG_ID
,ISNULL(Eqp.LAST_INSPTN_ID,0) AS LAST_INSPTN_ID
    , ISNULL(LastInsp.INVID,0) AS INVID
FROM dbo.T_AS400_ELVPEQP00              AS Eqp
    LEFT JOIN dbo.T_AS400_ELVPLOC00         AS Loc
        ON Eqp.ELBLDGCD = Loc.ELBLDGCD
    LEFT JOIN dbo.T_AS400_ELVPOWN00         AS RpOwn
        ON CASE
                    WHEN Eqp.ELERPNUM IS NULL OR Eqp.ELERPNUM = 0 THEN Eqp.ELOFLNUM
                    ELSE Eqp.ELERPNUM
                END = RpOwn.ELOFLNUM
    LEFT JOIN dbo.T_AS400_ELVPINS00             AS LastInsp
        ON Eqp.LAST_INSPTN_ID = LastInsp.T_AS400_ELVPINS00_ID
    LEFT JOIN BOISdbo.UV_COUNTY_LIST AS cnty 
        ON cnty.REF_VALUE = Loc.ELLCNTY 
WHERE 
    Loc.ELLSTATS IN ('O') -- O - Occupied
    AND 
    ISNULL(LastInsp.ELERSLT1, '') NOT IN ('O') -- O - Out of Service
    AND
    (@pintCounty = 0 OR cnty.REF_VALUE = @pintCounty)

My output list for this query only uses 6 of the fields:

[BOIS].[dbo].[T_AS400_ELVPINS00].ELDTINSYY
[BOIS].[dbo].[T_AS400_ELVPINS00].ELDTINSMM
[BOIS].[dbo].[T_AS400_ELVPINS00].ELDTINSDD
[BOIS].[dbo].[T_AS400_ELVPINS00].ELERSLT1
[BOIS].[dbo].[T_AS400_ELVPINS00].T_AS400_ELVPINS00_ID
[BOIS].[dbo].[T_AS400_ELVPINS00].INVID

Any ideas?

CREATE TABLE [dbo].[T_AS400_ELVPINS00](
    [ELBLDGCD] [int] NULL,
    [ELEEQNO] [int] NULL,
    [ELDTINSYY] [int] NULL,
    [ELDTINSMM] [int] NULL,
    [ELDTINSDD] [int] NULL,
    [ELINSCD] [varchar](6) NULL,
    [ELFEECO] [varchar](6) NULL,
    [ELIINSR] [varchar](6) NULL,
    [ELERSLT1] [varchar](1) NULL,
    [ELDTIBYY] [int] NULL,
    [ELDTIBMM] [int] NULL,
    [ELDTIBDD] [int] NULL,
    [ELECRDTYY] [int] NULL,
    [ELECRDTMM] [int] NULL,
    [ELECRDTDD] [int] NULL,
    [ELTINSP] [varchar](1) NULL,
    [ELECNUM] [int] NULL,
    [ELEFEE] [decimal](18, 0) NULL,
    [ELEBILL] [varchar](1) NULL,
    [ELDUPCT] [varchar](1) NULL,
    [ELDTDUPYY] [int] NULL,
    [ELDTDUPMM] [int] NULL,
    [ELDTDUPDD] [int] NULL,
    [ELEFLG] [varchar](1) NULL,
    [ELFORCD] [varchar](1) NULL,
    [ELEINV] [varchar](8) NULL,
    [ELELSEAL] [varchar](1) NULL,
    [ELELSDTY] [int] NULL,
    [ELELSDTM] [int] NULL,
    [ELELSDTD] [int] NULL,
    [ELELSNUM] [int] NULL,
    [ELREMVYY] [int] NULL,
    [ELREMVMM] [int] NULL,
    [ELREMVDD] [int] NULL,
    [ELEDFNCS] [varchar](1) NULL,
    [ELEDFNCDA] [varchar](5) NULL,
    [ELERFLDA] [varchar](1) NULL,
    [ELEDFNCDB] [varchar](5) NULL,
    [ELERFLDB] [varchar](1) NULL,
    [ELEDFNCDC] [varchar](5) NULL,
    [ELERFLDC] [varchar](1) NULL,
    [ELEDFNCDD] [varchar](5) NULL,
    [ELERFLDD] [varchar](1) NULL,
    [ELEDFNCDE] [varchar](5) NULL,
    [ELERFLDE] [varchar](1) NULL,
    [ELEDFNCDF] [varchar](5) NULL,
    [ELERFLDF] [varchar](1) NULL,
    [ELEDFNCDG] [varchar](5) NULL,
    [ELERFLDG] [varchar](1) NULL,
    [ELEDFNCDH] [varchar](5) NULL,
    [ELERFLDH] [varchar](1) NULL,
    [ELEDFNCDI] [varchar](5) NULL,
    [ELERFLDI] [varchar](1) NULL,
    [ELEDFNCDJ] [varchar](5) NULL,
    [ELERFLDJ] [varchar](1) NULL,
    [ELEDFNCDK] [varchar](5) NULL,
    [ELERFLDK] [varchar](1) NULL,
    [ELEDFNCDL] [varchar](5) NULL,
    [ELERFLDL] [varchar](1) NULL,
    [ELEDFNCDM] [varchar](5) NULL,
    [ELERFLDM] [varchar](1) NULL,
    [ELEDFNCDN] [varchar](5) NULL,
    [ELERFLDN] [varchar](1) NULL,
    [ELEDFNCDO] [varchar](5) NULL,
    [ELERFLDO] [varchar](1) NULL,
    [ELEDFNCDP] [varchar](5) NULL,
    [ELERFLDP] [varchar](1) NULL,
    [ELEDFNCDQ] [varchar](5) NULL,
    [ELERFLDQ] [varchar](1) NULL,
    [ELEDFNCDR] [varchar](5) NULL,
    [ELERFLDR] [varchar](1) NULL,
    [ELEDFNCDS] [varchar](5) NULL,
    [ELERFLDS] [varchar](1) NULL,
    [ELEDFNCDT] [varchar](5) NULL,
    [ELERFLDT] [varchar](1) NULL,
    [ELEIUPDTY] [int] NULL,
    [ELEIUPDTM] [int] NULL,
    [ELEIUPDTD] [int] NULL,
    [ELESCLTR] [varchar](1) NULL,
    [ELEDTCOY] [int] NULL,
    [ELEDTCOM] [int] NULL,
    [ELEDTCOD] [int] NULL,
    [ELDTORDY] [int] NULL,
    [ELDTORDM] [int] NULL,
    [ELDTORDD] [int] NULL,
    [ELDTRPRY] [int] NULL,
    [ELDTRPRM] [int] NULL,
    [ELDTRPRD] [int] NULL,
    [ELERPRCD] [varchar](1) NULL,
    [ELSTRHH] [int] NULL,
    [ELSTRMM] [int] NULL,
    [ELSTRSS] [int] NULL,
    [ELSTPHH] [int] NULL,
    [ELSTPMM] [int] NULL,
    [ELSTPSS] [int] NULL,
    [ELDTCLRYY] [int] NULL,
    [ELDTCLRMM] [int] NULL,
    [ELDTCLRDD] [int] NULL,
    [ELIUSER] [varchar](10) NULL,
    [ELIKEYID] [varchar](1) NULL,
    [T_AS400_ELVPINS00_ID] [int] IDENTITY(1,1) NOT NULL,
    [DOC_LINK_ID] [int] NULL,
    [CRTD_BY] [varchar](30) NOT NULL,
    [CRTD_DT] [datetime] NOT NULL,
    [MODFD_BY] [varchar](30) NULL,
    [MODFD_DT] [datetime] NULL,
    [INVID] [int] NULL,
    [PARENT_INSPTN_ID] [int] NULL,
    [ELEEQP_ID] [int] NULL,
    [COMMENTS] [varchar](1500) NULL,
    [ELFEECO_ID] [int] NULL,
    [T_AS400_ELVPAPP0_ID] [int] NULL,
 CONSTRAINT [PK_T_AS400_ELVPINS00] PRIMARY KEY CLUSTERED 
(
    [T_AS400_ELVPINS00_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Paste the [execution plan](http://stackoverflow.com/a/7359705/562459) into your question. — Mike Sherrill 'Cat Recall', Oct 31 '14 at 17:52
how are you limiting your records returned in your query? if you're selecting "all" of your primary key, i don't think you're going to get around a scan — Kritner, Oct 31 '14 at 17:52
Depending on your query, you could try to put an index on those six columns that you need - then maybe you'd get a cheaper, leaner nonclustered index scan instead of a clustered index (= table) scan — marc_s, Oct 31 '14 at 18:14
Why don't you at least create an index on the fields that you need? Maybe you need a basic indexing tutorial. — usr, Oct 31 '14 at 18:16
The column names are appalling. How do you work with such a schema?! — usr, Oct 31 '14 at 18:17
Column names are not my choice... :/ and yes... a headache to find what you're looking for. — M Kenyon II, Oct 31 '14 at 18:22
Ever seen a Microsoft Dynamics GP db schema? Same sort of crap (legacy table names - I think it used to run on a proprietary db with a column name limit) - nowt you can do to avoid a scan if you are returning all the rows, but you can make the query faster by not returning all the columns - depends what you need, can you post your execution plan? — Charleh, Oct 31 '14 at 18:23
Saved the execution plan, but it's too large to post here. Not sure how I can attach here on stackoverflow. — M Kenyon II, Oct 31 '14 at 18:26
Would it really be faster to create a non-clustered index for those fields? I thought joining on the primary key would be the fastest. — M Kenyon II, Oct 31 '14 at 18:32

score 0 · Answer 1 · answered Oct 31 '14 at 18:48

0

Where you have functions inline with your select:

BOISdbo.UDF_PARSE_DATE_FIELDS(NULL,ELEYCEXP,ELEMCEXP,99)

Replace those function calls with the actual code that is in the function....it will be far from pretty, but should perform better.

answered Oct 31 '14 at 18:48

How 'bout a Fresca

2,267
1
15
26

How can I speed up a clustered index scan on 1.6 million rows?

1 Answers1