SQL Query running extremely slow

Show you query

Hi,

thanks for your reply. The query is as follows:

SELECT top (100)    dbo.MySource.My_Source_ID, dbo.MySource.Source, dbo.Sources.My_Sources_ID, dbo.Sources.My_Building_ID, dbo.Sources.My_Source_ID AS Expr1,
                      dbo.Sources.OS_building_toid, dbo.Sources.Inferred_Building_Toid_reference, dbo.Dan_addresses.Dan_Id, dbo.Dan_addresses.Building_ID,
                      dbo.Dan_addresses.Source_Id, dbo.Dan_addresses.Source_Id2, dbo.MyPostCodes.My_Post_Code_ID, dbo.MyPostCodes.PostCode,
                      dbo.AV_Both_Addresses.My_AV_Both_ID, dbo.AV_Both_Addresses.My_Building_ID AS Expr2, dbo.AV_Both_Addresses.Prox_PostCode,
                      dbo.Residential_AV.Residential_Occupier_names, dbo.Flats.prim_sub_building, dbo.Flats.Secd_Sub_Building, dbo.AV_Both_Addresses.Floors,
                      dbo.AV_Both_Addresses.PAF_Department, dbo.AV_Both_Addresses.Part_residential, dbo.CommercialBuildings_AV.Occupier_AV_ID,
                      dbo.Occupiers_Owner.My_Occupier_Owner_ID, dbo.Occupiers_Owner.Occupier_Name, dbo.CommercialBuildings_AV.PAF_Occupier_ID,
                      dbo.CommercialBuildings_AV.Occupier_Experian, dbo.Unit.Unit_Name, dbo.Unit.My_Unit_Id, dbo.Address_AV.Building_Name1, dbo.Address_AV.Building_Name2,
                      dbo.Address_AV.Dep_Street_no, dbo.Address_AV.Street_No, dbo.CommercialBuildings_AV.Proximate_Id, dbo.My_Proximate.My_Proximate_ID,
                      dbo.My_Proximate.Proximate, dbo.Streets.Street, dbo.Streets.Dep_Street, dbo.Streets.My_Street_ID, dbo.Localities.Locality, dbo.Localities.Double_Locality,
                      dbo.Localities.My_Locality_ID, dbo.MyTowns.Town, dbo.MyTowns.My_Town_ID, dbo.Counties.County, dbo.Counties.My_County_ID, dbo.Sources.UDPRN,
                      dbo.Sources.PAF_DeliveryPoint_Suffix, dbo.Dan_addresses.oblong_Id, dbo.AV_Both_Addresses.P_O_Box_Number, dbo.AV_Both_Addresses.Postcode_Type,
                      dbo.Sources.Large_postal_user, dbo.Sources.Small_Postal_user, dbo.Residential_AV.Number_of_households, dbo.AV_Both_Addresses.Just_Built,
                      dbo.Residential_AV.PAF_UMRRN, dbo.Residential_AV.council_Tax_Reference, dbo.CommercialBuildings_AV.VOA_SVR_address_split,
                      dbo.CommercialBuildings_AV.VOA_SVR_address_merged, dbo.MyJunks.brackets, dbo.MyJunks.My_Junks_ID, dbo.MyJunks.My_Building_ID AS Expr3,
                      dbo.MyJunks.o_record, dbo.MyJunks.former, dbo.MyJunks.junk, dbo.MyJunks.except_address, dbo.MyJunks.void, dbo.AV_Both_Addresses.change_desc,
                      dbo.AV_Both_Addresses.change_code, dbo.AV_Both_Addresses.secondary_site, dbo.AV_Both_Addresses.primary_site,
                      dbo.AV_Both_Addresses.PO_Boxes_without_a_number, dbo.Sources.Name, dbo.TW_Tbl.address_number_unit_equivalent, dbo.Sources.PAF_Occupier_code,
                      dbo.Sources.Experian_Occupier_code, dbo.Sources.Oblong_Occupier_code, dbo.TW_Tbl.My_TW_ID, dbo.TW_Tbl.My_Building_ID AS Expr4,
                      dbo.TW_Tbl.TW_Occupier_code_ID, dbo.CommercialBuildings_AV.Super_category_usage_classification, dbo.CommercialBuildings_AV.Yell_usage_code,
                      dbo.CommercialBuildings_AV.My_Commercial_Building_ID, dbo.CommercialBuildings_AV.My_Building_ID AS Expr5, dbo.CommercialBuildings_AV.Code_Desc_ID,
                      dbo.Codes.Codes_ID, dbo.Codes.CodeName, dbo.Code_Description.Code_Desc_ID AS Expr6, dbo.Code_Description.Codes_ID AS Expr7,
                      dbo.Code_Description.Codes, dbo.Code_Description.CodeDescription, dbo.CommercialBuildings_AV.Yell_usage_text, dbo.AV_Both_Addresses.SoHo_indicator,
                      dbo.CommercialBuildings_AV.My_Unit_ID AS Expr8, dbo.CommercialBuildings_AV.Vacant, dbo.Sources.Number_of_employees_location,
                      dbo.Sources.Number_Of_Employees_Company, dbo.Sources.Telephone_number, dbo.Sources.Turnover, dbo.Sources.Internet_address_Web_URL,
                      dbo.CommercialBuildings_AV.URL_Group_Linkage, dbo.Occupiers_Owner.Contact_Name_Title_Generic, dbo.Occupiers_Owner.Contact_name_First_name_generic,
                      dbo.Occupiers_Owner.Contact_name_Surname_generic, dbo.Occupiers_Owner.Contact_name_Job_title_generic,
                      dbo.Occupiers_Owner.Contact_name_Title_high_value, dbo.Occupiers_Owner.Contact_name_First_name_high_value,
                      dbo.Occupiers_Owner.Contact_name_Surname_high_vlaue, dbo.Occupiers_Owner.Contact_name_Job_title_high_value, dbo.Sources.Number_of_floors,
                      dbo.Sources.Total_floorspace_all_floors, dbo.Floors_Type.Floors_Type_Id, dbo.Floors_Type.Floor_Id, dbo.Floors_Type.Area, dbo.Floors.My_Floor_ID,
                      dbo.Floors.[Floor], dbo.Flats.My_Flat_ID, dbo.Flats.My_Building_ID AS Expr9, dbo.tbl_Reg_County.Reg_county_ID, dbo.Residential_AV.Residential_ID,
                      dbo.Residential_AV.My_Building_ID AS Expr10, dbo.Sources.Maximum_width, dbo.Sources.Depth_Length, dbo.Sources.Mail_Preference_Services,
                      dbo.Sources.Telephone_Preference_Services, dbo.CommercialBuildings_AV.Occupier_verification, dbo.AV_Both_Addresses.PAF_VOA_SVR_Merge_flag
FROM         dbo.AnchorPoints INNER JOIN
                      dbo.My_Proximate INNER JOIN
                      dbo.Counties INNER JOIN
                      dbo.tbl_Reg_County ON dbo.Counties.My_County_ID = dbo.tbl_Reg_County.County_ID INNER JOIN
                      dbo.Streets INNER JOIN
                      dbo.Localities INNER JOIN
                      dbo.MyTowns ON dbo.Localities.My_Town_ID = dbo.MyTowns.My_Town_ID INNER JOIN
                      dbo.tbl_Postcode_street ON dbo.Localities.My_Locality_ID = dbo.tbl_Postcode_street.locality_ID INNER JOIN
                      dbo.Address_AV ON dbo.tbl_Postcode_street.Post_street_ID = dbo.Address_AV.Post_street_ID INNER JOIN
                      dbo.MyPostCodes ON dbo.tbl_Postcode_street.Postcode_ID = dbo.MyPostCodes.My_Post_Code_ID ON
                      dbo.Streets.My_Street_ID = dbo.tbl_Postcode_street.Street_ID ON dbo.tbl_Reg_County.Reg_county_ID = dbo.MyTowns.Reg_County_ID INNER JOIN
                      dbo.Sources ON dbo.Address_AV.My_Building_ID = dbo.Sources.My_Building_ID INNER JOIN
                      dbo.Residential_AV ON dbo.Address_AV.My_Building_ID = dbo.Residential_AV.My_Building_ID INNER JOIN
                      dbo.MySource ON dbo.Sources.My_Source_ID = dbo.MySource.My_Source_ID INNER JOIN
                      dbo.MyJunks ON dbo.Address_AV.My_Building_ID = dbo.MyJunks.My_Building_ID INNER JOIN
                      dbo.Floors INNER JOIN
                      dbo.Floors_Type ON dbo.Floors.My_Floor_ID = dbo.Floors_Type.Floor_Id INNER JOIN
                      dbo.Flats ON dbo.Floors_Type.Floors_Type_Id = dbo.Flats.Floor_type_id ON dbo.Address_AV.My_Building_ID = dbo.Flats.My_Building_ID INNER JOIN
                      dbo.Dan_addresses ON dbo.Address_AV.My_Building_ID = dbo.Dan_addresses.Building_ID INNER JOIN
                      dbo.AV_Both_Addresses ON dbo.Address_AV.My_Building_ID = dbo.AV_Both_Addresses.My_Building_ID INNER JOIN
                      dbo.Unit INNER JOIN
                      dbo.Codes INNER JOIN
                      dbo.Code_Description ON dbo.Codes.Codes_ID = dbo.Code_Description.Codes_ID INNER JOIN
                      dbo.CommercialBuildings_AV ON dbo.Code_Description.Code_Desc_ID = dbo.CommercialBuildings_AV.Code_Desc_ID ON
                      dbo.Unit.My_Unit_Id = dbo.CommercialBuildings_AV.My_Unit_ID ON dbo.Address_AV.My_Building_ID = dbo.CommercialBuildings_AV.My_Building_ID INNER JOIN
                      dbo.Occupiers_Owner ON dbo.CommercialBuildings_AV.Occupier_AV_ID = dbo.Occupiers_Owner.My_Occupier_Owner_ID ON
                      dbo.My_Proximate.My_Proximate_ID = dbo.CommercialBuildings_AV.Proximate_Id ON
                      dbo.AnchorPoints.My_Anchor_Point_ID = dbo.CommercialBuildings_AV.My_Anchor_Point_ID INNER JOIN
                      dbo.TW_Tbl ON dbo.Address_AV.My_Building_ID = dbo.TW_Tbl.My_Building_ID

your query was created in Query Builder? looks like you did, i see many tables without join clearly, you must try to do step by step, with this you can take control of your query

Thanks this is step by step the following query executes in no times

SELECT     TOP (100) Address_AV.My_Building_ID, Address_AV.Building_Name1, Address_AV.Building_Name2, Address_AV.Dep_Street_no, Address_AV.Street_No,
                      Address_AV.Building_Name1_Mangled, Address_AV.Building_Name2_Mangled, Address_AV.Dep_Street_Mangled, Address_AV.Street_Mangled,
                      Address_AV.Town_Mangled, Flats.prim_sub_building, Flats.My_Flat_ID, Flats.My_Building_ID AS Expr1, Flats.Secd_Sub_Building, Floors_Type.Floors_Type_Id,
                      Floors_Type.Floor_Id, Floors_Type.Area, Floors.My_Floor_ID, Floors.Floor, tbl_Reg_County.Reg_county_ID, tbl_Reg_County.County_ID,
                      tbl_Reg_County.My_country_ID, tbl_Postcode_street.Post_street_ID, tbl_Postcode_street.Postcode_ID, tbl_Postcode_street.Street_ID, tbl_Postcode_street.locality_ID,
                      Streets.My_Street_ID, Streets.Street, Streets.Dep_Street, MyTowns.My_Town_ID, MyTowns.Town, MyPostCodes.My_Post_Code_ID, MyPostCodes.PostCode,
                      Counties.County, Counties.My_County_ID, Localities.My_Locality_ID, Localities.My_Town_ID AS Expr2, Localities.Locality, Localities.Double_Locality,
                      tbl_Reg_County.Region_ID
FROM         MyPostCodes INNER JOIN
                      Address_AV INNER JOIN
                      Flats ON Address_AV.My_Building_ID = Flats.My_Building_ID INNER JOIN
                      Floors_Type ON Flats.Floor_type_id = Floors_Type.Floors_Type_Id INNER JOIN
                      Floors ON Floors_Type.Floor_Id = Floors.My_Floor_ID INNER JOIN
                      tbl_Postcode_street ON Address_AV.Post_street_ID = tbl_Postcode_street.Post_street_ID INNER JOIN
                      Streets ON tbl_Postcode_street.Street_ID = Streets.My_Street_ID ON MyPostCodes.My_Post_Code_ID = tbl_Postcode_street.Postcode_ID INNER JOIN
                      Localities ON tbl_Postcode_street.locality_ID = Localities.My_Locality_ID INNER JOIN
                      MyTowns INNER JOIN
                      tbl_Reg_County ON MyTowns.Reg_County_ID = tbl_Reg_County.Reg_county_ID ON Localities.My_Town_ID = MyTowns.My_Town_ID INNER JOIN
                      Counties ON tbl_Reg_County.County_ID = Counties.My_County_ID





but when i try to include another table SOURCES which is related to Address_AV through My_Building_ID it takes almost four minutes. I am sure there are other tables which are causing delays.





SELECT     TOP (100) Address_AV.My_Building_ID, Address_AV.Building_Name1, Address_AV.Building_Name2, Address_AV.Dep_Street_no, Address_AV.Street_No,
                      Address_AV.Building_Name1_Mangled, Address_AV.Building_Name2_Mangled, Address_AV.Dep_Street_Mangled, Address_AV.Street_Mangled,
                      Address_AV.Town_Mangled, Flats.prim_sub_building, Flats.My_Flat_ID, Flats.My_Building_ID AS Expr1, Flats.Secd_Sub_Building, Floors_Type.Floors_Type_Id,
                      Floors_Type.Floor_Id, Floors_Type.Area, Floors.My_Floor_ID, Floors.[Floor], tbl_Reg_County.Reg_county_ID, tbl_Reg_County.County_ID,
                      tbl_Reg_County.My_country_ID, tbl_Postcode_street.Post_street_ID, tbl_Postcode_street.Postcode_ID, tbl_Postcode_street.Street_ID, tbl_Postcode_street.locality_ID,
                      Streets.My_Street_ID, Streets.Street, Streets.Dep_Street, MyTowns.My_Town_ID, MyTowns.Town, MyPostCodes.My_Post_Code_ID, MyPostCodes.PostCode,
                      Counties.County, Counties.My_County_ID, Localities.My_Locality_ID, Localities.My_Town_ID AS Expr2, Localities.Locality, Localities.Double_Locality,
                      tbl_Reg_County.Region_ID, Sources.My_Sources_ID, Sources.My_Source_ID, Sources.My_Building_ID AS Expr3, Sources.UDPRN, Sources.Large_postal_user,
                      Sources.Small_Postal_user, Sources.Number_of_floors, Sources.Maximum_width, Sources.Depth_Length, Sources.Number_of_employees_location,
                      Sources.Number_Of_Employees_Company, Sources.Telephone_number, Sources.Internet_address_Web_URL, Sources.OS_building_toid,
                      Sources.Inferred_Building_Toid_reference, Sources.Mail_Preference_Services, Sources.Telephone_Preference_Services, Sources.PAF_Occupier_code,
                      Sources.Experian_Occupier_code, Sources.Oblong_Occupier_code, Sources.Turnover, Sources.Total_floorspace_all_floors, Sources.PAF_DeliveryPoint_Suffix,
                      Sources.Name
FROM         MyPostCodes INNER JOIN
                      Address_AV INNER JOIN
                      Flats ON Address_AV.My_Building_ID = Flats.My_Building_ID INNER JOIN
                      Floors_Type ON Flats.Floor_type_id = Floors_Type.Floors_Type_Id INNER JOIN
                      Floors ON Floors_Type.Floor_Id = Floors.My_Floor_ID INNER JOIN
                      tbl_Postcode_street ON Address_AV.Post_street_ID = tbl_Postcode_street.Post_street_ID INNER JOIN
                      Streets ON tbl_Postcode_street.Street_ID = Streets.My_Street_ID ON MyPostCodes.My_Post_Code_ID = tbl_Postcode_street.Postcode_ID INNER JOIN
                      Localities ON tbl_Postcode_street.locality_ID = Localities.My_Locality_ID INNER JOIN
                      MyTowns INNER JOIN
                      tbl_Reg_County ON MyTowns.Reg_County_ID = tbl_Reg_County.Reg_county_ID ON Localities.My_Town_ID = MyTowns.My_Town_ID INNER JOIN
                      Counties ON tbl_Reg_County.County_ID = Counties.My_County_ID INNER JOIN
                      Sources ON Address_AV.My_Building_ID = Sources.My_Building_ID

that's because still wrong, (i think at first look), for example you must have a central Table
and from this take the others.
you has many joins with tables without relations all tables (or almost) must have a relation to the central table,

like this

select * frm tblCentral  C
inner join tblB B on C.rIDB = B.ID
inner join TBL D on C.rIDD = D.ID
inner join TBLE E on C.rIDE = E.ID
....all use C

or

select * frm tblCentral  C
inner join tblB B on C.rIDB = B.ID
inner join TBL D on B.D = D.ID
inner join TBLE E on D.rI = E.ID
...C is central, b with c , D with B in turn C, and E with D in turn B in turn C

as you can see, all tables has one relation to the principal, or in defect have one relation to the table and this in turn have relation to the principal

I have started the query from scratch and all tables are linked to main table, when i continue adding the tables the query process time increases significantly, all the below tables have got almost 30 millions records but i am selecting only the top 1000. The query is running from past six minutes when i added the last R1 table, before the R1 table query executed in two minutes. The query is

SELECT TOP 1000 A1.[My_Building_ID] ,A1.[Building_Name1],A1.[Building_Name2],A1.[Dep_Street_no],A1.[Street_No],
  F1.[My_Flat_ID] ,F1.[prim_sub_building],F1.[Secd_Sub_Building], S1.[My_Sources_ID]
      ,S1.[My_Source_ID],S1.[UDPRN],S1.[Large_postal_user],S1.[Small_Postal_user],S1.[Number_of_floors],S1.[Maximum_width]
     ,S1.[Depth_Length],S1.[Number_of_employees_location] ,S1.[Number_Of_Employees_Company],S1.[Telephone_number]
      ,S1.[Internet_address_Web_URL],S1.[OS_building_toid] ,S1.[Inferred_Building_Toid_reference]
     ,S1.[Mail_Preference_Services],S1.[Telephone_Preference_Services],S1.[PAF_Occupier_code]
      ,S1.[Experian_Occupier_code],S1.[Oblong_Occupier_code],S1.[Turnover]
      ,S1.[Total_floorspace_all_floors],S1.[PAF_DeliveryPoint_Suffix],S1.[Name],
      R1.[Residential_ID],R1.[Number_of_households] ,R1.[Residential_Occupier_names],R1.[council_Tax_Reference]
      ,R1.[PAF_UMRRN]
     
  FROM [AV_CORE_2012_New].[dbo].[Address_AV] as A1 inner join
  [AV_CORE_2012_New].[dbo].[Flats] as F1           --will add child table later
  on
  A1.My_building_id=F1.My_Building_Id inner join
  [AV_CORE_2012_New].[dbo].[Sources] as S1 on                  --child
  A1.My_building_id=S1.My_Building_Id inner join
  [AV_CORE_2012_New].[dbo].[Residential_AV] as R1 on
  A1.My_building_id=R1.My_Building_Id

you must add all fields related not only the ID, that's because if you have two id's in the second table, the first show two rows, this happen because only have join in one conditional

example

Table A
ID  Value
1    B
2    C

Table B
ID Value   X
1    B        Z
1    E        Q

in the join only with the first one
JOIN......ON ID = ID
1    B    Z
1    E    Q

add the second to the join
JOIN ....ON ID = ID AND Value = Value

1   B    Z

other stuff is the index this will help you

http://msdn.microsoft.com/en-us/library/ff650692.aspx
http://msdn.microsoft.com/en-us/library/ms188783.aspx

(namely you must add more related columns in the joins part)

But my child table includes only the primary key of my parent table that is
Table A has primary key my_building_id and the only matching record in child table is my_building_id

my_building_id is unique in parent table and can be repeated in child tables.

i don't have other matching columns in parent / child tables and i believe this is the best design practice?

that's depends,
if i have one table with employee, another with deptos and other with sections

empl
ID    Depto    Seccion

Depto
ID     NameDepto

Seccion
IDDepto IDSeccion NameSeccion

as i say, that's depends.
Select E.Name, D.Name as Depto, S.Name as Seccion
Join
employees
JOIN Depto d on E.Depto = d.ID
JOIN Seccion S on E.Depto = S.IDDepto
                         and e.Seccion = S.ID

you have a problem there, if the principal has many Id in the child, the query will show as many rows has the child not matter if the principal only has one

Hi All,

thanks you very much for your help. As this was the first time i had to deal with large amount of data (over 30 millions records in for tables) i learnt a lot. I created the indexes on foreign keys in the child tables and it took only five seconds to display top 1000 records without indexes it was taking forever and was not showing any results.
However it did take aroud 40 minutes to bring back 30 millions records which i think is acceptable.

Best regards