February 21, 2013 at 8:29 am
How can I create (in T-SQL) an Index that will make loading 16,000 records faster into a web data grid? The table has a primary key and about 40 fields but only 10 show on the data grid. Improving this performance may not even be possible. I have one index already:
USE [SoftIT]
GO
ALTER TABLE [dbo].[tblTransactions] ADD CONSTRAINT [PK_dbo.tblTransactions] PRIMARY KEY CLUSTERED
(
[Transaction_Number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
February 21, 2013 at 8:44 am
Could you please Provide column Data Types?
You could could build a nonclustered index as...
CREATE NONCLUSTERED INDEX <INDEX NAME> ON <Table Name> (<10 col names>)
Keep in mind, if you have column data types as TEXT etc( look up BOL), then use INCLUDE ( look up BOL).
Hope this helps.
February 21, 2013 at 8:50 am
CREATE TABLE [dbo].[tblTransactions](
[PO_Number] [varchar](50) NULL,
[Buyer_Initial] [varchar](50) NULL,
[Quantity] [int] NULL,
[Unit_Price] [money] NULL,
[Software_Description] [varchar](100) NULL,
[AllocationAccount] [varchar](50) NULL,
[PurchaseAccount] [varchar](50) NULL,
[HeatTicketNumber] [varchar](50) NULL,
[PurchaseCostCenter] [varchar](25) NULL,
[PO_Date] [date] NULL,
[Transaction_Date] [date] NULL,
[Transaction_Number] [int] IDENTITY(1,1) NOT NULL,
[AllocationDate] [date] NULL,
[AllocatedYN] [varchar](10) NULL,
[EndUserFirstName] [varchar](100) NULL,
[EndUserMiddleName] [varchar](100) NULL,
[EndUserLastName] [varchar](100) NULL,
[LAN_ID] [varchar](50) NULL,
[EndUserLocation] [varchar](100) NULL,
[TermDate] [date] NULL,
[EmployeeStatus] [varchar](50) NULL,
[Notes] [varchar](255) NULL,
[LicenseAvailable] [varchar](3) NULL,
[Transaction_Type] [varchar](50) NULL,
[AllocationCostCenter] [varchar](50) NULL,
[SoftwareShortName] [varchar](10) NULL,
[PC_Name] [varchar](100) NULL,
[TransferedSoftware] [varchar](10) NULL,
[TransferFromFName] [varchar](50) NULL,
[TransferFromLName] [varchar](50) NULL,
[TransferFromLANID] [varchar](25) NULL,
[OriginalTransactionNumber] [varchar](6) NULL,
[TransferFromCostCenter] [varchar](10) NULL,
[CostCenter] [varchar](10) NULL,
[SWstatus] [varchar](25) NULL,
[SWstatusReason] [varchar](25) NULL,
[EmployeeEmail] [varchar](50) NULL,
[DupeCheck] [varchar](50) NULL,
[TransferToCostCenter] [varchar](10) NULL,
[TransferToLName] [varchar](100) NULL,
[TransferToFName] [varchar](100) NULL,
[EquipmentType] [varchar](25) NULL,
[OriginalTransactionType] [varchar](100) NULL,
[DupeReason] [varchar](100) NULL,
[LastRewDt] [date] NULL,
[NextRewDt] [date] NULL,
[LastRewPO] [varchar](50) NULL,
[Termination] [varchar](4) NULL,
CONSTRAINT [PK_dbo.tblTransactions] PRIMARY KEY CLUSTERED
(
[Transaction_Number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
February 21, 2013 at 9:08 am
briancampbellmcad (2/21/2013)
How can I create (in T-SQL) an Index that will make loading 16,000 records faster into a web data grid? The table has a primary key and about 40 fields but only 10 show on the data grid.
Without seeing the query that populates that grid, that's impossible to answer. Indexes are driven by the queries that run against the table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2013 at 10:05 am
Are Transaction_Numbers -- i.e. clustered key values -- added in ascending sequence?
If so, make sure SQL is sorting the rows before doing the INSERTs.
If not, and you're sure you have the right clustered key chosen, you will need to decrease the FILLFACTOR on the clustered index. Determining the best specific FILLFACTOR to use would require further knowledge and testing.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 21, 2013 at 10:31 am
ScottPletcher (2/21/2013)
Are Transaction_Numbers -- i.e. clustered key values -- added in ascending sequence?If so, make sure SQL is sorting the rows before doing the INSERTs.
Why?
The exec plans are the same whether you do an INSERT INTO ... SELECT FROM, or a INSERT INTO ... SELECT FROM ... ORDER BY DestinationClusteredIndexColumn
The optimiser will put a sort into the plan if it decides one is necessary and will not if it decides otherwise, regardless of the presence or absence of an order by (which just defines the identity order if there is one)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply