|
|
New job, perusing one of the dozens of databases we use on a daily basis. I found:
0) All of the views in at least one of our databases includes this:
SELECT TOP(100) PERCENT
...
ORDER BY...
Evidently, the first line enables you to trick SqlServer 2008 (and earlier) into allowing the ORDER BY clause in a view. Of course, newer versions of SQL Server ignore both clauses, and IMHO doing ORDER BY in a view is kinda pointless anyway
1) I encountered a view that has 519 OR clauses that evaluate the value of 20 columns. When run in its original form, it took 11 seconds to execute and returns 2900 rows. I modified it to declare a varchar variable and then called CHARINDEX for each column one time, and execution time is now < 1 second.
2) I encountered another view like the one above where execution time was 4 seconds, and using the same technique, I reduced it to < 1 second.
3) Formatting of the SQL I've seen so far is, well, abysmal - and that's me being nice .
I consider my SQL skills to be slightly above the beginner level, but jeeze...
EDIT =======================
I'm surprised nobody has yet pointed out that you can't declare local vars in views (ask me how I know). 
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
modified 20-Jun-14 10:55am.
|
|
|
|
|
If the SQL is at that level, there's a fair chance nothing is indexed either (or the indexing is crap), might be somewhere to look next and win some points!
|
|
|
|
|
0) I agree, a silly practice. I've seen it occasionally.
3) The worst I've seen were obviously created by the designer. 
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
Following a few 'best practices' in MS SQL makes a world of difference vs just getting the job done. Most folks just git 'er done and can't be bothered to spend a little time doing research. On top of that, a lot of companies have DBA's that don't even review the code the developers plug into the DB.
I'm not sure what their real job is... but it involves maintaining the system without being aware of what the system is doing.
|
|
|
|
|
John Simmons / outlaw programmer wrote: I encountered a view that has 519 OR clauses that evaluate the value of 20 columns. When run in its original form, it took 11 seconds to execute and returns 2900 rows. I modified it to declare a varchar variable and then called CHARINDEX for each column one time, and execution time is now < 1 second.
Congratulations - you've just ensured that SQL won't be able to use an index on any those columns to process your query! 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I'm not sure if that's a "Bad Thing" (TM) given the improvment thus far.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Regardless of the improvement seen, is the solution offered the best solution available?
Bear in mind, somewhere down the road, someone will look at the solution offered, decide is was the best option, and use it again (possibly in an area where it doesn't make sense to do so).
|
|
|
|
|
Perhaps not a bad thing per se; but it does suggest that there is room for farther improvement as time permits.
Did you ever see history portrayed as an old man with a wise brow and pulseless heart, waging all things in the balance of reason?
Is not rather the genius of history like an eternal, imploring maiden, full of fire, with a burning heart and flaming soul, humanly warm and humanly beautiful?
--Zachris Topelius
Training a telescope on one’s own belly button will only reveal lint. You like that? You go right on staring at it. I prefer looking at galaxies.
-- Sarah Hoyt
|
|
|
|
|
|
Have you already discovered you need a new DBA? 
As for 0, but I believe you already know that, you can't 'trick' SQL into ordering your views.
When you use a TOP(X) PERCENT SQL needs to know what it need to get the TOP of. The only way to make sure you always get the same records (given none are deleted or inserted) is by ordering the query.
Within the TOP(X) rows order isn't guaranteed. In practice it will be ordered about 100% of the time though (unless you do a TOP(100) PERCENT, because SQL will know that's everything so it won't have to order ).
It's an OO world.
public class SanderRossel : Lazy<Person>
{
public void DoWork()
{
throw new NotSupportedException();
}
}
|
|
|
|
|
|
The tat on that page doesn't make the mutt attractive: but it does distract from it's ugly mug, which may be the idea...
Those who fail to learn history are doomed to repeat it. --- George Santayana (December 16, 1863 – September 26, 1952)
Those who fail to clear history are doomed to explain it. --- OriginalGriff (February 24, 1959 – ∞)
|
|
|
|
|
Terrible law...
I was going to tattoo the names of each of my goldfish on their sides so I could keep track of them all in my aquarium. Now how will I tell who is who?
|
|
|
|
|
Easy! an aquarium for each fish...
entia non sunt multiplicanda praeter necessitatem
|
|
|
|
|
That would be cruel! They are the siamese fighting fishes[^] and they are so fond of each other 
--
"My software never has bugs. It just develops random features."
|
|
|
|
|
staple a nametag to each one
You cant outrun the world, but there is no harm in getting a head start
Real stupidity beats artificial intelligence every time.
|
|
|
|
|
Have you considered color coding with permanent markers?
|
|
|
|
|
First thought: This doesn't need to be a law. This should be common sense.
Second thought: There are chihuahuas in pink sweaters and socks in my neighborhood. If they're going to humiliate the dog that much, the tattoo isn't far off. New Yorkers are crazy... I would know. I'm one of them*.
* But I'm a completely different kind of crazy...
|
|
|
|
|
Ian Shlasko wrote: There are chihuahuas in pink sweaters and socks in my neighborhood.
There ought to be a law against that. 
Once you lose your pride the rest is easy.
I would agree with you but then we both would be wrong.
The report of my death was an exaggeration - Mark Twain
Simply Elegant Designs JimmyRopes Designs
I'm on-line therefore I am.
JimmyRopes
|
|
|
|
|
In the summer time, yes. When it's really cold, who cares what color? Keep your doggies warm!
|
|
|
|
|
Chihuahuas aren't dogs; they're rodents.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
"If I can drop-kick it over my back fence, it's not a dog."
-- Jeff Dunham
|
|
|
|
|
And so are those hairless objects, like the Chinese Crested[^]
And where is the point in a Sphynx cat[^]?
Don't get me wrong, I like dogs; I like cats. But those? No. Just "No".
Those who fail to learn history are doomed to repeat it. --- George Santayana (December 16, 1863 – September 26, 1952)
Those who fail to clear history are doomed to explain it. --- OriginalGriff (February 24, 1959 – ∞)
|
|
|
|
|
OriginalGriff wrote: Sphynx cat
Nothing wrong with a hairless pu.. [CARRIER LOST]
You'll never get very far if all you do is follow instructions.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
