Results 1 to 11 of 11
  1. #1
    Romio is offline Novice
    Windows Vista Access 2007
    Join Date
    Feb 2015
    Posts
    8

    Slow Query

    I have the following situation

    Table one, contains about 30 fields about people identity data, with an IDTag (text) as primary key
    Table two, contains an custom numeric identifier of the area where people lives, having a code (text) as primary key, that is as well the foreign key in table one
    Between the two tables is a one-to-one relation

    The query I use is a select query that retrieves data from the two tables, based on various criteria filled in a form
    The output is dispalyed in a subform of the main form

    Now it starts the problem

    A third table contains some history data of the persons in table one. The IDTag from table one is foreign key for table 3
    For each IDTag we may have multiple records in table 3. Only some of the people (IDTag) in table one have corresponding record in table 3
    I set a one to many relation between table 1 and 3, that shows all records in table one and only those from table 3 that matches a certain criteria provided by the form
    It also shows only distinct values (some values may be doubled, so I need this)
    The output will show all persons recorded on table one, with or without history, and fills a query field that shows if the person have a certain string in his history or N/A for those that does not.

    If the first query works fine (about 1 min to process 100k records), when I add the relation with the third table, the query becomes incredibly sluggish, taking about 15 minutes to run.



    The form is a dynamic one, requreying each time a field content is updated, but under these circumstances, becomes impossible to use.

    Any ideea to speed up the stuff?

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Put indexes on the linking fields and any criteria fields. Maybe combine Table 1 and 2 since it is 1-1 data and then just link that to table 3 so you would only have 2 tables. Are you sorting or doing any calculations in the query?

  3. #3
    Romio is offline Novice
    Windows Vista Access 2007
    Join Date
    Feb 2015
    Posts
    8
    I have calculations on the initial query, but that does not slow very much the process.
    All the trouble starts when i am adding the third table
    It adds an calculated field to the initial query ( OP_Fill: IIf(IsNull([Organizatia_Politica]),"N/A",[Organizatia_Politica]) )
    Without this field, all those people that have no history, are skipped, because of the ”null” value of the field
    But even if i don't use this field, the query is still slow, from the moment I create the relation between table 1 and 3
    I have indexes on IDTag (linking field) and on the criteria field

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Is your DB split into a FE and BE? Is the BE Access or SQL? How many records in Table3? Do you have to include all records everytime you do this or can you limit it to something smaller like a specific date or range?

  5. #5
    Romio is offline Novice
    Windows Vista Access 2007
    Join Date
    Feb 2015
    Posts
    8
    Is your DB split into a FE and BE? No
    Is the BE Access or SQL? Access
    How many records in Table3? Few hundreds now, but it will grow to tens of thousands
    Do you have to include all records everytime you do this or can you limit it to something smaller like a specific date or range?


    SELECT DISTINCT
    Membri_Partid.Timestamp,
    Membri_Partid.Data_Adeziunii,
    Membri_Partid.Judet,
    Membri_Partid.Organizatia,
    Membri_Partid.Cod_Org,
    Membri_Partid.CNP,
    Membri_Partid.CNP_Valid,
    IIf([CNP_Valid]=-1,1,2) AS Membership,
    Membri_Partid.Nr_Carnet,
    Membri_Partid.Nume,
    Membri_Partid.Prenume,
    Membri_Partid.Data_Nasterii,
    Int(DateDiff("d",[Data_Nasterii],Date())/365.25) AS Varsta,
    DateSerial((DatePart("yyyy",Date())),(DatePart("m" ,[Data_Nasterii])),(DatePart("d",[Data_Nasterii]))) AS Aniversare,
    Membri_Partid.Locul_Nasterii,
    IIf(Left([Membri_Partid]![CNP],1) Mod 2=0,2,1) AS Sex,
    Membri_Partid.ActID_Seria,
    Membri_Partid.ActID_Nr,
    Membri_Partid.Strada,
    Membri_Partid.Nr,
    Membri_Partid.Bloc,
    Membri_Partid.Scara,
    Membri_Partid.Etaj,
    Membri_Partid.Apartament,
    Membri_Partid.Oras_Comuna,
    Membri_Partid.Sat,
    Membri_Partid.Judet_Sector,
    Membri_Partid.Cod_Loc_SV,
    Membri_Partid.Nr_SV,
    Membri_Partid.Cod_SV,
    Trim(IIf(IsNull([Strada]) Or [Strada]="","","Strada " & [Strada] & ", ") & IIf(IsNull([Nr]) Or [Nr]="","","nr." & [Nr] & ", ") & IIf(IsNull([Bloc]) Or [Bloc]="","","bl. " & [Bloc] & ", ") & IIf(IsNull([Scara]) Or [Scara]="","","sc. " & [Scara] & ", ") & IIf(IsNull([Etaj]) Or [Etaj]="","","et. " & [Etaj] & ", ") & IIf(IsNull([Apartament]) Or [Apartament]="","","ap." & [Apartament] & ", ") & IIf(Left(DLookUp("Tip","Coduri_Localitati","[Cod_Localitate]='" & [Cod_Loc_SV] & "'"),1)="M","Municipiul ",IIf(Left(DLookUp("Tip","Coduri_Localitati","[Cod_Localitate]='" & [Cod_Loc_SV] & "'"),1)="O","Orasul ","Comuna ")) & [Oras_Comuna] & ", " & IIf(IsNull([Sat]) Or [Sat]="",""," Sat " & [Sat] & ", ") & IIf([Oras_Comuna]="BUCURESTI",StrConv(Right([Judet_Sector],8),3),"Judetul " & [Judet_Sector])) AS Domiciliu,
    Membri_Partid.Strada_c,
    Membri_Partid.Nr_c,
    Membri_Partid.Bloc_c,
    Membri_Partid.Scara_c,
    Membri_Partid.Etaj_c,
    Membri_Partid.Apartament_c,
    Membri_Partid.Oras_Comuna_c,
    Membri_Partid.Sat_c,
    Membri_Partid.Judet_Sector_c,
    Membri_Partid.Cod_Loc_OSV,
    Membri_Partid.Nr_OSV,
    Membri_Partid.Cod_OSV,
    IIf([Cod_Loc_OSV]="0" Or [Cod_Loc_OSV]="" Or IsNull([Cod_Loc_OSV]),"",IIf(IsNull([Strada_c]) Or [Strada_c]="","","Strada " & [Strada_c] & ", ") & IIf(IsNull([Nr_c]) Or [Nr_c]="","","nr." & [Nr_c] & ", ") & IIf(IsNull([Bloc_c]) Or [Bloc_c]="","","bl. " & [Bloc_c] & ", ") & IIf(IsNull([Scara_c]) Or [Scara_c]="","","sc. " & [Scara_c] & ", ") & IIf(IsNull([Etaj_c]) Or [Etaj_c]="","","et. " & [Etaj_c] & ", ") & IIf(IsNull([Apartament_c]) Or [Apartament_c]="","","ap." & [Apartament_c] & ", ") & IIf(Left(DLookUp("Tip","Coduri_Localitati","[Cod_Localitate]='" & [Cod_Loc_OSV] & "'"),1)="M","Municipiul ",IIf(Left(DLookUp("Tip","Coduri_Localitati","[Cod_Localitate]='" & [Cod_Loc_OSV] & "'"),1)="O","Orasul ","Comuna ")) & [Oras_Comuna_c] & IIf(IsNull([Sat_c]) Or [Sat_c]="","",", Sat " & [Sat_c]) & IIf([Oras_Comuna_c]="BUCURESTI","," & Right([Judet_Sector_c],9),", Judetul " & [Judet_Sector_c])) AS AdrCor,
    Membri_Partid.[Telefon(1)], Membri_Partid.[Telefon(2)], Left([Telefon(1)],3) & Mid([Telefon(1)],5,3) & Right([Telefon(1)],4) & "-" & Left([Telefon(2)],3) & Mid([Telefon(2)],5,3) & Right([Telefon(2)],4) AS TelString, IIf(Left([Telefon(1)],2)="07" Or Left([Telefon(2)],2)="07",-1,0) AS Mobil,
    [Telefon(1)] & Chr(13) & Chr(10) & [Telefon(2)] AS Telefoane,
    Membri_Partid.[e-mail],
    Membri_Partid.Nationalitate,
    Membri_Partid.Religia,
    Membri_Partid.Stare_Civila,
    IIf(IsNull([Studii]),"N/A",IIf([Studii]="","N/A",[Studii])) AS St_Fill,
    IIf(IsNull([Profesie]),"N/A",IIf([Profesie]="","N/A",[Profesie])) AS Prof_Fill,
    IIf(IsNull([Domeniu]),"N/A",IIf([Domeniu]="","N/A",[Domeniu])) AS Dom_Fill,
    IIf(IsNull([Statut_Social]),"N/A",IIf([Statut_Social]="","N/A",[Statut_Social])) AS Stat_Fill,
    IIf(IsNull([Recomandare]),"N/A",[recomandare]) AS RecFill,
    (DateDiff("yyyy",[Data_Nasterii],Now())>61) And (Left([Membri_Partid]![CNP],1) Mod 2=0) Or (DateDiff("yyyy",[Data_Nasterii],Now())>64) And (Left([Membri_Partid]![CNP],1) Mod 2<>0) AS OP, DateDiff("yyyy",[Data_Nasterii],Now())<31 AS OT,
    Zonare.Cod_Zona,
    Nz([Cod_Zona],0) AS CodZ,
    Membri_Partid.inValidat,
    IIf(IsNull([Organizatia_Politica]),"N/A",[Organizatia_Politica]) AS OP_Fill
    FROM (Membri_Partid LEFT JOIN Zonare ON Membri_Partid.Cod_OSV = Zonare.Cod_SV) LEFT JOIN IstoricPolitic ON Membri_Partid.IDTag = IstoricPolitic.IDTag
    WHERE
    (((Membri_Partid.Data_Adeziunii)>([Forms]![Cautare_Membru_Frm]![DA_Start]-1) And
    (Membri_Partid.Data_Adeziunii)<([Forms]![Cautare_Membru_Frm]![DA_End]+1)) AND
    ((Membri_Partid.Judet) Like IIf(IsNull([Forms]![Cautare_Membru_Frm]![OrgJud]),"*",[Forms]![Cautare_Membru_Frm]![OrgJud])) AND
    ((Membri_Partid.Cod_Org) Like IIf(IsNull([Forms]![Cautare_Membru_Frm]![CodLoc]),"*",[Forms]![Cautare_Membru_Frm]![CodLoc])) AND
    ((Membri_Partid.CNP) Like IIf(IsNull([Forms]![Cautare_Membru_Frm]![CNP]),"*",[Forms]![Cautare_Membru_Frm]![CNP] & "*")) AND
    ((IIf([CNP_Valid]=-1,1,2)) Like IIf([Forms]![Cautare_Membru_Frm].[Calitate]=0,"*",[Forms]![Cautare_Membru_Frm].[Calitate])) AND
    ((Membri_Partid.Nr_Carnet) Like IIf([Forms]![Cautare_Membru_Frm]![Nr_Carnet]="0","0","*" & [Forms]![Cautare_Membru_Frm]![Nr_Carnet] & "*")) AND
    ((Membri_Partid.Nume) Like "*" & [Forms]![Cautare_Membru_Frm]![Nume] & "*") AND
    ((Membri_Partid.Prenume) Like "*" & [Forms]![Cautare_Membru_Frm]![Prenume] & "*") AND
    ((Int(DateDiff("d",[Data_Nasterii],Date())/365.25))>=([Forms]![Cautare_Membru_Frm]![AgeMin]) And (Int(DateDiff("d",[Data_Nasterii],Date())/365.25))<=([Forms]![Cautare_Membru_Frm]![AgeMax])) AND
    ((DateSerial((DatePart("yyyy",Date())),(DatePart(" m",[Data_Nasterii])),(DatePart("d",[Data_Nasterii]))))>([Forms]![Cautare_Membru_Frm]![DN_Start]-1) And (DateSerial((DatePart("yyyy",Date())),(DatePart("m ",[Data_Nasterii])),(DatePart("d",[Data_Nasterii]))))<([Forms]![Cautare_Membru_Frm]![DN_End]+1)) AND
    ((IIf(Left([Membri_Partid]![CNP],1) Mod 2=0,2,1)) Like IIf([Forms]![Cautare_Membru_Frm].[Gender]=0,"*",[Forms]![Cautare_Membru_Frm].[Gender])) AND
    ((Membri_Partid.Judet_Sector) Like IIf(IsNull([Forms]![Cautare_Membru_Frm]![Jud]),"*",[Forms]![Cautare_Membru_Frm]![Jud])) AND
    ((Membri_Partid.Cod_Loc_SV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm]![CodLocD]),"*",[Forms]![Cautare_Membru_Frm]![CodLocD])) AND
    ((Membri_Partid.Cod_SV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodSV1]),"*",[Forms]![Cautare_Membru_Frm]![CodSV1]) Or (Membri_Partid.Cod_SV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodSV2]),"",[Forms]![Cautare_Membru_Frm]![CodSV2]) Or (Membri_Partid.Cod_SV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodSV3]),"",[Forms]![Cautare_Membru_Frm]![CodSV3]) Or (Membri_Partid.Cod_SV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodSV4]),"",[Forms]![Cautare_Membru_Frm]![CodSV4]) Or (Membri_Partid.Cod_SV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodSV5]),"",[Forms]![Cautare_Membru_Frm]![CodSV5]) Or (Membri_Partid.Cod_SV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodSV6]),"",[Forms]![Cautare_Membru_Frm]![CodSV6]) Or (Membri_Partid.Cod_SV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodSV7]),"",[Forms]![Cautare_Membru_Frm]![CodSV7]) Or (Membri_Partid.Cod_SV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodSV8]),"",[Forms]![Cautare_Membru_Frm]![CodSV8]) Or (Membri_Partid.Cod_SV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodSV9]),"",[Forms]![Cautare_Membru_Frm]![CodSV9]) Or (Membri_Partid.Cod_SV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodSV10]),"",[Forms]![Cautare_Membru_Frm]![CodSV10])) AND
    ((Membri_Partid.Cod_OSV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodOSV1]),"*",[Forms]![Cautare_Membru_Frm]![CodOSV1]) Or (Membri_Partid.Cod_OSV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodOSV2]),"",[Forms]![Cautare_Membru_Frm]![CodOSV2]) Or (Membri_Partid.Cod_OSV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodOSV3]),"",[Forms]![Cautare_Membru_Frm]![CodOSV3]) Or (Membri_Partid.Cod_OSV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodOSV4]),"",[Forms]![Cautare_Membru_Frm]![CodOSV4]) Or (Membri_Partid.Cod_OSV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodOSV5]),"",[Forms]![Cautare_Membru_Frm]![CodOSV5]) Or (Membri_Partid.Cod_OSV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodOSV6]),"",[Forms]![Cautare_Membru_Frm]![CodOSV6]) Or (Membri_Partid.Cod_OSV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodOSV7]),"",[Forms]![Cautare_Membru_Frm]![CodOSV7]) Or (Membri_Partid.Cod_OSV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodOSV8]),"",[Forms]![Cautare_Membru_Frm]![CodOSV8]) Or (Membri_Partid.Cod_OSV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodOSV9]),"",[Forms]![Cautare_Membru_Frm]![CodOSV9]) Or (Membri_Partid.Cod_OSV) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodOSV10]),"",[Forms]![Cautare_Membru_Frm]![CodOSV10])) AND
    ((Left([Telefon(1)],3) & Mid([Telefon(1)],5,3) & Right([Telefon(1)],4) & "-" & Left([Telefon(2)],3) & Mid([Telefon(2)],5,3) & Right([Telefon(2)],4)) Like "*" & Left([Forms]![Cautare_Membru_Frm]![Telefon],3) & Mid([Forms]![Cautare_Membru_Frm]![Telefon],5,3) & Right([Forms]![Cautare_Membru_Frm]![Telefon],4) & "*") AND
    ((IIf(Left([Telefon(1)],2)="07" Or Left([Telefon(2)],2)="07",-1,0)) Like IIf([Forms]![Cautare_Membru_Frm]![Mobil]=False,"*",-1)) AND
    ((IIf(IsNull([Studii]),"N/A",IIf([Studii]="","N/A",[Studii]))) Like "*" & [Forms]![Cautare_Membru_Frm]![Studii] & "*") AND
    ((IIf(IsNull([Profesie]),"N/A",IIf([Profesie]="","N/A",[Profesie]))) Like "*" & [Forms]![Cautare_Membru_Frm]![Prof] & "*") AND
    ((IIf(IsNull([Domeniu]),"N/A",IIf([Domeniu]="","N/A",[Domeniu]))) Like "*" & [Forms]![Cautare_Membru_Frm]![Dom] & "*") AND
    ((IIf(IsNull([Statut_Social]),"N/A",IIf([Statut_Social]="","N/A",[Statut_Social]))) Like "*" & [Forms]![Cautare_Membru_Frm]![Stat_S] & "*") AND
    ((IIf(IsNull([Recomandare]),"N/A",[recomandare])) Like "*" & [Forms]![Cautare_Membru_Frm]![Rec1] & "*" And (IIf(IsNull([Recomandare]),"N/A",[recomandare])) Like "*" & [Forms]![Cautare_Membru_Frm]![Rec2] & "*") AND
    (((DateDiff("yyyy",[Data_Nasterii],Now())>61) And (Left([Membri_Partid]![CNP],1) Mod 2=0) Or (DateDiff("yyyy",[Data_Nasterii],Now())>64) And
    (Left([Membri_Partid]![CNP],1) Mod 2<>0)) Like (IIf([Forms]![Cautare_Membru_Frm]![OS]=0,"*",[Forms]![Cautare_Membru_Frm]![OS]))) AND ((DateDiff("yyyy",[Data_Nasterii],Now())<31) Like (IIf([Forms]![Cautare_Membru_Frm]![OT]=0,"*",[Forms]![Cautare_Membru_Frm]![OT]))) AND ((Nz([Cod_Zona],0)) Like IIf(IsNull([Forms]![Cautare_Membru_Frm].[CodZona]),"*",[Forms]![Cautare_Membru_Frm].[CodZona])) AND ((Membri_Partid.inValidat)=No) AND ((IIf(IsNull([Organizatia_Politica]),"N/A",[Organizatia_Politica])) Like IIf([Forms]![Cautare_Membru_Frm]![Fusion]=False,"*","*" & "UNPR" & "*")))
    ORDER BY Membri_Partid.Timestamp;

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Goodness! How many functions are there? Lots and lots. Way slowing things down. DISTINCT is slowing things down too.

    Separate these out into multiple simplified queries. At least it will show you where exactly the problem is occurring. If you could do those many functions on a smaller subset of data that would help (in the last query, not the first).

    Are you sure your form is designed efficiently? To redo all of these fields every time "a field" is updated doesn't seem the best way.

  7. #7
    Romio is offline Novice
    Windows Vista Access 2007
    Join Date
    Feb 2015
    Posts
    8
    I will start to remove functions.
    any idea how to avoid DISTINCT?

  8. #8
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Depends on what you are using it for/what you are trying to accomplish. There is Group By, also not very efficient and not to be used on the above query with all its functions and complexity. Maybe on the last query once all data and fields have been created.

    Post your database if you want help with making your way thru this minefield.

  9. #9
    Romio is offline Novice
    Windows Vista Access 2007
    Join Date
    Feb 2015
    Posts
    8
    Short storry

    table 1 contains unique IDTAGs
    In table 3, for some of the IDTAGS i have one or more records about some history. Some of these records may be doubled

    When criteria in form is not set, I need to see all members of table one, but only once. Without the distinct statement, the query multiplies each IDTAG record with the number of history records in table 3

    When criteria is set on the form, I need to see all members of table one that matches the criteria.

    How can i do all of this without the DISTINCT statement?

  10. #10
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Maybe use some temp tables and separate queries. Not sure if it will reduce the time but can see.
    Query1 (Make table or Append if you want to keep indexes), combine Table1 and Table2 into tblTagInfo
    Query2(Make Table or Append), combine TblTagInfo and Table3 into tblTagHistory
    Query3 - try to use GroupBy to get Distinct on tblTagHistory

    Also as aytee said, maybe break out some of those calculations to see which ones are taking the longest to run.

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Also do you need all the like *something*? The initial * prevents the use of indexes

    and criteria like this

    ((Membri_Partid.Judet_Sector) Like IIf(IsNull([Forms]![Cautare_Membru_Frm]![Jud]),"*",[Forms]![Cautare_Membru_Frm]![Jud]))

    looks like it could be simplified to

    Membri_Partid.Judet_Sector =nz([Forms]![Cautare_Membru_Frm]![Jud],Membri_Partid.Judet_Sector)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Slow running query
    By shawnvon in forum Queries
    Replies: 5
    Last Post: 08-29-2016, 02:49 PM
  2. Very Slow Query
    By 83dons in forum Access
    Replies: 14
    Last Post: 04-17-2015, 09:07 AM
  3. Query running Slow
    By mike02 in forum Queries
    Replies: 2
    Last Post: 07-26-2013, 12:47 PM
  4. slow query when asking for sum of minutes
    By sf827 in forum Queries
    Replies: 13
    Last Post: 01-04-2012, 09:00 PM
  5. Slow query - Help to speed up?
    By jgelpi16 in forum Queries
    Replies: 9
    Last Post: 05-26-2011, 01:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums