|
Hi,
i am handling a VLDB( very large database), out of which some tables contains billions of records. a few applications are continuously accessing this database. Right now i am using a Database manager library ( its a c# dll) to handle the database. I am looking forward for suggestions and useful links to help me to increase the performance of these applications.
What are the efficient ways to handle such kind of large database? , Now it is taking more time to return from a query/
|
|
|
|
|
In (as they say) no particular oprder, I would look at
> partitioned tables and/or views as these can have a massive performance gain.
> is all the data required continually, could some of it be archived.
> the physical location of the data and indices - ensure seperate controllers etc
> the indices themselves - can they be improved
There are a few starting points, I'm sure other people will be able to come up with more
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks, i know that no one will have a ready made answer.
anyway i am not sure about your 3rd and 4th points, can u put some light on it?
|
|
|
|
|
By putting your data and indices on totally separate disks (including disk controllers) you will improve performance. Checking the execution plans of the most common queries will reveal if teh indices on the tables are the most efficiant. There also loads of other possibilities.
I would do as Mycroft suggests, get a DBA with strong VLDB experience in to review your system.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Have you engaged a DBA to optimise your database, with a VLDB it is probably worth paying for professional help. Unless a professional DBA or highly skilled developer has been involved in the DB then there are a myriad of things that can be done to improve performance.
From your lack of understanding of 3-4 in Bobs suggestions I suspect there has not been any optimisation past throwing some indexes at the tables. Indexes and data files can reside on multiple disks, dramatically increasing the response time.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Very good answers from both Ashfield and Mycroft.
To add a few things: You said that applications are accessing the database. What you didn't mention is that do they mostly only read or do they modify the contents of the database. Depending on the answer you should take a bit different paths in optimization. For example if the database is mostly only read, usage of indices can be very agressive while if it's commonly also updated, you will need to get a 'balanced' solution. This principle is not used only against the whole database but for different areas in the database so for different tables you could have different optimization goals.
As Mycroft suggested, you would benefit if you get a good DBA to have a look at the problems and advice you further. The solution won't be purely technical but it will include technical modifications as well as going through the logic of the applications and possibly modifying it if necessary and possible.
One technical thing to add to the excellent list from Ashfield, you could consider also using federations.
|
|
|
|
|
|
sujithkumarsl wrote: but also looking to improve the performance
Start by investigating the execution plans for the problem queries. From there on you should be able to locate the bottlenecks and eliminate them with the info from all the replies.
If you later have some specific questions or problems, I think you should open a new thread with the new question. In that case, don't forget to add enough information about the problem.
|
|
|
|
|
Can anyone suggest the architecture of my applications in order to communicate with the database, right now we are using 3 tier architecture, we have a database manager library to communicate with the database.
Can anyone suggest a design , which can make things faster?
My small attempt...
|
|
|
|
|
This question is too vague.
As I replied earlier, break the problem into parts, post a new question on a new thread and provide enough information about the problem and the requirements.
Also remember to select correct forum in order to get good answers.
|
|
|
|
|
We have this stored procedure:
DECLARE ParentCursor Cursor for
select iitm_id_parent from z_structure where iitm_id = @pverID
open ParentCursor
fetch next from ParentCursor into @versionId
while (@@fetch_status = 0)
begin
select @isDraft = isnull(ipve_draft, 0) from i_product_version where ipve_id = @versionId
select @nCount = count(*)
from i_prod_wwrk
where wwrk_id = @workflowID and ipro_id in (select ipro_id from i_product_version where ipve_id = @versionId)
if ((@nCount > 0) and (@isDraft = 0))
insert into e_parents (expo_id, pver_id, pver_parent_id) values (@expoID, @pverID, @versionId)
fetch next from ParentCursor into @versionId
end
close ParentCursor
deallocate ParentCursor
And now, we are optimizing it into:
INSERT INTO E_PARENTS (EXPO_ID, PVER_ID, PVER_PARENT_ID)
SELECT @expoID, @pverID, z.iitm_id_parent
FROM z_structure z INNER JOIN i_product_version pv ON z.iitm_id_parent = pv.ipve_id
WHERE z.iitm_id = @pverID AND ISNULL(pv.ipve_draft, 0) = 0 AND
EXISTS (SELECT 1 FROM i_prod_wwrk pw
WHERE pw.wwrk_id = @workflowID AND pw.ipro_id IN
(SELECT ipro_id FROM i_product_version WHERE ipve_id = z.iitm_id_parent)
)
Do you think this is correct? And are there any further suggestions? 
Thanks!
Rafferty
|
|
|
|
|
In most cases single statement performs much better than cursors so bsed on this you're on a correct path.
What comes to the actual performance, use execution plan information and execution statistics from Enterprise Manager. Compare the execution of the cursor based logic to the statistics from single insert. Also see if execution plan is acceptable and doesn't contain severe bottlenecks
|
|
|
|
|
Hey it's you again!
Are you saying that the single statement can contain bottlenecks compared to cursors?
I will try to read up on execution plan info and execution statistics.
Thanks.
Rafferty
|
|
|
|
|
Rafferty
You got Mika's statement exactly wrong, cursors are evil, try and avoid them wherever possible.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: cursors are evil
Exactly
|
|
|
|
|
Rafferty Uy wrote: Are you saying that the single statement can contain bottlenecks compared to cursors
Not at all.
What I'm saying is that these two techniques are (totally) different and statement based is the better approach. But when you create a statement (complex or not) you should always verify the performance part.
I've seen many situations where a cursor based procedure is converted to a statement but the result has been performing worse. The simple reason has been that the optimization and execution verification has been forgotten or ignored. So the basic idea is that always verify the results.
Mika
|
|
|
|
|
If you choose to still use a cursor, you can use the FAST_FORWARD keyword to improve the performance a bit.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Hm.. I didn't know about the FAST FORWARD feature. Thanks for the tip.
Just to finalize this thread, will anyone be so kind to check if my optimized query above is correct (will give the same results as the old one)?
Thanks!
Rafferty
|
|
|
|
|
Dear All,
can any one help me to give some sample code on how to access *.dbf file using c#
Thanks in advance
|
|
|
|
|
Look in the documentation for the System.Data.SqlClient namespace.
|
|
|
|
|
DBF are DBase files, they may be 1 of several versions including foxpro. Using a DBase IV driver would be the first step as this used support most versions but you may need to install and test a few to get the correct driver.
You should be able to open it with Access and it may give you some clues as to the version.
Once you have the driver installed you simply use a connection string as any other database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Good heavens  Is dbf still alive and kicking? The last time I used it was over two decades ago and I thought it was already buried... deep.
|
|
|
|
|
Every now and then it raises it's head, we currently have to use a foxpro driver to access a bastardised version of a DBase IV format. Someone released a NEW dbf viewer this year so it is still around.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks fo ryour reply,
FYI, I have using the ODBC connection to connect the *.dbf file before and it's working fine ,and i don't know why know , suddently i have problem to connecting to the database.
I have attached my source code for your refference.Plzzzzzzzz guide me on how to solve tyhis problem
using System.Data.Odbc;
string thisConnection = @"Driver={Microsoft dBASE Driver (*.dbf)};SourceType=DBF;SourceDB=C:\data\;Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;";
string sql = @"SELECT * FROM C:\data\QC.dbf";
OdbcConnection conn = new OdbcConnection(thisConnection);
OdbcCommand cmd = new OdbcCommand(sql);
cmd.Connection = conn;
OdbcDataReader reader = null;
try
{
conn.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
MessageBox.Show(reader[1].ToString());
}
reader.Close();
}
catch (OdbcException err)
{
MessageBox.Show(err.Message);
}
finally
{
conn.Close();
}
|
|
|
|
|
Hi All! I am new to using the .NET framework database objects and have a question about how to design my program using Adapter/DataTable queries.
My Database...My database consists of about 10 different tables. No one table is indepedent. All have at least one relationship with another table.
My Program...The program needs to be designed such, that when the user clicks "OK" on whichever dialog, the program will commit the new data to the database.
Here is the jist of what I'm curious about...What is best design for the interaction between my OleDbDataAdapter and the DataSet or DataTables? Do you think these objects should be created with each form? When I need to run a query involving two tables using OleDbCommand it seems I have to use an adapter which then reads the database on disk and not an In-memory object.
Currently, on startup my program reads each table into a DataSet object. For basic queries involving a single table this works fine (using the Select method of DataTable). But anything more complex I now need to create a new DataAdapter and DbCommand objects.
Would it be reasonable to have one OleDbConnection object for application. Then mutiple OleDbAdapter objects for each form. I would also have a OleDbCommandBuilder in each form to allow for the Update method when user wants to commit data.
To illustrate my startup code is below.
Thanks for your help!!!
m_dsPTData = New DataSet<br />
<br />
m_dbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFilename)<br />
m_dbConnection.Open()<br />
<br />
m_dbDataAdapter = New OleDbDataAdapter()<br />
<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Congregations", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "Congregations")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM HomeTalks", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "HomeTalks")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM CongregationSpeakers", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "CongregationSpeakers")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM CongregationSpeakerOutlines", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "CongregationSpeakerOutlines")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Outlines", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "Outlines")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM OutlineCategories", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "OutlineCategories")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Readers", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "Readers")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Chairmen", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "Chairmen")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Hospitality", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "Hospitality")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM AwayTalks", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "AwayTalks")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Notes", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "Notes")<br />
m_dbDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Conductors", m_dbConnection)<br />
m_dbDataAdapter.Fill(m_dsPTData, "Conductors")<br />
<br />
Dim commandBuilder As OleDbCommandBuilder = _<br />
New OleDbCommandBuilder(m_dbDataAdapter)<br />
<br />
m_dbConnection.Close()<br />
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
