InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 04:52:06 GMT20http://www.sqlservercentral.com/Forums/Topic814074-360-1.aspx[quote][b]sindbad7000 (11/10/2009)[/b][hr]Thanks alot it works... now it retrieves the 5200 records in only one second...thanks alot again i appreciate that alotbut please may i ask about the tools you used??[/quote]I looked at the actual query plan you kindly uploaded. The stand-out features were the clustered index scan of the TransactionDetail table and the table scan of the temporary table. Full scans on the inner side of a loop join are rarely good news - especially when the outer input has many rows, and so does the table being scanned. If you hadn't been able to create the new indexes, I would have suggested forcing a HASH or MERGE join for those parts of the plan. Not as optimal as the index by any means, but at least TransactionDetail would only have been scanned once.I wrote the index definition by inspection: clearly the lookup was on the ID (which I mistakenly assumed was UNIQUE). Adding the included columns wasn't required, by they were listed as output columns in the plan so I thought I'd add them for neatness, and to avoid a key lookup.I could have saved myself 2 minutes' work by looking at the 'missing index' information in the plan - but the way forward was pretty clear anyway.As [i]TheSQLGuru[/i] mentioned, it's most just a question of experience :-)PaulTue, 10 Nov 2009 14:27:14 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic814074-360-1.aspxIn terms of the tools that can be used to identify this missing index yourself, have a look at the execution plan that you posted to the forum.At the top you'll see a section of text starting with 'Missing Index' - if you right-click on the text and select 'Missing Index Details...' the index creation script will open in a new window.The suggestions that are offered are not always optimal, but they are often a good starting point.Other than reviewing and selectively implementing the suggestions that SQL Server offers, it's usually a case of learning to interpret execution plans and then how to optimise queries and your database design to help ensure that SQL Server chooses an optimal plan. This ebook is a good starting point if you're interested in learning more:http://www.red-gate.com/specials/Grant.htm?utm_source=simpletalk?utm_medium=email?utm_content=Grant080527?utm_campaign=sqltoolbeltChrisTue, 10 Nov 2009 08:11:06 GMTChris Howarth-536003http://www.sqlservercentral.com/Forums/Topic814074-360-1.aspx[quote]but please may i ask about the tools you used?? [/quote] In this particular case I think Chris mentioned that the query plan xml actually contains missing index information. But in general performance tuning is a HUGE topic that takes lots of study and lots of experience to become good at. Find some books, blogs, articles, classes and start learning. Hire a tuning consultant to mentor you while reviewing your system for you. Tue, 10 Nov 2009 08:02:21 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic814074-360-1.aspxPaul's SuggestionCREATE NONCLUSTERED INDEX [UQ dbo.TransactionDetail TransactionId (AccountId, Direction, Amount)]ON [dbo].[TransactionDetail] (TransactionId ASC)INCLUDE (AccountId, Direction, Amount)WITH (SORT_IN_TEMPDB = ON, MAXDOP = 1, ONLINE = OFF);but i removed the UNIQUE from the index because this field 'TransactionId' is not UNIQUETue, 10 Nov 2009 07:34:03 GMTsindbad7000http://www.sqlservercentral.com/Forums/Topic814074-360-1.aspx[quote][b]sindbad7000 (11/10/2009)[/b][hr]Thanks alot it works... now it retrieves the 5200 records in only one second...thanks alot again i appreciate that alotbut please may i ask about the tools you used??[/quote]Which suggestion did you choose to implement?ChrisTue, 10 Nov 2009 07:28:21 GMTChris Howarth-536003http://www.sqlservercentral.com/Forums/Topic814074-360-1.aspxThanks alot it works... now it retrieves the 5200 records in only one second...thanks alot again i appreciate that alotbut please may i ask about the tools you used??Tue, 10 Nov 2009 06:47:36 GMTsindbad7000http://www.sqlservercentral.com/Forums/Topic814074-360-1.aspxPaul's suggestion is good, and is consistent with the missing index that was suggested by SQL Server in the execution plan that you provided.If you almost always return rows from dbo.TransactionDetail by looking up the TransactionID column then you may wish to consider changing PK_TransactionDetail to be NONCLUSTERED, then creating a CLUSTERED index on the TransactionID column. This change would help you minimise additional disk space requirements and would also likely improve performance of other queries (those based on looking-up based on the TransactionID column). This would be done instead of creating the 'missing index' suggested previously.ChrisTue, 10 Nov 2009 05:58:18 GMTChris Howarth-536003http://www.sqlservercentral.com/Forums/Topic814074-360-1.aspxA full optimization will take a few minutes, but the main problem seems to be a missing index, something like:[code]CREATE UNIQUE NONCLUSTERED INDEX [UQ dbo.TransactionDetail TransactionId (AccountId, Direction, Amount)]ON [dbo].[TransactionDetail] (TransactionId ASC)INCLUDE (AccountId, Direction, Amount)WITH (SORT_IN_TEMPDB = ON, MAXDOP = 1, ONLINE = OFF);[/code]and change the temporary table creation to:[code]CREATE TABLE #Accounts (AccountNumber nvarchar(20) PRIMARY KEY);[/code]Tue, 10 Nov 2009 02:45:11 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic814074-360-1.aspxWhen i removed the Order By clause the time decreased from "1 minute and 48 seconds" to "2 seconds only".....but i really need this Order By ClauseTue, 10 Nov 2009 02:37:08 GMTsindbad7000http://www.sqlservercentral.com/Forums/Topic814074-360-1.aspxattached the updated plan and a txt file that Contains : the 4 tables creation, the stored proc. and the function i used in the stored proc.Tue, 10 Nov 2009 02:22:16 GMTsindbad7000http://www.sqlservercentral.com/Forums/Topic814074-360-1.aspxYes, the posted execution plan just shows the plan for:DECLARE @ISLEAF BIT SELECT @IsLeaf = IsLeafAccount FROM Account WHERE AccountNum = @AccountNum;...which isn't really the performance-critical bit :-PPlease do take the time to post the plan for the final SELECT - the cause of the slowness is almost certain to be obvious from it, thanks.By the way...SET NOCOUNT [b]OFF[/b]?I think you would benefit from reading Erland Sommarskog's work on the subject: [url]http://www.sommarskog.se/dyn-search-2005.html[/url].PaulTue, 10 Nov 2009 02:15:42 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic814074-360-1.aspxHave a look at this link [url]http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url].Also please post the DDL (indexes as well), when you say 700 accounts records is that in the #accounts table ?Your query plan is not complete either.Mon, 09 Nov 2009 11:36:08 GMTDave Ballantynehttp://www.sqlservercentral.com/Forums/Topic814074-360-1.aspxHere Is My SQL Query : ALTER PROCEDURE [dbo].[usp_rpt_AccountStatement] @dateFrom smalldatetime = null, @dateTo smalldatetime = null, @AccountNum nvarchar(20) = null, @ComponentNumber nvarchar(20) = NULLASBEGIN SET NOCOUNT OFF; CREATE TABLE #Accounts (AccountNumber nvarchar(20)) DECLARE @ISLEAF BIT SELECT @IsLeaf = IsLeafAccount FROM Account WHERE AccountNum = @AccountNum IF @IsLeaf = 0 BEGIN INSERT INTO #Accounts(AccountNumber) SELECT AccountNum FROM dbo.fnGetAccountChildren(@AccountNum) END ELSE BEGIN INSERT INTO #Accounts(AccountNumber) VALUES (@AccountNum) END SELECT TransactionDetail.Direction * TransactionDetail.Amount AS Debit, ABS((TransactionDetail.Direction - 1) * TransactionDetail.Amount) AS Credit, [Transaction].TransactionDate, [Transaction].Description_En, [Transaction].Description_Ar, Component.ComponentId,Component.ComponentName_En,Component.ComponentName_Ar FROM [Transaction] INNER JOIN TransactionDetail ON [Transaction].TransactionId = TransactionDetail.TransactionId INNER JOIN Account ON TransactionDetail.AccountId = Account.AccountId INNER JOIN [Component] ON ([Transaction].[ComponentId] = [Component].[ComponentNumber]) WHERE ((@datefrom IS NULL) OR (@dateTo IS NOT NULL) OR ([Transaction].TransactionDate >= @datefrom) ) AND ((@dateto IS NULL) OR(@dateFrom IS NOT NULL) OR ([Transaction].TransactionDate <= @dateto) ) AND ((@dateFrom IS NULL) OR (@dateTo IS NULL) OR ([Transaction].TransactionDate BETWEEN @datefrom AND @dateto)) AND Account.AccountNum IN (SELECT AccountNumber COLLATE database_default FROM #Accounts) AND ((@ComponentNumber IS NULL) OR ([Transaction].[ComponentId] = @componentNumber)) ORDER BY [Transaction].[TransactionDate]ENDi ran it now it Retrieved 5200 rows in 1.48 minuteThe Transaction Table has 17720 records, The TransactionDetail table has 36346 records, The Account table has 718 records and finally the Component table has 110 records.Mon, 09 Nov 2009 10:09:17 GMTsindbad7000http://www.sqlservercentral.com/Forums/Topic814074-360-1.aspxThat kind of query should return much quicker. As the others said though, post more info and we can help more effiiciently.Thu, 05 Nov 2009 22:22:54 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic814074-360-1.aspx[quote][b]sindbad7000 (11/5/2009)[/b][hr]please i am using sql server 2005, i wrote a simple query that joins only 4 tables. 2 of them have thousands of records. the query returns 4500 records in 1 minute and 30 seconds...is that acceptable??[/quote]Nope... not acceptable. But can't help because there's not enough info. Please see the second link in my signature line below to get better help.Thu, 05 Nov 2009 22:05:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic814074-360-1.aspxpost the query and the query plan, and we can take a lookThu, 05 Nov 2009 02:30:02 GMTSilverfoxhttp://www.sqlservercentral.com/Forums/Topic814074-360-1.aspxHow does the query plan look ?Is it using indexes ?Thu, 05 Nov 2009 02:29:17 GMTDave Ballantynehttp://www.sqlservercentral.com/Forums/Topic814074-360-1.aspxplease i am using sql server 2005, i wrote a simple query that joins only 4 tables. 2 of them have thousands of records. the query returns 4500 records in 1 minute and 30 seconds...is that acceptable??Thu, 05 Nov 2009 02:13:42 GMTsindbad7000