December 14, 2001 at 1:05 pm
With Yukon on the horizon, is there going to be a need for SQL tuning by developers building apps for database interaction, or will the database (or should I say optimizer) really take care of the queries?
December 14, 2001 at 2:27 pm
The server does a great job of managing resources, but it cannot rewrite the queries because it does not know the intention. There is still a need for developers to write good sql.
Steve Jones
December 14, 2001 at 3:17 pm
Twice in a week, I agree with Steve. The important thing is to write good SQL - avoid cursors, denormalizing, long operations in triggers. I see entirely too much time spent on performance work arounds that could have been fixed with tuning or recasting the solution.
Andy
December 18, 2001 at 8:47 am
Steve and Andy, thanks for your responses. I'm inexperienced in writing good sql and spend too much time writing and re-writing sql and stored procs (and talking to the dba much more than she would like). Are there any tools out there that can help me automate the process of writing better sql?
December 18, 2001 at 11:01 am
Not that I've seen. Tools are usually only as good as you are - until you master TSQL you're going to have questions. I wouldnt consider it bad to be asking the DBA - thats what they do! Just try to figure it out yourself first. You can always post here too, we'll try to help as we can.
Andy
January 2, 2002 at 11:15 am
I have only known SQL for about two years. About a year ago I thought I had learned all there was to learn about SQL. How wrong I was! In May 2001, I began going through the Transact-SQL Help available in Query Analyzer and focused on 5 commands I didn't previously know and learned them. This has helped me to see how much there is to learn and has helped me to learn faster than if I hadn't used this tool. This isn't the only way I learn, but it certainly helps to know what commands are available so that when you design a query or stored procedure you can select from more than one command. For example, I used to use the LEFT and RIGHT functions a lot and even used them both together to get the middle of a varchar field. Then I learned that SUBSTRING does the same thing and so I use that whenever I need the middle of a field.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
January 2, 2002 at 6:26 pm
In Oracle, you can have the concept of a query re-write, namely with whats called materialised views in which it can alter your select to look up over some "pre-generated" data for the view (refreshed periodically) rather than joining over the existing tables. This works, but the use of it is very specific and requires some thought as to its use and purpose (especially in oltp systems). The cost of generating a plan can be expensive in itself, so re-writing the query to boot and getting the expected results is very tricky and resource expensive. I believe the plan generator will certainly become more complex and "assist" where possible to maximise performance. Sloppy SQL is sloppy SQL (and sloppy design!)... until there is a "read mind" option we can install, SQL tuning is at the forefront of a DBA's life at times.
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
January 2, 2002 at 6:43 pm
With the talk that Yukon will allow queries and stored procedures in languages other than T-SQL (it supports the .NET Framework), I think the role of the DBA is going to only increase, but so is his/her expected knowledge base.
Microsoft is shooting for language independence, and I know there are more than a few people out there wondering how well procedural languages are going to work for set-based operations. As a result, it would seem that DBAs are going to play a large role in ensuring queries are optimized. The big question is whether or not the DBA is going to have to learn all the respective languages that developers may be coding in for an organization or not.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 2, 2002 at 7:22 pm
Thats a great point. Hard enough to master one language, but if you get everyone using their flavor you'll have procs written in Perl, Cobol, etc - after all in .Net, everything is equal!
Andy
January 3, 2002 at 5:46 am
I just hope the switch to using various languages doesn't all happen at once. I consider myself a quick learner, but trying to learn many different programming languages at once can be tricky. Where I work I suspect some of our programmers will try to use C++, VB, and Java. All languages I'd love to learn and with this .NET approach it might give DBA's and SQL Programmers the excuse to dedicate time to these languages so that anything written in them can be tunes properly.
Robert Marda
SQL Programmer
bigdough.com
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
January 3, 2002 at 11:37 am
The beauty( theoretically) is that you can write in whatever language you like and it can easily interact with modules (functions, stored procedures, etc) written in other languages.
Steve Jones
January 3, 2002 at 4:49 pm
Sure...but each has its own little tricks. Not sure I want to have to learn ALL the .Net languages core syntax to stay afloat!
Andy
January 3, 2002 at 7:31 pm
I've begun learning VB and have noticed that the queries sent to SQL Server are the same as if I created them in Query Analyzer. Hopefully, all the languages will have to use something that looks like SQL, that way we'll have something to go on as we learn the parts of the languages we need to know.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
January 4, 2002 at 10:56 am
There are some differences in the way the languages compile (they generate different ILS). However, you don't need to learn them. If a C# app runs faster than a VB/NET app, who cares? If the speed different matters, then you can buy better hardware or recode it, but you would not have to throw away the VB code. It will be like it is now, but integration between C++, C#, and VB (as well as Perl, Cobol, etc) will be easier.
Steve Jones
January 15, 2002 at 12:38 am
In Tsql the "show [estimated] accesspath"-options are great to check if they match whatever you've supposed it was going to do. This works fine for single query tuning. When using tools like ado,.. you'll have to build some knollage on how this interacts with your rdbms. Ease of development has many times got some performance implications. This goes for code as for sql.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply