|
Format expression: dd/MM/yyyy should work. Are you sure that the field data type is datetime and not an string.
Regards,
Syed Mehroz Alam
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
|
Hi all..
To keep it short - We automatically update a set of tables (lets call it the DATA tables) in a client's database by dumping the raw data into a table (DUMP table) which has in INSERT trigger on it, which will then go and update the various data tables. I'm extending this functionality by adding a SETTING table which will allow clients to specify which fields they want updated. The SETTING table has two fields, an ID for each field in the DATA tables, and a setting field, which can be 0 for never update, 1 for always update, and 2 for only update if the corresponding field is null in the data table.
Some people might complain that this is horrible design - There are reasons for it.
My problem lies with optimization of this query. I get 30000 rows (bout 30-40 columns) at a time in the dump table and the trigger runs for about 40 minutes if it updates blindly, but now I need two extra SELECTs per field - first to check the update setting, then to check if the value is NULL should the setting be 2. So this comes down to more or less (ignore syntax trivialities):
<br />
SELECT @setting = UpdateSetting FROM Setting WHERE ID = @fieldid<br />
IF @setting = 1 OR (@setting = 2 AND IS NULL (SELECT field FROM Data WHERE id = @rowID)<br />
UPDATE Data SET Field = @newfieldval WHERE ID = @rowID<br />
Obviously this query will take AGES to complete, compared to the previous:
<br />
UPDATE Data SET field1 = @newField1Val, <br />
field2 = @newField2Val,<br />
[etc]<br />
...<br />
WHERE id = @rowID<br />
How should I go about optimising this? I am still somewhat novice when it comes to SQL. So far I'm considering building up an update string per row, which should at least cut down the number of updates significantly, but the number of extra selects are still a problem. Ideas? Clarifications needed?
Thanks in advance.
|
|
|
|
|
Would you consider taking the logic that builds up your queries out to a service at the client? This way the service could build up your required updates and then fire them at your database, not taking up large amounts of DB resource, or locking tables.
IMHO it would also be somewhat easier to maintain, as the updates you build up here are going to be quite large? Could make it somewhat more readable...
I know that's not what you're asking for, but i think you'll find it difficult to optimise your update by too much.
|
|
|
|
|
You could try combining the logic to the update. Something like (don't mind the typos):
UPDATE Data SET Field = @newfieldval
WHERE ID = @rowID
AND ( 1 = (SELECT UpdateSetting FROM Setting WHERE ID = @fieldid)
OR ( 1 = (SELECT UpdateSetting FROM Setting WHERE ID = @fieldid)
AND IS NULL (SELECT field FROM Data WHERE id = @rowID)))However, few considerations:
- where does the rowid come from, could you eliminate that variable
- as I understood that this is from inside the trigger, what happens if several rows are updated
If you're using SQL Server, I would combine the previous statement with inserted (and perhaps deleted) table table if possible. That would help to make the whole update process a single batch.
Also don't forget about good indexing.
Hope it helps,
Mika
|
|
|
|
|
I have to write a insert/update command with in a case. IS it possible?
for example
select case(select count(*) from table1 a where a.no=b.no and a.act=b.act)
when 0 then
insert into temp(name,desc,status) values(b.name,b.desc,1)
when 1 then
update temp set name=b.name,desc = b.desc,status=2 where id=b.id
else
insert into temp(name,desc,status) values(b.name,b.desc,3)
end from table2 b
But as case statement accepts only expressions can some one let me know how the above can be modified
|
|
|
|
|
You cannot combine insert and update statements, so you have to execute two separate commands. However you can execute them in a single batch (or write a procedure to wrap them).
|
|
|
|
|
Hi all,
Hope you are doing well. My question is based from MS SQL XSD registering. I know that I can create the schema and register it to use for incoming xml parameters and such. The question is, what if that XSD had references to other XSD files with the "include" attribute? So if I use include to bring in two other xsd files to reference in that one, is MS SQL going to keep up and know that they are in the same namespace? Or would it just crash at parse time because of some missing link?
If you have tried this before please post your experience back, thanks.
|
|
|
|
|
Hello,
Can anyone recommend a nice book for learning Sql Server Integration Services?
Thank you.
modified on Monday, December 22, 2008 9:57 AM
|
|
|
|
|
givi_s wrote: Can anyone recommend a nice book for learning Sql Server Integration Services?
Is it for Lounge.... I think you are in wrong place 
-------
Any way you can Read This[^]
|
|
|
|
|
Wow that'll teach you to redirect someone to the lounge, instant 4 univotes.
Actually here is also appropriate for this sort of enquiry.
Do you recommend the book or is it just the first one you found?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I am using OleDb to write into Access Database. In program, can we write any null value in a column in Access Database?
Thanks.
|
|
|
|
|
hung1984 wrote: In program, can we write any null value in a column in Access Database
No, only System.DBNull.
|
|
|
|
|
For example, is this line allowed?
sCommand.Parameters.Add("@Model", OleDbType.VarChar, 20).Value = "";
|
|
|
|
|
Yes, that is allowed but it isn't a null value, it is an empty string.
|
|
|
|
|
Hi to all,
I have a problem.
Actually i have a table in a database.In which there are four rows whose first two words of a column resembles.I want one of it.
And i have around 15 such similar groups.I want one from each.
Help me out.
|
|
|
|
|
Could you post a little example with the source data, example what the output should look like and the SQL you've done so far.
|
|
|
|
|
i have created one dll and i refer this from my Application(Windows)
while running it is showing COMException was unhandled Unknown error (0x80005000),can anyone suggest some solution.
|
|
|
|
|
You have to add reference to yr application's bin folder by right click on that and click "Add Rererence"
OR
If yr dll in System32 folder then use below code:
First include below namespace
using System.Runtime.InteropServices;
and then give dll reference as shown below.
[DllImport("dllName.dll")]
public static extern (function datatype) DllFunctionName();
Hope It will help u.
Krishnraj
|
|
|
|
|
I am developing one web application which needs to supports multi lamguages like english,hinsi,punjab and gujarati. I am displaying the Labels from ApplicationResource.preoperties(english,hindi..etc) for all languages. But the problem is whenever i am trying to save&edit the values from DB, its displaying wrong charecters in jsp page.plz help me regarding the same..
Your help is highly appreciated....
Regards,
Kumar
kumar.cergenx@gmail.com
|
|
|
|
|
Hi all,
I have one silly question - is view in sql server 2000 increase performance ?
Krishnraj
|
|
|
|
|
indexed View increase perfomance
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/SQL7/2000/2005/2008
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@its.co.za
|
|
|
|
|
How is this accomplished? If you put indexes, aren't they on the table and not on the view?
|
|
|
|
|
|
Hello Friends
I have two table name Commitment & CommitmentDetail. There is a field name IsRejected in Commitment Table and another name Remarks in CommitmentDetail Table. Now I need to write a query that will select or print Remarks only if the value of IsRejected is 3. Otherwise it will not.
Any Help will be appreciated.
Ahanking in Advance
Johnny
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
