SQL Server query

Elax

Elax

Senior Member
Joined
Aug 26, 2003
Messages
888
Reaction score
73
Location
Pretoria
Hi,

I've recently started work at a new company which has a product running on asp.net with ms sql server as the back end

98% of all business logic sits inside stored procedures, with asp.net mainly handling the UI. This I'm fine with, even if I hadn't previously done much with stored procedures as such.

My problem comes with some of the old t-sql code I'm now maintaining. Some of the procedurs use a lot of #temptables, as well as declare @temptable to store temporary stuff (think around 10-15 for one sproc), prior to returning the final result set

Not having been exposed to sql server sproc programming before (I know sql just fine), I was wondering if this is the accepted norm of doing this, or if this points at trying to force tsql to do stuff it hasn't been designed for, or if it points at inefficient db design.

Opinions?
 
Temp tables have their place for larger datasets. Derived tables might be a lot quicker for smaller data sets than temp tables.

Edit: this is from what I remember as my current role doesn't primarily deal with SQL. :)
 
That is the worst application design ever. I have encountered numerous products with this approach it shows a clear lack of understanding on how to properly architect a system.

Temptables are the devils playground and should only be used where really needed.

But yes thats very inefficient design.

To give you an example a product i ran across at work (not ours) has 2500 stored procedures. Our current application we writing has 0.I will NEVER used a stored procedure ever again.
 
semaphore said:
That is the worst application design ever. I have encountered numerous products with this approach it shows a clear lack of understanding on how to properly architect a system.

Temptables are the devils playground and should only be used where really needed.

But yes thats very inefficient design.

To give you an example a product i ran across at work (not ours) has 2500 stored procedures. Our current application we writing has 0.I will NEVER used a stored procedure ever again.
Click to expand...

In a high throughput environment you want to leverage off the fact that SP's reuse execution plans.
EDIT:
I wouldn't have business logic in SP's though
 
Skandalis said:
In a high throughput environment you want to leverage off the fact that SP's reuse execution plans.
EDIT:
I wouldn't have business logic in SP's though
Click to expand...

Yeah. No.

I still will not use SP's.

Standard sql queries still have execution plan caching.

http://msdn.microsoft.com/en-us/library/ms181055(v=sql.105).aspx

And even to go on top of that in a high throughput environment there is always a better alternative before a database hit becomes required. Such as a caching environment such as Redis or Memacached, or even a NoSql scenario.
 
semaphore said:
That is the worst application design ever. I have encountered numerous products with this approach it shows a clear lack of understanding on how to properly architect a system.

Temptables are the devils playground and should only be used where really needed.

But yes thats very inefficient design.

To give you an example a product i ran across at work (not ours) has 2500 stored procedures. Our current application we writing has 0.I will NEVER used a stored procedure ever again.
Click to expand...

Amen!

I did once used stored procs to help with some data prepare manipulation for ReportBuilder reports, but that was 12 years ago, so I was probably noob
 
Last edited:
Architecture aside, maybe give some comments regarding sql/temp tables as per OP question.
 
kripstoe said:
Architecture aside, maybe give some comments regarding sql/temp tables as per OP question.
Click to expand...

Well any stored procedure that is using 10-15 temp tables per procedure definitely needs re-engineering.
 
Have you profiled the queries and compared them to some of the alternatives you are proposing? You are proposing alternatives, aren't you?
 
I question the competence of any database developer who dismisses the use of stored procedures out of hand.

Like any other feature in a development environment, stored procedures have their place and can also be misused.
 
zippy said:
I question the competence of any database developer who dismisses the use of stored procedures out of hand.
Click to expand...

Funny. How one of the biggest systems in SA runs perfectly fine without them.
 
zippy said:
I question the competence of any database developer who dismisses the use of stored procedures out of hand.

Like any other feature in a development environment, stored procedures have their place and can also be misused.
Click to expand...

I can see the advantages when having multiple front ends, as a tier between the actual physical tables and the front end code. I'm more worried about the numerous temp tables used in some of the sp's I'm dealing with
 
semaphore said:
Funny. How one of the biggest systems in SA runs perfectly fine without them.
Click to expand...

...

While that may be true, you cannot then say stored procs are to be avoided at all cost just because some big systems exist without them. Some companies are really dependant on using stored procs, and they run really high traffic (real time) systems with multiple front ends and users across the world. I should know.
 
semaphore said:
Funny. How one of the biggest systems in SA runs perfectly fine without them.
Click to expand...

...

While that may be true, you cannot then say stored procs are to be avoided at all cost just because some big systems exist without them. Some companies are really dependant on using stored procs, and they run really high traffic (real time) systems with multiple front ends and users across the world. I should know.
 
zippy said:
I question the competence of any database developer who dismisses the use of stored procedures out of hand.

Like any other feature in a development environment, stored procedures have their place and can also be misused.
Click to expand...

Thank you.
 
zippy said:
I question the competence of any database developer who dismisses the use of stored procedures out of hand.

Like any other feature in a development environment, stored procedures have their place and can also be misused.
Click to expand...

SPs certainly have their place. Not using stored procedure's at all is not a good thing and not something to brag about. Your DB server should handle as much of the logic as it can.
 
kripstoe said:
...

While that may be true, you cannot then say stored procs are to be avoided at all cost just because some big systems exist without them. Some companies are really dependant on using stored procs, and they run really high traffic (real time) systems with multiple front ends and users across the world. I should know.
Click to expand...

Good for you. But i question the individual who believes that stored procedures are the answer to high traffic systems across the world. Like you should know. There is always 100% better ways of doing something than using a god forsaken stored procedure.

Just because a system was designed for the heavy use of stored procedures, does not instantly mean it was done correctly.
 
Bar0n said:
SPs certainly have their place. Not using stored procedure's at all is not a good thing and not something to brag about. Your DB server should handle as much of the logic as it can.
Click to expand...

Its that flawed logic that lets stored procedures proliferate by the thousands.

http://www.codinghorror.com/blog/2004/10/who-needs-stored-procedures-anyways.html

Our application server processes around 10,000 requests every 10 seconds and this is hitting the DB doing math etc the cpu does not even spike to 1%. Our DB server hums along nicely because its doing what its meant to do make sure our data is safe not waste its precious cpu power being rammed up the ass by sprocs.
 
semaphore said:
Good for you. But i question the individual who believes that stored procedures are the answer to high traffic systems across the world. Like you should know. There is always 100% better ways of doing something than using a god forsaken stored procedure.

Just because a system was designed for the heavy use of stored procedures, does not instantly mean it was done correctly.
Click to expand...

Did a sp touch you in your naughty place?
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X