none
Identical queries: One is fast, the other slow RRS feed

  • Question

  • I have two queries that are doing the same - but using data from two different tables that have the same number of records. One of the queries is executed in less than a second while the other takes more than 10 seconds. I have tried to improve the slow query, but with no luck.

    I have made some tests that show that the problem is not related to data. Two queries that seem to be identical can be fast and slow!

    Test 1: If I simply copy the fast query, then as expected the copy is fast, too.

    Test 2: If I create a new query and copy the SQL command from the fast query to the new query, then the new query turns out to be slow! SQL for this query is:

    TRANSFORM Avg([Elstatistik x Paneldata m Normering].kW) AS AvgOfkW
    SELECT [Selected values].Region, [Selected values].Kategori, [Selected values].iYear, [Base Workday].sWorkday, [Base Month].sMonth
    FROM [Base Workday] INNER JOIN ([Base Month] INNER JOIN ([Base Hour] INNER JOIN ((([Base Time] INNER JOIN [Elstatistik x Paneldata m Normering] ON [Base Time].Time = [Elstatistik x Paneldata m Normering].Time) INNER JOIN [Selected values] ON ([Elstatistik x Paneldata m Normering].Region = [Selected values].Region) AND ([Elstatistik x Paneldata m Normering].Kategori = [Selected values].Kategori)) INNER JOIN [Base Date] ON ([Base Date].aDate = [Base Time].aDate) AND ([Selected values].iYear = [Base Date].iYear)) ON [Base Hour].iHour = [Base Time].iHour) ON [Base Month].iMonth = [Base Date].iMonth) ON [Base Workday].iWorkday = [Base Date].iWorkday
    GROUP BY [Selected values].Region, [Selected values].Kategori, [Selected values].iYear, [Base Workday].sWorkday, [Base Month].iMonth, [Base Month].sMonth
    ORDER BY [Base Workday].sWorkday DESC , [Base Month].iMonth, [Base Hour].sHour
    PIVOT [Base Hour].sHour;
    

    As can be seen I am using a Crosstab query. The number of records in data table [Elstatistik x Paneldata m Normering] is 6,628,608. It is a linked table.

    What is going on? I have made a "Compact and Repair" on both databases - and restarted my PC.

    Best regards, Helge.

    Friday, January 13, 2017 8:45 AM

Answers

  • Hi Helge,

    Thanks for sharing the test db, I made a test with this file under Access 2013, and I could reproduce this issue. As your found, it is related with table name.

    For a workaround, I would suggest you try “AS” in your query.

    Here is a query which works fast, pay attention to “INNER JOIN T2345678901234567 AS TT ON [Base Time].Time = TT.Time) ”.

    TRANSFORM Avg(TT.kW) AS AvgOfkW
    SELECT [Selected values].Region, [Selected values].Kategori, [Selected values].iYear, [Base Workday].sWorkday, [Base Month].sMonth
    FROM [Base Hour] 
    INNER JOIN ([Base Workday] 
    INNER JOIN ([Base Month] 
    INNER JOIN ((([Base Time] 
    INNER JOIN T2345678901234567 AS TT ON [Base Time].Time = TT.Time) 
    INNER JOIN [Selected values] ON (TT.Region = [Selected values].Region) AND (TT.Kategori = [Selected values].Kategori)) 
    INNER JOIN [Base Date] ON ([Base Date].aDate = [Base Time].aDate) AND ([Selected values].iYear = [Base Date].iYear)) ON [Base Month].iMonth = [Base Date].iMonth) ON [Base Workday].iWorkday = [Base Date].iWorkday) ON [Base Hour].iHour = [Base Time].iHour
    GROUP BY [Selected values].Region, [Selected values].Kategori, [Selected values].iYear, [Base Workday].sWorkday, [Base Month].iMonth, [Base Month].sMonth
    ORDER BY [Base Workday].sWorkday DESC , [Base Month].iMonth, [Base Hour].sHour
    PIVOT [Base Hour].sHour;

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 17, 2017 5:53 AM
  • Hi Edward

    Sorry that I did not mention that your query is fast.

    Okay, then it is not the SQL command length that matters.

    And it is not the "As NewTableName" that matters since I could make my first query fast by changing the name of the data table.

    So at the end of the day we know that there is something spooky going on and that we in my case have found a way to get around it. However, this is not very satisfactory: From now on, whenever I have a query that does not respond in a second I'll be suspicious that I again have met the spook.

    Does anyone know how to make Microsoft be aware of this conversation - and perhaps even comment on it!

    Best regards,
    Helge

    • Marked as answer by Helge Larsen Wednesday, January 18, 2017 8:47 AM
    Wednesday, January 18, 2017 8:47 AM
  • In another forum I have got an answer that could be THE answer (I have not yet checked it).

    See msoffice/forum/msoffice_access.

    The answer says: "Be careful with your use of Reserved Words".

    Allan Browne has made a Database Issue Checker Utility that will find reserved words in your database.

    Best regards,
    Helge
    • Marked as answer by Helge Larsen Wednesday, January 25, 2017 10:44 AM
    • Edited by Helge Larsen Wednesday, January 25, 2017 10:48 AM
    Wednesday, January 25, 2017 10:44 AM

