SQL Tuning where no PK exists

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Performance Tuning » SQL Tuning where no PK exists

23 posts, Page 1 of 3

1 2 3 »»»

SQL Tuning where no PK exists

Rate Topic

Display Mode

Topic Options

Author

Message

SQLNewbster

Posted Thursday, November 12, 2009 10:30 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, November 15, 2009 3:42 PM
Points: 9, Visits: 16

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

Post #817972

GilaMonster

Posted Thursday, November 12, 2009 11:12 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 37,712, Visits: 29,966

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

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #818005

SQLNewbster

Posted Thursday, November 12, 2009 12:04 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, November 15, 2009 3:42 PM
Points: 9, Visits: 16

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]

Post Attachments

PerfAssistanceTransactions.sqlplan (5 views, 848 bytes)

Post #818033

GilaMonster

Posted Thursday, November 12, 2009 12:30 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 37,712, Visits: 29,966

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

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #818049

GilaMonster

Posted Thursday, November 12, 2009 12:34 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 37,712, Visits: 29,966

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 2008, MVP
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

Post #818053

SQLNewbster

Posted Thursday, November 12, 2009 12:54 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, November 15, 2009 3:42 PM
Points: 9, Visits: 16

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

Post #818069

GilaMonster

Posted Thursday, November 12, 2009 1:00 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 37,712, Visits: 29,966

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 2008, MVP
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

Post #818073

SQLNewbster

Posted Thursday, November 12, 2009 5:34 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, November 15, 2009 3:42 PM
Points: 9, Visits: 16

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

Post Attachments

PerfAssistanceTransactions.sqlplan (2 views, 7.28 KB)

PerfAssistanceTransactions2.sqlplan (2 views, 27.13 KB)

PerfAssistanceTransactions3.sqlplan (1 view, 7.28 KB)

Post #818188

GilaMonster

Posted Friday, November 13, 2009 2:46 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 37,712, Visits: 29,966

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 2008, MVP
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

Post #818316

SQLNewbster

Posted Friday, November 13, 2009 3:56 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, November 15, 2009 3:42 PM
Points: 9, Visits: 16

Thanks for your help Gail!!

Post #818363

« Prev Topic | Next Topic »

23 posts, Page 1 of 3

1 2 3 »»»

Permissions