Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Slow Performance


Query Slow Performance

Author
Message
sindbad7000
sindbad7000
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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??
Dave Ballantyne
Dave Ballantyne
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1818 Visits: 8370
How does the query plan look ?
Is it using indexes ?



Clear Sky SQL
My Blog
Silverfox
Silverfox
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2740 Visits: 1161
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45187 Visits: 39925
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21099 Visits: 18259
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

sindbad7000
sindbad7000
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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.
Attachments
SlowPerformance.sqlplan (37 views, 6.00 KB)
Dave Ballantyne
Dave Ballantyne
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1818 Visits: 8370
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
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10340 Visits: 11350
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 :-P

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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
sindbad7000
sindbad7000
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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.
Attachments
Plan3.sqlplan (53 views, 55.00 KB)
TableCreation.txt (38 views, 22.00 KB)
sindbad7000
sindbad7000
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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
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