|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread |
Rating: 
|
Display Modes |
ASP Free Forums Sponsor:
|
|
SlickEdit: Code in over 40 languages across 7 platforms. SlickEdit’s unmatched power, speed, and flexibility allows even the most accomplished developers to write better code faster. Download a free trial today! |
|
#1
May 5th, 2005, 11:17 PM
|
||||
|
||||
|
Best Practices : ASP Coding, SQL Statements, and Database Structuring
Hi all,
In the past week or so, I have noticed quite a bit of the same syntax errors in ASP code and/or SQL statements. I thought it would be good for newbies and/or seasoned programmers to have a thread to help develop their code better. Please add "Best Practices" techniques and NOT opinions. In other words facts and research that can back it up. Thanks!  Also please title the post to allow the user to understand what the best practice is. Links along with your best practice would be great!Code:
Table of Contents 1. Watch out for Reserved Words in SQL Statements 2. Semicolon after SQL Statements 3. Do not use SELECT * in your SQL queries 4. Extensions for Included Files 5. Paths, Using MapPath 6. Select Case Statement 7. Spaces in Scripts 8. String Concatenation w/ VBS 9. Variable Declaration and Names 10. Variable Value Trimming 11. Object and Variable Initialization 12. Using Page Scope for Best Performance 13. Using Global Variables Sparingly and Efficiently 14. Avoiding the Use of Public Variables 15. Using Application Scope for Objects 16. Avoiding the Use of Server Variables 17. Declaring Objects with the <OBJECT> Tag 18. When Not to Use ASP 19. Using the Dictionary Object for Lookup 20. Timing Out Connection Requests 21. Browser Connections and ASP 22. Organizing Application Directories and Files 23. Combine multiple sequential Response.Write lines 24. Type Notation 25. Use good script coding style 26. Avoid context switching 27. Use buffering 28. Avoid Redundant Code 29. Subs and Functions 30. Objectives of Good Relational Database Design 31. Benefits of Good Database Design 32. Naming Tables and Fields 33. Do Not Cache Database Connections in the Application or Session Objects 34. Release the database connection to the connection pool as quickly as possible 35. Use Option Explicit to Speed up Processing 36. Use Local Variables in Subroutines and Functions 37. Assign Request Variables to Script Variables 38. Avoid Redimensioning Arrays 39. Avoid String Concatenation in Loops 40. Use Server.Transfer Instead of Response.Redirect Whenever Possible 41. Use Trailing Slashes in Directory URLs 42. Do Performance Testing 43. SQL Server Benchmarks 44. Avoid NULLable columns 45. Avoid wildcard characters at the beginning of a word 46. Use 'Derived tables' wherever possible 47. Use the more readable ANSI-Standard Join clauses instead of the old style joins 48. Do not prefix your stored procedure names with "sp_" with sql server 49. Do not store binary or image files (Binary Large Objects or BLOBs) inside the database 50. Access tables in the same order in all your stored procedures/triggers consistently 51. Make SQL Statements more readable 52. Increase timeouts 53. In SQL Server 2000 - use SCOPE_IDENTITY not SELECT max() 54. Use MS Access/SQL Server SQL analyzers 55. When using Joins 56. Apply the latest Security Packs / Service Packs 57. Using Count 58. SQL injection Last edited by bslintx : August 11th, 2005 at 10:05 PM. Reason: added addition to thread title
|
|
#2
May 5th, 2005, 11:25 PM
|
||||
|
||||
|
Watch out for Reserved Words in SQL Statements
I noticed recently that folks out there are using "reserved" words in their SQL statements. This is a big no-no. Try to make field names unique to what that table is describing. For example: Date is a reserved word and CAN be used ias a field name if put within brackets as so...[Date] but this is not a habit to get into. Let's say you have a table called Events. Then you have an event date. Name it EventDate or Event_Date. It is self-explanatory when someone reviews your code what that field name refers to rather than Date.
Update: Contrary to belief...Password IS NOT a reserved word  Here are some reserved words in the SQL standard to stray away from when writing FIELDS for sql statements: Code:
ABSOLUTE ACTION ADD AFTER ALL ALLOCATE ALTER AND ANY ARE ARRAY AS ASC ASENSITIVE ASSERTION ASYMMETRIC AT ATOMIC AUTHORIZATION AVG BEFORE BEGIN BETWEEN BIGINT BINARY BIT BIT_LENGTH BLOB BOOLEAN BOTH BREADTH BY CALL CALLED CASCADE CASCADED CASE CAST CATALOG CHAR CHAR_LENGTH CHARACTER CHARACTER_LENGTH CHECK CLOB CLOSE COALESCE COLLATE COLLATION COLUMN COMMIT CONDITION CONNECT CONNECTION CONSTRAINT CONSTRAINTS CONSTRUCTOR CONTAINS CONTINUE CONVERT CORRESPONDING COUNT CREATE CROSS CUBE CUBE CURRENT CURRENT_DATE CURRENT_DEFAULT_TRANSFORM_GROUP CURRENT_PATH CURRENT_ROLE CURRENT_ROLE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_TRANSFORM_GROUP_FOR_TYPE CURRENT_USER CURSOR CYCLE DATA DATE DAY DEALLOCATE DEC DECIMAL DECLARE DEFAULT DEFERRABLE DEFERRED DELETE DEPTH DEREF DESC DESCRIBE DESCRIPTOR DETERMINISTIC DIAGNOSTICS DISCONNECT DISTINCT DO DOMAIN DOUBLE DROP DYNAMIC DYNAMIC EACH ELEMENT ELSE ELSEIF END EQUALS ESCAPE EXCEPT EXCEPTION EXEC EXECUTE EXISTS EXIT EXTERNAL EXTRACT FALSE FETCH FILTER FIRST FLOAT FOR FOREIGN FOUND FREE FROM FULL FUNCTION GENERAL GET GLOBAL GO GOTO GRANT GROUP GROUPING HANDLER HAVING HOLD HOUR IDENTITY IF IMMEDIATE IN INDICATOR INITIALLY INNER INOUT INPUT INSENSITIVE INSERT INT INTEGER INTERSECT INTERVAL INTO IS ISOLATION ITERATE JOIN KEY LANGUAGE LARGE LAST LATERAL LATERAL LEADING LEAVE LEFT LEVEL LIKE LOCAL LOCALTIME LOCALTIMESTAMP LOCATOR LOOP LOWER MAP MATCH MAX MEMBER MERGE METHOD MIN MINUTE MODIFIES MODULE MONTH MULTISET NAME NAMES NATIONAL NATURAL NCHAR NCLOB NCLOB NEW NEXT NO NONE NOT NULL NULLIF NUMERIC OBJECT OCTET_LENGTH OF OLD ON ONLY OPEN OPTION OR ORDER ORDINALITY OUT OUTER OUTPUT OVER OVERLAPS PAD PARAMETER PARTIAL PARTITION PATH POSITION PRECISION SION PREPARE PRESERVE PRIMARY PRIOR PRIVILEGES PROCEDURE PUBLIC RANGE RANGE READ READS REAL RECURSIVE REF REFERENCES REFERENCING RELATIVE RELEASE REPEAT RESIGNAL RESTRICT RESULT RETURN RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP ROUTINE ROW ROWS SAVEPOINT SCHEMA SCOPE SCROLL SEARCH SECOND SECTION SELECT SENSITIVE SESSION SESSION_USER SET SETS SIGNAL SIMILAR SIZE SMALLINT SOME SPACE SPECIFIC SPECIFICTYPE SQL SQLCODE SQLERROR SQLEXCEPTION SQLSTATE SQLWARNING START STATE STATIC SUBMULTISET SUBSTRING SUM SYMMETRIC SYSTEM SYSTEM_USER TABLE TABLESAMPLE TEMPORARY THEN TIME TIMESTAMP TIMEZONE_HOUR TIMEZONE_MINUTE TO TRAILING TRANSACTION TRANSLATE TRANSLATION TREAT TRIGGER TRIM TRUE UNDER UNDO UNION UNIQUE UNKNOWN UNNEST UNTIL UPDATE UPPER USAGE USER USING VALUE VALUES VARCHAR VARYING VIEW WHEN WHENEVER WHERE WHILE WINDOW WITH WITHIN WITHOUT WORK WRITE YEAR ZONE Last edited by bslintx : August 18th, 2005 at 11:57 AM.
|
|
#3
May 5th, 2005, 11:28 PM
|
||||
|
||||
|
I'll keep this thread in mind when I start seriously learning how to code in ASP.
|
|
#4
May 6th, 2005, 12:21 AM
|
||||
|
||||
|
Semicolon after SQL Statements?
A semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server. Is this necessary? If using MS Access or SQL Server it is not necessary..... but some database programs do require it. So best practice is to start using it!!!
Code:
SQL ="SELECT ProductNameFROM Products;"
|
|
#5
May 6th, 2005, 12:32 AM
|
||||
|
||||
|
Do not use SELECT * in your SQL queries
Do not use SELECT * in your queries. If you have 8 fields and you ONLY need 3 then simply select those 3. Bottom line is, select ONLY the fields you need to query.
This technique results in reduced disk I/O and better performance http://www.codeproject.com/cs/database/sqldodont.asp Code:
SQL="SELECT CustomerID, CustomerFirstName, City FROM Customers;" In light of a few comments that believe this is not a best practice procedure I researched more info to help you understand why NOT to even though some think it doesn't matter in some cases when it actually does....so for that...YOU be the judge...should i? or shouldn't I? Reasons why you should not use SELECT * in you SQL statements. Instead you should enumerate all the fields that you want to select. There are probably more, but thise should be sufficient into your making your decision. source: http://www.adopenstatic.com/faq/selectstarisbad.asp By selecting only the fields that you want you don't return any superfluous fields. This results in an immediate speed increase. For example consider the following table:  Suppose we wish to create a drop down select box. To do this we really only need to the information from the first two fields (CustomerID and CustomerName). However, if we use SELECT * then we are pulling all the information for each customer from the database as we write each record, even if we don't need it (ie we are pulling data in the CustomerAddress and CustomerComments fields even though we don't need this information). By only selecting the fields we need we reduce the amount of data pulled from the database and thus speed up our application.
6. ADO makes two queries to the database. Before it fills your recordset, ADO must query the system tables to determine the name and datatypes all your fields. It is much more faster to specify your fields names in the SQL query, as the system table lookup no longer happens Last edited by bslintx : August 9th, 2005 at 09:12 AM. Reason: Added concrete facts why not to use the SELECT * in sql statement
|
|
#6
May 6th, 2005, 12:49 AM
|
||||
|
||||
|
Extensions for Included Files
For consistency, use include files in order to make specific information available to more than one referring page (changes to include files are distributed to all the pages that include them).
Standards: .inc—for large amounts of data with client-side scripting .txt—for text-formatted data files without scripting Do not use .inc for pages containing server-side scripts. If a user manages to display them, any business rules in the scripts will be exposed. Use the .asp extension for all pages containing scripting, or for which scripting is planned, in order to avoid displaying proprietary information coded as scripts in ASP pages.
|
|
#7
May 6th, 2005, 12:54 AM
|
||||
|
||||
|
Quote:
.Of course, if you don't know how many records might be fetched and it's a large table, then bslintx is right - request only the columns you need.
__________________
Have I made your day? If so, please show your appreciation either by sending money  or the cheaper, preferred option of clicking on the scales icon at the top right of this posting. My reputation needs all the help it can get. 
|
|
#8
May 6th, 2005, 01:00 AM
|
||||
|
||||
|
Paths, Using MapPath
Consider using the MapPath method instead of literal paths in ASP applications. The ASP Server.MapPath method allows you to physically relocate an ASP application without recoding scripts. This saves program modification and maintenance effort.
Note: Performance is affected slightly, because every time you use MapPath in a script, IIS must retrieve the current server path. Consider placing the result of the method call in an application variable in order to avoid retrieving the server path. Code:
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
FilePath = Server.MapPath("myDb.mdb")
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";"
objConn.Open strConn
|
|
#9
May 6th, 2005, 01:05 AM
|
||||
|
||||
|
Select Case Statement
For readability and efficiency, use the Select Case statement in place of If&Else in order to repeatedly check for the same variable for different values. For example:
Code:
<%
Dim dat
dat=WeekDay(Date)
Select Case dat
case 1
response.write("Today is Sunday")
case 2
response.write("Today is Monday")
case 3
response.write("Today is Tuesday")
case 4
response.write("Today is Wednesday")
case 5
response.write("Today is Thursday")
case 6
response.write("Today is Friday")
case 7
response.write("Today is Saturday")
end select
%>
Last edited by bslintx : May 8th, 2005 at 08:02 PM. Reason: had invalid var..replaced with an easer example to follow...thanks splinters!
|
|
#10
May 6th, 2005, 01:17 AM
|
||||
|
||||
|
Spaces in Scripts
To enhance script readability, use spaces before and after operators, such as plus (+), minus (-), and equal (=).
Example: Code:
intYearsService = intYearCurrent — intYearFirst
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
