Performance issue
-
What is the best way to write an SQL stored procedure: by using temporary tables (e.g #tmpSomething or @tmpSomething) or by using multiple selects? How is this affecting the speed of execution and the memory usage? Thank you in advance.
I am fighting against the Universe... Reference-Rick Cook
-
What is the best way to write an SQL stored procedure: by using temporary tables (e.g #tmpSomething or @tmpSomething) or by using multiple selects? How is this affecting the speed of execution and the memory usage? Thank you in advance.
I am fighting against the Universe... Reference-Rick Cook
Albu Marius wrote:
What is the best way to write an SQL stored procedure: by using temporary tables (e.g #tmpSomething or @tmpSomething) or by using multiple selects? How is this affecting the speed of execution and the memory usage? Thank you in advance.
It depends on what query you are running. There is no simple answer. Experiment, see what is better. Then 6 months later when your database schema has changed expect the performance metrics to have changed that your initial findings no longer hold true.
Upcoming FREE developer events: * Glasgow: SQL Server Managed Objects AND Reporting Services ... My website
-
Albu Marius wrote:
What is the best way to write an SQL stored procedure: by using temporary tables (e.g #tmpSomething or @tmpSomething) or by using multiple selects? How is this affecting the speed of execution and the memory usage? Thank you in advance.
It depends on what query you are running. There is no simple answer. Experiment, see what is better. Then 6 months later when your database schema has changed expect the performance metrics to have changed that your initial findings no longer hold true.
Upcoming FREE developer events: * Glasgow: SQL Server Managed Objects AND Reporting Services ... My website
I'm sorry, I should have been more specific in my post. The situation : 2 selects (not very large)each returning about 3000 records . One select is saved in a temporary table (#tmpSomething) and the other one is joined with the first and the result is outputed (duration - few miliseconds). I was advised to never use temporary tables because of the performance issues. I know this is the case of @ tables which store all the data in memory and for a large number of records (tens of thousands) the SQL Server hangs for a time before returning the result. But my problem is why can 't I use # table (which are stored on disk)? Is the impact to performance so big due to the I/O operations? As I said I was advised to always use a select contained in the bigger select instead of temporary tables. This seems to me as a bad practice because this way the SQL code if pretty hard to understand and I'm not sure this resolves the performance issues. If someone can shed some light on this dilema of mine , please do.
I am fighting against the Universe... Reference-Rick Cook
-
I'm sorry, I should have been more specific in my post. The situation : 2 selects (not very large)each returning about 3000 records . One select is saved in a temporary table (#tmpSomething) and the other one is joined with the first and the result is outputed (duration - few miliseconds). I was advised to never use temporary tables because of the performance issues. I know this is the case of @ tables which store all the data in memory and for a large number of records (tens of thousands) the SQL Server hangs for a time before returning the result. But my problem is why can 't I use # table (which are stored on disk)? Is the impact to performance so big due to the I/O operations? As I said I was advised to always use a select contained in the bigger select instead of temporary tables. This seems to me as a bad practice because this way the SQL code if pretty hard to understand and I'm not sure this resolves the performance issues. If someone can shed some light on this dilema of mine , please do.
I am fighting against the Universe... Reference-Rick Cook
Albu Marius wrote:
I was advised to never use temporary tables because of the performance issues.
That is rubbish. A few years ago I went from taking 20 minutes for a query down to 7 seconds by introducing a couple of temporary tables. (The source table was growing at a gigabyte per week, so there was A LOT of data in there)
Albu Marius wrote:
I know this is the case of @ tables which store all the data in memory and for a large number of records (tens of thousands) the SQL Server hangs for a time before returning the result
That is also incorrect. Table variables (or @ tables as you put it) are just anothe form of temp table. They will get flushed to the TEMPDB if it needs to, but typically you'd use them in situations where the dataset was small enough that it wouldn't go there.
Albu Marius wrote:
But my problem is why can 't I use # table (which are stored on disk)?
Again, incorrect. If they are small enough and used quickly enough they will be created, populated and dropped without needing to be flushed to disk. Of course the log will still be updated.
Albu Marius wrote:
Is the impact to performance so big due to the I/O operations?
Seriously, you need to experiment. There is so much going on that you can't know otherwise. I don't know, you don't know and anyone that says they do know is lying. The myths you have picked up are a result of someone at ONE time seeing that it operates in a particular way. The next query with the same technique make operate differently because the query optimiser recons it will be better.
Albu Marius wrote:
As I said I was advised to always use a select contained in the bigger select instead of temporary tables. This seems to me as a bad practice because this way the SQL code if pretty hard to understand and I'm not sure this resolves the performance issues. If someone can shed some light on this dilema of mine , please do.
You are right it is bad practice. But the bad practice is to have a rule that doesn't take in to account the current environment your query is operating in. Temp tables can improve performance (as I demonstrated above) and quite significantly. They can also reduce performance. Which is why you need to experiment with different ways of do
-
Albu Marius wrote:
I was advised to never use temporary tables because of the performance issues.
That is rubbish. A few years ago I went from taking 20 minutes for a query down to 7 seconds by introducing a couple of temporary tables. (The source table was growing at a gigabyte per week, so there was A LOT of data in there)
Albu Marius wrote:
I know this is the case of @ tables which store all the data in memory and for a large number of records (tens of thousands) the SQL Server hangs for a time before returning the result
That is also incorrect. Table variables (or @ tables as you put it) are just anothe form of temp table. They will get flushed to the TEMPDB if it needs to, but typically you'd use them in situations where the dataset was small enough that it wouldn't go there.
Albu Marius wrote:
But my problem is why can 't I use # table (which are stored on disk)?
Again, incorrect. If they are small enough and used quickly enough they will be created, populated and dropped without needing to be flushed to disk. Of course the log will still be updated.
Albu Marius wrote:
Is the impact to performance so big due to the I/O operations?
Seriously, you need to experiment. There is so much going on that you can't know otherwise. I don't know, you don't know and anyone that says they do know is lying. The myths you have picked up are a result of someone at ONE time seeing that it operates in a particular way. The next query with the same technique make operate differently because the query optimiser recons it will be better.
Albu Marius wrote:
As I said I was advised to always use a select contained in the bigger select instead of temporary tables. This seems to me as a bad practice because this way the SQL code if pretty hard to understand and I'm not sure this resolves the performance issues. If someone can shed some light on this dilema of mine , please do.
You are right it is bad practice. But the bad practice is to have a rule that doesn't take in to account the current environment your query is operating in. Temp tables can improve performance (as I demonstrated above) and quite significantly. They can also reduce performance. Which is why you need to experiment with different ways of do
Thank you for your advice. Your explanations cleared this issue for me.
I am fighting against the Universe... Reference-Rick Cook