August 14, 2013 at 8:11 pm
This is more of a question to people who read code and understand exactly what it does. Like someone posts a bunch of code.. and there are people answering the questions related to it.
I would like to know if you have a process or method to analyze the code in your head.. or do you type up a query to see what it does. I have seen people look at a stored procedure that runs into a 2 page printout and know what it does.
Most of it is probably experience,knowledge, etc.. but there must an underlying technique to break it down into "human"...
August 14, 2013 at 9:59 pm
An interesting question. Probably because I don't think I'm particularly good at interpreting other people's code.
When I must do so, I usually look at the source tables first. How are they JOINed? What are any CROSS APPLYs or OUTER APPLYs generating? If there are CTEs, I try to understand them one at a time. All this of course is complicated if people don't use good naming and indentation (formatting).
Sometimes, for a particularly difficult section I might try isolating it and running it independent of the rest and comparing its results to the source table(s) so I can gain some insight into what it does.
I am curious to hear what others have to say on this topic.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 15, 2013 at 5:48 am
You'd have to define "what it does" better. I can look at the code and quickly get a sense of how it was written. Are there corellated sub-queries in the SELECT statement, do the JOIN statements have ON clauses, are there functions on columns in the JOIN or WHERE, etc., All sorts of bad code smells. That's frequently one of the first things I look at. After that, I can walk through multi-page code to try to discern intent. But to really see "what it does" I usually end up running it. Sometimes even running it in small chunks to understand what each piece has returned. None of that is even mentioning getting a look at the execution plan. That's a big one because unless you're also walking the data structure with the code, you can't tell that the parameter/variable used was a varchar and the column has a datetime, but that'll show up in the execution plan. All sorts of stuff like this.
I guess, at least for me, it's multi-layered. It just requires a lot of knowledge to fully understand a query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 15, 2013 at 6:16 am
I look at the tables and see the layout and relationships in my head. It helps if foreign keys are created, but that's not always so. With that, I look at the statements in order and see what it's trying to accomplish and how. By the time I get to the end, I have a general sense of what the whole thing is trying to do and may have a sense of problems, opportunities for improvement, etc.
Of course, all this is dependent on knowing what wrong in the first place. Do I need to add something to the process? Is it performing poorly? Does it have errors? Is it missing things in some cases? This determines how I look at the code and what I'm looking for.
I guess the bottom line is that the more experience you have with SQL, the better you'll be able to interpret and understand other people's code. There are usually several approaches to solve a problem and the one being used might not necessarily be right or wrong, but then again it could be completely inefficient. Maintainability also has to be kept in mind. I find there are opportunities for improvement in lots of code I look at, either by rewriting a query into a CTE, eliminating an unneeded cursor, creating a covering index, etc. But that means lots of testing to make sure your solution doesn't break something else. We never operate in a vacuum and we shouldn't ever forget that.
August 16, 2013 at 8:12 am
I've always had a theory that there are two kinds of coders: mechanics and poets. I'm more of a poet myself - if the code looks wrong or ugly or inelegant, it's probably wrong or won't work. Being a code poet unfortunately can't be taught.
Being a code mechanic, on the other hand, means applying the tools and knowledge and experience you have to analyze and solve the problem. Specific to SQL, this means diagramming mentally or on paper the logical structure and flow of the code. It means recognizing the math behind the joins/where clauses. It means methodical analysis of data relationships. And above all, it means looking at, analyzing, and forcing yourself to understand a whole lot of diverse code scenarios so you can recognize patterns.
August 16, 2013 at 9:44 am
It can be very frustrating looking at other peoples code to try and determine what it is doing. What I like to do is make a copy of it and put it in the format I code in, using indentation is key, for me, to be able to quickly look at the code and figure out what it is doing. Sometimes I think it has become an OCD for me to rewrite their code into the format I like.
I do have to admit I love taking other peoples code and reformatting it and the finding ways to speed it up. It amazes me how some people don't take the time to see what indexes are set up on the tables they are joining to figure out the best way to code the join. Or they leave order by's in when they aren't needed or try to group records together but they are puliing in the record key.
What is CTE?
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
August 16, 2013 at 12:04 pm
A Common Table Expression is a temp table with the scope of a single DML statement. There's all kinds of cool ways to use them but the basic syntax is something like this
WITH cte AS
(
SELECT name, address, phone
FROM employee
)
SELECT *
FROM cte
August 16, 2013 at 12:37 pm
calvo (8/16/2013)
A Common Table Expression is a temp table with the scope of a single DML statement. There's all kinds of cool ways to use them but the basic syntax is something like this
WITH cte AS
(
SELECT name, address, phone
FROM employee
)
SELECT *
FROM cte
Careful about referring to it as a temp table. It's not a separate storage allocation like a temporary table or table variable. It's a type of derived table, a query, just a query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 16, 2013 at 12:50 pm
Thanks for catching that, I should have been a little more careful with my summary. The exact words that Microsoft uses, as Grant lead on, says
A common table expression (CTE) can be thought of as a temporary result set...
August 16, 2013 at 1:11 pm
Thanks for the info, I'll have to look into 'cool' ways I can use this. I know it's just a simple example but I can't see how I would use this. My initial thought is I would just create a #temp table if I need to do something similar.
Select name, address, phone
into #cte
from employee
Select *
from #cte
Maybe this is just my preference to compartmentalize my SQL. I tend to avoid sub queries as much as possible, not always. To me it just makes it easier to read and follow and I assume it will be easier for someone else to read.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
August 16, 2013 at 1:16 pm
below86 (8/16/2013)
Thanks for the info, I'll have to look into 'cool' ways I can use this. I know it's just a simple example but I can't see how I would use this. My initial thought is I would just create a #temp table if I need to do something similar.
Select name, address, phone
into #cte
from employee
Select *
from #cte
Maybe this is just my preference to compartmentalize my SQL. I tend to avoid sub queries as much as possible, not always. To me it just makes it easier to read and follow and I assume it will be easier for someone else to read.
Sub-queries are awesome. Take a look at CROSS APPLY too. All kinds of great things you can do with derived tables & sub-queries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 16, 2013 at 2:07 pm
I guess in the 10+ years I've been writing SQL I haven't found that 'awesome' moment with subqueries yet. Like I said I don't completely avoid them, I do find some helpful. I know of an individual that works here that loves them, I've seen them 4 deep in the 'from' and even some within his select. It works, and some of it works pretty fast, I was surprised. 😀 Some I was able to help by breaking out some of the subqueries into temp tables. Especially the ones he used in multiple queries. It's just a nightmare at times figuring out what he was doing. I do lean more towards writing my code so that anyone, beginner to advanced, can read and follow my code. I don't want to make it so complicated that it always falls back to me to maintain. Some of my pivot or unpivot code has a tendency to fall back on me.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply