October 29, 2020 at 4:29 pm
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.
October 29, 2020 at 4:49 pm
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 *)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 29, 2020 at 4:49 pm
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.
October 29, 2020 at 5:22 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 29, 2020 at 5:22 pm
How many rows are in the table?
October 29, 2020 at 7:27 pm
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?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 29, 2020 at 8:33 pm
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?
October 29, 2020 at 8:47 pm
You should probably just post your query.
October 29, 2020 at 9:41 pm
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"
October 29, 2020 at 10:24 pm
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.
_____________
Code for TallyGenerator
October 30, 2020 at 12:01 am
Thanks and appreciated!
October 30, 2020 at 11:51 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply