Query Slow Performance

Question

Post reply

Query Slow Performance

sindbad7000

Valued Member

Points: 72
More actions
November 5, 2009 at 2:13 am

#385715

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

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

How does the query plan look ?

Is it using indexes ?

Clear Sky SQL
My Blog[/url]

Silverfox SSC-Insane Points: 23150 More actions · Answer 2

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

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

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

Jeff Moden SSC Guru Points: 1004425 More actions · Answer 3

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.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

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[/url]
Learn Extended Events

sindbad7000 Valued Member Points: 72 More actions · Answer 5

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.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 6

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[/url]

Paul White SSC Guru Points: 150468 More actions · Answer 7

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
All articles available on SQL.kiwi
@SQL_Kiwi

sindbad7000 Valued Member Points: 72 More actions · Answer 8

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.

sindbad7000 Valued Member Points: 72 More actions · Answer 9

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

Paul White SSC Guru Points: 150468 More actions · Answer 10

A full optimization will take a few minutes, but the main problem seems to be a missing index, something like:

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);

and change the temporary table creation to:

CREATE TABLE #Accounts (AccountNumber nvarchar(20) PRIMARY KEY);

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Chris Howarth-536003 SSCommitted Points: 1775 More actions · Answer 11

Paul'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.

Chris

sindbad7000 Valued Member Points: 72 More actions · Answer 12

Thanks alot it works... now it retrieves the 5200 records in only one second...

thanks alot again i appreciate that alot

but please may i ask about the tools you used??

Chris Howarth-536003 SSCommitted Points: 1775 More actions · Answer 13

sindbad7000 (11/10/2009)
Thanks alot it works... now it retrieves the 5200 records in only one second...
thanks alot again i appreciate that alot
but please may i ask about the tools you used??

Which suggestion did you choose to implement?

Chris

sindbad7000 Valued Member Points: 72 More actions · Answer 14

Paul's Suggestion

CREATE 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 UNIQUE