SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 TSQL/ SP Best Practices
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

Tamsun
Starting Member

2 Posts

Posted - 09/11/2002 :  05:11:09  Show Profile  Reply with Quote
to VyasKN, thank you for your aticle, especially the section about "Common cases when to (not) use dynamic SQL"

Now I'am also puzzled with this quesion, that is:
I often use dynamic sql and temporary tables to prevent from using
cursors, because I think cursors will effect on performance badly, especially speed.
How can i get a good balance.

Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 09/11/2002 :  07:22:29  Show Profile  Visit Merkin's Homepage  Reply with Quote
Use dynamic SQL before you use cursors. But try to find another way before you resort to dynamic sql

Damian
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 09/11/2002 :  08:36:58  Show Profile  Reply with Quote
When it comes to performance, I believe the "Best Practise Practice" is to try several ways in your environment and capture metrics.

To say "set based is always faster than temp tables is always faster than dynamic sql is always faster than cursors", may hold true quite often, but I don't believe it is "Best Practise Practice" to follow it blindly.

Jay White
{0}

Edited by - Page47 on 09/11/2002 09:51:38
Go to Top of Page

Onamuji
Aged Yak Warrior

USA
504 Posts

Posted - 09/11/2002 :  09:06:45  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
quote:

I've never heard this mentioned anywhere. Could you elaborate a little on why a single procedure is good (even possible) for an entire .NET web application?



It is not one for an ENTIRE application, but for say a single page/control. And it would be actually 2 procedures per page if that page gets data and writes data to the database. I do this just on pages that NEED that tid-bit more speed than others.

Its always best to wrap your procedures tightly around objects/methods that do what you need. Never try to OVER generalize a procedure. The former DBA did that with an application, having one procedure for every table that did everything to that table. This made for some very sloppy and slow code.

Always remember that what you find to work the best for you at a certain time is just based on your skill level. As you progress in your skill you will certainly learn new tricks of the trade and make your past work look like a failure. Just remember that that was your skill then and this is your skill now.

I find this the common ground for bugs in applications that require months of work. You begin and as you progress you get better at what you are trying to do and by the end you are some where completely different on the skill required to accomplish your project. Unless that's just me and my schizophrenic mind working ... I always tend to developer new routines that are more robust and easier to use than the previous ones... or I find out a routine or object is not quite as robust as I had once previously thought.

Oh the joys of programming ... I can't believe .NET SP2 is already out ... heard little about it ...

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 09/11/2002 :  09:40:41  Show Profile  Visit nr's Homepage  Reply with Quote
Yep - had a system (well rewrote it) where every table had an SP. The SP worked out from the parameters comparing with the primary key whether it had to do an insert/update/delete.
Made for very good auditing of the tables but meant that you could only ever insert/update/delete one row at a time to any table and performance left a bit to be desired.
The initial data import would have taken about 6 months.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 09/11/2002 :  09:42:56  Show Profile  Visit Merkin's Homepage  Reply with Quote
Jay

I couldn't agree with you more on that one.
It annoys me when people find one technique that worked for well project under one set of requirements and constraints, then use it as gospel for the rest of their careers.

Now, if only you could spell "practice"

Damian
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 09/11/2002 :  09:50:05  Show Profile  Reply with Quote
Merk, "...one technique that worked for well project under one set of requirements..."

Say what?

Jay White
{0}
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 09/11/2002 :  09:56:03  Show Profile  Visit Merkin's Homepage  Reply with Quote
Argh!

I was kinda distracted and did a few edits of that sentence.... then I have a go at you about spelling ... HAHAHA

Let's try :

quote:


one technique that worked well for one project under one set of requirements



Hey, it's late in Sydney!

Damian
Go to Top of Page

Onamuji
Aged Yak Warrior

USA
504 Posts

Posted - 09/11/2002 :  10:52:44  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
Ya those damn procedures for one were very long 2000++ lines ... cursors all over ... and plus there was no seeable format that was followed... looks like the developer just tabbed or spaced his way till he felt comfy with it ... re-writes are always fun...

Go to Top of Page

Musician
Starting Member

Ireland
6 Posts

Posted - 09/19/2002 :  20:40:13  Show Profile  Reply with Quote
Regarding the .Net comments. In many cases I have stored procedures that return more than one resultset (for want of a better word) in the form : select a, b from table1;select c, d from table2.
Using the SQLDataReader's NextResult method you can having iterated through the data from the first result then move on to the next result.
Not being an expert in SQL Server I don't know the pros and cons of this method in stored procedures but the SQLDataReader is damn fast and getting all these results in one visit seems worth it.

Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 09/19/2002 :  20:45:01  Show Profile  Visit robvolk's Homepage  Reply with Quote
I think that's what Onamuji was referring to, and I think it does perform better because it will only require one connection and one DataReader object. The regular ADO Recordset object also has a similar NextRecordset method that should also work faster than separate/multiple recordsets would.

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 09/20/2002 :  04:30:57  Show Profile  Visit nr's Homepage  Reply with Quote
Don't use xp_sendmail from a production server - use a dedicated sql server for it or an external app..
If you have to don't send emails from triggers - big no-no.
If you need to send emails from a server put them in a table and have a separate process to send the emails.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

ytrenty
Starting Member

2 Posts

Posted - 09/20/2002 :  07:41:14  Show Profile  Reply with Quote
Lots of good info here, however I take exception to one statement from Onamuji's first reply:

quote:

10) When you have a table that you need to join to other tables (i would say more than 3) and it can produce a large number of rows I would suggest moving that join into a VIEW ... this will allow the query optimizer to do a much faster job than it would if you have the joins in a procedure...


It is actually not the case that a View is ever faster than code in a stored proc. Putting the code in a stored proc allows SQL server to compile an execution plan for the code. If you place that same SQL code in a view, then each time the View is called, SQL Server has to recompile the execution plan.

Furthermore, if you are using parameters in your stored proc to restrict returned rows in your SQL statement, then the stored proc will compile the execution plan to consider the indexes on your WHERE clause columns that are sargable. If that same code is in a view, and you want to restrict returned rows, you essentially perform a select from that view, which forces SQL server to first execute the view, collecting all the rows, and then only return the rows you want by applying your WHERE clause criteria to the returned result set.
Go to Top of Page

Onamuji
Aged Yak Warrior

USA
504 Posts

Posted - 09/20/2002 :  08:02:19  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
I don't know about that ... I took a large join that was in a procedure and placed it in a view and was able to take from 30-60 seconds to 3-4 seconds by just having it in a view and selecting from there ... also for you chaps that have Enterprise Edition ... indexed views are helpful sometimes

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 09/20/2002 :  08:25:47  Show Profile  Visit nr's Homepage  Reply with Quote
A view included as part of a query will be optimised as a single entity and so should end up with the same plan as just the query.
This isn't always the case - just as changing a query can change the plan even if it has the same effect.

Suspect that any performance increases obtained from a view could be obtained by optimiser hints or just recoding the query.

Whether you want to use a views or not is a matter of personal choice and depends on the environment but probably has litle to do with performance.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

IT1
Starting Member

5 Posts

Posted - 11/24/2005 :  10:59:40  Show Profile  Reply with Quote
quote:
Alwyays install and update servers/databases/tables/SPs/views/triggers/jobs/replication/linked servers/logins/everything else - via scripts
Hold all scripts (i.e. everything you do) in SourceSafe


excuse my ignorance but:
what do you mean by scripts?, and what benifit does SourceSafe provide?
Go to Top of Page

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts

Posted - 12/05/2005 :  12:00:29  Show Profile  Reply with Quote
on this link from this thread: http://vyaskn.tripod.com/coding_conventions.htm
it says: "Perform all your referential integrity checks, data validations using constraints (foreign key and check constraints)."

Can anyone tell me how you would do a referential integrity check in a constraint?
Go to Top of Page

VyasKN
SQL Server MVP & SQLTeam MVY

United Kingdom
313 Posts

Posted - 12/05/2005 :  12:24:27  Show Profile  Visit VyasKN's Homepage  Reply with Quote
quote:
Originally posted by coolerbob

on this link from this thread: http://vyaskn.tripod.com/coding_conventions.htm
it says: "Perform all your referential integrity checks, data validations using constraints (foreign key and check constraints)."

Can anyone tell me how you would do a referential integrity check in a constraint?



Using FOREIGN KEY constraints

--
HTH,
Vyas
http://vyaskn.tripod.com
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 12/05/2005 :  14:53:23  Show Profile  Reply with Quote
Blimey Vyas, you've been lying low for a while haven't you?!

Kristen
Go to Top of Page

VyasKN
SQL Server MVP & SQLTeam MVY

United Kingdom
313 Posts

Posted - 12/05/2005 :  15:13:34  Show Profile  Visit VyasKN's Homepage  Reply with Quote
quote:
Originally posted by Kristen

Blimey Vyas, you've been lying low for a while haven't you?!

Kristen



Yes :) Been a bit busy lately!

--
HTH,
Vyas
http://vyaskn.tripod.com
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000