none
Azure SQL database performance help RRS feed

  • Question

  • Hi

    I'm having serious trouble with an Azure SQL database that I am hoping somebody might be able to help me with.

    My database is a standard tier (S4) with 200 DTUs

    From time to time I will need to clear and reupload a large set of records to a single table with one primary key and no foreign key.

    Yesterday I uploaded 81000 records and it took 70 minutes!!!

    I have not tried to delete these records as I'm sure this will take at least twice as long.

    Can anyone instruct me as to what I might be doing wrong or what I can do to get this database to a point where it is useable>?
    Tuesday, October 15, 2019 8:43 AM

All replies

  • Hi

    How do you upload? SSIS package? From on premise to SQL Azure? Do you upload data row by row or in one statement?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, October 15, 2019 9:32 AM
  • From an on premises Access database using an insert query. All in the one query.
    Tuesday, October 15, 2019 10:34 AM
  • Have you read this article?

    https://docs.microsoft.com/en-us/sql/ssma/access/migrating-access-databases-to-sql-server-azure-sql-db-accesstosql?view=sql-server-ver15

    Personally I have not done such migration but is that possible to load data into SQL Server on premise and then load into SQL Azure?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, October 15, 2019 4:05 PM
  • Hi - I think I agree with Uri Dimant. Could you please try to load the data into SQL Server on premises and then load into Azure SQL Server from there.

    Also, I suggest, please try to see if you get any performance alerts / recommendations as you load the data into Azure. Please check the Intelligent Performance and Monitoring tabs for your Azure SQL DB resource. 

    Wednesday, October 16, 2019 10:45 AM
  • Hi

    It's not a migrate I am trying to achieve. It is an append query from an on premises access database. We have an access database running in house, then an azure SQL database that functions as the back end for our mobile apps. Users on premises create information in Access and then run an append query that sends the data to the Azure SQL database. I do not have an on premises SQL server.
    Wednesday, October 16, 2019 3:22 PM
  • You can install SQL Server EXPRESS (free) EDITION and then load the data to SQL Azure

    Can you explain how you query the data? Do you connect from ACCESS to AZURE?

    Is it possible to load the data into the chunks , 10000 rows for example?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, October 17, 2019 6:10 AM
  • Hi

    Most of the information is stored in tables inside an access database. I have a linked table inside the access database that links to the Azure SQL database.

    I have an append and a delete query in access that appends and deletes data from multiple access tables and the Azure SQL table.

    In an attempt to streamline the process I have a number of access queries that build a temporary table in access, then an append query that appends all records from the temp table to the linked Azure SQL table, all still inside access.

    This is taking forever in terms of time, but according to my SQL performance checks I am only using <2% of my DTUs

    Appending and deleting from tables inside access takes a matter of seconds, but appending and deleting from the linked Azure table is taking hours!!

    I want this ability to be available for all users from the access front end. i.e. users create attendance records and job information in access, then append that data to the Azure tables so users in the field can view it in their app.

    "Is it possible to load the data into the chunks , 10000 rows for example?"

    No

    Thursday, October 17, 2019 1:24 PM
  • Please see the following if you have not already: Migrating Access databases to SQL Server - Azure SQL DB (AccessToSQL)

    Please let us know if you have any additinal questions.

    Regards,

    Mike

    Friday, October 18, 2019 1:57 AM
    Moderator
  • Hi Mike

    Please see comments above.

    It is not a migrate i am trying to achieve.

    Monday, October 21, 2019 8:17 AM
  • The challenge here is that you are running the Standard service tier which has no OLTP memory allocation. Is it possible for you to do a POC with a Premium service tier instance? The other possibility is enabling use of the tempdb with your T-SQL statements: TempDB database in SQL Database

    At the service level you are currently running, you have 32Gb available and you have 2 TempDB data files. So, if you can enable any sort operation:

    SORT_IN_TEMPDB = { ON | OFF }

    Regards,

    Mike

    Monday, October 21, 2019 11:02 PM
    Moderator
  • Hi Mike

    I have scaled the DB up to Premium P1: 125 DTUs, 200 GB storage and re run the test.

    This time took 30 minutes to DELETE 37000 records and 30 minutes to INSERT 37000 records.

    Is this the best I can hope for in terms of performance or is there anything more that could possible be done?

    Again these INSERT and DELETE queries take a matter of seconds to complete on local access tables, which seems to outperform Azure SQL tables by an extreme margin.

    Tuesday, October 22, 2019 2:16 PM
  • Do you have an Azure Support Plan? If you do, please create a support request and send me the support request ID. With it, we have greater visibility into your specific deployment. If you do not have an Azure support plan, please send me your Azure subscription ID and I can have a one-time support request to have this specific issue investigated. I will return specific instructions to engage Azure support. I can be reached at AzCommunity.

    Please let me know if you have any additional questions.

    Regards,

    Mike

    P.S. If you are subscribed to a CSP, you need to engage your CSP to have this investigated.

    Tuesday, October 22, 2019 4:54 PM
    Moderator
  • Hi Mike

    I have just sent you on an email now. If you could look into this for me that would be great
    Wednesday, October 23, 2019 1:18 PM