Query Slow Performance

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 » Query Slow Performance

18 posts, Page 1 of 2

1 2 »»

Query Slow Performance

Rate Topic

Display Mode

Topic Options

Author

Message

sindbad7000

Posted Thursday, November 05, 2009 2:13 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 04, 2010 8:15 AM
Points: 6, Visits: 18

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??

Post #814074

Dave Ballantyne

Posted Thursday, November 05, 2009 2:29 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227

How does the query plan look ?
Is it using indexes ?

Clear Sky SQL
My Blog
Kent user group

Post #814078

Silverfox

Posted Thursday, November 05, 2009 2:30 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 6:50 AM
Points: 2,719, Visits: 1,065

post the query and the query plan, and we can take a look

--------------------------------------------------------------------------------------
Recommended Articles on How to help us help you and
solve commonly asked questions

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden
Managing Transaction Logs by Gail Shaw
How to post Performance problems by Gail Shaw
Help, my database is corrupt. Now what? by Gail Shaw

Post #814079

Jeff Moden

Posted Thursday, November 05, 2009 10:05 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:21 PM
Points: 32,893, Visits: 26,765

sindbad7000 (11/5/2009)

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.

--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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #814694

SQLRNNR

Posted Thursday, November 05, 2009 10:22 PM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:46 PM
Points: 18,732, Visits: 12,329

That kind of query should return much quicker. As the others said though, post more info and we can help more effiiciently.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #814700

sindbad7000

Posted Monday, November 09, 2009 10:09 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 04, 2010 8:15 AM
Points: 6, Visits: 18

Here Is My SQL Query :

ALTER PROCEDURE [dbo].[usp_rpt_AccountStatement]
@dateFrom smalldatetime = null,
@dateTo smalldatetime = null,
@AccountNum nvarchar(20) = null,
@ComponentNumber nvarchar(20) = NULL
AS
BEGIN
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]
END

i ran it now it Retrieved 5200 rows in 1.48 minute

The 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.

Post Attachments

SlowPerformance.sqlplan (29 views, 6.25 KB)

Post #815934

Dave Ballantyne

Posted Monday, November 09, 2009 11:36 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, May 10, 2013 4:07 PM
Points: 1,943, Visits: 8,227

Have a look at this link http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/.

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.

Clear Sky SQL
My Blog
Kent user group

Post #815996

Paul White

Posted Tuesday, November 10, 2009 2:15 AM

SSChampion

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:33 AM
Points: 10,989, Visits: 10,529

Yes, 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 Tongue

Please 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 OFF?

I think you would benefit from reading Erland Sommarskog's work on the subject: http://www.sommarskog.se/dyn-search-2005.html.

Paul

Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi

Post #816353

sindbad7000

Posted Tuesday, November 10, 2009 2:22 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 04, 2010 8:15 AM
Points: 6, Visits: 18

attached 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.

Post Attachments

Plan3.sqlplan (41 views, 55.04 KB)

TableCreation.txt (31 views, 22.29 KB)

Post #816356

sindbad7000

Posted Tuesday, November 10, 2009 2:37 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 04, 2010 8:15 AM
Points: 6, Visits: 18

When 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 Clause

Post #816363

« Prev Topic | Next Topic »

18 posts, Page 1 of 2

1 2 »»

Permissions