T-SQL help needed :) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

T-SQL help needed :)

Author

Topic

akira220984
Starting Member

13 Posts

Posted - 2010-01-21 : 06:56:18

Hi,

I'm new to this forum and to sql server / tsql in general, so go easy on me :D

Below I have some code which I have been asked to optimise as it runs far too slow. If there any pointers as to how I could go about this, it would be greatly appreciated.

If you need any other information, please ask and I will reply asap.

update ledgerTestRS
set RunningBalance =
(select sum(total)
from ledgerTestRS as innerLoop
where innerLoop.company_name = outerLoop.company_name
and innerLoop.ledger_name = outerLoop.ledger_name
and innerLoop.longtransactiondate <= outerLoop.longtransactiondate)
from ledgerTestRS outerLoop
inner join #tmpchanges on (outerLoop.ledger_id = #tmpchanges.ledger_id)

Ric

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 07:28:08

I recommend that you do two things:

1) Check the number of logical (not physical - they change, "logical" won't) I/Os as a baseline. Then you know if changed you make have improved things.

2) Look at the Query Plan and see what is giving you TABLE SCAN, or INDEX SCAN instead of INDEX SEEK

then you can use that to decide if additional indexes will help, or if indexes that you expected to be used aren't!!, and if adding columns to an index (so that it "covers" the query) improve things

Comment in the SHOWPLAN to see the Query Plan, OR the STATISTICS to see the logical stats


-- SET STATISTICS PROFILE ON; SET SHOWPLAN_TEXT ON	-- If Temp Tables involved
-- SET SHOWPLAN_TEXT ON
GO
-- SET STATISTICS IO ON; SET STATISTICS TIME ON

... put query here ...

SET STATISTICS IO OFF; SET STATISTICS TIME OFF
GO
SET SHOWPLAN_TEXT OFF
GO

You can put BEGIN TRANSACTION / ROLLBACK around your query so that it doesn't actually run. The STATISTICS will actually run the query, the PLAN route will NOT run (it just shows the PLAN)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-21 : 07:41:25

Specifically for your problem you are doing a running balance calculation and the way you have implemented it is with a triangle join. Basically you are looping over and over again and as your data size increased your computation time is going to increase at an exponential rate. That's why you are seeing the performance problems. This is an algorithm problem and while you may get some improvements with better indices etc, to see a big improvement (and we are talking probably 100-1000s of times faster) you want to change the algorithm.

There are a few different methods the quickest of which is to use a quirky update. This exploits the way that the UPDATE statement works but you need to have your data organised in specific ways (basically you need to have a CLUSTERED INDEX specifically built for the job).

Do a search on this site for RUNNING BALANCE. There have been an awful lot of posts about this (because this is a job that sql is specifically bad at). The methods are rather hard to understand though and I'm not the best person for the job because in my line of work we never need to do this.

Probably the best (quickest) way to do this is to make up a temp table with a customised CLUSTERED INDEX and then write a quirky update to work out the values. Then copy the values back from the temp table into the permanent one.

Peso posted a competition recently with this exact problem and the winning answer brought the execution time down from 30minutes to just a few seconds. There are a few quirky update experts on this site so I'm sure one of the them will step in to give you a hand.

Oh, and another thing. Welcome to sql team!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-21 : 08:30:51

Madhivanan is particularly good as these problems. (He also gives great advice and generally answer's questions in the afternoon (UK time))

Have a look at : http://sqlblogcasts.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

akira220984
Starting Member

13 Posts

Posted - 2010-01-22 : 03:59:17

Kristen, Charlie,

thank you both for your replies, I'm starting to move things on to the right path :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources