what's faster?

Question

what's faster?

Sanjay-940444

SSC-Addicted

Points: 422
More actions
July 19, 2010 at 12:48 am

#387382

Its a general question regarding writing fatest performing tsql codes.
Whats better ?
To perform whole task using many small and simple queries
or
to perform task using big, compact and probably complex queries.
Please share your experience and views.
Thanks
Sanjay

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 1

This all depends on each individual situation. Many small updates will be slower than one mass update statement, but for a complex query, most of the performance impact is probably finding out what value should be assigned, rather than the actual update.

On the other hand, small queries may be easier to understand and maintain. They might take an extra 10% extra time to run, but that might be OK.

So, I'm not going to say one way or the other as to which is better. Performance can go either way, but, unless it's a speed-critical query, maintainability is important.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Sanjay-940444 (7/19/2010)
Whats better ?
To perform whole task using many small and simple queries
or
to perform task using big, compact and probably complex queries.

It depends.

Seriously, there's no one single answer to that question. Sometimes doing everything in one query is faster, sometimes splitting things up is faster. Depends on too many things to give an always-right single answer.

If you're in a scenario where you're deciding between those, try both, see which is faster and by how much, then decide which way to go.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sanjay-940444 SSC-Addicted Points: 422 More actions · Answer 3

I understand that upto a large extent it depends on scenerio on hand but sometimes task is so big and complicated that it becomes difficult to decide which approach to adopt specially when database is badly designed and unnormalised. (It is the case with me currently.)

If we start to write small queries it gives understandiblity and seems efficient untill someone experienced comes and tells u that it cud be done in a single query which takes little time!!

Otherwise writing big queries becomes unmanageable later on and leads to 'patch work'.

Anyway thanks for your views.

Looking for more insights into this matter.

Thanks

Sanjay

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Sanjay-940444 (7/19/2010)
If we start to write small queries it gives understandiblity and seems efficient untill someone experienced comes and tells u that it cud be done in a single query which takes little time!!
Otherwise writing big queries becomes unmanageable later on and leads to 'patch work'.

Then do it in small queries and, if performance is unacceptably bad, evaluate rewriting as larger queries. If performance is acceptable, there's no reason to worry about rewriting.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sanjay-940444 SSC-Addicted Points: 422 More actions · Answer 5

Thanks Gail

strating with simple and smaller queries seems to be right approach.

Paul White SSC Guru Points: 150467 More actions · Answer 6

Sanjay-940444 (7/19/2010)
I understand that upto a large extent it depends on scenerio on hand but sometimes task is so big and complicated that it becomes difficult to decide which approach to adopt specially when database is badly designed and unnormalised. (It is the case with me currently.)
If we start to write small queries it gives understandiblity and seems efficient untill someone experienced comes and tells u that it cud be done in a single query which takes little time!!
Otherwise writing big queries becomes unmanageable later on and leads to 'patch work'.

Sanjay,

My personal preference would be to go with the smaller, more understandable, queries unless or until performance becomes unacceptable. The performance advantage of any complex monolithic query replacement would have to be significant to offset the disadvantages of that approach.

Aside from the maintainability issues with a large and complex query (which should not be understated!) large, complex queries are vulnerable to poor plan stability. By that, I mean that small changes to statistics and other information used by the query optimiser, might cause a sub-optimal plan to be produced. A single, large query is much harder for the optimiser because there are so many plan alternatives. These sorts of issues can be transient and very tricky to debug.

Since your life is already tough enough with the poor design, you probably don't want to add to your potential problems by introducing large, complex, and unstable queries - no matter how 'clever' they might be. If you do end up doing this for performance reasons, comment the code extensively, make sure the other members of your team understand what has been done and why, and do what you can to make life easy on the query optimiser.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Sanjay-940444 SSC-Addicted Points: 422 More actions · Answer 7

Thanks Paul!

More the query is complex more possible plans are possible and SQL Sever is not going to pick up best one necessarily.

Best design is one which is simple.

Paul White SSC Guru Points: 150467 More actions · Answer 8

Sanjay-940444 (7/19/2010)
More the query is complex more possible plans are possible and SQL Sever is not going to pick up best one necessarily. Best design is one which is simple.

You got it.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi