How to optimize sql query
Posted by Wasd8800@reddit | learnprogramming | View on Reddit | 5 comments
I am currently learning sql and I try to write a procedure in sql server where I used to take data from more than four table where each table has large amount of data due to that it take more than minutes to execute a procedure.can you guys can give me some advice to optimize it.
aqua_regis@reddit
Not a single word of actually usable information to help you.
Read your post from our point of view, for what we know. Then, figure out how we could possibly help you.
Wasd8800@reddit (OP)
Sorry for not explaining the problem clearly. I just want to know is there any proper way I need to follow when I implement join query in sql for performance optimisation
aqua_regis@reddit
Yet, there are too many variables to be able to give a generic help.
You have to explain your problems in far deeper details.
Just verbal rambling and beating about the bush doesn't help.
plastikmissile@reddit
It's impossible to give advice without context. So it would be much better if you told us exactly what you're trying to achieve, what your DB looks like, and what your query looks like. However, generally these are some of the things you need to ask yourself.
Do you really need all of this data? Instead of using
select *just select the columns you actually need. Also, depending on your scenario you may not need all of the data in the table, just the first 100 or so records for instance.Are you using indexes? I'm guessing you're using a lot of joins which means keys and the like are being accessed constantly.
Are you joining properly? A badly crafted
joincan be terrible for your query's performance.Have you used a SQL profiler? They'll tell you which parts of your query are taking a long time.
Svorky@reddit
Indexes indexes and indexes.
Beyond that you can look at the execution plan to figure out the expensive parts, or just comment out stuff and compare for a more hacky way.
Then see if pulling those bits out changes stuff. Window functions for example are often very expensive and doing the necessary calculations before the main query can speed things up a lot.