SQL Query running extremely slow

Hello there,
I am selecting almost 90 columns from 15 different tables, there are about 25 millions record in one table and almost the same number of records in other two tables as well.
All the tables have got relationships between them. and are joined through primary keys and are inner joined.
I am trying to retreive top 100 records and it is taking forever and not giving me any results evern after 10 minutes.
Any help would be highly appreciated.

best regards
Ali ShahSQL DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CrashmanCommented:
Show you query
0
Ali ShahSQL DeveloperAuthor Commented:
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
0
CrashmanCommented:
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
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Ali ShahSQL DeveloperAuthor Commented:
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
0
CrashmanCommented:
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
0
Ali ShahSQL DeveloperAuthor Commented:
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
0
CrashmanCommented:
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)
0
Ali ShahSQL DeveloperAuthor Commented:
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?
0
CrashmanCommented:
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
0
jogosCommented:
Two suggestions
- don't run your query , but show the estimated execution plan .... you probably will find indexes that need to be created (or columns included to get covered index)  
- run your query with MyPostCodes.My_Post_Code_ID = 'xxxxx' to test, so the whole bunch get filtered and see if you didn't make a mistake

Observation
<<FROM         MyPostCodes INNER JOIN
                      Address_AV INNER JOIN
                      Flats ON Address_AV.My_Building_ID = Flats.My_Building_ID >>

I don't see a join-condition between MyPostCodes  and Address_AV .... it's hidden some joins lower

<< 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>>

It makes your join more comprehensive when you put everything in the order of joining.

 
FROM         MyPostCodes 
INNER JOIN   tbl_Postcode_street ON MyPostCodes.My_Post_Code_ID = tbl_Postcode_street.Postcode_ID
 INNER JOIN     Streets ON tbl_Postcode_street.Street_ID = Streets.My_Street_ID 
INNER JOIN   Address_AV  ON Address_AV.Post_street_ID = tbl_Postcode_street.Post_street_ID
 INNER JOIN     Flats ON Address_AV.My_Building_ID = Flats.My_Building_ID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ali ShahSQL DeveloperAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.