All replies

  • Hi Helge

    Two tables, same number of records...

    But are they identical?
    When it comes to indexing fields?
    Does one of the tables har Auto on SubdatasheetName Property?

    Info on the SubdatasheetName Property here: Read it :-)


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00


    Friday, January 13, 2017 10:21 AM
  • Hi Peter

    Both tables had Auto on SubdatasheetName Property. I changed both to None. But no improvement.

    By the way, I saw that the fast query could switch from Datasheet View to Design View in a moment, whereas the slow query had a delay when switching (delay time equal to time used to execute the query). Strange!BR Helge

    Friday, January 13, 2017 10:44 AM
  • Hi again

    Made a new test:

    Originally, the two linked tables were placed in the same database. Now I have tried to copy the tables to two new databases, both have a size of 400 MB. Then I link to these new tables (in to databases). When I now run the two queries I still have a slow one and a fast one. BUT THE SLOW QUERY HAS TURNED FAST, AND THE FAST QUERY HAS TURNED SLOW.

    Perhaps the problem is with the linking of tables. Next, I will try to copy my datatables to the database that holds the queries, thus omitting linking.

    Best regards from a very confused Helge.

    Friday, January 13, 2017 11:09 AM
  • I have copied my datatables to the database that holds the queries: Slow query is still slow.

    Friday, January 13, 2017 12:23 PM
  • Hi Helge

    Strange things happens. Please try to open a new database. An empty one.
    Then from that empty database, Import all queries, forms, tables etc from the one that holds the fast and slow.


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00


    Friday, January 13, 2017 1:05 PM
  • Hi

    Have you tried to recompile the query?

    1. Open the Query in Design Mode.
    2. Save it.
    3. Reexecute it.


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Friday, January 13, 2017 1:10 PM
  • Helge -

    A couple thoughts come to mind -

    (1) I would compare the property sheets of both queries. Especially in light of your Copy the query vs copy the SQL test.

        (a) What is the Recordset type? For a Pivot, the Snapshot type has been better than the Dynasets for me. (To get to it, Propertysheet for the query. Show/hide on the ribbon, Propertysheet. Make sure you have selected the query & not a column or table).

        (b) You might try setting the crosstab query RecordLocks to No locks.

    (2) What is the performance of the underlying query w/o the crosstab/pivot? I like to build an underyling Select query first, then use that query in the crosstab/pivot. It's the steps that the engine uses anyway & you can use that to your troubleshooting advantage.


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com


    • Edited by MainSleuth Friday, January 13, 2017 2:01 PM Reorganized ideas
    Friday, January 13, 2017 1:57 PM
  • Hi again

    I have recompiled the query: No improvement!

    You wrote: Please try to open a new database. An empty one. Then from that empty database, Import all queries, forms, tables etc from the one that holds the fast and slow.

    I have tried: Fast is still fast, slow is still slow!

    You wrote: What is the performance of the underlying query w/o the crosstab/pivot?

    I had already tried to split the query into a Select query (actually a 'Totals' query calculation an average) and a Crosstab query using the Select query. It turned out that also the Select query is slow.

    Now I will try your other suggestions.

    BR Helge

    Friday, January 13, 2017 2:38 PM
  • I had already tried to split the query into a Select query (actually a 'Totals' query calculation an average) and a Crosstab query using the Select query. It turned out that also the Select query is slow.

    Hi Helge,

    Did you look at the keys of the fields that are used in the join part?

    Imb.

    Friday, January 13, 2017 2:49 PM
  • Hi MainSleuth

    Query Property Sheet:

          Record Locks was already 'No Locks'.

          Changed Recordset Type from 'Dynaset' to 'Snapshot': No improvement!

    Now I think it is time for weekend. Perhaps I will then get a good idea by NOT thinking of the problem ;-)

    Have a nice weekend, Helge

    Friday, January 13, 2017 2:55 PM
  • Hi Helge,

    What is your Access version? It is amazing based on your description. Are you able to create a demo Access database and share us through OneDrive, and then we could try to reproduce your issue at our side.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 16, 2017 7:18 AM
  • Hi everyone,

    I have isolated  the problem in a database "Slow query.accdb" that you can download from OneDrive:

    https://1drv.ms/u/s!ApFgZHtizNrSbco074Xt8n-wQmQ

    Actually, I am not sure that I have isolated THE problem. Perhaps my database only illustrates a small corner of the problem.

    The database holds a query "myQuery" that draws on the large table "T2345678901234567" (6,628,608 records) and other small tables. It takes 15 seconds to run the query. But if I rename the table to "T234567890123456", the query is run in less than a second. That is, the query turns slow if length of table name exceeds 16.

    I am using Access 2013.

    Best regards,
    Helge

    Monday, January 16, 2017 12:12 PM
  • Hi Helge,

    Thanks for sharing the test db, I made a test with this file under Access 2013, and I could reproduce this issue. As your found, it is related with table name.

    For a workaround, I would suggest you try “AS” in your query.

    Here is a query which works fast, pay attention to “INNER JOIN T2345678901234567 AS TT ON [Base Time].Time = TT.Time) ”.

    TRANSFORM Avg(TT.kW) AS AvgOfkW
    SELECT [Selected values].Region, [Selected values].Kategori, [Selected values].iYear, [Base Workday].sWorkday, [Base Month].sMonth
    FROM [Base Hour] 
    INNER JOIN ([Base Workday] 
    INNER JOIN ([Base Month] 
    INNER JOIN ((([Base Time] 
    INNER JOIN T2345678901234567 AS TT ON [Base Time].Time = TT.Time) 
    INNER JOIN [Selected values] ON (TT.Region = [Selected values].Region) AND (TT.Kategori = [Selected values].Kategori)) 
    INNER JOIN [Base Date] ON ([Base Date].aDate = [Base Time].aDate) AND ([Selected values].iYear = [Base Date].iYear)) ON [Base Month].iMonth = [Base Date].iMonth) ON [Base Workday].iWorkday = [Base Date].iWorkday) ON [Base Hour].iHour = [Base Time].iHour
    GROUP BY [Selected values].Region, [Selected values].Kategori, [Selected values].iYear, [Base Workday].sWorkday, [Base Month].iMonth, [Base Month].sMonth
    ORDER BY [Base Workday].sWorkday DESC , [Base Month].iMonth, [Base Hour].sHour
    PIVOT [Base Hour].sHour;

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 17, 2017 5:53 AM
  • Hi Edwards

    Probably it is not the length of the table name, but length of SQL command that matters.

    I have found this link: https://www.progress.com/faqs/datadirect-openaccess-troubleshooting-faqs/progress-datadirect-openaccess-sdk-faq-for-odbc

    It says: "The Interactive SQL tool supports command length maximum of 1024."

    However, I'm not sure that it deals with MS Access.

    Best regards,
    Helge

    Tuesday, January 17, 2017 9:33 AM
  • Hi Helge,

    Did my above query work fast at your side? As my test, I think it is not related with command length. If I replace “TT” with “T01234567890123456789” and output the query length of your query and my new query, length of my query is larger than yours, but my query is fast.

    Here is the code for query length.

    Sub test()
    Dim objdefslow As QueryDef
    Dim queryslow As String
    Set objdefslow = CurrentDb.QueryDefs("myQuery")
    queryslow = objdefslow.SQL
    Debug.Print Len(queryslow)  ‘output 1015
    Dim objdeffast As QueryDef
    Dim queryfast As String
    Set objdeffast = CurrentDb.QueryDefs("newQuery")
    queryfast = objdeffast.SQL
    Debug.Print Len(queryfast) ‘output 1056
    End Sub

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 18, 2017 5:59 AM
  • Hi Edward

    Sorry that I did not mention that your query is fast.

    Okay, then it is not the SQL command length that matters.

    And it is not the "As NewTableName" that matters since I could make my first query fast by changing the name of the data table.

    So at the end of the day we know that there is something spooky going on and that we in my case have found a way to get around it. However, this is not very satisfactory: From now on, whenever I have a query that does not respond in a second I'll be suspicious that I again have met the spook.

    Does anyone know how to make Microsoft be aware of this conversation - and perhaps even comment on it!

    Best regards,
    Helge

    • Marked as answer by Helge Larsen Wednesday, January 18, 2017 8:47 AM
    Wednesday, January 18, 2017 8:47 AM
  • In another forum I have got an answer that could be THE answer (I have not yet checked it).

    See msoffice/forum/msoffice_access.

    The answer says: "Be careful with your use of Reserved Words".

    Allan Browne has made a Database Issue Checker Utility that will find reserved words in your database.

    Best regards,
    Helge
    • Marked as answer by Helge Larsen Wednesday, January 25, 2017 10:44 AM
    • Edited by Helge Larsen Wednesday, January 25, 2017 10:48 AM
    Wednesday, January 25, 2017 10:44 AM