SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Slow Performance


Query Slow Performance

Author
Message
sindbad7000
sindbad7000
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

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



Clear Sky SQL
My Blog
Silverfox
Silverfox
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: 10236 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 Guru
SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)SSC Guru (377K reputation)

Group: General Forum Members
Points: 377857 Visits: 42926
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)

Group: General Forum Members
Points: 110476 Visits: 18623
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
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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 (50 views, 6.00 KB)
Dave Ballantyne
Dave Ballantyne
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12626 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
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60498 Visits: 11396
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
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 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 (66 views, 55.00 KB)
TableCreation.txt (130 views, 22.00 KB)
sindbad7000
sindbad7000
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

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