|
I have a nightly process (SISS Package) that will do data dump from several tables used for order entry to tables used for reporting. My question is whether there is a general rule from a performance stand point that says wether it is faster to do an update on existing rows in the reporting table then insert new ones or just delete for a range then do a bulk insert. The number of records being updated/inserted would be anywhere from 2 records to 150,000.
Assume that appropriate indexes will be used for deletes and updates.
|
|
|
|
|
I think It's better to update to avoid fragmentation in index.
Unless you drop and rebuild the index after each bulk operation.
|
|
|
|
|
Thanks for the information. Do inserts always cause fragmentation or is this more of a problem with bulk inserts?
|
|
|
|
|
They always do if the new records are inserted between existing record unless there is a space according to a previously deleted record.
You can minimize this effect by configuring fill factor and padding for indexes.
If you use the clustered index as an identity column, fragmentation should not occur.
So there is no very direct do and don't in this case. You need to study your database structure carefully.
|
|
|
|
|
confused:
When I install my web site on D: drive, sql update query through ADO connection in ASP
fails with error message "Query not updatable" or
"Database is readonly", although I open it in write mode.
There is no such problem and it works well when web site is installed on C: drive.
My D: drive is NTFS formatted while C: drive is FAT.
Please help me!:
Balu Chettri
|
|
|
|
|
I think it has nothing to do with drivers but permissions for the specific directory. Therefore, whereever you placed the databsse, give (RW)permissions both to directory and the database.
I think that will solve the problem.
|
|
|
|
|
Can anybody tell me command for updating database using odbc. I am working with asp.net/c#.
|
|
|
|
|
The same as the command for updating directly "UPDATE..."
Do you mean, which class should you use? If so, you would use the OdbcCommand class and perform an ExecuteNonQuery().
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thanks
No it is showing error to connect to the database
i had given code like this
OdbcConnection c = new OdbcConnection("connectionstring");
OdbcCommand c2 = new OdbcCommand("UPDATE OUTSTANDING SET feild1 =?,feild2 =? WHERE (feild3 =?)", c);
c2.Parameters.Add("@feild1", OdbcType.VarChar, 255).Value = TextBox1.Text.Trim();
.....
can you tell me if there is any mistake.
|
|
|
|
|
Im guessing where you add the parameter but have you tried debugging? Where does it break at?
Cleako
|
|
|
|
|
thanks cleako
i tried with try catch method. it is loading data from the database when I have selected it.but not able to update.
|
|
|
|
|
Oh my mistake, it's so obvious now. You are setting the parameters in the Update command and then adding parameters to the command which works with stored procedures. If you are doing the field1=? etc... you have to post the values at the end as comma delimited values I believe so it should be "UPDATE SET field1=?, field2=? WHERE field3=?", value1, value2, value3
ClEakO
|
|
|
|
|
I am totally confused. I am so sorry. I am very new to this.
I am not using any stored procedure. all the data's are coming to the textboxes(different textboxes) and from there I am updating. so value means value from the textbox.
Thanks a lot again.
|
|
|
|
|
|
|
Is it possible to generate Insert, Update methods for my view.
The view is only a sub-view of one table.
_____________________________
...and justice for all
APe
|
|
|
|
|
yes its possible. And it will store in the real table
Regards,
Sylvester G
Senior Software Engineer
Xoriant Solutions
sylvester_g_m@yahoo.com
|
|
|
|
|
Ok, that sounds good.
Right now I don't get any Delete, Insert or Update methods in my dataadapter. What can I have done wrong?
_____________________________
...and justice for all
APe
|
|
|
|
|
Hi Guys!
I'm working with SQL Server 2000 and I have only started working with it for the last 3 weeks now. I'm basically teaching myself how to use it. Therefore, lots of trial and error  Here are the porblems:
When I create a report and try to run it by pressing the play button, I get a Cannot find TargetServer URL error message. What am I supposed to enter for TargetServer URL, please explain with as little jargon as possible.
Second problem: After I allow the report to continue its execution, the report will show the desired output, but, if I copy the rdl file to the server location that I want the report to be stored in, the report will not run. It gives me the following error message:-
"An error has occurred during report processing. (rsProcessingAborted) Get Online Help
Cannot create a connection to data source 'XXXXX'. (rsErrorOpeningConnection) Get Online Help
SQL Server does not exist or access denied."
Whats going on? Please explain with as little computer jargon as possible.
Please note that I am working for a company that requires me to work with this software and there is no time to go for a course to learn the software, I need to urgently solve this problem asap. Please help me somebody, or else I'm fired!!!
|
|
|
|
|
Hi
How to get all the tables with their schema name in sql server 2005.
I am using adventures database. I know how to display all the tables in the database.
select * from information_schema.tables where table_type='base table'
I want to append the owner name (dbo.sales, sales.product )
can any one help me to solve my problem please?
kesavan
|
|
|
|
|
Hi
Use :
select Table_Schema+'.'+Table_Name as fullname from information_schema.tables where table_type='base table'
|
|
|
|
|
Hello,
We're developing this small helpdesk application in we run into this problem:
I have a table of tickets and table of user, each ticket has in the database the field user_id where nulls are allowed. I'm using typed dataset generetad using the Visual Studio, so there is also the relation FK_users_tickets generated.So in my myDataSet.tickets table a method addTicketsRow() was created with several arguments, one of them being of type myDataSet.usersRow. I know that's the parent usersRow, so it's ok, the problem is when i want to add the new row with the user_id field null.
How to provide usersRow parameter which is gona be null? Well ofcourse i tried providing the null parameter but im just getting nullreference exception. I also tried to find some constructor or method to create empty usersRow, but i found just the method myDataSet.users.newUsersRow(), but this one will just create another new usersRow and will set it's id, because there is autoincrement on the user_id field and obviously i don't want to do that.
Looks litle confused, but hope the explanation is understoodable.
Actually I'm almost sure it should work with the null parameter, because i had a piece of code like this before and that time it worked, but now i've already spend two days on solving and googling and still didn't come up with answear...so any help would be realy appriciated.
thnx Honga
|
|
|
|
|
You have to ask yourself, "what is the reason for having the ForeignKeyConstraint"? The purpose of a foreign key constraint is to enforce the data integrity of the relationship. Depending on what it is you're trying to accomplish here's some suggestions:
1) Set the EnforceConstraints property of the Dataset to false. However, this will affect all your constraints, not just the specific foreign key constraint. So if you have other constraints you need to be aware of what the affect will be of turning them off.
2) Use a DataRelation instead. The trick is that it will add a foreign key constraint which you will have to remove. This will work if the purpose of your constraint is simply to indicate a relation between the tables and not to enforce data integrity. Then you can use methods like DataRow.GetChildRows().
3) If you don't want to enforce data entegrity and you aren't using methods like DataRow.GetChildRows() then you may not need the constraint at all
|
|
|
|
|
Thanks for answer,
the DataSet is generated from the database using the VisualStudio so there is a DataRelation between those 2 tables already. So I can simply erase this relation and I guess it is gonna work fine after that. The problem that I don't understand,is however, little more general.
The generated addRow method parameters looks something like this:
ticketsTable.addTicketsRow(DateTime ticket_date,string ticket_title,....,MyDataSet.usersRow user_id)
and I know that in the database running on SQL Server, the field user_id in table tickets allows Nulls(and also in the generated DataSet). So when Visual Studio created the DataSet for me with this wonderful method, what parameter should I supply to this method for the usersRow, so the field in the database would be Null? When I supply null, which was my first idea, I just get "object reference not set to instance..." exception.
Honga
|
|
|
|
|
Thanks for your answer,
The DataSet is generated from Visual Studion from SQL Server so there already is a DataRelation between those 2 tables "tickets" and "users". So I guess that when I delete this Relation it is gonna work fine. The problem I don't understand is however more general.
My addRow method on table "tickets" looks something like this:
myDataSet.ticketsTable.addTicketsRow(string ticket_title,DateTime ticket_created,...,MyDataSet.usersRow parentusersRowbyFK_ticket_users)
In the DataSet the FK_tickets_users is set to "Relation only". In the database there is field user_id of type int and this field I'm setting by the MyDataSet.usersRow parentusersRowbyFK_ticket_users parameter.
This field user_id allows Nulls. So my question is what parameter should I provide to the addTicketsRow method so the field user_id in the database would be Null?
As I said before, null was my first try, but than I'm getting NullReferenceObjectException.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
