T-SQL question

[lightng]

The reason why I disagree is that by using views, table value functions or whatever you are breaking a larger task or query down into manageable chunks. Get each bit working first, hell you can even apply unit testing now in sql if you want to go that far. The resulting query is much easier to read. Following naming conventions, formatting etc is a necessary of course but a big ass query is still a big ass query at the end of the day.

Just my opinion, and that isnt to say I cant see where youre coming from Spacecadet.

[lightng]

Just read your last post, Spacey. You put together a good argument. I get your point about creating database objects left right and centre. One thing I would like to see are namespaces, folders or some way of organising mssql objects rather than having to create a custom naming convention.

Anyway, its getting late now. Why the hell am I discussing this at this time? Last thing I want to do is start dreaming in Sql.

[mudskipper]

The problem is that the 'breaking into manageable chunks' means the optimiser can't do its stuff efficiently.

Functions are bad
Views are bad

Have a look at common table expressions - they can be helpful for complex queries.

[mudskipper]

... although step 1 is getting your indexes right!

[lightng]

Interesting links K2P2. Stored procs over views eh? I've come across performance problems with functions in "where" clauses before and know to avoid where possible.

In the main, I am a programmer not a DBA so you can see where I come from breaking problems into component parts, abstracting functionality and looking where possible for re-use. I understand that things may not always transfer over so neatly to the database world. When object oriented databases start performing as well as relational databases, I will be a happy man.

I don't usually look at performance till I've got the query right then I spend some time looking at costs of various parts. Maybe this is the wrong approach but its worked for me in the past.

... although step 1 is getting your indexes right!

Getting the indexes right first time is also something I am not good at; it's usually something I tweak at a later date. I don't know where the indexes are needed until I can see how the data plays out and how it is consumed. Sometimes there are obvious areas where I know an index is needed but I'm not good enough (and usually am not given enough of a decent spec) to plan every single one ahead of time.

Tips are always welcome though. It's certainly interesting to see how other people do things

[HairyArsedBloke]

One thing I would like to see are namespaces, folders or some way of organising mssql objects rather than having to create a custom naming convention.

Aye

[mudskipper]

Way back in the olden days, programmers designed queries, not DBAs. It is a different skill to designing code. Do they still teach students stuff like 3NF at college? It really is worth learning the basics of how indexes work and how to read a query execution plan - it will make a huge difference to how your queries run.

[lightng]

Way back in the olden days, programmers designed queries, not DBAs. It is a different skill to designing code. Do they still teach students stuff like 3NF at college? It really is worth learning the basics of how indexes work and how to read a query execution plan - it will make a huge difference to how your queries run.

Normalisation forms: The key, the whole key and nothing but the key etc. Of course, all basic stuff. You can only read a query execution plan once you have a query and therefore it is only then that you know where indexes should be.

So saying that indexes are the first thing that should be considered is not necessarily true.

Too many indexes are just as bad as not enough. There are performance implications both ways. Thats why I don't just chuck in indexes willy nilly at the start.

[mudskipper]

Normalisation forms: The key, the whole key and nothing but the key etc. Of course, all basic stuff. You can only read a query execution plan once you have a query and therefore it is only then that you know where indexes should be.

So saying that indexes are the first thing that should be considered is not necessarily true.

Too many indexes are just as bad as not enough. There are performance implications both ways. Thats why I don't just chuck in indexes willy nilly at the start.

Agreed - willy nilly indexes can be counter productive. The point is that you can (and, with complex queries, should) design your queries - in the old SSADM days, you'd know what indexes you needed before you touched the database.

[lightng]

Agreed - willy nilly indexes can be counter productive. The point is that you can (and, with complex queries, should) design your queries - in the old SSADM days, you'd know what indexes you needed before you touched the database.

In the old SSADM days you had the luxury of designing up front. SSADM - I think I read about that in an old computer studies history e-book. :-P

Most of my work involves changing requirements in an agile environment with dynamic requirements. Its not always ideal but thats the world we young 'uns who have just come out of college operate. The way the data is consumed and populated can change from iteration to iteration.

Joking aside, the assumptions you make about my age are WILDLY inaccurate. If I wasn't such a cool and hip oldie, I would have probably found your comments mildly offensive, at least briefly, and then, I would have dismissed them as a product of senile dementia.