|
Hi All,
I am working on an application in which on every 100 miliseconds there is an insert statement executing. I am using MySQL server as database and ODBC connector. Can anyone please suggest me how should I change the application's architecture to do this in a better way. Currently on every insert I am creating an connection and command object to perform the INSERT operation.
Regards,
Amit
|
|
|
|
|
I'm going to assume that the application is written using a .NET language because you've not indicated otherwise. If this is incorrect please say.
Use SqlConnection, SqlCommand, etc. rather than the ODBC varients.
|
|
|
|
|
Yes, server part in which INSERT statement has to occur on every 100 millisecond is written in C# using VS 2005. Database MySQL 5.0.
These INSERT statements has to occur anyhow in silent mode (in background) while user can do other Add/Edit/Delete or Update related operations on Database. Actually these insert statements are executed for the data received by the server application on an interval of 100 to 500 millisecond.
Please suggest a better way to do this.
|
|
|
|
|
Sorry, I misread your original post, I though you said "SQL Server" not "MySQL".
You should use the appropriate .NET Data Providers for your database.
logicon wrote: These INSERT statements has to occur anyhow in silent mode (in background)
If you are writing a .NET application you have to define any user interface that may be updated as a result of any database interaction. It is all "silent mode" unless you write code to the contrary.
logicon wrote: while user can do other Add/Edit/Delete or Update related operations on Database.
So, you have multiple threads interacting with the database. MySQL supports transactions now so that isn't a problem.
logicon wrote: Please suggest a better way to do this.
Don't use ODBC, use the appropriate .NET data provider for your database. Other than that, there isn't a better way.
|
|
|
|
|
Thanks Colin,
Problems with current application is that its highly unstable. Some times it works file and sometime the insertion operation fails and gives exception. Even on some test machine it is working file since a long time, but on some other machine it gives exceptions on some insert operations.
I find my self helpless with the provided stack trace.
Which .NET provider is suitable for MySQL 5.0 and VS 2005?
Regards,
Kumar
|
|
|
|
|
logicon wrote: Which .NET provider is suitable for MySQL 5.0 and VS 2005?
One provided by MySQL themselves I would assume.
logicon wrote: I find my self helpless with the provided stack trace.
Maybe others might be able to help - Have you thought about posting it along with the relevant code?
|
|
|
|
|
I've done some googling and reading, and found a few solutions to this, but none that struck as being being particularly elegant, so thought I'd ask for some advice 
I have a dynamically generated sql query which enables users to do keyword searches. The user can specify some wildcards to enable partail matching and so forth. It works, and is fairly secure - well I think it is, but I'm sure some crafty soul could hack it if they wanted to.
Anyway, while it works, I'd like to remove all of the querying logic from my application in to stored procedures (as much as a proof of concept and learning exercise than anything else), but I'm becoming unstuck by my lack of T-SQL knowledge.
The major problems I'm having are:
1) How to parse the input string and build the search criteria (including wildcards)
2) How to efficiently iterate over a record set and pull out records matching the criteria
As noted earlier, I have found some solutions - but the amount of SQL involved was fairly impressive, involving the building of temprorary tables, and multiple passes of the table etc. I just get the feeling that given a relatively large table (about 3 million records), performance is going to be an issue.
I'm not too concerned about being bound to SQL Server 2005 in this instance, so could anybody point me in the right direction of the techniques and language constructs I should look at? Woould this be an ideal place to use a CLR stored procedure?
Me: Can you see the "up" arrow?
User:Errr...ummm....no.
Me: Can you see an arrow that points upwards?
User: Oh yes, I see it now!
-Excerpt from a support call taken by me, 08/31/2007
|
|
|
|
|
i had a 70MB tab splitted file
i read that file in memory (string builder) and than i convert it in to a datatable
after that i set the datasource of grid equals to this table
but the problem is it takes more than one minute to show the data (the application takes around 1.5 minutes after i set the datasource property of the grid)
|
|
|
|
|
Just load it directly (without using Datasource). Also, make sure you don't have any "look and feel" enabled on the grid prior to loading (sorting, sizing, etc.). Enable this after the load.
1) Disable look and feel.
2) Create rows equal to count of rows.
3) Fill rows/cells using for loop.
4) Enable look and feel.
I gave up using Datasource a long time ago.
|
|
|
|
|
Thanks for your reply..
but can i have a sample application/code
bcz i tried it by writing a code as u mentioned
i had a 70MB file that contain more than 8,000,00 rows
the code is as follows
foreach(DataColumn dc in dt.Columns)
{
this.grd.Columns.Add(dc.ColumnName,dc.Caption);
}
Object[] obj = new Object[dt.Columns.Count];
foreach (DataRow dr in dt.Rows)
{
int i = 0;
foreach (object ob in dr.ItemArray)
{
obj[i] = ob;
i++;
}
grd.Rows.Add(obj);
i = 0;
}
|
|
|
|
|
That is a lot of rows for a grid! You may want to look into dynamic loading (a few rows at a time). This is how I normally load a DataGridView:
foreach(DataColumn dc in dt.Columns)
{
this.grd.Columns.Add(dc.ColumnName,dc.Caption);
}
int totalRows = dt.Rows.Count;
this.grd.Rows.Add(totalRows);
DataRow dr;
for (int i = 0; i < totalRows; i++)
{
dr = dt.Rows[i];
this.grd.Rows[i].Tag = dr;
this.grd.Rows[i].Cells[0].Value = dr["FirstColumn"].ToString();
this.grd.Rows[i].Cells[1].Value = dr["SecondColumn"].Tostring();
}
|
|
|
|
|
I am using Crystal Reports in my application I made using Visual Studio.NET.
I installed my application on the client machine, with all the DLLs
required(added the merge modules), the engine and the rest. When I start my
application and, from it, try to open up a report I get an exception "Query
engine error: C:\WINDOWS\Temp\temp_*******.rpt" with some wierd numbers
instead of *******. Can anyone tell me what the problem is here and how to
solve it. I am running the application on Windows 98.
|
|
|
|
|
What is the error message that is normally a bit of a clue?
|
|
|
|
|
santhoshmohan wrote: some wierd numbers
Define "weird numbers"? How is one number any more or less weird than another. For example, is 8 more or less weird than 17? And what about 42? How weird is that?
|
|
|
|
|
Hi all,
I would like to know something about performance tuning.
My doubt is I have A stored procedure for selecting data from a traxn' table.In that SELECT command, i want to invoke a UDF(find its count or sum like...).
Is it affects the perfomance of selecting data from DataBase???
Thnkx in advance
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
I would suggest you could have first glimpsed at the various typographical and lexical gaps in the post that you have made.
1) Even whilst mobile phones are now equipped with state-of-the-art dictionaries to dissuade user from cryptic ambiguous text messaging, there seems to be a little-to-more ambiguities over here:
Vipin dev wrote: traxn'
Vipin dev wrote: Thnkx
Did it mean 'HIGH VOLTAGE <small>TRACTION</small>' or 'TRANSACTION'?
|
|
|
|
|
Sorry Mr.Vasudevan
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
Using a UDF within a stored procedure can have negative performance effects as it must run for every record it pulls form the database.
|
|
|
|
|
Hi , here's my query result.
I want to filter out the column with NULL values. How can I do it ?
Dept Name Newyork LA Washington City1 SanFrn City2
Department1 0 0 0 NULL 1 NULL
Department2 4 0 0 NULL 2 NULL
Department3 0 0 0 NULL 0 NULL
Thanks in advance,
|
|
|
|
|
SELECT DeptName,ISNULL(NEWYORK,0),ISNULL(LA,0) from MYTABLE WHERE....
All I ever wanted is what others have....
CrazySanker
|
|
|
|
|
Dear Vipin dev,
Actually, I just want to remove the columns with NULL value. If I use ISNULL(NEWYORK,0), the column still in the result set and column name is 0.
Is there anyway I can do it ?
Thanks in advance,
|
|
|
|
|
|
hi check out this
select * from tablename where col is null or col='NULL'
|
|
|
|
|
I think what you want is COALESCE
select COALESCE(fieldname, 0)
COALESCE will select the first non null from a list so you can even have
select COALESCE(field1,field2,field3,field4, 0)
|
|
|
|
|
Hi
How can i check Day Light saving time in sql.
I know there is a inbuilt function called ISDST in C# , but i want that check to be done in Sql procedure.Thanks in Advance
Regards
DilipRam
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
