SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Tuning where no PK exists


SQL Tuning where no PK exists

Author
Message
SQLNewbster
SQLNewbster
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)

Group: General Forum Members
Points: 370670 Visits: 46956
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


SQLNewbster
SQLNewbster
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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]
Attachments
PerfAssistanceTransactions.sqlplan (13 views, 848 bytes)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)

Group: General Forum Members
Points: 370670 Visits: 46956
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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)

Group: General Forum Members
Points: 370670 Visits: 46956
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


SQLNewbster
SQLNewbster
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)

Group: General Forum Members
Points: 370670 Visits: 46956
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


SQLNewbster
SQLNewbster
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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
Attachments
GilaMonster
GilaMonster
SSC Guru
SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)SSC Guru (370K reputation)

Group: General Forum Members
Points: 370670 Visits: 46956
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


SQLNewbster
SQLNewbster
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 16
Thanks for your help Gail!!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search