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...
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.
Do not email me - If I respond it is for everyones benefit, not just yours. Other people make have the same problem and are searching the forums for an answer, if I email a response those people will not benefit.
ahmed eldeghedy wrote:
what i mean
when i take instans form the object (tabel database)
i whant the data bases retrun table by the data not block
That still does not make sense. What do you mean by "block"? How do you want data blocked? What sort of data do you want blocked?
Perhaps you want to revoke access to the tables and have everything done through views and grant access to the views based on the role the user has. Perhaps you want to do everything through stored procedures which will only do what the stored procedure has been programmed to do and nothing more. But I just don't know from your description.
Table Name dbo.Books where I kept Books Information. At First You need to put All information on dbo.Books table. Now this book is not Active because I need to make a Copy of this book which will be stored at dbo.CopyBooks table.
1001 Sql Server 2000
1002 Sql Server 2005
On my frmAddCopy.vb
When You click btnAddCopy then only ISBN will send to database and return BookID. Also How will I incresed copyID in database. Please tell me more information. I dont know how to use function and procedure in SQL SERVER 2005 dataBase.
Is their any thing that i can do it in sql server 2005 database?
If you don't know how to use functions or procedures in SQL Server, you should buy a book and start learning. I don't think I can help you much if you don't have basic knowledge of the tools you are using.
If you aren't familiar with the SQL language, this[^] may be a good place to start. Look at using the MAX aggregate function to help you increment the CopyID sequence.
First of all, don't shout. It's rude. This is a free site, so be grateful that people are willing to give up their time to help you.
Secondly, this is pretty basic arithmatic isn't it? Calculate the number of days difference between the ReturnDate and SubmittedDate using the DATEDIFF function and multiply the result by the daily fine to get the overall fine.
i am using an oledbcommand object to perform both insert and update based on the command text supplied and i am adding the parameters for update also in the order of insert. So incase of update though, its not giving any error/exception but database is not getting updated.
Can any body pls tell me how to resolve this or any other way to implement both the functionalities using single method.
The order of parameters should not be an issue. You need to first check your update stored procedures or statements. The easiest way to do this is to trying and execute them (using EXEC) directly in the database. This will enable you confirm whether they are OK.
For something like this you might consider using cursors - things which iterate through your table like a traditional loop. They're generally best avoided although I think quite appropriate in this case.
Last Visit: 31-Dec-99 18:00 Last Update: 28-Sep-23 22:23