Post reply

Why is this view slow ( takes 1:38 to select all records )

  • November 3, 2015 at 12:20 pm

    #399270

    The code for the view is attached ( see txt file )

    I have added many indexes yet it is slow.

    Any help is appreciated

    TABLE: 'DirectoryProviderHospitals'

    PK_DirectoryProviderHospitalsclustered, unique, primary key located on PRIMARYPNPID

    TABLE: 'DirectoryProviderSpecialties'

    PK_DirectoryProviderSpecialtiesclustered, unique, primary key located on PRIMARYPNPID

    TABLE: 'CountyCode'

    PK_CountyCodeclustered, unique, primary key located on PRIMARYCountyCode

    TABLE: PROVIDERS

    CREATE INDEX INDX_PROVIDERS_CountyCode on PROVIDERS ( CountyCode );

    CREATE INDEX INDX_PROVIDERS_PNPID on PROVIDERS ( PNPID );

  • November 3, 2015 at 12:26 pm

    #1837466

    Indexes aren't going to help much when you're doing select * from view. Indexes are useful when filtering data.

    You have scalar functions, they're slow

    You have unions, which are forcing SQL to materialise the view and sort/distinct sort the entire thing. That's slow.

    Ditch the scalar functions. If the data in the three queries are distinct, replace the union with union all. As it is though, it's going to be slow.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 3, 2015 at 2:01 pm

    #1837480

    Further on Gail's excellent answer, can you post the actual execution plan and preferably the DDL (create scripts) for all tables, functions and indices?

    😎

    First glance at the code suggests that the functions are the main culprits second by the UNION just as Gail mentioned, most likely these functions can be rewritten as inline table value functions delivering the same results in a fraction of the time needed for the scalar functions.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply