| Author |
 Topic  |
|
Tamsun
Starting Member
2 Posts |
 Posted - 09/11/2002 : 05:11:09
|
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.
|
 |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
Australia
4970 Posts |
Posted - 09/11/2002 : 07:22:29
|
Use dynamic SQL before you use cursors. But try to find another way before you resort to dynamic sql
Damian |
|
|
|
Page47
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 09/11/2002 : 08:36:58
|
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 |
|
|
|
Onamuji
Aged Yak Warrior
USA
504 Posts |
Posted - 09/11/2002 : 09:06:45
|
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 ...
|
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 09/11/2002 : 09:40:41
|
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. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
Australia
4970 Posts |
Posted - 09/11/2002 : 09:42:56
|
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 |
|
|
|
Page47
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 09/11/2002 : 09:50:05
|
Merk, "...one technique that worked for well project under one set of requirements..."
Say what? 
Jay White
{0} |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
Australia
4970 Posts |
Posted - 09/11/2002 : 09:56:03
|
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 |
|
|
|
Onamuji
Aged Yak Warrior
USA
504 Posts |
Posted - 09/11/2002 : 10:52:44
|
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...
|
|
|
|
Musician
Starting Member
Ireland
6 Posts |
Posted - 09/19/2002 : 20:40:13
|
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.
|
|
|
|
robvolk
Most Valuable Yak
USA
15566 Posts |
Posted - 09/19/2002 : 20:45:01
|
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.
|
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 09/20/2002 : 04:30:57
|
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. |
|
|
|
ytrenty
Starting Member
2 Posts |
Posted - 09/20/2002 : 07:41:14
|
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.
|
|
|
|
Onamuji
Aged Yak Warrior
USA
504 Posts |
Posted - 09/20/2002 : 08:02:19
|
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 
|
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 09/20/2002 : 08:25:47
|
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. |
|
|
|
IT1
Starting Member
5 Posts |
Posted - 11/24/2005 : 10:59:40
|
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? |
|
|
|
coolerbob
Aged Yak Warrior
United Kingdom
841 Posts |
Posted - 12/05/2005 : 12:00:29
|
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? |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
United Kingdom
313 Posts |
Posted - 12/05/2005 : 12:24:27
|
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 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 12/05/2005 : 14:53:23
|
Blimey Vyas, you've been lying low for a while haven't you?!
Kristen |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
United Kingdom
313 Posts |
Posted - 12/05/2005 : 15:13:34
|
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 |
|
|
|
Topic |
|
TSQL/ SP Best Practices
