Queries

  • I am looking to see if there are any ways to improve the run time for select queries. Thoughts and ideas?

    I have select * from Table takes approx 40 mins. I know if I select required columns that might be little faster. But I need all columns for ETL. Select w/o data from tables takes less than a 1 minute.

    So bulk into table with data is approx 55 mins. Bulk into table w/o data is less than 30 secs.

     

  • SELECT * FROM TABLE can't really be optimised, as far as I am aware. It's a full table scan.

    (Even though you want all the columns, you should still name them, rather than using *)

     

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Do you have any indexes on the table you are inserting into?

    If there are and it's loading into an empty table then it would be faster to disable all indexes (except any clustered index) insert the data then enable the indexes.

    If there are foreign keys on the table then it would be faster to disable the foreign keys while inserting and enable them again after the insert.

    If you make the batch size at least as big as the number of rows you are inserting it will make it faster.

    Does the query you are getting the data from have joins?

    If so it might be possible to improve it with the addition of some indexes.

    It would be helpful if you could provide the DDL of the tables and the insert statement.

  • In addition to Jonathan's excellent advice, be sure to also pre-allocate enough log space to handle the full INSERT, especially if the log growth is a small amount.  Log space must be pre-formatted, and doing that multiple times as INSERTs are being done is very slow.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • How many rows are in the table?

  • I am looking to see if there are any ways to improve the run time for select queries.

    This was the request I responded to. The remainder of your post provides only background information.

    Other who responded have made (high-quality) educated guesses, perhaps accurately discerning the question that you intended to ask, but for some reason were unable to put into words.

    Perhaps in future you could put more thought into asking the question you actually want answers to, rather than leaving it to guesswork?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Thanks. Actually I have a JSON querying which is taking longer time for half million rows take approx. 15 mins to finish. However using query hints it runs faster. Any other thoughts?

  • You should probably just post your query.

  • Admingod wrote:

    Thanks. Actually I have a JSON querying which is taking longer time for half million rows take approx. 15 mins to finish. However using query hints it runs faster. Any other thoughts?

    Yes - don't use JSON.

     

    and don't jump from one question to another - select * from table isn't the same as a select from json - and not only that but you failed again to supply any useful info.

    telling us "the car is broken. what is the issue" doe not help anyone and does not allow us to help you - so the thoughts most of us have when we see your questions is "not this guy again"

  • It seems like you use a SQL Server database as a dumb storage for the application data.

    all the business logic is in the application, the data stored in the format used by application (JSON in you case) and only thing which is required from the SQL Server is to take the data, save it on disk and return it back on request.

    iIt was all so smart and brilliant at the beginning, but now, when the data base he's grown, it started to hurt.

    the are 2 ways out of it.

     

    1. Hire a SQL Server developer. I mean - a proper one. Knowing the syntax of basic SQL command would not be sufficient.

    Let him/her develop a proper relational database, with all the so much hated normalisation in place, implement the business logic on the database level, so there would not need to pump whole the database from the database server to the application server in order to find 10 latest transactions for some account.

    Build interfaces between app and db layers, usually in form of stored procedure calls, which gives you a flexibility of implementing changes in different parts of app or db without the necessity of redeploying the whole system.

     

    2. Ditch SQL Server and move to file storage system.

    SQL Server is insanely expensive platform for storing JSON files, and it's so much ineffective in terms of overheads involved in the process. For the cost of a license you can buy so much cloud file storage, with all its bells and whistles.

    It will also improve performance. Nothing to compare with performance of properly designed relational database, but still much better comparing to what you have now.

  • Thanks and appreciated!

  • The only real way to improve "move all the data, all at once, right now" style queries is spending money. Buy bigger, better, faster, more hardware. That's the answer. Or, change the way you're moving data around.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply