Database

How can I optimize this SQL Query?

10-Dec-08 4:41

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.

The need to optimize rises from a bad design.My articles[^]

Rafferty Uy4-Dec-08 17:52

Rafferty Uy

4-Dec-08 17:52

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? Smile | :)

Thanks!

Rafferty

Wendelius4-Dec-08 19:38

4-Dec-08 19:38

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

The need to optimize rises from a bad design.My articles[^]

Rafferty Uy4-Dec-08 20:10

Rafferty Uy

4-Dec-08 20:10

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

Mycroft Holmes4-Dec-08 21:41

Mycroft Holmes

4-Dec-08 21:41

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

Wendelius4-Dec-08 21:59

4-Dec-08 21:59

Mycroft Holmes wrote:
cursors are evil

Exactly

The need to optimize rises from a bad design.My articles[^]

Wendelius4-Dec-08 21:58

4-Dec-08 21:58

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

The need to optimize rises from a bad design.My articles[^]

Ben Fair5-Dec-08 4:09

Ben Fair

5-Dec-08 4:09

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)

How to access *.dbf file using c#

Rafferty Uy8-Dec-08 17:18

Rafferty Uy

8-Dec-08 17:18

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

Kumaran Jeyasingam4-Dec-08 14:32

Kumaran Jeyasingam

4-Dec-08 14:32

Dear All,

can any one help me to give some sample code on how to access *.dbf file using c#

Thanks in advance

PIEBALDconsult4-Dec-08 15:33

PIEBALDconsult

4-Dec-08 15:33

Look in the documentation for the System.Data.SqlClient namespace.

Mycroft Holmes4-Dec-08 21:45

Mycroft Holmes

4-Dec-08 21:45

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

Wendelius5-Dec-08 8:24

5-Dec-08 8:24

Good heavens OMG | :OMG:

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.

The need to optimize rises from a bad design.My articles[^]

Mycroft Holmes5-Dec-08 14:07

Mycroft Holmes

5-Dec-08 14:07

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

Program Design - Master DataSet/Adapter for program?

Kumaran Jeyasingam14-Dec-08 14:58

Kumaran Jeyasingam

14-Dec-08 14:58

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();
}

David Hovey4-Dec-08 8:53

David Hovey

4-Dec-08 8:53

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 />

Re: Program Design - Master DataSet/Adapter for program?

Wendelius4-Dec-08 9:25

Re: Program Design - Master DataSet/Adapter for program?

4-Dec-08 9:25

I think your post would have been better off in Design and Architecture forum, but anyway.

David Hovey wrote:
when the user clicks "OK" on whichever dialog, the program will commit the new data to the database

Just a note: Take care that database operations are done transactionally and no user interference is allowed while transaction is running.

David Hovey wrote:
Do you think these objects should be created with each form

Forms and data (and logic) should be separated so that you can modify each (almost) independently. Get to know for example MVC (Model-View-Controller) concept.

David Hovey wrote:
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

I don't see any reason why you should store the results to a disk. You use joins in the SQL statement.

David Hovey wrote:
But anything more complex I now need to create a new DataAdapter and DbCommand objects

That would be logical in many cases, although you can reuse the command if you change the command text.

David Hovey wrote:
Would it be reasonable to have one OleDbConnection object for application

You should get and open the connection to the database when you need to operate with it. After executing the statements, you close the connection. Also use connection pooling.

David Hovey wrote:
Then mutiple OleDbAdapter objects for each form

Again, separate forms and database operations. For example if you someday want to run the logic on a middle-tier and only the UI on client (or web page) it greately helps you if you have a good and logical layering in your code.

David Hovey wrote:
I would also have a OleDbCommandBuilder in each form to allow for the Update method when user wants to commit data.

Same as previous, but note what I wrote in the beginning about transactions and user interference.

Hope this helps you forward,

Mika

The need to optimize rises from a bad design.My articles[^]

David Hovey4-Dec-08 10:56

David Hovey

4-Dec-08 10:56

It does very much. I found a PDF of excerpt from a book "Object-Orientated Development with ActionScript 2.0" (chapter 18) that talks about MVC.

I can see the basic concepts of each part of MVC and I can see the benefits, but exactly how to relate it to my program is difficult.

The example in the book is a Clock with digital and analog views. Rather simple.

Can you help me a beginning question I have though? From my understanding each View should have one Controller. Does that mean the Controller is designed specifically for that particular view?

WTF | :WTF:

This sure does require alot more planning that the way I was going about it. It will take more time...

Will need to study MVC further obviously.

Re: Program Design - Master DataSet/Adapter for program?

Wendelius5-Dec-08 7:50

5-Dec-08 7:50

David Hovey wrote:
how to relate it to my program is difficult

In the beginning it is, but don't worry. When you get over the 'new concepts' and 'ideology that changes everything' part you actually discover that it's no rocket sience. Just a new three (or four) letter abbreviation to a very simple and logical thing.

I think there are several good articles about MVC at this site, have a look at them, for example: Building an MVP Framework for .NET. Part 1: The Basics of MVC and MVP[^]

David Hovey wrote:
each View should have one Controller

That's the basic idea but in some implementations one controller can have several views.

David Hovey wrote:
This sure does require alot more planning that the way I was going about it

It does, but you don't have to use Visual Studio or Rational Rose etc. In the simplest form it's basic OOP principles and what code do you write and where. So actually what I typically use is pencil and paper. Well, lots of paper because the first versions always go to the garbage pin Smile | :)

David Hovey wrote:
It will take more time

It will in this project, but you also learn a lot. That's an investment!

The need to optimize rises from a bad design.My articles[^]

Database Problem

sajjadlashari3-Dec-08 22:50

sajjadlashari

3-Dec-08 22:50

I am trying to connect SQL Server 2005 with asp.net there is one problem
Kindly reply me quick as i able to do my work.

This is the problem

Could not load file or assembly 'Microsoft.SqlServer.Management.Sdk.Sfc,Version = 10.0.0.0,Culture = neutral, PublicKey Token = 89845dcd8080cc91'or one of its dependencies.

Re: Database Problem

J4amieC3-Dec-08 23:08

J4amieC

3-Dec-08 23:08

At a guess Id say that its failing to load an assembly. Have you added all required references?

Re: Database Problem

Wendelius4-Dec-08 7:40