|
Hi!
I am trying to create a statement, that deletes rows where UpdateDate is older than 7 day and than selects that rows for me. I need to get those rows back to my application to write the log file. In 2 steps (select and than delete by ID) is no problem. But is there a way to do this in one single step?
What I need to get back is table.ID, table.CreationDate, table.Name ... It is nearly the complete row.
THX for your help!
|
|
|
|
|
Member 9935038 wrote: But is there a way to do this in one single step?
What do you mean with "single step"?
If you need them to be a single operation then it'd be wrapped in a transaction. That'd mean that there'd be two more extra statements.
If you're wondering if there's a command that does both a select and a delete, then the answer is no, but if you need that you can wrap 'em both in a stored procedure.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I imagine MySql has temp tables like Microsoft Sql does. You can insert the values you want to log into a temp table and then delete the records and then select from your temp table at the end.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
I have 2 database,
oracle transaction with multi OracleConnection c #
Table 1 in the database 1 <pre lang="c#">
Table 2 in the database 2
When save new data to Table 1, I want to save the data in Table 2 in the database 2
How can I do this in oracle transaction
<pre lang="c#"><pre lang="c#"><pre lang="c#"> using (OracleConnection connection1 = new OracleConnection(connectionstring1) )// databsse1
{OracleConnection connection2=new OracleConnection (connectionstring2);//database2
connection.Open();
OracleCommand command = connection.CreateCommand();
OracleTransaction trans;
trans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
command.Transaction = trans;
try
{
command.Text =sql1//insert into table1 in database1
command.ExecuteNonQuery();
command.Text =sql2//insert into table2 in database2
//Here the problem by looking for a solution
How can I do that
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
}
}
|
|
|
|
|
Create a new Command with connection2 and execute your sql2 in the new command. Don't forget to set the same transaction for the new command.
|
|
|
|
|
Just a thought.
if the tables structures are the same have you not thought about using database triggers?[^]
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Hi,
First, I didn't see a mongoDB forum here so I think that's the closest place to ask a DB question.
Ok, so I'm using MongoDB in my web application and using C# driver to interact with it.
I ran across a problem that I can see a lot of people encountered and that is: How can I deserialize a JSON string that I get from mongo when I have a field, called DateAndTime which has the value much like this one:
ISODate("2014-04-19T06:01:17.171Z")
I get an exception that this is, and of course it is, an invalid JSON type.
I came across a solution in Stackoverflow which said to do this:
string json;
var v = Newtonsoft.Json.JsonConvert.DeserializeObjectAsync<T>(json);
but this also didn't helped.
How can I get around this issue or solve it?
Or perhaps is there a way to configure how mongo will save dates?
|
|
|
|
|
We have a requirement to do some serious record by record processing and I have had some suggestions to do the processing in c# rather than use a TDE or cursor. Personally I prefer TSQL but I suspect that is because that is what I am used to.
What has been suggested is that we use Dapper [^]and loops or linq to process the records then update back to the database. I will prefer using bulk copy to write the records back but that requires a datatable and converting from ORM List<> to data table for multi million recordsets may be detrimental to the performance.
Both processes would run on the same server or at least of the same spec.
Any opinions on which would be the fastest method of processing!
[edit] link recovery [\edit]
Never underestimate the power of human stupidity
RAH
modified 16-Feb-14 7:09am.
|
|
|
|
|
Mycroft Holmes wrote: do some serious record by record processing
Will you be writing to that record (manipulating it), or will the result be written/aggregated elsewhere?
Mycroft Holmes wrote: for multi million recordsets
Does it have to process the original data, or could you work from a copy?
Mycroft Holmes wrote: Both processes would run on the same server or at least of the same spec.
"Could" run - if you were to cut that table in five, you could use five clients to do the processing.
That need not be dedicated clients; if you're on a network, then there are a few computers on there that are probably idle at some point. Easiest way to detect that is by writing a screensaver.
If you go this route, please write an article and hand us the resulting code 
--edit
Y'er link is broken.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
modified 16-Feb-14 6:48am.
|
|
|
|
|
We will need to extract the records (probably up to 1m), process each record 36 to 120 times and write the information back to the DB.
The original data is already a copy, partially aggregated as much as possible.
Really not interested in getting into a processing farm, another team failed to do this and I'll stick to stored procs if that is a requirement. A commercial package takes approx 4 hours to process and we don't need their level of complexity so it should be do able.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
It's heavily depends on the nature of the process...
1. There are computations involved?
2. There are string processing involved?
3. It's an in-place update or the results go to new table?
4. What the volume of the data we talk about?
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
1. Yes per record dependant on the previous transaction (think compound interest on steroids)
2. No all decimal - some referencing string values external to the transaction
3. Indeterminate - this will depend on the design we take on, my preferences will be for insert as it ia faster than update
4. 4+ million records each pass.
I expect the passes to be broken down to smaller chunks but 1m is not unfeasable.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
IMHO - do it in SQL.
Do running computations (via the select itself) and put the result in some temporary table - then use it as you wish...
Passing 4+ million records over the net can be very painful. How ever a well configured SQL with properly indexed table can handle 4+ million records with no problem...
Try to turn the dependencies into some parameters (pre-calculated maybe in the previous run or fixed from some external source) if you can, so even string manipulations will not slow you down...
In an optimal case you my turn it into some SQL job and left it alone for ages...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
Kornfeld Eliyahu Peter wrote: do it in SQL
That is my default option but I don't want to eliminate what may be a better solution just because I am comfortable with TSQL.
Trust me the various transaction and working tables will be indexed within an inch of their life, one reason I want to use inserts instead of updates.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I can't see no reason to do in SQL - as you have no nothing that beyond the capabilities of SQL, why add networking (even in server data transfer will add to it...)?
Can you explain why you doubted about SQL in the first place?
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
I don't think it is a doubt SQL, but rather, an examine all possibilities. I'd do the same; examine all possibilities if no to provide justification when asked why I picked what I did.
Tim
|
|
|
|
|
Tim is right, in my original post I made it clear I was investigating options put forward by one of my senior devs. Ignoring the option would be a disservice to that dev.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I would say that performance-wise an database engine is going to be faster than shifting all that data over the network to the client, processing it and sending it back again.
With the proviso that you set up the indexes correctly I would say that SQL will be the fastest method of processing the data from a computer point of view(in terms of the human side and GUI etc. that is something only you would know about).
Go with SQL as that is what you are most comfortable with and correctly created indexes can helps thing fly.
I would avoid cursors and use temporary tables(don’t forget to add indexes to the temporary tables too) doing the processing in steps – my experience is that this is the fastest way of processing large quantities of data.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
We are well experienced with TSQL and the importance of indexing correctly, it just that one of the senior devs suggested a c# solution so I though I'd get some other opinions.
I have a rule of thumb that uses table vars for small reference type info, temp table with indexing for serious volume and cursors only under duress.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Sounds like you know exactly what you need to use 
I get the checking with others because a senior dev suggested something 
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
There's a third option, you don't need to choose between C# or processing in the database.
You can do both using a CLR SQL Server User-Defined Function[^].
That should appeal to both you and that senior developer. 
Note that I've never done it, so I can't say how much fuzz there is to it. But I know that MS invented it with performance in mind.
|
|
|
|
|
Per the other post... "process each record 36 to 120 times"
Given that it seems likely that the processing isn't going to be simple. So that suggests the TSQL is going to be rather CPU intensive.
So what is the expectation of other work that the database needs to do at the same time that this runs, now and in the future?
And what is the growth rate of the record set?
Does this occur every day?
Moving records out of and into the system is of concern but given the processing numbers above it is something I would consider. A separate application allows processing to be moved off box (easier at least with my experience.)
|
|
|
|
|
I have done this on both sides and the DB side is much much faster. I have big data modeling app (35 million transactions across 20 tables) with DB procedures and a big ETL app that has to exist and run on the MSoft client side. The DB is much much faster.
|
|
|
|
|
Quote:
select distinct(Emp_Status),
count ( Att_Mints/60) as OTHOUR,
COUNT (Att_Totalmints /60 ) as ProductionHours
from Attendence
inner join EmployeeMaster on fk_Att_EmpCode=fk_Att_EmpCode
where year(Att_Date)='2014' and Month (Att_Date)='1'
group by Emp_Status
in above query Count of
Att_Mints is =150
and count of
Att_Totalmints is 120
i want output of OTHOURS=1:30min
and
ProductionHours =2 hour
please help me
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
