Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    May 2007
    Posts
    14

    Unanswered: It takes 5 minutes to run a mdb query ;-(

    I have an Access mdb. The main table has 13 fields with over 300 000 records – every day I append 2700 new records. The size is 54M.

    One of queries is used to represent all records and all fields in Pivot chart view. After that the desired parameters are filtered out in Pivot chart view.

    The problem is that the speed is very slow – it takes 5 minutes to run the query. The same is when you change parameters in x-axis or when you filtered out some field.

    What can I do to speed up the query?

    1.To create a form with criteria for which field in query.
    2.To learn SQL Server or MySQL. How many records I can store in access mdb?

  2. #2
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    I have the same size DB presently and yes it does chug a little - I find that post appending you MUST compact. I assume that you are cumming over a long period? Could you considerchecking for duplication and redundant information - I'm suggesting housekeeping only.
    Gareth

  3. #3
    Join Date
    May 2007
    Posts
    14
    Unfortunately there are no duplications or redundant information in records. I use this mdb to analyze sales – articles, prices, clients, time of supply, target groups…

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Try looking at what you're searching on ... Whatever columns you based your filtering criteria on need to be indexed in the table(s) otherwise, Access brute forces the filtering ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by watermariana
    One of queries is used to represent all records and all fields in Pivot chart view. After that the desired parameters are filtered out in Pivot chart view.
    Can the filtering not be done in the query? I admit I do not knoe how Access handles filtering in Pivot Chart View but if it does draw all 130k records and then subsequently filters then I would not expect it to run very quickly whatever your rdbms.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    A couple of things I did to make my query work faster:
    1. Broke the query up into multiple queries with less linked tables and made some of them into make-table queries. Especially the ones which had a lot of linked tables and was trying to sum, avg, etc in the same query.
    2. Changed the query properties to a snapshot.
    3. Added some fields in my main table and wrote those values from the form/a procedure so I didn't need to link that table into the query (i.e. lookup tables.) A lot of people think this is unneccessary but you'd be surprised at how you can increase the speed of a query grouping on values in your table verses having to link in a separate table to get those values (especially on 300,000 records.) I could group and sum on 1+ million records within seconds! Every linked table costs you time! Especially tables joined on 2 fields (clustered joins) - this will cost you dearly!!
    4. Created indexes on some of my key grouping fields (as stated above.)
    5. Limited my use of complex expressions in the queries breaking them into smaller equations.
    6. Avoided ALL dlookup expressions in my query (this really slows things down!)
    7. Compacted and repaired the db (as stated above.)
    8. Talked with the network guys who had slow connections to their computer (we used citrix for some users), eliminated some daisy chained hubs and increased connection line speeds (replaced slow network cards)/had users run the queries on a fast PC verses a terminal...Moved some MSAccess based tables to their drive verses over the network.
    9. Removed and re-created/re-indexed tables (SQL Server)/imported sorted data into new indexed created tables.
    10. Eliminated calculating the summed price/total in the query by storing that value in the table.
    11. Created summization tables from some of the extremely large tables (i.e. 5+ million records) which ran nightly.
    Last edited by pkstormy; 05-28-07 at 14:26.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    May 2007
    Posts
    14
    Thank you garethfx, M Owen, pootle flump and especially pkstormy!

    I’ll try to do all things you recommend me and tell you what happened.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by watermariana
    One of queries is used to represent all records and all fields in Pivot chart view.
    I'd be intrigued to see the SQL you have used to achieve this - the penny hasn't quite dropped on this so please humour me
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT * FROM MyTable
    ?????
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by watermariana
    ...in Pivot chart view.
    ?????
    George
    Home | Blog

  11. #11
    Join Date
    May 2007
    Posts
    14
    Yes s/g like this;-)
    I was delighted by idea to have only 2 queries in this mdb;-)

    Code:
    SELECT CInt(Format([Data],"ww",2,3)) AS Седмица, Weekday([Data],2) AS Ден, Format([Data],"dddd",2,3) AS [Ден име], CDate(Format([Data],"dd\.mm\.yyyy",2,3)) AS Дата, ProdPrediVurnato.Mar6rutID, CelevaGrupa.CelevaGrupaName, KlientskaGrupa.KlientskaGrupaName, ProdPrediVurnato.ClientID, Client.ClientName, ProdPrediVurnato.shofiorID, Shofior.shofiorName, Shofior.VidPlasior, ProdPrediVurnato.VidArtikul, art.Grupa, art.ArtikulName, Sum(ProdPrediVurnato.Koli4estvo) AS SumOfKoli4estvo, Sum(ProdPrediVurnato.Stojnost) AS SumOfStojnost, ProdPrediVurnato.Cena, ProdPrediVurnato.Probeg, ProdPrediVurnato.Kurs, TurgPredst.TurgPredstName, ProdPrediVurnato.Pla6tane, Mar6rut.Mar6rutName, Rajon.RajonName, ProdPrediVurnato.casNaPose6tenie, ProdPrediVurnato.ProverkaZaBrak
    FROM (TurgPredst INNER JOIN ((((((ProdPrediVurnato INNER JOIN art ON ProdPrediVurnato.[A ID] = art.[A ID]) INNER JOIN Client ON ProdPrediVurnato.ClientID = Client.ClientID) INNER JOIN Shofior ON ProdPrediVurnato.shofiorID = Shofior.shofiorID) INNER JOIN KlientskaGrupa ON Client.KlientskaGrupaID = KlientskaGrupa.KlientskaGrupaID) INNER JOIN CelevaGrupa ON Client.CelevaGrupaID = CelevaGrupa.CelevaGrupaID) INNER JOIN Mar6rut ON ProdPrediVurnato.Mar6rutID = Mar6rut.Mar6rutID) ON TurgPredst.TurgPredstID = Mar6rut.TurgPredstID) INNER JOIN Rajon ON Mar6rut.RajonID = Rajon.RajonID
    WHERE (((ProdPrediVurnato.Data) Between [Forms]![Switch]![Nachalo] And [Forms]![Switch]![Krai]))
    GROUP BY CInt(Format([Data],"ww",2,3)), Weekday([Data],2), Format([Data],"dddd",2,3), CDate(Format([Data],"dd\.mm\.yyyy",2,3)), ProdPrediVurnato.Mar6rutID, CelevaGrupa.CelevaGrupaName, KlientskaGrupa.KlientskaGrupaName, ProdPrediVurnato.ClientID, Client.ClientName, ProdPrediVurnato.shofiorID, Shofior.shofiorName, Shofior.VidPlasior, ProdPrediVurnato.VidArtikul, art.Grupa, art.ArtikulName, ProdPrediVurnato.Cena, ProdPrediVurnato.Probeg, ProdPrediVurnato.Kurs, TurgPredst.TurgPredstName, ProdPrediVurnato.Pla6tane, Mar6rut.Mar6rutName, Rajon.RajonName, ProdPrediVurnato.casNaPose6tenie, ProdPrediVurnato.ProverkaZaBrak
    HAVING (((ProdPrediVurnato.Mar6rutID) Like "*" & [Forms]![Switch]![izborM] & "*") AND ((ProdPrediVurnato.ClientID)<>"121385881") AND ((art.Grupa) Like "*" & [Forms]![Switch]![Combo63] & "*") AND ((art.ArtikulName) Like "*" & [Forms]![Switch]![Combo46] & "*"));

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Something to think about:
    Naming conventions - spaces are a definate NO. Avoid using spaces in table/field/anything names.

    Speed issues:
    Like comparisons are "slow" in terms of SQL performance

    Being picky:
    The WHERE and HAVING clauses suggest this doesn't show ALL records
    George
    Home | Blog

  13. #13
    Join Date
    May 2007
    Posts
    14
    OK.
    Thanks!

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by watermariana
    I was delighted by idea to have only 2 queries in this mdb;-)
    Unlikely to be a good idea. If you are performing analysis of large (in JET terms) datasets then you need to use finely tuned queries specific to the job, not a couple of "all things to all men" queries.

    Quote Originally Posted by watermariana
    Code:
    SELECT CInt(Format([Data],"ww",2,3)) AS Седмица, Weekday([Data],2) AS Ден, Format([Data],"dddd",2,3) AS [Ден име], CDate(Format([Data],"dd\.mm\.yyyy",2,3)) AS Дата, ProdPrediVurnato.Mar6rutID, CelevaGrupa.CelevaGrupaName, KlientskaGrupa.KlientskaGrupaName, ProdPrediVurnato.ClientID, Client.ClientName, ProdPrediVurnato.shofiorID, Shofior.shofiorName, Shofior.VidPlasior, ProdPrediVurnato.VidArtikul, art.Grupa, art.ArtikulName, Sum(ProdPrediVurnato.Koli4estvo) AS SumOfKoli4estvo, Sum(ProdPrediVurnato.Stojnost) AS SumOfStojnost, ProdPrediVurnato.Cena, ProdPrediVurnato.Probeg, ProdPrediVurnato.Kurs, TurgPredst.TurgPredstName, ProdPrediVurnato.Pla6tane, Mar6rut.Mar6rutName, Rajon.RajonName, ProdPrediVurnato.casNaPose6tenie, ProdPrediVurnato.ProverkaZaBrak FROM (TurgPredst INNER JOIN ((((((ProdPrediVurnato INNER JOIN art ON ProdPrediVurnato.[A ID] = art.[A ID]) INNER JOIN Client ON ProdPrediVurnato.ClientID = Client.ClientID) INNER JOIN Shofior ON ProdPrediVurnato.shofiorID = Shofior.shofiorID) INNER JOIN KlientskaGrupa ON Client.KlientskaGrupaID = KlientskaGrupa.KlientskaGrupaID) INNER JOIN CelevaGrupa ON Client.CelevaGrupaID = CelevaGrupa.CelevaGrupaID) INNER JOIN Mar6rut ON ProdPrediVurnato.Mar6rutID = Mar6rut.Mar6rutID) ON TurgPredst.TurgPredstID = Mar6rut.TurgPredstID) INNER JOIN Rajon ON Mar6rut.RajonID = Rajon.RajonID WHERE (((ProdPrediVurnato.Data) Between [Forms]![Switch]![Nachalo] And [Forms]![Switch]![Krai])) GROUP BY CInt(Format([Data],"ww",2,3)), Weekday([Data],2), Format([Data],"dddd",2,3), CDate(Format([Data],"dd\.mm\.yyyy",2,3)), ProdPrediVurnato.Mar6rutID, CelevaGrupa.CelevaGrupaName, KlientskaGrupa.KlientskaGrupaName, ProdPrediVurnato.ClientID, Client.ClientName, ProdPrediVurnato.shofiorID, Shofior.shofiorName, Shofior.VidPlasior, ProdPrediVurnato.VidArtikul, art.Grupa, art.ArtikulName, ProdPrediVurnato.Cena, ProdPrediVurnato.Probeg, ProdPrediVurnato.Kurs, TurgPredst.TurgPredstName, ProdPrediVurnato.Pla6tane, Mar6rut.Mar6rutName, Rajon.RajonName, ProdPrediVurnato.casNaPose6tenie, ProdPrediVurnato.ProverkaZaBrak HAVING (((ProdPrediVurnato.Mar6rutID) Like "*" & [Forms]![Switch]![izborM] & "*") AND ((ProdPrediVurnato.ClientID)<>"121385881") AND ((art.Grupa) Like "*" & [Forms]![Switch]![Combo63] & "*") AND ((art.ArtikulName) Like "*" & [Forms]![Switch]![Combo46] & "*"));
    You should move the entire HAVING clause into the WHERE clause. No need to put any critieria into a having clause that is not performed on an aggregate. More efficient to filter in the where clause where possible.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    The one problem I see is the Like statement in the Having clause. When you have the * in the front of the criteria (ex. *soemthing*) Access cannot use any indexes you have on that column and instead performs a table scan (one row at a time from the beginning of the table to the end). Do you absolutely need the * in front? Can you figure out a way to avoid it?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •