StoredProcedures - which is fastest and why - Database Discussion Boards

Re: Database design for a private message system

Wayne Gaylard19-Mar-10 1:48

19-Mar-10 1:48

Sorry, didn't get the part about folder ownership. This how I would set it up

Tables:

folders:
id int
name varchar
userid int

messages:
id int
fromid int
toid int
message varchar(250)
messagedate datetime
folderid int

users:
id int
name varchar

Then when you want to look up all messages from one person (say his id is 1) to another person (id = 2) in a particular folder (say id = 1) you would have a query like this

SELECT 
    m.message
FROM 
    messages m 
INNER JOIN 
    folders f 
ON 
    m.folderid = f.id 
WHERE 
    m.fromid = 1 
AND m.toid = 2
AND f.id = 1;

stored procedur to update multiple tables

Enobong Adahada18-Mar-10 2:05

Enobong Adahada

18-Mar-10 2:05

thanks alot, actually the first table gets inserted, but the rest do not.
thanks.

stored procedur to update multiple tables

Enobong Adahada18-Mar-10 1:21

Enobong Adahada

18-Mar-10 1:21

Hi,
i have this stored prcedure to update multiple tables in sqlserver DB,
when i run it , i get an saying tbpassport,tbaccounts, tbpermanentaddress and tbcontactaddress cannot allow nulls.
this is the code, where am i getting it wrong.
CREATE PROCEDURE InsertEmployeeRecord
@emp_id char(5), @surname char(25), @fisrtname char(25), @middlename char(25),
@sex char(10), @date_of_birth char(12), @ministry_code char(3), @dept_code char(3), @nationality char(15),
@state_code char(3), @lga_code char(3), @designation char(15), @date_of_employment char(12),
@unit_code char(3), @employement_type char(15), @marital_status char(10), @religion char(10), @glevel char(10), @step char(8),
@picture image,@filename char(52), @bank_name char(52), @bank_code char(3), @sortcode char(9), @branch char(100), @account_no char(40),
@pcity char(45), @ptown char(45), @pstreet char(100), @pphone char(200),
@ccity char(45), @ctown char(45), @cstreet char(100), @cphone char(200)
AS
BEGIN
SET NOCOUNT ON
INSERT tbemployee (emp_id,surname,firstname,middlename,
sex,date_of_birth,ministry_code,dept_code,nationality,
state_code,lga_code,designation,date_of_employment,
unit_code,employment_type,marital_status,religion,glevel,step)
VALUES(@emp_id, @surname, @fisrtname, @middlename, @sex, @date_of_birth, @ministry_code, @dept_code, @nationality,
@state_code, @lga_code, @designation, @date_of_employment, @unit_code, @employement_type, @marital_status,
@religion, @glevel, @step)

INSERT INTO tbpassport (emp_id,filename,picture)
VALUES(@emp_id, @filename, @picture)

INSERT INTO tbaccounts (emp_id,bank_code,bank_name,sortcode,account_no,branch)
VALUES (@emp_id, @bank_code, @bank_name, @sortcode, @account_no, @branch)

INSERT INTO tbpermanentaddress (emp_id, city, town, street, phone)
VALUES (@emp_id, @pcity, @ptown, @pstreet, @pphone)

INSERT INTO tbcontactaddress (emp_id,city,town,street, phone)
VALUES (@emp_id,@ccity, @ctown, @cstreet, @cphone)
END
GO

thanks.

Re: stored procedur to update multiple tables

Wayne Gaylard18-Mar-10 1:49

Wayne Gaylard

18-Mar-10 1:49

It seems as though your tables have been set up not to accept null values. You either need to edit the table to accept nulls (probably not the best approach), or check whether your input values are not null before running the stored procedure(my preferrred approach) .

Re: stored procedur to update multiple tables

The Man from U.N.C.L.E.18-Mar-10 7:38

The Man from U.N.C.L.E.

18-Mar-10 7:38

you kind of lost the thread there with your second post, however..

Either change the tables to allow nulls,
Or ensure no null values are passed to the procedure,
Or best of all, use isnull checks for each insert.

e.g.

SQL

INSERT INTO tbcontactaddress (emp_id,city,town,street, phone)
VALUES 
 (
   isnull(@emp_id, '')
  ,isnull(@ccity, '')
  ,isnull(@ctown, '')
  ,isnull(@cstreet, '')
  ,isnull(@cphone, '')
 )

Another consideration is that rather than passing so many parameters you may be better off passing it all wrapped up in a single XML parameter.

If you have knowledge, let others light their candles at it.
Margaret Fuller (1810 - 1850)
[My Articles] [My Website]

list all host names

Mogamboo_Khush_Hua18-Mar-10 0:23

Mogamboo_Khush_Hua

18-Mar-10 0:23

select spid, status, loginame, hostname, blocked, db_name(dbid), cmd from master..sysprocesses

Above query lists all hosts currently logged in using which login name.

But i want which hostname have logged in past using which login name . where do i found this information ?

Is there any log is maintained for that.

Re: list all host names

Eddy Vluggen18-Mar-10 2:40

Eddy Vluggen

18-Mar-10 2:40

Member 6392036 wrote:
i want which hostname have logged in past

AFAIK, the information isn't stored historically. If you want to know who was online when, then you'll need to add some kind of logging.

I are Troll Suspicious | :suss:

StoredProcedures - which is fastest and why

alwinSCH17-Mar-10 22:39

alwinSCH

17-Mar-10 22:39

CREATE PROCEDURE sp_Get_Borderel_4d_data
@MaatschappijID numeric,
@VervalDatum datetime,
@LanguageID char(1)
AS
DECLARE @MaatschappijIDLoc numeric
DECLARE @VervalDatumLoc datetime
DECLARE @LanguageIDLoc char(1)

SET @MaatschappijIDLoc = @MaatschappijID
SET @VervalDatumLoc = @VervalDatum
SET @LanguageIDLoc = @LanguageID
SELECT
Agent.AgentID,
Agent.Naam AS AgentNaam,
ProRataDetail.InternPolisNummer,
(sum(ProRataNettoPremie) - sum(ProRataCommissie)) AS Techpremie,
sum(ProRataNettoPremie) As ProRataNettoPremie,
sum(ProRataTaksen) As ProRataTaksen,
sum(ProRataBrutoPremie) As ProRataBrutoPremie,
sum(ProRataCommissie) As ProRataCommissie,
sum(ProRataBeheerskosten) As ProRataBeheerskosten
FROM
fn_Get_ProRataDetail(0, '{00000000-0000-0000-0000-000000000000}', 0, 0, @MaatschappijIDLoc, @VervalDatumLoc, @LanguageIDLoc)
as ProRataDetail
LEFT JOIN Polis ON Polis.PolisID = ProRataDetail.PolisID
LEFT JOIN Klant ON Klant.KlantID = Polis.KlantID
LEFT JOIN Agent ON Agent.AgentID = Polis.AgentID
WHERE
Polis.Demo <> 1
GROUP BY
Agent.AgentID,
Agent.Naam,
ProRataDetail.InternPolisNummer
ORDER BY
Agent.Naam,
ProRataDetail.InternPolisNummer

GO
----------------------------------------------------------
CREATE PROCEDURE sp_Get_Borderel_4d_data
@MaatschappijID numeric,
@VervalDatum datetime,
@LanguageID char(1)
AS

SELECT
Agent.AgentID,
Agent.Naam AS AgentNaam,
ProRataDetail.InternPolisNummer,
(sum(ProRataNettoPremie) - sum(ProRataCommissie)) AS Techpremie,
sum(ProRataNettoPremie) As ProRataNettoPremie,
sum(ProRataTaksen) As ProRataTaksen,
sum(ProRataBrutoPremie) As ProRataBrutoPremie,
sum(ProRataCommissie) As ProRataCommissie,
sum(ProRataBeheerskosten) As ProRataBeheerskosten
FROM
fn_Get_ProRataDetail(0, '{00000000-0000-0000-0000-000000000000}', 0, 0, @MaatschappijID, @VervalDatum, @LanguageID)
as ProRataDetail
LEFT JOIN Polis ON Polis.PolisID = ProRataDetail.PolisID
LEFT JOIN Klant ON Klant.KlantID = Polis.KlantID
LEFT JOIN Agent ON Agent.AgentID = Polis.AgentID
WHERE
Polis.Demo <> 1
GROUP BY
Agent.AgentID,
Agent.Naam,
ProRataDetail.InternPolisNummer
ORDER BY
Agent.Naam,
ProRataDetail.InternPolisNummer

GO
--------------------------------------------------------------------------
Why is the first procedure, faster than the second?
The ony difference are the extra local variables!

thanks,
A.

Re: StoredProcedures - which is fastest and why

Mycroft Holmes17-Mar-10 23:27

Mycroft Holmes

17-Mar-10 23:27

The question is why are you using 3 extra variables, that takes 3 extra cycles and therefore it is slower Poke tongue | ;-P

Never underestimate the power of human stupidity
RAH

Re: StoredProcedures - which is fastest and why

alwinSCH17-Mar-10 23:53

alwinSCH

17-Mar-10 23:53

I can run it a 1000 times!
the difference is:
the first one with local variables: I get a result in 5 seconds.
the second one without local variables: I get a result in 20 minutes!!!!

Thank you

Re: StoredProcedures - which is fastest and why

WoutL18-Mar-10 0:26

WoutL

18-Mar-10 0:26

This looks like a problem caused by Parameter sniffing. Just google on it and you'll see what I mean.

Wout Louwers

Re: StoredProcedures - which is fastest and why

alwinSCH18-Mar-10 0:50

alwinSCH

18-Mar-10 0:50

thank you all,
it has something to do with Parameter Sniffing!

It explains a lot!

Is this the best practice? to disable parameter sniffing?

Thank you

Re: StoredProcedures - which is fastest and why

WoutL18-Mar-10 1:15

WoutL

18-Mar-10 1:15

Well,

You are using the parameters in the call to fn_Get_ProRataDetail. You could try what happens ig you do the local var trick in that function. That way other sp's may benefit too. But the local var trick is the only one I know besides using With Recompile.

Wout Louwers

Re: StoredProcedures - which is fastest and why

Mycroft Holmes18-Mar-10 11:44

Mycroft Holmes

18-Mar-10 11:44

See thats the reason I spend time here, never heard of parameter sniffing, sounds disgusting. There are some excellent articles out there, another tool in the ongoing fight for performance.

Thanks Wout.

Never underestimate the power of human stupidity
RAH

DataGridView problem with cell coloring [modified]

Mattzimmerer17-Mar-10 15:54

Mattzimmerer

17-Mar-10 15:54

I'm not sure if this is the best place to post this, but I'm retrieving a table form an SQL database and databinding the contents into a DataGridView.

I have some code that searches the entire DataGridView and locates null values, and I intend to color the fields red so the user knows he needs to put something in.

private void button1_Click(object sender, EventArgs e)
{
    //MessageBox.Show(dataGridView.ColumnCount.ToString());
    for (int column = 0; column < 1; column++)//this.dataGridView.ColumnCount
    {
        for (int row = 0; row < this.dataGridView.RowCount; row++)
        {
            //MessageBox.Show("Row,Column: "+row.ToString()+","+column.ToString());
            if (this.dataGridView.Rows[row].Cells[column].Value == null)
            {
                this.dataGridView.Rows[row].Cells[column].Style.BackColor= Color.Red;               //EDITED: changed property to BackColor like I should have posted it...
                //MessageBox.Show("Null at: " + row.ToString() + "," + column.ToString());
            }
        }
    }
}

theres still some messageboxes in there that I used to figure this out...
If I change the conditional statement to (this.dataGridView.Rows[row].Cells[column].Value != null) so that it searches for non null fields, it successfully colors the cells.

I guess my problem has to do with the nullity (lol is that a word?) of my cells.

I guess I should include how I am populating my dataGridView...:

private void Form1_Load(object sender, EventArgs e)
{
    // TODO: This line of code loads data into the '_Vehicle_EquipmentDataSet._Pickups___Cars' table. You can move, or remove it, as needed.
    this.pickups___CarsTableAdapter.Fill(this._Vehicle_EquipmentDataSet._Pickups___Cars);
}

that was auto generated =)

Perhaps I can handle the null data when the list is populated?

Thanks in advance!!!

modified on Thursday, March 18, 2010 12:45 PM

Re: DataGridView problem with cell coloring

Luc Pattyn17-Mar-10 16:22

Luc Pattyn

17-Mar-10 16:22

Mattzimmerer wrote:
this.dataGridView.Rows[row].Cells[column].Style.ForeColor = Color.Red;

how do you see the ForeColor of an empty cell?

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.

Re: DataGridView problem with cell coloring

Mattzimmerer17-Mar-10 18:58

Mattzimmerer

17-Mar-10 18:58

Ohh, i should have changed that back. No I first tryed backcolor or w/e, which works find for populated cells... that was a purley desperate attempt..

THE PROBLEM PERSISTS!!!!

Re: DataGridView problem with cell coloring

darkelv17-Mar-10 17:49

darkelv

17-Mar-10 17:49

You should check the value from the data source itself, not datagridview's cells. Subscribe to the datagridview's CellFormatting event, then in the handler get the DataBoundItem (object, datarow etc) and set color base on the DataBoundItem's data itself.

I'm binding to collection of object, btw, so YMMV.

private void dataGridView_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            string dataPropertyName = this.dataGridView.Columns[e.ColumnIndex].DataPropertyName;
            ObjectType theObject;
            switch (dataPropertyName)
            {
                case "ColumnName":
                    theObject = (ObjectType)this.dataGridView.Rows[e.RowIndex].DataBoundItem;

                    // Set the color base on theObject's property mapped to dataPropertyName
                    break;

            }

        }

Re: DataGridView problem with cell coloring

Mattzimmerer18-Mar-10 9:41

Mattzimmerer

18-Mar-10 9:41

Fixed my problem:

private void Null_check_Click(object sender, EventArgs e)
{
    for (int row = 0; row < this.dataGridView.RowCount-1; row++)
        for (int column = 0; column < this.dataGridView.ColumnCount; column++)
            if (this.dataGridView.Rows[row].Cells[column].Value.ToString() == "")
                this.dataGridView.Rows[row].Cells[column].Style.BackColor = Color.Red;
}

just needed sleep I guess...

Primary Indexed?

Mike65432117-Mar-10 9:06

Mike654321

17-Mar-10 9:06

CREATE TABLE [PreferenceType] (
[PreferenceTypeId] [BIGINT] IDENTITY (1,1) NOT NULL,
[LongName] [NVARCHAR](500),
[ShortName] [NVARCHAR](250)
);

CREATE TABLE [Table2]
(
[Table2Id] [BIGINT] IDENTITY (1,1) NOT NULL,
[PreferenceTypeId] [INT]
)

Given the Preference Lookup table above, if I do an Inner Join to Table2 on the PreferenceTypeId field, I should index like this:

CREATE INDEX [PreferenceTypeId_Idx] ON [Table2]([PreferenceTypeId]); <--- good?

But I shouldn't index like this:

CREATE INDEX [PreferenceTypeId2_Idx] ON [PreferenceType]([PreferenceTypeId]); <--- bad?

because Primary Key fields should already be indexed, correct?

Re: Primary Indexed? [modified]

i.j.russell17-Mar-10 9:39

i.j.russell

17-Mar-10 9:39

You should make PreferenceType.PreferenceTypeId a PrimaryKey (Yes it will be automatically indexed) and you should make Table2.PreferenceTypeId a ForeignKey constraint referencing PreferenceType.PreferenceTypeId (this will also be automatically indexed).

[Edit]
Foreign keys are NOT automatically indexed.

modified on Wednesday, March 17, 2010 5:06 PM

Re: Primary Indexed?

Mike65432117-Mar-10 9:53

Mike654321

17-Mar-10 9:53

KEWL, i learned something new, that foreign keys automatically get indexed for me.

Re: Primary Indexed?

Jörgen Andersson17-Mar-10 10:50

Jörgen Andersson

17-Mar-10 10:50