IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

Modifier un plan d'exécution


Sujet :

Développement SQL Server

  1. #1
    Rédacteur/Modérateur

    Avatar de Fabien Celaia
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    octobre 2002
    Messages
    4 195
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 51
    Localisation : Suisse

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Service public

    Informations forums :
    Inscription : octobre 2002
    Messages : 4 195
    Points : 19 417
    Points
    19 417
    Billets dans le blog
    25
    Par défaut Modifier un plan d'exécution
    J'ai un cas un peu particulier : une requête écrite "avec les pieds" par un provider.

    En gros, la requête, c'est une table et 39 jointures externes...

    Bien évidemment, ça marche tant que les tables sont petites... après, c'est pourri...

    En réécrivant la requête (une SP avec 2 tables temporaires), on arrive a augmenter la vitesse d'exécution de 10x...

    En creusant un peu, en forçant des LOOP JOIN, on obtient des temps tout à fait acceptables (de l'ordre des 2 sec)

    Première question... pourquoi l'optimiseur ne choisit-il pas le loop join malgré des statistiques à jour ?

    Seconde question : je tente de forcer le Hint (soit le LOOP JOIN, soit le FAST 500) via Plan Guide... et je n'y arrive pas
    Je n'y arrive pas, ça veux dire que le hint n'est pas pris en compte, bien qu'il le soit si je lance en interactif.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    EXEC sp_create_plan_guide @name = N'FC_MON_TEST', 
    @stmt = N'select person0_.PersonId as PersonId1_43_0_, person0_.CreationTimestamp as Creation2_43_0_, person0_.CreationUser as Creation3_43_0_, person0_.FlagError as FlagErro4_43_0_, person0_.MunicipalityGlossaryId as Municipa6_43_0_, person0_.PersonType as PersonTy5_43_0_, midentific1_.PersonId as PersonId9_23_1_, midentific1_.IdentificatorId as Identifi1_23_1_, midentific1_.IdentificatorId as Identifi1_23_2_, midentific1_.CreationTimestamp as Creation2_23_2_, midentific1_.CreationUser as Creation3_23_2_, midentific1_.DeletionTimestamp as Deletion4_23_2_, midentific1_.DeletionUser as Deletion5_23_2_, midentific1_.PersonIdentificationType as PersonId6_23_2_, midentific1_.PersonId as PersonId9_23_2_, midentific1_.PersonIdentificator as PersonId7_23_2_, midentific1_.SystemIdentificator as SystemId8_23_2_, municipali2_.MunicipalityGlossaryId as Municipa1_35_3_, municipali2_.Canton as Canton2_35_3_, municipali2_.Name as Name3_35_3_, municipali2_.MunicipalityGlossaryNumber as Municipa4_35_3_, mnotes3_.PersonId as PersonId7_41_4_, mnotes3_.NoteId as NoteId2_41_4_, mnotes3_.NoteId as NoteId2_41_5_, mnotes3_.CreationTimestamp as Creation3_41_5_, mnotes3_.CreationUser as Creation4_41_5_, mnotes3_.Text as Text5_41_5_, mnotes3_.PersonId as PersonId7_41_5_, mpersontim4_.PersonId as PersonI54_44_6_, mpersontim4_.PersonDataId as PersonDa1_44_6_, mpersontim4_.PersonDataId as PersonDa1_44_7_, mpersontim4_.CreationTimestamp as Creation2_44_7_, mpersontim4_.CreationUser as Creation3_44_7_, mpersontim4_.ValidFrom as ValidFro4_44_7_, mpersontim4_.ValidUntil as ValidUnt5_44_7_, mpersontim4_.DeletionTimestamp as Deletion6_44_7_, mpersontim4_.DeletionUser as Deletion7_44_7_, mpersontim4_.PersonId as PersonI54_44_7_, mpersontim4_.ArmedForcesLiability as ArmedFor8_44_7_, mpersontim4_.ArmedForcesService as ArmedFor9_44_7_, mpersontim4_.ArmedForcesValidFrom as ArmedFo10_44_7_, mpersontim4_.CivilDefense as CivilDe11_44_7_, mpersontim4_.CivilDefenseValidFrom as CivilDe12_44_7_, mpersontim4_.ContactId as Contact55_44_7_, mpersontim4_.DataLock as DataLoc13_44_7_, mpersontim4_.DataLockValidFrom as DataLoc14_44_7_, mpersontim4_.DataLockValidUntil as DataLoc15_44_7_, mpersontim4_.DateOfBirth as DateOfB16_44_7_, mpersontim4_.DateOfBirthFormat as DateOfB17_44_7_, mpersontim4_.DateOfDeath as DateOfD18_44_7_, mpersontim4_.DateOfDeathTo as DateOfD19_44_7_, mpersontim4_.EventType as EventTy20_44_7_, mpersontim4_.ExtendedAttributesId as Extende56_44_7_, mpersontim4_.FireService as FireSer21_44_7_, mpersontim4_.FireServiceLiability as FireSer22_44_7_, mpersontim4_.FireServiceValidFrom as FireSer23_44_7_, mpersontim4_.FirstName as FirstNa24_44_7_, mpersontim4_.HealthInsuranceValidFrom as HealthI25_44_7_, mpersontim4_.HealthInsured as HealthI26_44_7_, mpersontim4_.InsuranceAddressId as Insuran57_44_7_, mpersontim4_.InsuranceName as Insuran27_44_7_, mpersontim4_.HouseholdNumber as Househo28_44_7_, mpersontim4_.LanguageOfCorrespondance as Languag29_44_7_, mpersontim4_.MaritalStatusId as Marital58_44_7_, mpersontim4_.MatrimonialInheritanceArrangement as Matrimo30_44_7_, mpersontim4_.MatrimonialInheritanceArrangementValidFrom as Matrimo31_44_7_, mpersontim4_.MoveInDate as MoveInD32_44_7_, mpersontim4_.MoveOutDate as MoveOut33_44_7_, mpersontim4_.MrMrsType as MrMrsTy34_44_7_, mpersontim4_.NationalityCountryId as Nationa59_44_7_, mpersontim4_.NationalityStatus as Nationa35_44_7_, mpersontim4_.NationalityValidFrom as Nationa36_44_7_, mpersontim4_.NormalizedFirstName as Normali37_44_7_, mpersontim4_.NormalizedOfficialName as Normali38_44_7_, mpersontim4_.OfficialName as Officia39_44_7_, mpersontim4_.PaperLock as PaperLo40_44_7_, mpersontim4_.PaperLockValidFrom as PaperLo41_44_7_, mpersontim4_.PaperLockValidUntil as PaperLo42_44_7_, mpersontim4_.PersonNameId as PersonN60_44_7_, mpersontim4_.PersonState as PersonS43_44_7_, mpersontim4_.PlaceOfBirthCountryId as PlaceOf61_44_7_, mpersontim4_.PlaceOfBirthForeignTown as PlaceOf44_44_7_, mpersontim4_.PlaceOfBirthMunicipalityId as PlaceOf62_44_7_, mpersontim4_.PlaceOfBirthUnknown as PlaceOf45_44_7_, mpersontim4_.PlaceOfDeathCountryId as PlaceOf63_44_7_, mpersontim4_.PlaceOfDeathForeignTown as PlaceOf46_44_7_, mpersontim4_.PlaceOfDeathMunicipalityId as PlaceOf64_44_7_, mpersontim4_.PlaceOfDeathUnknown as PlaceOf47_44_7_, mpersontim4_.Religion as Religio48_44_7_, mpersontim4_.ReligionValidFrom as Religio49_44_7_, mpersontim4_.ResidenceId as Residen65_44_7_, mpersontim4_.ResidencePermitId as Residen66_44_7_, mpersontim4_.ResidentState as Residen50_44_7_, mpersontim4_.RestrictedVotingAndElectionRightFederation as Restric51_44_7_, mpersontim4_.Sex as Sex52_44_7_, mpersontim4_.Title as Title53_44_7_, contact5_.ContactId as ContactI1_14_8_, contact5_.AddressId as AddressI4_14_8_, contact5_.PersonId as PersonId5_14_8_, contact5_.ValidFrom as ValidFro2_14_8_, contact5_.ValidUntil as ValidUnt3_14_8_, mailaddres6_.AddressId as AddressI2_1_9_, mailaddres6_.AddressLine1 as AddressL3_1_9_, mailaddres6_.AddressLine2 as AddressL4_1_9_, mailaddres6_.CountryCodeIso2 as CountryC5_1_9_, mailaddres6_.DwellingNumber as Dwelling6_1_9_, mailaddres6_.ForeignZipCode as ForeignZ7_1_9_, mailaddres6_.HouseNumber as HouseNum8_1_9_, mailaddres6_.Locality as Locality9_1_9_, mailaddres6_.NormalizedStreet as Normali10_1_9_, mailaddres6_.NormalizedTown as Normali11_1_9_, mailaddres6_.PostOfficeBoxNumber as PostOff12_1_9_, mailaddres6_.PostOfficeBoxText as PostOff13_1_9_, mailaddres6_.Street as Street14_1_9_, mailaddres6_.SwissZipCode as SwissZi15_1_9_, mailaddres6_.SwissZipCodeAddOn as SwissZi16_1_9_, mailaddres6_.SwissZipCodeId as SwissZi17_1_9_, mailaddres6_.Town as Town18_1_9_, mailaddres6_.FirstName as FirstNa19_1_9_, mailaddres6_.LastName as LastNam20_1_9_, mailaddres6_.MrMrsType as MrMrsTy21_1_9_, mailaddres6_.Title as Title22_1_9_, mailaddres6_.OrganisationName as Organis23_1_9_, mailaddres6_.OrganisationNameAddOn1 as Organis24_1_9_, mailaddres6_.OrganisationNameAddOn2 as Organis25_1_9_, mailaddres6_.AddressType as AddressT1_1_9_, person7_.PersonId as PersonId1_43_10_, person7_.CreationTimestamp as Creation2_43_10_, person7_.CreationUser as Creation3_43_10_, person7_.FlagError as FlagErro4_43_10_, person7_.MunicipalityGlossaryId as Municipa6_43_10_, person7_.PersonType as PersonTy5_43_10_, extendedat8_.ExtendedAttributesId as Extended1_22_11_, extendedat8_.NativeLanguageCodeIso2 as NativeLa2_22_11_, mailaddres9_.AddressId as AddressI2_1_12_, mailaddres9_.AddressLine1 as AddressL3_1_12_, mailaddres9_.AddressLine2 as AddressL4_1_12_, mailaddres9_.CountryCodeIso2 as CountryC5_1_12_, mailaddres9_.DwellingNumber as Dwelling6_1_12_, mailaddres9_.ForeignZipCode as ForeignZ7_1_12_, mailaddres9_.HouseNumber as HouseNum8_1_12_, mailaddres9_.Locality as Locality9_1_12_, mailaddres9_.NormalizedStreet as Normali10_1_12_, mailaddres9_.NormalizedTown as Normali11_1_12_, mailaddres9_.PostOfficeBoxNumber as PostOff12_1_12_, mailaddres9_.PostOfficeBoxText as PostOff13_1_12_, mailaddres9_.Street as Street14_1_12_, mailaddres9_.SwissZipCode as SwissZi15_1_12_, mailaddres9_.SwissZipCodeAddOn as SwissZi16_1_12_, mailaddres9_.SwissZipCodeId as SwissZi17_1_12_, mailaddres9_.Town as Town18_1_12_, mailaddres9_.FirstName as FirstNa19_1_12_, mailaddres9_.LastName as LastNam20_1_12_, mailaddres9_.MrMrsType as MrMrsTy21_1_12_, mailaddres9_.Title as Title22_1_12_, mailaddres9_.OrganisationName as Organis23_1_12_, mailaddres9_.OrganisationNameAddOn1 as Organis24_1_12_, mailaddres9_.OrganisationNameAddOn2 as Organis25_1_12_, maritalsta10_.MaritalStatusId as MaritalS1_31_13_, maritalsta10_.CancelationReason as Cancelat2_31_13_, maritalsta10_.DateOfMaritalStatus as DateOfMa3_31_13_, maritalsta10_.DateOfSeparation as DateOfSe4_31_13_, maritalsta10_.MaritalStatus as MaritalS5_31_13_, maritalsta10_.OfficialProofOfMaritalStatus as Official6_31_13_, maritalsta10_.PlaceOfMarriageCountryId as PlaceOf11_31_13_, maritalsta10_.PlaceOfMarriageForeignTown as PlaceOfM7_31_13_, maritalsta10_.PlaceOfMarriageMunicipalityId as PlaceOf12_31_13_, maritalsta10_.PlaceOfMarriageUnknown as PlaceOfM8_31_13_, maritalsta10_.Separation as Separati9_31_13_, maritalsta10_.SeparationValidUntil as Separat10_31_13_, country11_.CountryId as CountryI1_15_14_, country11_.CodeIso2 as CodeIso2_15_14_, country11_.Name as Name3_15_14_, country11_.CountryNumber as CountryN4_15_14_, municipali12_.MunicipalityId as Municipa1_34_15_, municipali12_.Canton as Canton2_34_15_, municipali12_.HistoryNumber as HistoryN3_34_15_, municipali12_.Name as Name4_34_15_, municipali12_.MunicipalityNumber as Municipa5_34_15_, country13_.CountryId as CountryI1_15_16_, country13_.CodeIso2 as CodeIso2_15_16_, country13_.Name as Name3_15_16_, country13_.CountryNumber as CountryN4_15_16_, moccupatio14_.PersonDataId as PersonDa1_45_17_, occupation15_.OccupationId as Occupati2_45_17_, occupation15_.OccupationId as Occupati1_42_18_, occupation15_.Employer as Employer2_42_18_, occupation15_.JobTitle as JobTitle3_42_18_, occupation15_.KindOfEmployment as KindOfEm4_42_18_, occupation15_.placeOfEmployerAddressId as placeOfE9_42_18_, occupation15_.PlaceOfWorkAddressId as PlaceOf10_42_18_, occupation15_.UIDOrganisationCategory as UIDOrgan5_42_18_, occupation15_.UIDOrganisationId as UIDOrgan6_42_18_, occupation15_.ValidFrom as ValidFro7_42_18_, occupation15_.ValidUntil as ValidUnt8_42_18_, address16_.AddressId as AddressI2_1_19_, address16_.AddressLine1 as AddressL3_1_19_, address16_.AddressLine2 as AddressL4_1_19_, address16_.CountryCodeIso2 as CountryC5_1_19_, address16_.DwellingNumber as Dwelling6_1_19_, address16_.ForeignZipCode as ForeignZ7_1_19_, address16_.HouseNumber as HouseNum8_1_19_, address16_.Locality as Locality9_1_19_, address16_.NormalizedStreet as Normali10_1_19_, address16_.NormalizedTown as Normali11_1_19_, address16_.PostOfficeBoxNumber as PostOff12_1_19_, address16_.PostOfficeBoxText as PostOff13_1_19_, address16_.Street as Street14_1_19_, address16_.SwissZipCode as SwissZi15_1_19_, address16_.SwissZipCodeAddOn as SwissZi16_1_19_, address16_.SwissZipCodeId as SwissZi17_1_19_, address16_.Town as Town18_1_19_, address16_.FirstName as FirstNa19_1_19_, address16_.LastName as LastNam20_1_19_, address16_.MrMrsType as MrMrsTy21_1_19_, address16_.Title as Title22_1_19_, address16_.OrganisationName as Organis23_1_19_, address16_.OrganisationNameAddOn1 as Organis24_1_19_, address16_.OrganisationNameAddOn2 as Organis25_1_19_, address16_.AddressType as AddressT1_1_19_, address17_.AddressId as AddressI2_1_20_, address17_.AddressLine1 as AddressL3_1_20_, address17_.AddressLine2 as AddressL4_1_20_, address17_.CountryCodeIso2 as CountryC5_1_20_, address17_.DwellingNumber as Dwelling6_1_20_, address17_.ForeignZipCode as ForeignZ7_1_20_, address17_.HouseNumber as HouseNum8_1_20_, address17_.Locality as Locality9_1_20_, address17_.NormalizedStreet as Normali10_1_20_, address17_.NormalizedTown as Normali11_1_20_, address17_.PostOfficeBoxNumber as PostOff12_1_20_, address17_.PostOfficeBoxText as PostOff13_1_20_, address17_.Street as Street14_1_20_, address17_.SwissZipCode as SwissZi15_1_20_, address17_.SwissZipCodeAddOn as SwissZi16_1_20_, address17_.SwissZipCodeId as SwissZi17_1_20_, address17_.Town as Town18_1_20_, address17_.FirstName as FirstNa19_1_20_, address17_.LastName as LastNam20_1_20_, address17_.MrMrsType as MrMrsTy21_1_20_, address17_.Title as Title22_1_20_, address17_.OrganisationName as Organis23_1_20_, address17_.OrganisationNameAddOn1 as Organis24_1_20_, address17_.OrganisationNameAddOn2 as Organis25_1_20_, address17_.AddressType as AddressT1_1_20_, personname18_.PersonNameId as PersonNa1_49_21_, personname18_.AliasName as AliasNam2_49_21_, personname18_.AlliancePartnershipName as Alliance3_49_21_, personname18_.CallName as CallName4_49_21_, personname18_.DeclaredForeignName as Declared5_49_21_, personname18_.FirstNameFather as FirstNam6_49_21_, personname18_.OfficialNameFather as Official7_49_21_, personname18_.OfficialProofOfNameOfFather as Official8_49_21_, personname18_.ForeignPassportFirstName as ForeignP9_49_21_, personname18_.ForeignPassportName as Foreign10_49_21_, personname18_.FirstNameMother as FirstNa11_49_21_, personname18_.OfficialNameMother as Officia12_49_21_, personname18_.OfficialProofOfNameOfMother as Officia13_49_21_, personname18_.OriginalName as Origina14_49_21_, personname18_.OtherName as OtherNa15_49_21_, personname18_.ValidFrom as ValidFr16_49_21_, country19_.CountryId as CountryI1_15_22_, country19_.CodeIso2 as CodeIso2_15_22_, country19_.Name as Name3_15_22_, country19_.CountryNumber as CountryN4_15_22_, municipali20_.MunicipalityId as Municipa1_34_23_, municipali20_.Canton as Canton2_34_23_, municipali20_.HistoryNumber as HistoryN3_34_23_, municipali20_.Name as Name4_34_23_, municipali20_.MunicipalityNumber as Municipa5_34_23_, country21_.CountryId as CountryI1_15_24_, country21_.CodeIso2 as CodeIso2_15_24_, country21_.Name as Name3_15_24_, country21_.CountryNumber as CountryN4_15_24_, municipali22_.MunicipalityId as Municipa1_34_25_, municipali22_.Canton as Canton2_34_25_, municipali22_.HistoryNumber as HistoryN3_34_25_, municipali22_.Name as Name4_34_25_, municipali22_.MunicipalityNumber as Municipa5_34_25_, mplacesofo23_.PersonDataId as PersonDa1_46_26_, placeofori24_.PlaceOfOriginId as PlaceOfO2_46_26_, placeofori24_.PlaceOfOriginId as PlaceOfO1_50_27_, placeofori24_.Canton as Canton2_50_27_, placeofori24_.DeliveredPlaceOfOriginId as Delivere3_50_27_, placeofori24_.ExpatriationDate as Expatria4_50_27_, placeofori24_.HistoryMunicipalityId as HistoryM5_50_27_, placeofori24_.NaturalizationDate as Naturali6_50_27_, placeofori24_.OriginName as OriginNa7_50_27_, placeofori24_.ReasonOfAcquisition as ReasonOf8_50_27_, mrelations25_.PersonDataId as PersonDa1_47_28_, relationsh26_.RelationshipId as Relation2_47_28_, relationsh26_.RelationshipId as Relation1_54_29_, relationsh26_.BasedOnLawAddOn as BasedOnL2_54_29_, relationsh26_.BasedOnLaw as BasedOnL3_54_29_, relationsh26_.Care as Care4_54_29_, relationsh26_.GuardianRelationshipId as Guardian5_54_29_, relationsh26_.AddressId as AddressI8_54_29_, relationsh26_.PersonId as PersonId9_54_29_, relationsh26_.Relationship as Relation6_54_29_, relationsh26_.ValidFrom as ValidFro7_54_29_, mailaddres27_.AddressId as AddressI2_1_30_, mailaddres27_.AddressLine1 as AddressL3_1_30_, mailaddres27_.AddressLine2 as AddressL4_1_30_, mailaddres27_.CountryCodeIso2 as CountryC5_1_30_, mailaddres27_.DwellingNumber as Dwelling6_1_30_, mailaddres27_.ForeignZipCode as ForeignZ7_1_30_, mailaddres27_.HouseNumber as HouseNum8_1_30_, mailaddres27_.Locality as Locality9_1_30_, mailaddres27_.NormalizedStreet as Normali10_1_30_, mailaddres27_.NormalizedTown as Normali11_1_30_, mailaddres27_.PostOfficeBoxNumber as PostOff12_1_30_, mailaddres27_.PostOfficeBoxText as PostOff13_1_30_, mailaddres27_.Street as Street14_1_30_, mailaddres27_.SwissZipCode as SwissZi15_1_30_, mailaddres27_.SwissZipCodeAddOn as SwissZi16_1_30_, mailaddres27_.SwissZipCodeId as SwissZi17_1_30_, mailaddres27_.Town as Town18_1_30_, mailaddres27_.FirstName as FirstNa19_1_30_, mailaddres27_.LastName as LastNam20_1_30_, mailaddres27_.MrMrsType as MrMrsTy21_1_30_, mailaddres27_.Title as Title22_1_30_, mailaddres27_.OrganisationName as Organis23_1_30_, mailaddres27_.OrganisationNameAddOn1 as Organis24_1_30_, mailaddres27_.OrganisationNameAddOn2 as Organis25_1_30_, mailaddres27_.AddressType as AddressT1_1_30_, person28_.PersonId as PersonId1_43_31_, person28_.CreationTimestamp as Creation2_43_31_, person28_.CreationUser as Creation3_43_31_, person28_.FlagError as FlagErro4_43_31_, person28_.MunicipalityGlossaryId as Municipa6_43_31_, person28_.PersonType as PersonTy5_43_31_, residence29_.ResidenceId as Residenc2_55_32_, residence29_.DwellingAddressId as Dwellin12_55_32_, residence29_.EGID as EGID3_55_32_, residence29_.EWID as EWID4_55_32_, residence29_.HouseholdCode as Househol5_55_32_, residence29_.MovingDate as MovingDa6_55_32_, residence29_.TypeOfHousehold as TypeOfHo7_55_32_, residence29_.ArrivalAddressId as Arrival13_55_32_, residence29_.ArrivalCountryId as Arrival14_55_32_, residence29_.ArrivalForeignTown as ArrivalF8_55_32_, residence29_.ArrivalMunicipalityId as Arrival15_55_32_, residence29_.ArrivalUnknown as ArrivalU9_55_32_, residence29_.DepartureAddressId as Departu16_55_32_, residence29_.DepartureCountryId as Departu17_55_32_, residence29_.DepartureForeignTown as Departu10_55_32_, residence29_.DepartureMunicipalityId as Departu18_55_32_, residence29_.DepartureUnknown as Departu11_55_32_, residence29_.ResidenceType as Residenc1_55_32_, address30_.AddressId as AddressI2_1_33_, address30_.AddressLine1 as AddressL3_1_33_, address30_.AddressLine2 as AddressL4_1_33_, address30_.CountryCodeIso2 as CountryC5_1_33_, address30_.DwellingNumber as Dwelling6_1_33_, address30_.ForeignZipCode as ForeignZ7_1_33_, address30_.HouseNumber as HouseNum8_1_33_, address30_.Locality as Locality9_1_33_, address30_.NormalizedStreet as Normali10_1_33_, address30_.NormalizedTown as Normali11_1_33_, address30_.PostOfficeBoxNumber as PostOff12_1_33_, address30_.PostOfficeBoxText as PostOff13_1_33_, address30_.Street as Street14_1_33_, address30_.SwissZipCode as SwissZi15_1_33_, address30_.SwissZipCodeAddOn as SwissZi16_1_33_, address30_.SwissZipCodeId as SwissZi17_1_33_, address30_.Town as Town18_1_33_, address30_.FirstName as FirstNa19_1_33_, address30_.LastName as LastNam20_1_33_, address30_.MrMrsType as MrMrsTy21_1_33_, address30_.Title as Title22_1_33_, address30_.OrganisationName as Organis23_1_33_, address30_.OrganisationNameAddOn1 as Organis24_1_33_, address30_.OrganisationNameAddOn2 as Organis25_1_33_, address30_.AddressType as AddressT1_1_33_, address31_.AddressId as AddressI2_1_34_, address31_.AddressLine1 as AddressL3_1_34_, address31_.AddressLine2 as AddressL4_1_34_, address31_.CountryCodeIso2 as CountryC5_1_34_, address31_.DwellingNumber as Dwelling6_1_34_, address31_.ForeignZipCode as ForeignZ7_1_34_, address31_.HouseNumber as HouseNum8_1_34_, address31_.Locality as Locality9_1_34_, address31_.NormalizedStreet as Normali10_1_34_, address31_.NormalizedTown as Normali11_1_34_, address31_.PostOfficeBoxNumber as PostOff12_1_34_, address31_.PostOfficeBoxText as PostOff13_1_34_, address31_.Street as Street14_1_34_, address31_.SwissZipCode as SwissZi15_1_34_, address31_.SwissZipCodeAddOn as SwissZi16_1_34_, address31_.SwissZipCodeId as SwissZi17_1_34_, address31_.Town as Town18_1_34_, address31_.FirstName as FirstNa19_1_34_, address31_.LastName as LastNam20_1_34_, address31_.MrMrsType as MrMrsTy21_1_34_, address31_.Title as Title22_1_34_, address31_.OrganisationName as Organis23_1_34_, address31_.OrganisationNameAddOn1 as Organis24_1_34_, address31_.OrganisationNameAddOn2 as Organis25_1_34_, address31_.AddressType as AddressT1_1_34_, country32_.CountryId as CountryI1_15_35_, country32_.CodeIso2 as CodeIso2_15_35_, country32_.Name as Name3_15_35_, country32_.CountryNumber as CountryN4_15_35_, municipali33_.MunicipalityId as Municipa1_34_36_, municipali33_.Canton as Canton2_34_36_, municipali33_.HistoryNumber as HistoryN3_34_36_, municipali33_.Name as Name4_34_36_, municipali33_.MunicipalityNumber as Municipa5_34_36_, address34_.AddressId as AddressI2_1_37_, address34_.AddressLine1 as AddressL3_1_37_, address34_.AddressLine2 as AddressL4_1_37_, address34_.CountryCodeIso2 as CountryC5_1_37_, address34_.DwellingNumber as Dwelling6_1_37_, address34_.ForeignZipCode as ForeignZ7_1_37_, address34_.HouseNumber as HouseNum8_1_37_, address34_.Locality as Locality9_1_37_, address34_.NormalizedStreet as Normali10_1_37_, address34_.NormalizedTown as Normali11_1_37_, address34_.PostOfficeBoxNumber as PostOff12_1_37_, address34_.PostOfficeBoxText as PostOff13_1_37_, address34_.Street as Street14_1_37_, address34_.SwissZipCode as SwissZi15_1_37_, address34_.SwissZipCodeAddOn as SwissZi16_1_37_, address34_.SwissZipCodeId as SwissZi17_1_37_, address34_.Town as Town18_1_37_, address34_.FirstName as FirstNa19_1_37_, address34_.LastName as LastNam20_1_37_, address34_.MrMrsType as MrMrsTy21_1_37_, address34_.Title as Title22_1_37_, address34_.OrganisationName as Organis23_1_37_, address34_.OrganisationNameAddOn1 as Organis24_1_37_, address34_.OrganisationNameAddOn2 as Organis25_1_37_, address34_.AddressType as AddressT1_1_37_, country35_.CountryId as CountryI1_15_38_, country35_.CodeIso2 as CodeIso2_15_38_, country35_.Name as Name3_15_38_, country35_.CountryNumber as CountryN4_15_38_, municipali36_.MunicipalityId as Municipa1_34_39_, municipali36_.Canton as Canton2_34_39_, municipali36_.HistoryNumber as HistoryN3_34_39_, municipali36_.Name as Name4_34_39_, municipali36_.MunicipalityNumber as Municipa5_34_39_, msecondary37_.ResidenceId as Residenc1_56_40_, municipali38_.MunicipalityId as Municipa2_56_40_, municipali38_.MunicipalityId as Municipa1_34_41_, municipali38_.Canton as Canton2_34_41_, municipali38_.HistoryNumber as HistoryN3_34_41_, municipali38_.Name as Name4_34_41_, municipali38_.MunicipalityNumber as Municipa5_34_41_, residencep39_.ResidencePermitId as Residenc1_57_42_, residencep39_.Category as Category2_57_42_, residencep39_.EntryDate as EntryDat3_57_42_, residencep39_.ResidencePermitTill as Residenc4_57_42_, residencep39_.ValidFrom as ValidFro5_57_42_ from Person person0_ left outer join Identificator midentific1_ on person0_.PersonId=midentific1_.PersonId left outer join MunicipalityGlossary municipali2_ on person0_.MunicipalityGlossaryId=municipali2_.MunicipalityGlossaryId left outer join Note mnotes3_ on person0_.PersonId=mnotes3_.PersonId left outer join PersonData mpersontim4_ on person0_.PersonId=mpersontim4_.PersonId left outer join Contact contact5_ on mpersontim4_.ContactId=contact5_.ContactId left outer join Address mailaddres6_ on contact5_.AddressId=mailaddres6_.AddressId left outer join Person person7_ on contact5_.PersonId=person7_.PersonId left outer join ExtendedAttributes extendedat8_ on mpersontim4_.ExtendedAttributesId=extendedat8_.ExtendedAttributesId left outer join Address mailaddres9_ on mpersontim4_.InsuranceAddressId=mailaddres9_.AddressId left outer join MaritalStatus maritalsta10_ on mpersontim4_.MaritalStatusId=maritalsta10_.MaritalStatusId left outer join Country country11_ on maritalsta10_.PlaceOfMarriageCountryId=country11_.CountryId left outer join Municipality municipali12_ on maritalsta10_.PlaceOfMarriageMunicipalityId=municipali12_.MunicipalityId left outer join Country country13_ on mpersontim4_.NationalityCountryId=country13_.CountryId left outer join PersonData_Occupation moccupatio14_ on mpersontim4_.PersonDataId=moccupatio14_.PersonDataId left outer join Occupation occupation15_ on moccupatio14_.OccupationId=occupation15_.OccupationId left outer join Address address16_ on occupation15_.placeOfEmployerAddressId=address16_.AddressId left outer join Address address17_ on occupation15_.PlaceOfWorkAddressId=address17_.AddressId left outer join PersonName personname18_ on mpersontim4_.PersonNameId=personname18_.PersonNameId left outer join Country country19_ on mpersontim4_.PlaceOfBirthCountryId=country19_.CountryId left outer join Municipality municipali20_ on mpersontim4_.PlaceOfBirthMunicipalityId=municipali20_.MunicipalityId left outer join Country country21_ on mpersontim4_.PlaceOfDeathCountryId=country21_.CountryId left outer join Municipality municipali22_ on mpersontim4_.PlaceOfDeathMunicipalityId=municipali22_.MunicipalityId left outer join PersonData_PlaceOfOrigin mplacesofo23_ on mpersontim4_.PersonDataId=mplacesofo23_.PersonDataId left outer join PlaceOfOrigin placeofori24_ on mplacesofo23_.PlaceOfOriginId=placeofori24_.PlaceOfOriginId left outer join PersonData_Relationship mrelations25_ on mpersontim4_.PersonDataId=mrelations25_.PersonDataId left outer join Relationship relationsh26_ on mrelations25_.RelationshipId=relationsh26_.RelationshipId left outer join Address mailaddres27_ on relationsh26_.AddressId=mailaddres27_.AddressId left outer join Person person28_ on relationsh26_.PersonId=person28_.PersonId left outer join Residence residence29_ on mpersontim4_.ResidenceId=residence29_.ResidenceId left outer join Address address30_ on residence29_.DwellingAddressId=address30_.AddressId left outer join Address address31_ on residence29_.ArrivalAddressId=address31_.AddressId left outer join Country country32_ on residence29_.ArrivalCountryId=country32_.CountryId left outer join Municipality municipali33_ on residence29_.ArrivalMunicipalityId=municipali33_.MunicipalityId left outer join Address address34_ on residence29_.DepartureAddressId=address34_.AddressId left outer join Country country35_ on residence29_.DepartureCountryId=country35_.CountryId left outer join Municipality municipali36_ on residence29_.DepartureMunicipalityId=municipali36_.MunicipalityId left outer join Residence_Municipality msecondary37_ on residence29_.ResidenceId=msecondary37_.ResidenceId left outer join Municipality municipali38_ on msecondary37_.MunicipalityId=municipali38_.MunicipalityId left outer join ResidencePermit residencep39_ on mpersontim4_.ResidencePermitId=residencep39_.ResidencePermitId where person0_.PersonId=@PersonId', 
    @type = N'SQL', 
    @params = N'@PersonId int', 
    @hints = N'OPTION (FAST 500)'
    -- ou @hints = N'OPTION (LOOP JOIN)'
    GO
    Sr DBA Oracle / MS-SQL / MySQL / Postgresql / SAP-Sybase / Informix / DB2

    N'oublie pas de consulter mes articles, mon blog, les cours et les FAQ SGBD

    Attention : pas de réponse technique par MP : pensez aux autres, passez par les forums !

  2. #2
    Modérateur

    Profil pro
    dba
    Inscrit en
    janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 643
    Points : 13 070
    Points
    13 070
    Par défaut
    Bonjour,

    Pas trop le temps de chercher/tester, mais voici une piste :

    Apparemment, la requête est dans une SP...
    Il me semble qu'il faudrait spécifier @type = N'OBJECT' et ajouter le paramètre @module_or_batch pour indiquer la SP en question.

    Cela dit, je ne comprends pas très bien, car tu parles de table temporaire, mais je n'en vois pas dans la requête ?!?


    Et pour la première question...
    une raison possible, est dû au fait que le moteur ne cherche pas LE meilleur plan d'exécution, mais en fait un plan qu'il estime suffisamment bon. vu le nombre de jointure, il devient difficile pour le moteur d'étudier toutes les possibilités, il est donc possible qu'il ait considéré que le plan mis en œuvre soit "suffisamment bon".
    il faudrait le plan d'exécution réel pour en savoir plus. Mais je ne sais pas si cela sera suffisant...

  3. #3
    Rédacteur/Modérateur

    Avatar de Fabien Celaia
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    octobre 2002
    Messages
    4 195
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 51
    Localisation : Suisse

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Service public

    Informations forums :
    Inscription : octobre 2002
    Messages : 4 195
    Points : 19 417
    Points
    19 417
    Billets dans le blog
    25
    Par défaut
    Non, la requête originale n'est pas dans une SP... ce serait tellement simple si c'était le cas : il suffirait d'ajouter le hint spécifique pour que la SP fonctionne mieux.

    La SP que tu as sans doute vu ci-dessus sert à créer un plan guide afin de le modifier par la suite.
    Sr DBA Oracle / MS-SQL / MySQL / Postgresql / SAP-Sybase / Informix / DB2

    N'oublie pas de consulter mes articles, mon blog, les cours et les FAQ SGBD

    Attention : pas de réponse technique par MP : pensez aux autres, passez par les forums !

  4. #4
    Modérateur

    Profil pro
    dba
    Inscrit en
    janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 643
    Points : 13 070
    Points
    13 070
    Par défaut
    pour la sp, j'avais mal compris à cause de
    En réécrivant la requête (une SP avec 2 tables temporaires),
    Mais du coup, je comprend qu'il s'agissait juste d'un test...

    Comment le texte de la requête a été récupéré ?

    Par ailleurs, qu'est-ce que tu entends par
    bien qu'il le soit si je lance en interactif.
    Directement depuis SSMS ? Dans ce cas, comment était spécifié le paramètre @IdPersonne (déclaré, ou mis en dur ?)

Discussions similaires

  1. Plan d'exécution pas logique
    Par pat29 dans le forum Administration
    Réponses: 6
    Dernier message: 07/03/2008, 14h37
  2. Réponses: 12
    Dernier message: 22/06/2006, 10h26
  3. Plan d' exécution
    Par rod59 dans le forum Décisions SGBD
    Réponses: 2
    Dernier message: 15/06/2006, 21h50
  4. modifier arrière plan de formulaire
    Par stan30160 dans le forum IHM
    Réponses: 8
    Dernier message: 01/06/2006, 11h07
  5. Comparer des plan d'exécution
    Par sygale dans le forum Oracle
    Réponses: 7
    Dernier message: 06/04/2006, 17h58

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo