SQL Tuning where no PK exists

  • I need some assistance in tuning a query in order to get the best performance. The table could feasibly have 100million rows of data and there is no primary key or IDENTITY column. Thing to note, is the source db is not owned by me, I am merely writing an integration of sorts to that source to use in another application. So my queries are datasets that I will ultimately place in an ADO .NET recordset and use in my application. This question has nothing to do with the recordset itself, I am still constructing the query in order to get the best possible performance in Query Analyzer first, before adding anything else to the mix.

    The current index that is being used (Index seek) is company_no, period, year_for_period, account_number. Because of that index alone, the query went from a table scan and returning data in 2:25 to .20 seconds... What else can I do in order to get better performance. The resultset returns 120,000 rows out of a possible 8million.

    Table Structure:

    CREATE TABLE [dbo].[GL](

    [Company_No] [varchar](8) NOT NULL,

    [Account_Number] [varchar](32) NOT NULL,

    [Period] [numeric](2, 0) NOT NULL,

    [Year_For_Period] [numeric](4, 0) NOT NULL,

    [Journal_ID] [varchar](2) NOT NULL,

    [Amount] [numeric](15, 2) NOT NULL,

    [Source] [varchar](20) NOT NULL,

    [Description] [varchar](50) NOT NULL,

    [Date_Created] [datetime] NOT NULL,

    [Date_Last_Modified] [datetime] NOT NULL,

    [Last_Maintained_By] [varchar](30) NOT NULL,

    [Currency_ID] [numeric](20, 0) NULL,

    [Foreign_Amount] [numeric](20, 2) NULL,

    [Trans_No] [varchar](14) NULL,

    [Passcode] [varchar](8) NULL,

    [Transaction_Date] [datetime] NULL,

    [Units] [numeric](15, 2) NULL DEFAULT ((0)),

    [Transaction_Number] [numeric](19, 0) NULL,

    [StatusFlag] [char](1) NOT NULL DEFAULT ('R')

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    ALTER TABLE [AES].[GL] WITH CHECK ADD CONSTRAINT [FK_GL_CHRTACCTS_ACCNO] FOREIGN KEY([Account_Number], [Company_No])

    REFERENCES [AES].[Chart_Of_Accts] ([Account_No], [Company_No])

    GO

    ALTER TABLE [AES].[GL] CHECK CONSTRAINT [FK_GL_CHRTACCTS_ACCNO]

    GO

    ALTER TABLE [AES].[GL] WITH CHECK ADD CONSTRAINT [FK_GL_CMPY_CMPNO] FOREIGN KEY([Company_No])

    REFERENCES [AES].[Company] ([Company_Id])

    GO

    ALTER TABLE [AES].[GL] CHECK CONSTRAINT [FK_GL_CMPY_CMPNO]

    GO

    ALTER TABLE [AES].[GL] WITH CHECK ADD CONSTRAINT [FK_GL_JOUR_JID] FOREIGN KEY([Journal_ID])

    REFERENCES [AES].[Journal] ([Journal_ID])

    GO

    ALTER TABLE [AES].[GL] CHECK CONSTRAINT [FK_GL_JOUR_JID]

    GO

    ALTER TABLE [AES].[GL] WITH CHECK ADD CONSTRAINT [FK_GL_PERIODS_CMPNO] FOREIGN KEY([Company_No], [Period], [Year_For_Period])

    REFERENCES [AES].[Periods] ([Company_No], [Period], [Year_For_Period])

    GO

    ALTER TABLE [AES].[GL] CHECK CONSTRAINT [FK_GL_PERIODS_CMPNO]

    Query:

    IF OBJECT_ID('TEMPDB..#gltransactions','U') IS NOT NULL

    Drop TABLE #AESTransactions

    select IDENTITY(INT,19000,1) as TrxID, a.Company_No as CompanyCode, a.Account_Number as FullAccountCode,

    substring(a.Account_Number,1,4) as ProfitCenterCode, substring(a.Account_Number,5,5) as NaturalAccountCode,

    a.Year_For_Period as FiscalYear, a.Period as FiscalPeriod, a.Transaction_Date as TrxDate, a.Amount as TrxAmount,

    CASE

    WHEN a.Amount <0 THEN a.Amount

    ELSE 0

    END as TrxCreditAmount,

    CASE

    WHEN a.Amount >=0 THEN a.Amount

    ELSE 0

    END as TrxDebitAmount,

    a.Foreign_Amount as ForeignTrxAmount, a.Units as TrxUnitAmount, a.Description as TrxDescription,

    a.Journal_ID as JournalType, a.Source as TrxSource, a.Trans_No as TrxNumber, a.Passcode as TrxPasscode,

    a.StatusFlag

    INTO #glTransactions

    from dbo.GL a

    where a.Company_No='01'

    and a.Year_For_Period=2009

    and a.Period=1

    Select * from #glTransactions

  • Please post index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The SQLPlan is attached and here is the index:

    USE [GLDemo]

    GO

    /****** Object: Index [BizTransIDX] Script Date: 11/12/2009 12:01:00 ******/

    CREATE NONCLUSTERED INDEX [BizTransIDX] ON [dbo].[GL]

    (

    [Company_No] ASC,

    [Year_For_Period] ASC,

    [Period] ASC,

    [Account_Number] ASC

    )

    INCLUDE ( [Journal_ID],

    [Amount],

    [Source],

    [Description],

    [Date_Created],

    [Date_Last_Modified],

    [Last_Maintained_By],

    [Currency_ID],

    [Foreign_Amount],

    [Trans_No],

    [Passcode],

    [Transaction_Date],

    [Units],

    [Transaction_Number],

    [StatusFlag]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  • Sure that's the right execution plan? It shows only a conditional drop object.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • That's an incredibly wide index. What about dropping that and creating a clustered index on

    Company_No], [Year_For_Period], [Period]

    It's a bit wider than I like for a cluster, but it may work here.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I agree that's a wide index. Sorry, for not making this clear in the original request. My original index was on [Company_No],[Period],[Year_For_Period] and incredibly the performance between that index and the current one listed in my last post yielded about the same performance (within 1 second consistently)...

    I did notice you mentioned clustered and I didn't have either as a clustered index. I can try that one while I wait for another response..

    Thanks

  • SQLNewbster (11/12/2009)


    I did notice you mentioned clustered and I didn't have either as a clustered index.

    A clustered index can't have include columns, just specify the key

    I can try that one while I wait for another response..

    Can you check the exec plan that you posted while waiting for a second opinion please? I don't think the current one shows what you think it shows.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail - I wanted to clarify that I wasn't waiting on a second opinion, I was waiting on another round of feedback from your...

    Attached is the execution plan in 3 files because the entire plan would not save as a single file for some reason. I have created the clustered index and ran the query again with a result of 14 seconds query return time. That's 11 seconds better than before. 121000 rows returned of a possible 8 million.

    Demetrius

  • Looks like the clustered index is about as good as you're going to get here. Query needs a few too many columns for a covering nonclustered index to be a good choice and too many rows for lookups to be viable.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for your help Gail!!

  • Gail, since this is 2k, do you think a partitioned view would help at all here? Or would it just muck things up?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (11/13/2009)


    Gail, since this is 2k, do you think a partitioned view would help at all here? Or would it just muck things up?

    Haven't worked much with them, that's why I never recommend them. Couple times I did had unfavourable results, but that may have been because of bad design. Basically comes down to whether the partition elimination can be correctly done.

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Just so you guys don't go too far off track, I am working in a SQL 2k5 environment, not 2k...

    Demetrius

  • SQLNewbster (11/13/2009)


    Just so you guys don't go too far off track, I am working in a SQL 2k5 environment, not 2k...

    Then why did you post in a SQL 2000 forum?

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Umm, sorry. I didn't realize this was the SQL 2k forum. I just joined when I posted this. Many apologies for that.

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply