Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SQL Tuning where no PK exists Expand / Collapse
Author
Message
Posted Thursday, November 12, 2009 10:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, November 12, 2009 11:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:18 PM
Points: 42,437, Visits: 35,492
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
Posted Thursday, November 12, 2009 12:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, November 12, 2009 12:30 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:18 PM
Points: 42,437, Visits: 35,492
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
Posted Thursday, November 12, 2009 12:34 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:18 PM
Points: 42,437, Visits: 35,492
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
Posted Thursday, November 12, 2009 12:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, November 12, 2009 1:00 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:18 PM
Points: 42,437, Visits: 35,492
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
Posted Thursday, November 12, 2009 5:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, November 13, 2009 2:46 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:18 PM
Points: 42,437, Visits: 35,492
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
Posted Friday, November 13, 2009 3:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase 123»»»

Permissions Expand / Collapse