How to fetch recordsby passing a column name (table is containing more than 5 lakhs records)

• Prakash-485822

  SSC Eights!

  Points: 982

  More actions

  July 17, 2008 at 5:39 am

  #70466

  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 ,

  127.0.0.1 [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

  Please help me to solve this issue

  Thanks and regards

  Prakash

• tosscrosby

  SSCoach

  Points: 18045

  More actions

  July 17, 2008 at 8:15 am

  #844427

  Can you create an index on formid? That should help. Also, can you narrow down the information being returned? "Select *" will have a negative performance impact as well and with the number of columns on your table, it's not helping your cause, especially without any good indexes (not sure you'd really want to attempt a covering index on this table!). 500,000 rows of data is really not a substantial amount of data anymore but multiply it by your row size and it's quite a large return size. You can also look at actual server constraints (memory, disk, etc.) but that's probably out of the scope of this topic.

  edit - How did you come up with the title of this topic when you never ask about "passing a column name"? Just a bit misleading.

  -- You can't be late until you show up.