Wednesday, April 22, 2009
Optimizing SQL Queries
Simple Expression Example:
A good rule of thumb when using where/and to query for values put the most restrictive ones first.
SELECT *
FROM Students
WHERE sex = ‘female’
AND grade = ‘A’
The above query would run faster if you checked for the A first because less students will have an Another words
SELECT *
FROM Students
WHERE grade = ‘A’
AND sex = ‘female’
Another Example:
SELECT COUNT(*)
FROM Students
Might not be as fast if student_ID is the primary key
SELECT COUNT(student_ID)
FROM Students
For Strings minimize using % or _ in LIKE statements another words don’t use LIKE ‘%Tom%’ if LIKE ‘Tom%’ is all you really need.
Carefully chosen indexes speed up SQL queries without slowing updates too much. Almost all tables can benefit from an index, and experience has shown that the "ideal index" is almost never the primary key (even though a primary key index may be required to preserve referential integrity).
Any way just a few ideas. http://www.ebooksquad.com/2008/10/28/optimizing-sql-queries-over-text-databases.html has a free ebook download which discusses Optimizing SQL Queries in more detail.
Tuesday, April 21, 2009
Creat a ColdFusion collection with the cfcollection tag
On occasion a developer may not have access to ColdFusion Administrator, for example, if you use a virtual hosting company. If you want your ColdFusion application to be able to create, delete, and maintain a collection you will need to use the cfcollection tag. The following will show you how to create a collection index it then index it to include a MS Access database this code works great for use with a serch function for your shopping cart site. You will need to create an Access database with a table named Products and you will need the following fields ItemID, ProductID, ProductName, BriefDescription, Details.
To create a simple collection form page:
Create a ColdFusion page with the following content:
[html]
[head]
[title]Collection Creation Input Form[/title]
[/head]
[body]
[h2]Specify a collection[/h2]
[form action="collection_action.cfm" method="POST"]
[!--- The following can be changed to your needs ---]
[p]Collection name:mycart
[/p]
[p]What do you want to do with the collection?[/p]
[input type="radio"
name="CollectionAction"
value="Create" checked]Create[br]
[input type="radio"
name="CollectionAction"
value="Repair"]Repair[br]
[input type="radio"
name="CollectionAction"
value="Optimize"]Optimize[br]
[input type="radio"
name="CollectionAction"
value="Delete"]Delete[br]
[input type="submit"
name="submit"
value="Submit"]
[/form]
[/body]
[/html]
Save the file as collection_form.cfm in the web root directory.
To create a simple collection form action page:
Create a ColdFusion page with the following content:
[html]
[head]
[title]cfcollection[/title]
[/head]
[body]
[h2]Collection creation[/h2]
[cfoutput]
[cfswitch expression=#Form.collectionaction#]
[cfcase value="Create"]
[cfcollection action="Create"
collection="mycart"
[!--- Replace with the path to your web root directory ---]
path="D:\Inetpub\www\"]
[p]The collection mycart is created.
[/cfcase]
[cfcase value="Repair"]
[cfcollection action="Repair"
collection="mycart"]
[p]The collection mycart is repaired.
[/cfcase]
[cfcase value="Optimize"]
[cfcollection action="Optimize"
collection="mycart"]
[p]The collection mycart is optimized.
[/cfcase]
[cfcase value="Delete"]
[cfcollection action="Delete"
collection="mycart"]
[p]Collection mycart deleted.
[/cfcase]
[/cfswitch]
[/cfoutput]
[/body]
[/html]
Save the file as collection_action.cfm in the web root directory.
In the web browser, enter the following URL to display the form page:
http://www.yourserver.com/collection_form.cfm
Verify that Create is selected and submit the form.
You successfully created a collection, named mycart. Now we have to index it.
Indexing a collection using the cfindex tag
You can index a collection using the cfindex tag, which eliminates the need to use the ColdFusion MX Administrator.
Create a ColdFusion page with the following content:
[html]
[head]
[title]Creating Index[/title]
[/head]
[body]
[h2]Indexing Complete[/h2]
[cfindex collection="mycart"
action="refresh"
extensions=" .mdb, .htm, .html, .xls, .txt, .mif, .doc"
[!--- Replace with the path to your web root directory ---]
key="D:\Inetpub\www\"
type="path"
urlpath="D:\Inetpub\www\"
recurse="Yes"
language="English"]
[cfoutput]
The collection mycart has been indexed.
[/cfoutput]
[/body]
[/html]
Save the file as collection_index_action.cfm in web_root.
In the web browser, enter the following URL to display the form page:
http://www.yourserver.com/ collection_index_action.cfm
A confirmation message appears upon successful completion.
Adding database information to a CF collection
To add your database information to your collection:
Create a ColdFusion page with the following content:
[html]
[head]
[title]Adding Query Data to an Index[/title]
[/head]
[body]
[!--- retrieve data from the table ---]
[cfquery name="mydata" datasource="mycart"]
SELECT * FROM Products
[/cfquery]
[!--- update the collection with the above query results ---]
[cfindex
query="mydata"
collection="mycart"
action="Update"
type="Custom"
key="ItemID"
title="ProductName"
Custom1="ProductName"
custom2="BriefDescription"
body="ItemID,ProductID,ProductName,BriefDescription,Details"]
[h2]Indexing Complete[/h2]
[!--- output the record set ---]
[p]Your collection now includes the following items:[/p]
[cfoutput query="mydata"]
[p]#ItemID# #ProductID# #ProductName# #BriefDescription# #Details#[/p]
[/cfoutput]
[/body]
[/html]
To create a simple collection form action page:
Create a ColdFusion page with the following content:
[html]
[head]
[title]Verity Search Results[/title]
[meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"]
[/head]
[body]
[cfsearch name="mydata"
collection="mycart"
criteria="#form.criteria#"]
[strong]Your Results[/strong]
[cfoutput query="mydata"]
[cfif URL IS ""]
[p][a href="http://www.yourserver.com/index.cfm?action=ViewDetails&ItemID=#key#"]#title#[/a][br]
[/p]
[cfelse]
[p][a href="http://www.yourserver.com/index.cfm?Action=ViewCategory&Category=9"][/a][br]
[/p]
[/cfif]
[/cfoutput]
[/body]
[/html]
Friday, April 10, 2009
Calling a flex Remote Object with Actionscript
Our .AS file
package model
{
import mx.rpc.remoting.RemoteObject;
import mx.core.Application;
public dynamic class ResultsRO extends RemoteObject
{
private var _appUrl:String;
private var domainUrl:String;
private var app:Application = Application.application as Application;
public function ResultsRO (destination:String=null)
{
super(destination);
if (this.app.url) {
this.appUrl = app.url;
} else {
this.app.callLater(autoSetUrl);
}
this.makeObjectsBindable=true;
}
private function autoSetUrl():void {
if (this.app.url) {
this.appUrl = app.url;
} else {
this.app.callLater(autoSetUrl);
}
}
private function set appUrl(xAppUrl:String):void {
this._appUrl = xAppUrl;
var stripDomainFromURL:RegExp = new RegExp("http://[^/]*/","i");
domainUrl = stripDomainFromURL.exec(this._appUrl)[0];
this.source = "yourPath.CFC.YourCFC";
}
}
}
Add a Bindable var to call it
[Bindable]public var YourFileRO:d yourFileRO = new YourFileRO("ColdFusion");
Add a fault catcher
public function onServiceFault(event:FaultEvent):void{
trace("onServiceFault()");
CursorManager.removeBusyCursor();
Alert.show(event.fault.faultDetail,event.fault.faultString);
}
Our event listenrs
this. YourFileRO.retrieve YourFileRO Data.addEventListener('result',recieve YourFileRO Data);
this. YourFileRO.addEventListener(FaultEvent.FAULT,onFault);
Wednesday, April 08, 2009
Nice Flex export to Excel ActionScript file
import flash.external.*;
if(dg.columns[i].headerText == "Origin Carriers"){
" click="loadDGInExcel(carrierRes)" fontSize="9" color="0x5970a0" right="4"/>
Wiredwizrd
Morgan Todd Lewistown, PA |
Experienced Information Technology Manager with a strong knowledge of technical guidance, IT best practices, security protocols, team leadership, and analyzing business requirements. |