Query Optimization

1
Query Optimization

• By Rick Mason, SeedChoices.com

2
Just because you can do something in ColdFusion

• Doesnt always mean ColdFusion is the best way to
  do it

3
Always have the database do as much of the work
as possible

• - Ben Forta

4
First step Create Indexes

• Greatest performance boost with the least effort
• MS Access automatically indexes primary keys and
  foreign keys
• SQL Server doesnt you must do it yourself

5
SQL Server indexes

• Clustered
• Non-Clustered

6
Clustered Indexes

• Mostly for tables primary key
• Only one clustered index per table
• Orders tables data in order you specify

7
Non-Clustered Indexes

• You can have multiple non-clustered indexes
• An important tool in query optimization
• All non-clustered indexes go through tables
  clustered index if one is available

8
(No Transcript)
9
(No Transcript)
10
Using SQL Server query analyzer
11
Second step Stored procedures

• What is a stored procedure?
• Pre-compiled SQL statement residing in the
  database
• You can pass parameters in
• You can receive result sets(s) out

12
Why use stored procedures?

• Pre-compile once, use many times
• Stored procedure is a re-usable object
• Reduce network traffic
• Separate business rules and logic from
  presentation layer
• Theyre faster

13
Writing stored procedures

• Create procedurename
• Input parameters
• Output parameters
• AS
• Declare local variables
• SQL statement

14
(No Transcript)
15
Stored procedure example
16
Calling stored procedures Using CFQuery

• ltCFQUERY NAME"DealInv" DATASOURCEMyData"gt
• CALL USP_CompanyProductList('CompanyDistrictID'
  )
• lt/cfquerygt

17
Calling Stored Procedures- Using cfstoredproc

• ltcfstoredproc procedureDealInv
• DatasourceMyDatagt
• ltcfprocparam typeIN
• Cfsql type CF_SQL_Integer
• ValueCompanyDistrictIDgt
• ltcfprocresult nameDealInvgt
• lt/cfstoredprocgt

18
You need to use cfstoredproc

• If you need more than one result set
• Youre using Oracle or DB2

19
Running a stored procedure

• ltcftrygt
• CFQuery or CFStoredProc goes here
• ltcfcatch typeDatabasegt
• Your query produced an error ltbrgt
• ltcfoutputgtCatch.Detaillt/cfoutputgt
• ltcfabortgt
• lt/cfcatchgt
• lt/cftrygt

20
Stored procedure Insert example

• Create Procedure InsPerson
• _at_PersonUserName varchar(20)
• _at_PersonPassword varchar(20)
• AS
• INSERT Into Person(PersonUserName,
  PersonPassword)
• Values(_at_PersonUserName, _at_PersonPassword)
• Select _at__at_Identity AS PersonID

21
What about MS Access?

• CFStoredProc can call stored Access queries and
  pass variables
• CFQUERY CALL will run queries but not let you
  pass variables
• Make sure cfadministrator has execute remote
  procedures turned on for your data source

22
(No Transcript)
23
Calling a stored procedure in Access

• ltcfstoredproc procedure "CustomersByCompany"
  DATASOURCEMyData"gt
• ltcfprocparam type"In" cfsqltype"CF_SQL_INTEGER"
  value'CompanyID'gt
• ltcfprocResult name"CompanyCustomers"gt
• lt/cfstoredprocgt

24
Automating Stored Procedure Creation

• With the Stored Procedure Wizard
• ftp//ftp.productivityenhancement.com/storedprocwi
  z10.zip
• By the folks at ColdFusionTraining.com

25
(No Transcript)
26
(No Transcript)
27
(No Transcript)
28
(No Transcript)
29
(No Transcript)
30
(No Transcript)
31
Additional references

• Using Stored Procedures by Ian Rutherford in CF
  Dev. Journal Oct, Dec 2001 Feb, May
  2002
• Stored Procedures in Access by Charlie Arehart in
  CF Dev. Journal Oct 1999
• MS SQL Server 7 Administrators Guide by Ron
  Talmage ISBN 0-7615-1389-2

32
Contact

• Rick_at_SeedChoices.com