dcsimg
Results 1 to 15 of 15

Thread: [RESOLVED] Slow results from Azure Query

  1. #1

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Resolved [RESOLVED] Slow results from Azure Query

    I have a table on Azure with 700k rows. I have a select query that returns the integer primary key and string columns. It is using an integer and a Boolean column in the WHERE and AND condition that returns about 70k rows. It's taking over 5 minutes to perform. What am I doing wrong?
    I don't know much about indexing, but my first thought was that the columns used in the condition are not indexed. Am I headed i the right direction? Anything you can suggest?

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,031

    Re: Slow results from Azure Query

    Have you tried executing queries that return fewer rows and increasing the size of the result to see how the execution time changes as a function of the size of the result set? If the issue is indexing then small result sets will still be slow because every record still needs to be compared to your conditions. You should be using indexes on appropriate columns but that may not be the biggest factor.

  3. #3
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,297

    Re: Slow results from Azure Query

    I would connect to the Azure database via SSMS and try the query there see how long it takes. Maybe turn on include actual query plan and see if you are table scanning the table, or index (want to see an index seek most of the time). Those should point you at if you are missing an index or not.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  4. #4

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Slow results from Azure Query

    I have often heard of the "plan" that you speak of Gary. I don't know how to do that. Can you point me to a tutorial someplace on how to use that?

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,839

    Re: Slow results from Azure Query

    Use the QUERY menu - the 4th menu from left (File, Edit, View and then Query). 2-thirds of the way down you see "INCLUDE ACTUAL EXECUTION PLAN". Select that menu item.

    Now execute your query in a query window.

    Instead of just getting a RESULT and MESSAGE window - you also now get an EXECUTION PLAN window.

    That window shows you each part of what was done to make your query run. Percentages are listed as to where all the time was spent. Look for those spots and you will be enlightened (or further confused!)

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Slow results from Azure Query

    Thanks! I'm setting up some tests to answer John's post and this will be the perfect time to use this.

  7. #7

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Slow results from Azure Query

    When the results set was small, it was still taking a long time. I read about indexing and I saw that the execution plan was creating an index as part of it's process. So I borrowed that and added a non-clustered index for the two columns I frequently use in select queries and I was astonished how fast it was. I tested once before it and took 382 seconds. After I added the index, the results of 70+k rows appeared in 1 second. Amazing. So I think this is my solution.
    I have one parting question. Does the server periodically update this index or is it maintained as changes are made to the table?

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,839

    Re: Slow results from Azure Query

    Quote Originally Posted by cory_jackson View Post
    I have one parting question. Does the server periodically update this index or is it maintained as changes are made to the table?
    For the most part indexes are maintained during INSERT and UPDATE operations.

    It can generally be said that each INDEX you add creates another I/O requirement for each INSERT. With that said adding just one index will basically make INSERT's take twice as long. Add another index, three times as long. These insert times are usually minimal, so usually not a concern. But keep this all in mind as you consider adding your 14th or 15th index to a table!

    Usually the index "blocks" on disk are only filled to a certain percentage - let's say 50%. That way there is room for new insert's to go into the "index block" without having to perform some kind of bucket split with pointers.

    At some point indexes become a mess - not sure if they get re-built during lazy-time or if you have to specifically re-build indexes. I've never personally did any index re-building myself - at least not in MS SQL. Older mainframe type SQL database we were much more on top of index tuning...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Slow results from Azure Query

    That's what I suspected about being maintained with operations. In fact I doubt it could work without that because a second alter the program often reruns a query where ti expects to find that changed row. But I was thinking perhaps it maintained an index then a small supplement for differences since the last index. I seem to remember Exchange Information Stores doing something like this.
    Interesting what you say about the space in the blocks. I have maintained paper (not digital) technical documents like this. This way when one updates the table if contents, index, table of illustrations, one only needed to change the ones after the change but limited to that section. Otherwise one would have to change all of them though the rest of the manual.
    One thing I was thinking about is clustered versus non-clustered. As I understand it, clustered means it saves the actual data of the tables in the order of the index. And non-clustered are not which allows them to simply add data to the end and not have to insert at a given point and reorganize all the data that follows. It seems that non-clustered is good enough for me, but do clustered indexes work like I described?

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,839

    Re: Slow results from Azure Query

    There has to be a reason to use clustered - in that you are going to GRAB lots of records for a particular student id, for instance.

    So you have a STUDENT CLASS table storing all the classes a student can take in HS. Primary Key is STUDENT ID + YEAR + COURSE + SECTION, for instance.

    If you make that INDEX clustered all the student data will most likely reside in a single 8000 byte cluster on disk. If you routinely get ALL STUDENT data then having the ability to serve that QUERY with potentially a SINGLE I/O operation is amazing return on investment.

    You can also create an INDEX and put data fields into it. Let's say you regularly need to hit your CHECK TABLE to get all checks ENTERED last month with the amount of the check. Make the CHECK DATE be an alternate index and put the amount in that INDEX as the second key. Now SELECT SUM(AMOUNT) FROM CHECKTABLE WHERE CHECKDATE BETWEEN '2019-01-01' and '2019-01-31' runs against only that index.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #11

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Slow results from Azure Query

    Oh cool. So that would be even faster than a stored procedure. Neat.

  12. #12
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,839

    Re: Slow results from Azure Query

    Whether that query is in a SPROC or not has no bearing...

    The fact that the SELECT can get the "rows" with a certain check date and at the same time get the amount without ever doing I/O back to the disk is the trick here.

    I/O is the evil cause of speed problems in queries...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  13. #13

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Slow results from Azure Query

    I see. Thanks for the edification. I have much to learn.

  14. #14
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,297

    Re: Slow results from Azure Query

    Think of it this way on a clustered index... the cluster is how the rows are stored in order in the table. A non-clustered index does not reorder the status on disk. There are trade off to both (I normally use the primary key as the clustered index of a table and have it set to BIGINT and use the identity property to fill it). When you query a table and get the index use (non-clustered) and need more columns then are in the index you perform a second read of the table (the RowID is stored on each row of the index) back to the table with the ROWID and get the other necessary columns. If this is a problem (lots of calls to the index and getting columns not in the index) then I change that index to something called a covering index (you add and included column list at the end of the create index statement). This prevents the second read back (IO) for the extra columns but of course there is a cost. The cost is the amount of storage the index takes on disk.

    As to maintaining indexes I do this on a weekly basis on my production systems. (most are over 2TB in size). Keeping the index from getting to fragmented (a re-index command) helps the search as we leaf pages are in the proper location and we don't get pointers to other pages for the data.

    I use SQL Server 2016-2017 for my production systems right now. But same concepts were in place from 2005 for covering index and maintenance from before that.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  15. #15

    Thread Starter
    Fanatic Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    858

    Re: Slow results from Azure Query

    Thank you Gary. That's interesting. And it makes sense.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width