|
More of a high level question, than a specific C# question. I need to put together an application that sits on top of what I consider to be a very large database. This database mainly consists of the following tables:
Table 1
id (int, primary key, index)
value1 (varchar 50)
value2 (varchar 50)
value3 (varchar 50)
value4 (varchar 50)
value5 (varchar 50)
value6 (varchar 50)
value7 (int)
value8 (int)
value9 (int)
Table 2
id (primary key, index)
table1_id (int, foreign key to table1 id)
value1 (varchar 50)
value2 (varchar 50)
value3 (int)
Currently table1 has around 350 million rows, table 2 has around 900 million rows. So as you can see the volumes of data here are fairly large.
My application needs to be able to search these tables on any of the data columns and display results in a datagrid (or other suitable control), obviously searches could result in large result set. User should also be able to update a single row, or a batch of rows.
The initial version of this application (coded before the database became so huge) just used a simple single line list view and worked perfectly. Now the volume of data are so large I have had to restrict search results to 500 rows, which is annoying end users. So I need to find a viable alternative
So the questions are as follows:
1) Whats the best approach for handling this in a C# based application. After googling for most of today I have come across a number of "solutions" however I do not want to go steaming down the track of one of these to discover that it is slow.
2) Although not specifically C# related, could this table structure be optimised in any way? As an example I am considering normalising the varchar data.
-- modified at 19:37 Wednesday 5th July, 2006
|
|
|
|
|
Hello,
I don't have an answer to your second question right now, but I can make a suggestion for the first question.
My suggestion is to use a grid control which allows you to implement a "virtual grid". Basically, a virtual grid does not store the data in memory for any of the cells in the grid. Instead, when the data for one of the cells needs to be displayed, the control makes a request (some sort of callback function) and retrieves the data that way. This would allow you to store only a small part of the data of your database in memory at a time - namely, just as much data as the user can see at one time. Here is basically how I see it working (I have done a virtual grid pulling from a database before, though my implementation was in C++ and probably not the most efficient one):
1) Application loads and so does the grid.
2) Grid requests data for the first hundred rows.
3) Your app queries your database and gets back a data set with those hundred rows and hands them to the grid view. The SQL pull back is fast, because it is SQL, and displaying the items in the grid is fast, because they are stored in memory.
[now there are only one hundred rows in memory]
4) The user uses the scroll bar to scroll down to the next hundred rows. The first hundred rows are now hidden, the next hundred must be displayed.
5) Grid request data for the second hundred rows.
6) Your app queries your databse and gets back a data set with the second hundred rows and hands them to the grid view. This takes about the same amount of time for the first hundred rows.
[there are only one hundred rows in memory)
And so on: as the user scrolls or searches, you simply pull back the data which needs to be displayed and pass it to the grid.
The great thing about doing it this way is that everything takes roughly the same amount of time and is fairly quick, even if your database grows bigger. Obviously the more rows you wish to display at once, the longer it will take and the memory it will take, but this isn't really an issue, because the number of rows that can fit on the screen is limited.
Now, I haven't built an application that has had to deal with the volume of data which you are working with, but conceptually I think this idea works. Please take this simply a suggestion, and not an expert opinion.
For a C# grid which supports virtual grid, just do a search on this site. One such grid is: SourceGrid[^]. I haven't tried this one yet, but it looks good.
Please let me know if you have any questions about this, and I'll try to answer them.
Sincerely,
Alexander Wiseman
|
|
|
|
|
MrEyes wrote:
2) Although not specifically C# related, could this table structure be optimised in any way? As an example I am considering normalising the varchar data.
Probably yes, but without knowing what "value1", "value2", etc, mean, it will be very hard to suggest anything. A lot of varchar(50) in a SQL Server always smells like someone did not spent too much time designing the database...
You have a lot of varchar fields, which would be easier to search if you use a full text indexing solution, like the one that comes with SQL Server (you didn't mention your specific DBMS).
MrEyes wrote:
The initial version of this application (coded before the database became so huge) just used a simple single line list view and worked perfectly. Now the volume of data are so large I have had to restrict search results to 500 rows, which is annoying end users. So I need to find a viable alternative
You can raise this value a lot (probably easily up to 10,000 or 50,000 rows, but I don't know your hardware) if you use the listview in virtual mode, as you won't need to add items to the listview.
Bear in mind that giving your users 50,000 rows is a pseudo-solution. It's a lot of data to search manually. If you spend 1 second reading or understanding each row, it can take up to 14 hours to read everything.
You'll need to give your users a decent UI, automatically clustering or grouping results, or some way to refine searches.
I don't see dead pixels anymore...
Yes, even I am blogging now!
|
|
|
|
|
Daniel Turini wrote: Probably yes, but without knowing what "value1", "value2", etc, mean, it will be very hard to suggest anything. A lot of varchar(50) in a SQL Server always smells like someone did not spent too much time designing the database...
You have a lot of varchar fields, which would be easier to search if you use a full text indexing solution, like the one that comes with SQL Server (you didn't mention your specific DBMS).
Basically this is an MS SQL database that stores unfiltered incoming SNMP traps from a vary large array of devices (6000+). The table model I have inherited is as follows
[captured_traps]
[id] [int] IDENTITY (1, 1) NOT NULL ,
[snmp_version] [int] NULL
[community_name] [varchar] (255)
[packet_type] [varchar] (50)
[oid] [varchar] (500)
[source_ip] [varchar] (15)
[generic] [int] NULL
[specific] [int] NULL
[time_stamp] [varchar] (15)
[trap_entered] [datetime] NULL
[status] [int] NULL
[captured_varbinds]
[id] [int] IDENTITY (1, 1) NOT NULL
[trap_id] [int] NULL
[varbind_oid] [varchar] (500)
[varbind_text] [varchar (500)
Storing data as string is, I believe unavoidable otherwise it would not be possible to search data. However the design has alot of repeated data in some fields, I believe that normalising the varchars would give a significant performance enhancement. Or as you suggested using text indexing
Daniel Turini wrote: Bear in mind that giving your users 50,000 rows is a pseudo-solution. It's a lot of data to search manually. If you spend 1 second reading or understanding each row, it can take up to 14 hours to read everything.
You'll need to give your users a decent UI, automatically clustering or grouping results, or some way to refine searches.
This is very true, and most searches do not create massive result sets. However the app allows users to search on any field or combination of fields within any date range. More often than not the search is for a specfic oid on a specific day. However the app needs to be able to support a user running a search for a specific oid receive in the last 3 years
|
|
|
|
|
MrEyes wrote:
Storing data as string is, I believe unavoidable otherwise it would not be possible to search data. However the design has alot of repeated data in some fields, I believe that normalising the varchars would give a significant performance enhancement. Or as you suggested using text indexing
I don't know SNMP enough to be sure of what I'm saying without looking at the data, but some fields feel like they deserve a separate table:
Packet Type -> Maybe a packet type table?
OID -> Is it something like a MAC Address or a unique object ID? If so, you should have an "object" table.
snmp_version -> this seems like an attribute of the "object" table, as it seems 1x1 related to OID.
Source IP and Community Name -> Aren't those somehow related to OID too?
Source IP -> You can (and maybe should) store IPs as INTs[^].
Timestamp -> This is a count of seconds since some arbitrary point on time. You could use a numeric field to store that, it'll be way smaller.
This may make your database way faster to search, as it will be much smaller, and you'll only need to search smaller varchar tables (maybe hundreds or thousands of records) before going to the main table (captured_traps). But logging it may be a bit slower, only a test may say...
I don't see dead pixels anymore...
Yes, even I am blogging now!
|
|
|
|
|
I make with the pen tools of my application some drawing operation (g.DrawImage...) then i call picturebox.Invalidate() but it is very slow.
How can i make it faster? I've tried also this:
SetStyle(ControlStyles.UserPaint |
ControlStyles.AllPaintingInWmPaint |
ControlStyles.DoubleBuffer, true);
|
|
|
|
|
I would like to create crystal reports dynamically - i.e. add fields to the report at runtime. Thanks to anyone who can point me in the right direction.
|
|
|
|
|
hello,
I've been on this problem for a while. I'm working on a windows form that has a datagrid control. lets say the user enters a value that is more that the specific length. I was able to truncate the value but my problem is I'm not sure how to put it back. Please advise. thanks!
private void TextBox_TextChanged(object sender, EventArgs e)
{
TextBox t = (TextBox) sender;
string val = t.Text;
int len=0, offset=0;
DataGridCell dgCell = this.dataGrid1.CurrentCell;
string sElementGroupID = dataGrid1[dataGrid1.CurrentCell.RowNumber, 0 ].ToString();
Al3UpdateFile objCell = new Al3UpdateFile(file_stream);
objCell.GetGroupElementValues(sElementGroupID, ref offset, ref len);
if(val.Length > len)
{
if(MessageBox.Show("Data Entered is longer than its allowed.Data will be truncated.","XDAL3Reader",MessageBoxButtons.OK,MessageBoxIcon.Information) == DialogResult.OK)
{
orignalText = val.Substring(0,len);
dataGrid1[dgCell] = orignalText;
}
}
else
{
return;
}
-- modified at 18:26 Wednesday 5th July, 2006
|
|
|
|
|
Hi!
I would like to make a program which will simulate some kind of shared folder.
So I can mount it and have "virtual drive".
Could some one give me some starting hint.
One is to study network file sharing protocol and than simulate that, but I hope there is some easier way. 
For having a virtual drive I already look for "shell namespace extensions" in c++ & MFC but that just ain't that. 
..:: Life Is Like A Box Of Ammo ::..
|
|
|
|
|
Hi. I am looking for a way to create a game trainer in C#.NET. That is to access the memory of a certain process (i.e. the game) and to modify some variables. I tried to use the Process class, but it is limited and has many security restrictions... I would even be happy to find a solution for this problem in C++...
Thanks in advance.
bunny EATING rabbit
|
|
|
|
|
|
Thanks a bunch man !! It was really helpfull !! I did it :> (next time i'll look harder  )
bunny EATING rabbit
|
|
|
|
|
I have a class of UserControls all of which need to implement the same method.
I can achieve this with an abstract method
public abstract void foo();
However this has a disadvantage:
(A) There is some commonality in the code that it would make sense to extract and put into the base class.
I know that I can achieve this by
//Base
public virtual void foo
(
BaseCode();
)
//Derived
public override void foo
(
SomeCode();
base.foo();
)
But then that gives me 2 disadvantages
(B) It doesn't force a derived class to do it's own implementation of foo
(C) if the derived class doesn't explicitly call base.foo(); my common code doesn't get called.
Is there some other modifier or pattern I can use to achieve A, B, and C?
Or. Is there a good reason why I should not be trying to achieve this?
|
|
|
|
|
You can try splitting it up into three methods. One method has your base code. The other method is abstract and must be overridden by subclasses. And the last method calls the base code method first, then the abstract method.
Logifusion[^]
|
|
|
|
|
A, B:
If the method that you put in the base class doesn't fully implement what the method should do, you shouldn't implement it at all. Make it abstract, and put the code in a protected method that the derived classes can use to implement the abstract method.
C:
You can't force anything about how an overridden method is implemented. If you want some code to always be called from the method, let the derived class implement another method instead, that is used by that method:
Base class:
public void Foo() {<br />
FooTask();<br />
SomeOtherCode();<br />
}<br />
<br />
protected abstract void FooTask();
Derived class:
protected override void FooTask() {<br />
SpecificImplementation();<br />
}
---
b { font-weight: normal; }
|
|
|
|
|
How do I convert an Image to a memory stream ? or better yet a byte[]? I've tried
Image tmp = ...
MemoryStream ms=new MemoryStream();
picLastSent.Image.Save(ms, ImageFormat.Jpeg);
But I keep getting a generic GDI+ exception.
I need it in byte[] format to send it out on a UDP multicast socket.
-Steven Hicks
CPACodeProjectAddict
|
|
|
|
|
hope it is helpful
sender:
MemoryStream ms = new MemoryStream();<br />
bitmap.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);<br />
ms.Flush();<br />
Byte[] photo = ms.GetBuffer();
receiver(displayed by a picturebox control):
<br />
pictureBox1.Image=Image.FromStream(ms,false);
|
|
|
|
|
Nope it creahes on the Image.save call with a Generic GDI+ Exception.
-Steven Hicks
CPACodeProjectAddict
|
|
|
|
|
|
|
|
Hello,
I have 2 forms that i need to show the respective version in a dialog box or in another form.
something like this:
form1 version: 1.0.0.0
form2 version: 1.0.0.1
Cpyright..........
Im thinking to put in a class so that's any new form can use it.
Any idea?
Donkaiser
|
|
|
|
|
Hi!
You can access the version of the assembly your classes are in, but there is no way built in to have different versions for different types in the same assembly.
Take a look at the Assembly.GetName() method and the AssemblyName.Version property.
Regards,
mav
--
Black holes are the places where god divided by 0...
|
|
|
|
|
I am using the WebBrowser Control in my application and I need to somehow access the HTTP headers of the HTTP Request in order to obtain a cookie that is passed from the browser to the server during the submit. The cookie is a temporary one, it is not possible to access it on the file system because it doesn't get saved to a file.
Many thanks to anyone that can help,
Michael
|
|
|
|
|
Go through the DHTML interface to access the cookie.
"Just about every question you've asked over the last 3-4 days has been "urgent". Perhaps a little planning would be helpful?"
Colin Angus Mackay in the C# forum
led mike
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
