locked
primary key RRS feed

  • Question

  • I have a tabel in my database that got 490985 row and got no primary key... does that affect the speed of inserting and retriving data??
    • Edited by Dr.X1987 Monday, October 4, 2010 1:49 PM
    • Changed type Kent Waldrop Tuesday, October 5, 2010 1:07 PM Seeking an answer
    Monday, October 4, 2010 12:32 PM

Answers

  • But in any case consider creating PK attribute in this table. If you get a situation to retrive some data, without PK and indexes that process will be real performance killer.
    Jasmin Azemovic PhD candidate , SQL Server MVP, MCT
    • Marked as answer by Dr.X1987 Thursday, October 7, 2010 2:50 PM
    Tuesday, October 5, 2010 6:59 AM
  • hi,

      As suggested in previous posts, it is better to create a primary key on the table, since the number of records also huge. If there is no key defined, then, its like heap and data retrival will be slower. If PK is there then, there wil be unique clustered index on the table and the data will be ORDERED. So, it is a good practice to have primary key on the table ...


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    • Marked as answer by Dr.X1987 Thursday, October 7, 2010 2:50 PM
    Wednesday, October 6, 2010 3:28 AM

All replies

  • It depends... For retrieving the data you need to show us  the query you run againts table. Generally speaking , every table should have CI but on what columns  will have to decide YOU.


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, October 4, 2010 1:27 PM
  • Thanks

    it is a simple INSERT Query ,but the problem is that there are many clients inserting in that table at the same time

    Monday, October 4, 2010 1:58 PM
  • this is the code of creating the tables I mentioned :

     

     

    CREATE TABLE [dbo].[POS_Bill_Detail](
    	[Date_Created] [datetime] NULL,
    	[Expiration_Date] [datetime] NULL,
    	[POS_Det_Discount] [float] NULL,
    	[POS_Det_ID] [bigint] IDENTITY(1,1) NOT NULL,
    	[POS_Det_Mat_ID] [bigint] NOT NULL,
    	[POS_Det_Nafdat_Type] [tinyint] NOT NULL,
    	[POS_Det_Net] [float] NOT NULL,
    	[POS_Det_Price] [float] NOT NULL,
    	[POS_Det_Qty] [float] NOT NULL,
    	[POS_Det_Total] [float] NOT NULL,
    	[POS_Det_Unit_ID] [int] NULL,
    	[POS_ID] [bigint] NOT NULL,
    	[unit_Equal] [float] NULL,
    	[User_ID] [tinyint] NOT NULL,
    	[Voucher_ID] [bigint] NULL
    ) ON [PRIMARY]
    GO
    

     

    USE [ACC_DB_2010_8]
    GO
    
    /****** Object: Table [dbo].[POS_Bill]  Script Date: 10/04/2010 17:07:19 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[POS_Bill](
    	[ATM_CreditCard_ACC_ID] [bigint] NULL,
    	[ATM_CreditCard_Paid_Val] [float] NULL,
    	[Date_Created] [datetime] NULL,
    	[IS_Num_Set_Zero] [bit] NULL,
    	[Notation] [varchar](100) NULL,
    	[POS_Acc_ID] [bigint] NOT NULL,
    	[POS_Back_Discount_Percent] [varchar](53) NULL,
    	[POS_Back_Money] [varchar](53) NULL,
    	[POS_Back_Value] [float] NULL,
    	[POS_Book_NO] [int] NULL,
    	[POS_BOX_ACC_ID] [bigint] NULL,
    	[POS_Currency_Equal] [float] NULL,
    	[POS_Currency_ID] [bigint] NULL,
    	[POS_Date] [smalldatetime] NOT NULL,
    	[POS_Dis_Gained] [float] NULL,
    	[POS_Dis_Granted] [float] NULL,
    	[POS_Discount_Percent] [float] NULL,
    	[POS_Discount_Value] [float] NULL,
    	[POS_Document_NO] [int] NULL,
    	[POS_Give_Value] [float] NULL,
    	[POS_ID] [bigint] IDENTITY(1,1) NOT NULL,
    	[POS_Is_Transfer] [bit] NOT NULL,
    	[POS_Left_Value] [float] NULL,
    	[POS_Net] [float] NULL,
    	[POS_NO] [int] NOT NULL,
    	[POS_Paid_Value] [float] NULL,
    	[POS_Seller_ID] [bigint] NULL,
    	[POS_Storage_ID] [int] NULL,
    	[POS_Storage_Movements_Transferred] [bit] NULL,
    	[POS_Time] [varchar](15) NULL,
    	[POS_Total_Qty] [float] NOT NULL,
    	[POS_Total_Value] [float] NOT NULL,
    	[POS_Type] [int] NULL,
    	[POS_With_Pays] [bit] NULL,
    	[Tax_Value] [float] NULL,
    	[User_ID] [tinyint] NOT NULL
    ) ON [PRIMARY]
    
    GO
    
    

    • Edited by Dr.X1987 Monday, October 4, 2010 2:07 PM
    Monday, October 4, 2010 2:04 PM
  • USE [ACC_DB_2010_8]
    GO
    /****** Object: StoredProcedure [dbo].[Save_POS_Mat_Inserting] Script Date: 10/04/2010 17:05:18 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[Save_POS_Mat_Inserting]
    @Bill_Type int , @UserID int , @BillDate datetime     
    AS
    BEGIN
     /* Procedure body */
     declare @Mat_ID bigint declare @Client_ID bigint declare @Mat_Qty float(53)
     declare @Storage_ID bigint declare @Bill_Date datetime
     declare @Created_ID int declare @Created_Date datetime
    declare @Updated_ID int declare @Updated_Date datetime
     declare @Service_Mat int
     declare @Bill_Detail cursor
     set @Bill_Detail = cursor for 
     Select POS_Bill_Detail.POS_Det_Mat_ID,POS_Bill.POS_Acc_ID,POS_Bill_Detail.POS_Det_Qty*POS_Bill_Detail.unit_Equal
     ,POS_Bill.POS_Storage_ID,POS_Bill.POS_Date,POS_Bill.User_ID,POS_Bill.Date_Created
     from POS_Bill,POS_Bill_Detail
     where POS_Bill.POS_ID= POS_Bill_Detail.POS_ID And POS_Bill_Detail.POS_Det_Nafdat_Type=@Bill_Type
     and POS_Bill.POS_Is_Transfer=0 And POS_Storage_Movements_Transferred=0 AND POS_Bill.User_ID= @UserID AND POS_Date =@BillDate
     open @Bill_Detail
     fetch next from @Bill_Detail into @Mat_ID, @Client_ID, @Mat_Qty, @Storage_ID, @Bill_Date,  @Created_ID, @Created_Date
     if @Created_Date is null set @Created_Date = getdate()
     if @Updated_Date is null set @Updated_Date = getdate()
     set @Updated_ID = @Created_ID
     set @Updated_Date = @Created_Date
     while (@@fetch_status = 0 )
     begin
     IF NOT EXISTS (SELECT * FROM [dbo].[Mat_Inserting_Year] WHERE
      (Mat_ID = @Mat_ID AND Storage_ID = @Storage_ID AND Acc_ID = @Client_ID AND Bill_Type = @Bill_Type))
     begin
      set @Service_Mat = (select Service_Mat from Material_Detail where Material_Detail.Mat_ID= @Mat_ID)
      if (@Service_Mat is null) OR (@Service_Mat = 0)
      begin
      if month(@Bill_Date) = 1
       INSERT INTO Mat_Inserting_Year
       VALUES (@Mat_ID, @Storage_ID, @Client_ID, @Bill_Type, @Mat_Qty, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, @Created_ID, @Created_Date, @Updated_ID, @Updated_Date)
      if month(@Bill_Date) = 2
       INSERT INTO Mat_Inserting_Year
       VALUES (@Mat_ID, @Storage_ID, @Client_ID, @Bill_Type, 0, @Mat_Qty, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, @Created_ID, @Created_Date, @Updated_ID, @Updated_Date)
      if month(@Bill_Date) = 3
       INSERT INTO Mat_Inserting_Year
       VALUES (@Mat_ID, @Storage_ID, @Client_ID, @Bill_Type, 0, 0, @Mat_Qty, 0, 0, 0, 0, 0, 0, 0, 0, 0, @Created_ID, @Created_Date, @Updated_ID, @Updated_Date)
      if month(@Bill_Date) = 4
    
    
    
       INSERT INTO Mat_Inserting_Year
    
    
    
       VALUES (@Mat_ID, @Storage_ID, @Client_ID, @Bill_Type, 0, 0, 0, @Mat_Qty, 0, 0, 0, 0, 0, 0, 0, 0, @Created_ID, @Created_Date, @Updated_ID, @Updated_Date)
    
    
    
      if month(@Bill_Date) = 5
    
    
    
       INSERT INTO Mat_Inserting_Year
    
    
    
       VALUES (@Mat_ID, @Storage_ID, @Client_ID, @Bill_Type, 0, 0, 0, 0, @Mat_Qty, 0, 0, 0, 0, 0, 0, 0, @Created_ID, @Created_Date, @Updated_ID, @Updated_Date)
    
    
    
      if month(@Bill_Date) = 6
    
    
    
       INSERT INTO Mat_Inserting_Year
    
    
    
       VALUES (@Mat_ID, @Storage_ID, @Client_ID, @Bill_Type, 0, 0, 0, 0, 0, @Mat_Qty, 0, 0, 0, 0, 0, 0, @Created_ID, @Created_Date, @Updated_ID, @Updated_Date)
    
    
    
      if month(@Bill_Date) = 7
    
    
    
       INSERT INTO Mat_Inserting_Year
    
    
    
       VALUES (@Mat_ID, @Storage_ID, @Client_ID, @Bill_Type, 0, 0, 0, 0, 0, 0, @Mat_Qty, 0, 0, 0, 0, 0, @Created_ID, @Created_Date, @Updated_ID, @Updated_Date)
    
    
    
      if month(@Bill_Date) = 8
    
    
    
       INSERT INTO Mat_Inserting_Year
    
    
    
       VALUES (@Mat_ID, @Storage_ID, @Client_ID, @Bill_Type, 0, 0, 0, 0, 0, 0, 0, @Mat_Qty, 0, 0, 0, 0, @Created_ID, @Created_Date, @Updated_ID, @Updated_Date)
    
    
    
      if month(@Bill_Date) = 9
    
    
    
       INSERT INTO Mat_Inserting_Year
    
    
    
       VALUES (@Mat_ID, @Storage_ID, @Client_ID, @Bill_Type, 0, 0, 0, 0, 0, 0, 0, 0, @Mat_Qty, 0, 0, 0, @Created_ID, @Created_Date, @Updated_ID, @Updated_Date)
    
    
    
      if month(@Bill_Date) = 10
    
    
    
       INSERT INTO Mat_Inserting_Year
    
    
    
       VALUES (@Mat_ID, @Storage_ID, @Client_ID, @Bill_Type, 0, 0, 0, 0, 0, 0, 0, 0, 0, @Mat_Qty, 0, 0, @Created_ID, @Created_Date, @Updated_ID, @Updated_Date)
    
    
    
      if month(@Bill_Date) = 11
    
    
    
       INSERT INTO Mat_Inserting_Year
    
    
    
       VALUES (@Mat_ID, @Storage_ID, @Client_ID, @Bill_Type, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, @Mat_Qty, 0, @Created_ID, @Created_Date, @Updated_ID, @Updated_Date)
    
    
    
      if month(@Bill_Date) = 12
    
    
    
       INSERT INTO Mat_Inserting_Year
    
    
    
       VALUES (@Mat_ID, @Storage_ID, @Client_ID, @Bill_Type, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, @Mat_Qty, @Created_ID, @Created_Date, @Updated_ID, @Updated_Date)
    
    
    
      end
    
    
    
     end
    
    
    
     else
    
    
    
     begin
    
    
    
     set @Mat_Qty = @Mat_Qty 
    
    
    
     set @Service_Mat = (select Service_Mat from Material_Detail where Material_Detail.Mat_ID= @Mat_ID)
    
    
    
     if (@Service_Mat is null) OR (@Service_Mat = 0)
    
    
    
     begin
    
    
    
     if month(@Bill_Date) = 1
    
    
    
      UPDATE Mat_Inserting_Year
    
    
    
      SET Month1_QTY = Month1_QTY + @Mat_Qty
    
    
    
       , Updated_ID = @Updated_ID, Updated_Date = @Updated_Date
    
    
    
       WHERE (Mat_ID = @Mat_ID AND Storage_ID = @Storage_ID AND Acc_ID = @Client_ID AND Bill_Type = @Bill_Type)
    
    
    
     if month(@Bill_Date) = 2
    
    
    
      UPDATE Mat_Inserting_Year
    
    
    
      SET Month2_QTY = Month2_QTY + @Mat_Qty
    
    
    
       , Updated_ID = @Updated_ID, Updated_Date = @Updated_Date
    
    
    
       WHERE (Mat_ID = @Mat_ID AND Storage_ID = @Storage_ID AND Acc_ID = @Client_ID AND Bill_Type = @Bill_Type)
    
    
    
     if month(@Bill_Date) = 3
    
    
    
      UPDATE Mat_Inserting_Year
    
    
    
      SET Month3_QTY = Month3_QTY + @Mat_Qty
    
    
    
       , Updated_ID = @Updated_ID, Updated_Date = @Updated_Date
    
    
    
       WHERE (Mat_ID = @Mat_ID AND Storage_ID = @Storage_ID AND Acc_ID = @Client_ID AND Bill_Type = @Bill_Type)
    
    
    
     if month(@Bill_Date) = 4
    
    
    
      UPDATE Mat_Inserting_Year
    
    
    
      SET Month4_QTY = Month4_QTY + @Mat_Qty
    
    
    
       , Updated_ID = @Updated_ID, Updated_Date = @Updated_Date
    
    
    
     WHERE (Mat_ID = @Mat_ID AND Storage_ID = @Storage_ID AND Acc_ID = @Client_ID AND Bill_Type = @Bill_Type)
    
    
    
     if month(@Bill_Date) = 5
    
    
    
      UPDATE Mat_Inserting_Year
    
    
    
      SET Month5_QTY = Month5_QTY + @Mat_Qty
    
    
    
       , Updated_ID = @Updated_ID, Updated_Date = @Updated_Date
    
    
    
       WHERE (Mat_ID = @Mat_ID AND Storage_ID = @Storage_ID AND Acc_ID = @Client_ID AND Bill_Type = @Bill_Type)
    
    
    
     if month(@Bill_Date) = 6
    
    
    
      UPDATE Mat_Inserting_Year
    
    
    
      SET Month6_QTY = Month6_QTY + @Mat_Qty
    
    
    
       , Updated_ID = @Updated_ID, Updated_Date = @Updated_Date
    
    
    
       WHERE (Mat_ID = @Mat_ID AND Storage_ID = @Storage_ID AND Acc_ID = @Client_ID AND Bill_Type = @Bill_Type)
    
    
    
     if month(@Bill_Date) = 7
    
    
    
      UPDATE Mat_Inserting_Year
    
    
    
      SET Month7_QTY = Month7_QTY + @Mat_Qty
    
    
    
       , Updated_ID = @Updated_ID, Updated_Date = @Updated_Date
    
    
    
       WHERE (Mat_ID = @Mat_ID AND Storage_ID = @Storage_ID AND Acc_ID = @Client_ID AND Bill_Type = @Bill_Type)
    
    
    
     if month(@Bill_Date) = 8
    
    
    
      UPDATE Mat_Inserting_Year
    
    
    
      SET Month8_QTY = Month8_QTY + @Mat_Qty
    
    
    
       , Updated_ID = @Updated_ID, Updated_Date = @Updated_Date
    
    
    
       WHERE (Mat_ID = @Mat_ID AND Storage_ID = @Storage_ID AND Acc_ID = @Client_ID AND Bill_Type = @Bill_Type)
    
    
    
     if month(@Bill_Date) = 9
    
    
    
      UPDATE Mat_Inserting_Year
    
    
    
      SET Month9_QTY = Month9_QTY + @Mat_Qty
    
    
    
       , Updated_ID = @Updated_ID, Updated_Date = @Updated_Date
    
    
    
       WHERE (Mat_ID = @Mat_ID AND Storage_ID = @Storage_ID AND Acc_ID = @Client_ID AND Bill_Type = @Bill_Type)
    
    
    
     if month(@Bill_Date) = 10
    
    
    
      UPDATE Mat_Inserting_Year
    
    
    
      SET Month10_QTY = Month10_QTY + @Mat_Qty
    
    
    
       , Updated_ID = @Updated_ID, Updated_Date = @Updated_Date
    
    
    
       WHERE (Mat_ID = @Mat_ID AND Storage_ID = @Storage_ID AND Acc_ID = @Client_ID AND Bill_Type = @Bill_Type)
    
    
    
     if month(@Bill_Date) = 11
    
    
    
      UPDATE Mat_Inserting_Year
    
    
    
      SET Month11_QTY = Month11_QTY + @Mat_Qty
    
    
    
       , Updated_ID = @Updated_ID, Updated_Date = @Updated_Date
    
    
    
       WHERE (Mat_ID = @Mat_ID AND Storage_ID = @Storage_ID AND Acc_ID = @Client_ID AND Bill_Type = @Bill_Type)
    
    
    
     if month(@Bill_Date) = 12
    
    
    
      UPDATE Mat_Inserting_Year
    
    
    
      SET Month12_QTY = Month12_QTY + @Mat_Qty
    
    
    
       , Updated_ID = @Updated_ID, Updated_Date = @Updated_Date
    
    
    
       WHERE (Mat_ID = @Mat_ID AND Storage_ID = @Storage_ID AND Acc_ID = @Client_ID AND Bill_Type = @Bill_Type)
    
    
    
     end
    
    
    
     end
    
    
    
     fetch next from @Bill_Detail into @Mat_ID, @Client_ID, @Mat_Qty, @Storage_ID, @Bill_Date,
    
    
    
             @Created_ID, @Created_Date
    
    
    
     if @Created_Date is null set @Created_Date = getdate()
    
    
    
     if @Updated_Date is null set @Updated_Date = getdate()
    
    
    
     set @Updated_ID = @Created_ID
    
     set @Updated_Date = @Created_Date
    
    
    
     end
    
    
    
    END
    
    
    this is one of the prosedures that uses that tables
    • Edited by Dr.X1987 Monday, October 4, 2010 2:32 PM
    Monday, October 4, 2010 2:06 PM
  • Can you avoid using cursor?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, October 4, 2010 2:09 PM
  • does the cursor have bad effects of performance?
    Monday, October 4, 2010 2:26 PM
  • Ohhh,yes man,unfortunately....
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, October 4, 2010 2:31 PM
  • ok thanks ... excuse my simple questions ... my knowled in this section is a pretty weak

    what can I use instead of them?

    Monday, October 4, 2010 5:52 PM
  • Using SET Based approach

    http://www.codeproject.com/KB/database/SetAndProceduralSQL.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, October 5, 2010 5:57 AM
  • But in any case consider creating PK attribute in this table. If you get a situation to retrive some data, without PK and indexes that process will be real performance killer.
    Jasmin Azemovic PhD candidate , SQL Server MVP, MCT
    • Marked as answer by Dr.X1987 Thursday, October 7, 2010 2:50 PM
    Tuesday, October 5, 2010 6:59 AM
  • hi,

      As suggested in previous posts, it is better to create a primary key on the table, since the number of records also huge. If there is no key defined, then, its like heap and data retrival will be slower. If PK is there then, there wil be unique clustered index on the table and the data will be ORDERED. So, it is a good practice to have primary key on the table ...


    -- Reddy Balaji C. ##Mark as answer if it solves the issue
    • Marked as answer by Dr.X1987 Thursday, October 7, 2010 2:50 PM
    Wednesday, October 6, 2010 3:28 AM