Click here to Skip to main content
15,394,358 members
Home / Discussions / Database
   

Database

 
AnswerRe: Table comparison in sql server 2000 Pin
Hesham Amin12-Oct-07 8:41
MemberHesham Amin12-Oct-07 8:41 
Questionvarchar and nvarchar Pin
Brendan Vogt11-Oct-07 3:55
MemberBrendan Vogt11-Oct-07 3:55 
AnswerRe: varchar and nvarchar Pin
Rob Philpott11-Oct-07 4:35
MemberRob Philpott11-Oct-07 4:35 
GeneralRe: varchar and nvarchar Pin
Mark Churchill11-Oct-07 17:48
MemberMark Churchill11-Oct-07 17:48 
QuestionPerformance issue Pin
Albu Marius11-Oct-07 3:35
MemberAlbu Marius11-Oct-07 3:35 
AnswerRe: Performance issue Pin
Colin Angus Mackay11-Oct-07 3:43
MemberColin Angus Mackay11-Oct-07 3:43 
GeneralRe: Performance issue Pin
Albu Marius11-Oct-07 4:34
MemberAlbu Marius11-Oct-07 4:34 
GeneralRe: Performance issue Pin
Colin Angus Mackay11-Oct-07 5:35
MemberColin Angus Mackay11-Oct-07 5:35 
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 doing things.


Upcoming FREE developer events:
* Glasgow: SQL Server Managed Objects AND Reporting Services ...

My website

GeneralRe: Performance issue Pin
Albu Marius11-Oct-07 6:31
MemberAlbu Marius11-Oct-07 6:31 
Questionhow to block ? Pin
ahmed eldeghedy11-Oct-07 2:58
professionalahmed eldeghedy11-Oct-07 2:58 
AnswerRe: how to block ? Pin
Colin Angus Mackay11-Oct-07 3:42
MemberColin Angus Mackay11-Oct-07 3:42 
GeneralRe: how to block ? Pin
Paul Conrad12-Oct-07 12:04
professionalPaul Conrad12-Oct-07 12:04 
AnswerRe: how to block ? Pin
Colin Angus Mackay11-Oct-07 7:00
MemberColin Angus Mackay11-Oct-07 7:00 
GeneralRe: how to block ? Pin
Paul Conrad12-Oct-07 12:05
professionalPaul Conrad12-Oct-07 12:05 
QuestionCopy Information Pin
Sarfaraj Ahmed11-Oct-07 0:58
MemberSarfaraj Ahmed11-Oct-07 0:58 
AnswerRe: Copy Information Pin
pmarfleet11-Oct-07 2:00
Memberpmarfleet11-Oct-07 2:00 
GeneralRe: Copy Information Pin
Sarfaraj Ahmed11-Oct-07 3:19
MemberSarfaraj Ahmed11-Oct-07 3:19 
GeneralRe: Copy Information Pin
pmarfleet11-Oct-07 8:59
Memberpmarfleet11-Oct-07 8:59 
GeneralRe: Copy Information Pin
Sarfaraj Ahmed11-Oct-07 3:55
MemberSarfaraj Ahmed11-Oct-07 3:55 
GeneralRe: Copy Information Pin
pmarfleet11-Oct-07 9:00
Memberpmarfleet11-Oct-07 9:00 
GeneralRe: Copy Information Pin
Sarfaraj Ahmed11-Oct-07 15:09
MemberSarfaraj Ahmed11-Oct-07 15:09 
GeneralRe: Copy Information Pin
pmarfleet11-Oct-07 21:39
Memberpmarfleet11-Oct-07 21:39 
Questionupdate database(urgently) Pin
Milind Panchal10-Oct-07 22:51
MemberMilind Panchal10-Oct-07 22:51 
AnswerRe: update database(urgently) Pin
N a v a n e e t h10-Oct-07 23:09
MemberN a v a n e e t h10-Oct-07 23:09 
AnswerRe: update database(urgently) Pin
Rob Philpott11-Oct-07 4:46
MemberRob Philpott11-Oct-07 4:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.