| Author |
Topic |
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
 Posted - 2004-10-28 : 02:37:45
|
| 1.) Please state your reasons, opinions, point of views and why? Minimizing our functions, joins and etc. is a good practice in writing our SQL query? For example, 1.) a single statement with two function or 2.)a statement with two lines but no functions which is faster and better? And what do you think should be a good SQL query practice?Instead of this…---------------------------My Querry---------------------------------------------------DECLARE @AmountIsZero as intSET @AmountIsZero = (select count(*) from tblAida where EmpID=14344 and endingamount= 0 AND flgPaid=0)IF @AmountIsZero > 0 BEGINUPDATE tblAida SET flgPaid = 1 WHERE EmpID = 14344 AND flgPaid=0END--------------------------------------------------------------------------------------- We can make this…update tblAidaset flgPaid = 1from tblAida as t1where flgPaid = 0and exists (select * from tblAida as t2 where t2. EmpID = t1. EmpID and t2.EndingAmount = 0)2.) Enumerate a good practice of SQL query for you?Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2004-10-28 : 03:20:31
|
| I would do thisUPDATE tblAidaSET flgPaid = 1WHERE flgPaid = 0 AND EndingAmount = 0In this instance why use a subquery to check the existence of a record on itself? |
 |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-10-28 : 04:07:38
|
Also this:quote:
DECLARE @AmountIsZero as intSET @AmountIsZero = (select count(*) from tblAida where EmpID=14344 and endingamount= 0 AND flgPaid=0)IF @AmountIsZero > 0 BEGINUPDATE tblAida SET flgPaid = 1 WHERE EmpID = 14344 AND flgPaid=0END
Should be written like this:IF EXISTS( SELECT * FROM tblAida WHERE EmpID=14344 AND endingamount=0 AND flgPaid=0 )BEGIN -- do the stuffEND Also AndyB13 is right, just go ahead and UPDATE, no need for unnecessary checks.rockmoose |
|
|
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
Posted - 2004-10-28 : 04:57:35
|
 Its seems that my topic have not understand! Yes both of you rockmoose and Andy are right! In fact your post areright example of a good practice of SQL query...Just for the benifit of Students and starters in SQL. I just want to know on all experts here the reasons, opinions, point of views and why? we should exercise a precise and exact statement on our SQL. Just what you have enumerated here.I want to know the advantages and benifits of a good SQL practice justlike what the two of you posted it here.I appriciate your response... TNXHOPE I CAN GET SOME INSIGHTS TO ALL EXPERTS HERE...Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-28 : 05:26:11
|
IMHO best practice is what is faster. usually that also means more readable code.sometimes those are functions sometimes not...it depends on your situatuion.Go with the flow & have fun! Else fight the flow  |
|
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2004-10-28 : 06:24:28
|
| I agree with spirit1ReadabilityWell commentedFits into QA without unnecessary horizontal scrollbarsAliasing where appropiate Good/uniform/meaningful naming conventions, depends on company policy I suppose |
|
|
|
jonasalbert20
Constraint Violating Yak Guru
300 Posts |
Posted - 2004-10-28 : 19:51:10
|
| Thanks to you guys here... Hope others should participate too... Please tell otherswhat are their opinions and point of views.Want Philippines to become 1st World COuntry? Go for World War 3... |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-10-28 : 21:02:51
|
| The fastest or shortest query is not always the best. Take into account readability.I recently had a query which came down to a choice of a complicated statement or simple statement. The simple one had about three time the cost according to the query plan - but as this would take a couple of seconds to run and was only run once a day I went with it.I would advise against using functions unless you have a good reason to as they can make queries very slow and have a number of associated bugs.SET @AmountIsZero = (select count(*) from tblAida where EmpID=14344 and endingamount= 0 AND flgPaid=0)IF @AmountIsZero > 0 Just shows the author hasn't thought about what they are doing.==========================================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. |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2004-10-28 : 21:49:06
|
| It also depends on what kind of a system your creating.OLTP (SnowFlake) or DataMart (StarSchema).There are alot of other factors that impact performance besides the formation of the query. Example Aggregate Tables, Partioning, Indexing, Disk Subsystem, Memory, CPU, OLTP/DW/DM Design, data volumes. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-10-29 : 07:04:24
|
From reading the example queries provided by jonasalbert20 I can mention the KISS principle.( Keep It Simple S.. )Jonas, you are complicating things that need not be complicated,maybe you don't have enough SQL experience to write "good" sql yet.Think about what you want to do, then write the sql.Example:I want to update [flgPaid] to 1 in table [tblAida]where [endingamount] = 0 and [flgPaid] is still 0....think.......write....UPDATE tblAidaSET flgPaid = 1WHERE flgPaid = 0 AND EndingAmount = 0 If you look at you original post...You do tend to overcomplicate things (to say the least).  1. Gain more experience in SQL2. Keep posting here  , you will learn a lot.As you gain experience You will start to understand principles such asIndexes, Joins, Normalization, Clustered Indexes etc.. a lot better.Knowledge of all of those things will aid you in designing good databases and write better SQL.Good practice:SimpleReadableEfficientOf course sometimes you have to compromise/choose between the above guidelines.Happy Learning,rockmoose |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-29 : 07:38:03
|
| I'd add to the list Atomic - which the original example isn'tEdit: I do mean "Atomic", don't I? Splitting headache today ...Kristen |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-10-29 : 08:07:36
|
| Atomic headache !, I usually get those on Saturdays and Sundays (nowadays) .Fidays are kind of like preparation for saturday's Atomic Cleavage Condition Anyway,All SQL statements are Atomic, aren't they ?I think you refer to the ACID principle:A transaction commits all or nothing ( Atomic ).But I agree,if you write a series of commands that alter the state of the database,it has to be Atomic.( in SQL Server you do that by correct transaction handling )jonasalbert, check out: ACID principlerockmoose |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-29 : 11:53:35
|
| That's it - ACID. Ta.Kristen |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2004-10-29 : 13:56:18
|
| Remember that in the real world, the Software Development Life Cycle is 90% (or more) maintenance. Therefore, unless there is a compelling reason otherwise, I prefer simpler, easier to read code. If you've ever come into a new environment where you are maintaining somebody else's code, you really gain an appreciation for simplicity. The same effect can be experienced by leaving a piece of code for 2 years and coming back to it to make an enhancement.Arguments over performance can be a compelling reason, but as Nigel points out, you need to look at real-world performance issues, not just isolated benchmarking and theory. Don't optimize for performance just for the sake of claiming you have highly optimized code. At the PASS summit, the Veritas keynote made the point well: you may have a highly optimized query, but if it's being called a million times in a loop unnecessarily, you still don't have an optimized solution.-----------------------------------------------------Words of Wisdom from AjarnMark, owner of Infoneering |
|
|
|
|
What is a good SQL Querry Practice?