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 12»»

Query Slow Performance Expand / Collapse
Author
Message
Posted Thursday, November 05, 2009 2:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, November 05, 2009 2:29 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:26 AM
Points: 1,949, Visits: 8,291
How does the query plan look ?
Is it using indexes ?




Clear Sky SQL
My Blog
Kent user group
Post #814078
Posted Thursday, November 05, 2009 2:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 14, 2014 2:43 AM
Points: 2,723, Visits: 1,090
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
Posted Thursday, November 05, 2009 10:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:19 AM
Points: 35,955, Visits: 30,245
sindbad7000 (11/5/2009)
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??


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

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #814694
Posted Thursday, November 05, 2009 10:22 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 20,458, Visits: 14,085
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


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
Posted Monday, November 09, 2009 10:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, November 09, 2009 11:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:26 AM
Points: 1,949, Visits: 8,291
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
Posted Tuesday, November 10, 2009 2:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:59 AM
Points: 11,168, Visits: 10,931
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

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
Posted Tuesday, November 10, 2009 2:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 (43 views, 55.04 KB)
TableCreation.txt (33 views, 22.29 KB)
Post #816356
Posted Tuesday, November 10, 2009 2:37 AM
Forum Newbie

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

Add to briefcase 12»»

Permissions Expand / Collapse