Solved

ADO.NET code to update a temporary table

Posted on 2010-08-26
5
454 Views
Last Modified: 2013-12-17
Hi,

I have some simple code below and I would like to get some ADO.NET code to update a temporary table. Hopefully using Datasets / data tables.



Thanks,

Ward
SqlConnection myConn = new SqlConnection("Data Source=localhost\\SQLEXPRESS;Initial Catalog=CarDB;Integrated Security=True;database=CarDB");
            string str = "create table #temp1 (col1 int, col2 int);";
 
            // Code to do a dataset to insert some records.
            
            SqlCommand myCommand = new SqlCommand(str, myConn);

            myConn.Open();
            myCommand.ExecuteNonQuery();
            myCommand.CommandText = "drop table #temp1;";
            myCommand.ExecuteNonQuery();

Open in new window

0
Comment
Question by:whorsfall
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 8

Expert Comment

by:redpipe
ID: 33539498
Can you say something about why you are using temporary tables and what you are trying to achieve trough your code? Such information will greatly help in giving you a to-the-point answer :)

You do not need to CREATE local temporary tables. In stead you just SELECT...INTO them. See the following article for basic tips on usage of SQL Servers local temp tables:
http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx

Many of the operations done by local temporary tables, that only exists in memory for a given session, can be done more effectively by using derived tables directly in the FROM part of your SELECT statement. A strategy many argue can give increased performance. An example of this is given in this aritcle http://www.sql-server-performance.com/articles/per/derived_temp_tables_p1.aspx 

Hope this helps you on your quest..?
0
 

Author Comment

by:whorsfall
ID: 33540839
Hi,

Thanks for your response. Ok I am building a data acquision application which needs to sample data at a high rate and record for later analysis. We estimate we need to store 400 results at 100 times per second.

So I am trying to find the fastest method to achieve this throughput. This why I was thinking about temporary tables.

I would even be happy with something that can run put it all in memory and only take a shoryt amount of time to save to disk.

Ward.
0
 
LVL 8

Accepted Solution

by:
redpipe earned 500 total points
ID: 33565716
Sorry for the delay, but I've been offline for a couple of days. When it comes to a reply to you question, I first have to admit that I have not worked directly on a solution with such high demands for data caching and object persistence.

If the acquisition part is entirely disconnected from the analysis part, you could do ordinary INSERT statements to a permanent table where one of the columns contains e.g. a binary representation of your 400 resultset. Databases as Berkeley and MySql are both perceived as swift and high capacity 'tools' for object persistence.

You could use queuing, but that is just an externalization of native database caching.

Another solution could be to use specialized databases that are targeted on such caching e.g.DB4o that claims to cache up to 200.000 objects per second (http://www.db4o.com/about/solutions/networks/default.aspx).
0
 

Author Comment

by:whorsfall
ID: 33575996
Hi,

Thanks for the great response - have you tried DB4o it sounds impressive?

thanks,

Ward
0
 
LVL 8

Expert Comment

by:redpipe
ID: 33584132
No I have not tried it myself. But I've heard great things from a friend of a friend
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question