|
I have a single client application that physically connects to a number of instruments in a lab. These instruments transmit large amounts of data (via RS232 & TCP/IP) which are all stored on our SQL server.
My first question is, performance wise, will it be better to create a seperate "Instrument##MessagesIn" table for every instrument or to have a common table for all instruments and only distinguish between them using an InstrumentID column?
My second question is, what are the advantages of using a seperate DB for certain data on a single server, the amount of history data recorded by this system is literally millions of records in tblHistory. Will a second database (on the same server) dedicated to history tables show any performance gain, or maybe relieve overhead, memory or CPU on my main database?
Regards
you can't forget something you never knew...
|
|
|
|
|
It's always a bad idea to dynamically create tables. It invariably means creating dynamic SQL to access and modify data in the database, which will cause many more query plans to be cached. Also, it's difficult to know when to drop a given table.
Keeping your history data in a separate database is not really necessary, but you should consider keeping it on a separate disk (or disk array) from the transactional data, so that overhead of long running queries on the history does not impact the I/O performance of the immediate data. Also, you can get locking problems if you don't separate historical and live data: if your history queries tend to read a lot of rows, that can cause updates or inserts to block until the history query's transaction is committed or rolled back. This can also impact any OLTP work which uses table scans (this should be avoided if at all possible) since the cost of the table scan to find a given row is on average the cost of reading half the number of rows in the table.
To do this in a single database requires understanding additional data files and filegroups so that you can place a specific table in a specific file or group of files which will be on a particular disk. It's often simpler to keep it in a completely separate database. This will also generally be easier to migrate to a separate server later, if required.
|
|
|
|
|
If you are just inserting new records then it will make little difference whether you have 1 table or many. There are other issues with splitting the data out to seperate tables namely that you will need to have additional code to determine which table to insert into and new code to handle the insertion. In addition, your history table will need to be populated by new code.
Moving the history table to another server is not worthwhile. If performance does ever begin to drop, you could look at whether having a history table for a time period that can be periodically archived off to the final history table would help.
Ian
|
|
|
|
|
Hi,
TO improve the performance of operations on a table I want to partition the table. So can someone let me know how to do this in SQL Server 2000.
Regards,
Uma
|
|
|
|
|
|
Hi
I want to create a partitioned table. can u give me the syntax for it.
Regards,
Uma
|
|
|
|
|
|
I would like to take the field values of a row in a DataTable and copy them to a new row in the same DataTable. Below is the code I used to do this.
Dim newrow As DataRow = Me.MyDataSet.MyDataTable.Rows(0)
Me.MyDataSet.MyDataTable.Rows.Add(newrow)
An exception is thrown saying this row already belongs to this table.
How can I accomplish this?
Thanks
|
|
|
|
|
Why on earth would you want to?
It is already there!
Steve
|
|
|
|
|
I have no idea why you would want to do this, but it can be accomplished with the ImportRow method (among other ways).
Me.MyDataSet.MyDataTable.ImportRow(newrow)
Why do you want to duplicate a row in the same datatable?
|
|
|
|
|
The data table contains patient information. One row, however, contains default values for any new patients. The user can edit any existing patient in the data table. When the user clicks on New the values from the default row are used to populate the new row with default values.
The primary key field cannot be included in the ImportRow. I think and error will occur when updating back to the database. Can this field be excluded?
|
|
|
|
|
Dim defaultRow As DataRow = Me.MyDataSet.MyDataTable.Rows(0)
Dim newRow as Datarow = Me.MyDataSet.MyDataTable.Newrow
'Set newRow values for all necessary columns
newRow.Item("MyColumn") = defaultRow.Item("MyColumn")
Me.MyDataSet.MyDataTable.Rows.Add(newrow)
I haven't tested this, but it should work. I would think, however, that it would be easier to simply specify default values for your columns in the dataset, or even at the database level.
|
|
|
|
|
There are hundreds of fields that are used for default values so your last idea would be to lengthy.
The importrow does not work because the table has a primary key field and that field cannot be imported. I tried unsuccessfully to remove that field from the datarow.
Any other ideas?
|
|
|
|
|
You could try passing the values from the defaultRow to the newRow using the ItemArray method, and then change the value of the primary key with the Item method.
newRow.ItemArray = defaultRow.ItemArray
newRow.Item("PrimaryKey") = NewPrimaryKeyValue
|
|
|
|
|
Being the field is a unique primary key VB might not allow you to assign a value to that field. But I will try this. Thanks.
|
|
|
|
|
I'm not sure what you mean. A primary key field needs to be assigned a value just like any other field.
|
|
|
|
|
The error I get is this:
Column 'MyTableid' is constrained to be unique
No matter what value I assign to the primary key field, even if the value I assign is unique to the table, this error is thrown.
|
|
|
|
|
Why don't you just set the columns in each row to have default values?
When you create your table, you can set the columns to have default values!
Then, if you do not change or provide a value for a particular field, the default value is inserted automatically. No need to copy a default row at all...
Steve
-- modified at 13:12 Tuesday 22nd August, 2006
|
|
|
|
|
That sounds good. But the table has a few hundred fields. Is there any way to set the columns to have default values without alot of coding?
|
|
|
|
|
Dim MyTable As DataTable
MyTable = New DataTable("MyTable")
Dim myDataColumn As DataColumn
'Create and add columns
'An Integer column
myDataColumn = New DataColumn
With myDataColumn
.DataType = System.Type.GetType("System.Int32")
.ColumnName = "Quantity"
.DefaultValue = 1
End With
tempChargesTable.Columns.Add(myDataColumn)
'A String column
myDataColumn = New DataColumn
With myDataColumn
.DataType = System.Type.GetType("System.String")
.ColumnName = "Description"
.DefaultValue = "My default text..."
End With
tempChargesTable.Columns.Add(myDataColumn)
'And so on...
'Add table to your dataset
MyDataSet.Tables.Add(MyTable)
That's how to do it in a table...
You could set the default values in your query but if you have hundreds of fields I suppose you are using a 'SELECT *' statement.
If your query is returning hundreds of fields, it is not very efficient. I assume your datasource is not normalised? If it isn't, your best solution would be to sort that out...
Where is the data coming from?
What are you going to do with the data?
Steve
|
|
|
|
|
The datasource is normalized. There is just alot of fields. The data is coming from an Access database. The data is patient info and is maintained using Add, Delete, and Edit functions I have built in. Because there are so many fields the user needs to fill in when a new patient is added the default values feature becomes very useful.
Because there are so many fields, to implement your code above would require alot of typing. What I think I can do is the following: (but I need to work out the exact syntax)
for ColNumb = 0 to MyTable.columns.count MyTable.Columns(ColNumb).DefaultValue=MyDefaultTable.Rows(0).item(ColNumb)
end for
The for next loop will scan through each column in MyTable and assign the field value from MyDefaultTable to the column default value. Both table have the exact field structure. MyTable contains the patient info and MyDefaultTable contains the default values. Think this will work?
|
|
|
|
|
I have a data entry form that I'm creating inside a class library rather than directly in a Windows Application. (Reason is that I need to be able to import the form into several different projects and I don't want to just cut and paste it into each.)
I have the form set up and linking to the database and all works fine...except that I cannot 'addnew'. When I do Me.BindingContext(DataSetName, "TableName").Addnew nothing happens.
All of the other functions of the BindingContext class are working fine. I can navigate, delete, edit & save records, but I cannot add a new record.
If I put the form in the Windows Application and re-establish the dataset and connections (due to the namespace change) everything works fine.
Any advice would be greatly appreciated.
|
|
|
|
|
I don't think that putting this code in a class library is what is causing the problem. There must be some other reason for it. When you put the code in a windows application are you absolutely sure that all you are doing is changing the namespace? Could there be some other reason that you don't quite see at the moment. Is there something additional in the namespace of the EXE that isn't in the namespace you are using in the class library that could be changing things? Are all the right things referenced in the class library?
|
|
|
|
|
All of the same references are made in both--System, System.Data, System.Drawing, System.Windows.Forms, and System.XML plus one of my own.
When I moved the form into to the Windows Aplication, I dragged the file from the class library into the Application--they were both open. Then I re-created the dataset in the Windows Application so that it would have the same namespace. I used the "Generate Dataset" tool in both.
Also...all of the other functions of the databinding were working properly.
|
|
|
|
|
Problem Solved Colin, and thanks for your insight.
Apparently there was a problem with enforcing constraints and some checkboxes on my form. I found the problem by manually coding the databindings for each text box and found that the Addnew function was working properly until I got to the checkboxes... I'm still not sure what caused that problem, but I know how to work around it. :->
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
