SQL Server Performance

Optimizing the query execution time

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by prakashuj, Jul 17, 2008.

  1. prakashuj New Member

    Dear All,
    I have table which contain more than 5 lakhs of records.
    When i exectue select * from table it is taking 45 to 60 sec to excetue.

    the data Structure is like this
    CREATE TABLE [Table1] (
    [$AuditTrailId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF__$AgIndCAI__$Audi__19E63423] DEFAULT (newid()),
    [$UserId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [$Operation] [bit] NOT NULL ,
    [$SessionId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [$IPId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [$DateTime] [datetime] NOT NULL CONSTRAINT [DF__$AgIndCAI__$Date__1ADA585C] DEFAULT (getdate()),
    [$DataProfileClassId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [FormId] [bigint] NOT NULL ,
    [FormCode] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [MAWBFormId] [bigint] NULL ,
    [SAWBFormId] [bigint] NULL ,
    [JobNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MasterConsignmentId] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ConsignmentId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [HAWBNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BusinessBranch] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [DestinationBranch] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [AgilityClearance] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CreditClient] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BusinessType] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LetterOfCredit] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ShipperName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ConsigneeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ConsigneeEmail] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [NoOfPieces] [int] NULL ,
    [GrossWeight] [decimal](27, 2) NULL ,
    [ChargeableWeight] [decimal](27, 2) NULL ,
    [Remarks] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Department] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ServiceType] [bigint] NULL ,
    [OriginCountry] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OriginPlace] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DeparturePort] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DestinationCountry] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [DestinationPort] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DeliveryPlace] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Transhipment] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [HAWBTerms] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MAWBTerms] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DocReceivedDate] [datetime] NULL ,
    [ShipperInvoiceNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ConsigneePONo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FileName] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DateCreated] [datetime] NOT NULL ,
    [CreatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [DateModified] [datetime] NULL ,
    [ModifiedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [StateId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OwnerOrgId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [OwnerLocId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SenderId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [RecipientId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [RequestFor] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [IsOpened] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SubmissionFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SubmissionComments] [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [IP] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Self] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CompanyCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EmpNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ClearanceAgentId] [bigint] NULL ,
    [ClearanceAgentName] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ClearanceAgentPerson] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ClearanceAgentContactNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BRORequired] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BROUploaded] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PODSignedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PODDateTime] [datetime] NULL ,
    [PODPlace] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PODRemarks] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CCMailId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CHAMailId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ReceivedFrom] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PaymentMode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PaymentDate] [datetime] NULL ,
    [Account] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Amount] [decimal](18, 0) NULL ,
    [BankName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ddchequeno] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [receiptno] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Cost] [numeric](9, 0) NULL ,
    [CostValue] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [hawbValue] [float] NULL ,
    [FormType] [int] NULL ,
    [TotalReceived] [int] NULL ,
    [PiecesRemaining] [int] NULL ,
    [PackingType] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LedgerConsigneeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LedgerConsigneeAddress] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [GoodsDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LedgerAccount] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BROSupportDoc] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ClientName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CompleteAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TelephoneFaxNos] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CreditResponsibilityBranch] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Salespersoncode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Contactperson] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [EMailId] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DispatchDeliver] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Pickupdate] [datetime] NULL ,
    [ICAReceived] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ReceivedGrossWeight] [decimal](18, 0) NULL ,
    [ReceivedChargeableWeight] [decimal](18, 0) NULL ,
    [CANConsigneeName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CANAddress] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CANdespatchDate] [datetime] NULL ,
    [CourrierAWBNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ConsigneeCHAName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ACANFormId] [bigint] NULL ,
    [CLFormId] [bigint] NULL ,
    [Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CommodityItemNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [RateCharge] [decimal](18, 0) NULL ,
    [ParentFormId] [bigint] NULL ,
    [CarrierRefNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [DeliveryMode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SLMailId] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Priority] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [NeedChangesReason] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ccpp] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [HoldReason] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [HoldRemarks] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [IsHold] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [HoldStatus] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK__$AgIndCAIHouseAi__18F20FEA] PRIMARY KEY CLUSTERED
    (
    [$AuditTrailId]
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO


    Easy there any possibilty to fecth the records in less time
    select * from Table1 where formid=12

  2. martins New Member

    Based on your query stated last (select * from Table1 where formid = 12), you can build an index on the FormID field and that should help.
  3. ndinakar Member

    Thats a pretty big table (in terms of number of columns you have). Do you really need ALL the columns? Retrieve only those you need and your query will be faster (In addition to the index as suggested by martins)
  4. venkatesanj@hcl.in New Member

    Oops what a big table.... :) [:D] Ok, regarding your table.
    1. You are using formid which is not indexed.
    2. Get the exact column you want.. specify like this.. Select collist from tablename where condition
    Regards,
    Venkatesan Prabu .J
  5. rachel New Member

    As mentioned here, your table is huge! If you have the opportunity to redesign things, try to split some of the columns out into side tables, linked by the ID value - nullable columns are good choices for this. Then you can just select what you need, joining to these side tables only if needed.

  6. Madhivanan Moderator

    Do you need all rows?
    Try this
    select * from Table1 where AuditTrailId>'' and formid=12

Share This Page