Wednesday, April 22, 2009

Optimizing SQL Queries

From simple expressions to string expressions or even in joins there are some simple yet effective things you can do to make you SQL Queries run a bit faster.

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

THE ACTION SCRIPT
// ActionScript file
      import mx.controls.Alert;
      import mx.core.UIComponent;
      import mx.core.Container;
      import mx.events.ItemClickEvent;
      import mx.utils.ObjectProxy;
      import flash.errors.*;
      import flash.events.*;
     

import flash.external.*;           
      import flash.net.URLLoader;
      import flash.net.URLVariables;
      import flash.net.URLRequest;
      import mx.controls.DataGrid;


       
        //The location of the excel export file
        public var urlExcelExport:String = "./upload/excelexport.cfm";
 


        private function convertDGToHTMLTable(dg:DataGrid):String {
            //Set default values
            var font:String = dg.getStyle('fontFamily');
            var size:String = dg.getStyle('fontSize');


            var str:String = '';
            var colors:String = '';
            var style:String = 'style="font-family:'+font+';font-size:'+size+'pt;"';                       
            var hcolor:Array;
           
            //Retrieve the headercolor
            if(dg.getStyle("headerColor") != undefined) {
                  hcolor = [dg.getStyle("headerColor")];
            } else {
                  hcolor = dg.getStyle("headerColors");
            }                      
           
            //Set the htmltabel
            str+= '<table border="1" width="'+dg.width+'"><thead><tr width="'+dg.width+'">';
           
            //Set the tableheader data (retrieves information from the datagrid header                     
            for(var i:int = 0;i<dg.columns.length;i++) {
                  colors = dg.getStyle("themeColor");
                 
                  if(dg.columns[i].headerText == "Action"){
                        // skip it
                  }else

if(dg.columns[i].headerText == "Origin Carriers"){
                        // skip this one too
                  }else if(dg.columns[i].headerText == "Destination Carriers"){
                        // skip this one too
                  }else if(dg.columns[i].headerText == "Carrier Phone"){
                        // skip this one too
                  }else{
                        if(dg.columns[i].headerText != undefined) {
                        str+="<th "+style+">"+dg.columns[i].headerText+"</th>";
                        } else {
                              str+= "<th "+style+">"+dg.columns[i].dataField+"</th>";
                        }
                  }    
            }
            str += "</tr></thead><tbody>";
            colors = dg.getStyle("alternatingRowColors");
            //Loop through the records in the dataprovider and
            //insert the column information into the table
            for(var j:int =0;j<dg.dataProvider.length;j++) {                             
                  str+="<tr width=\""+Math.ceil(dg.width)+"\">";
                       
                  for(var k:int=0; k < dg.columns.length; k++) {
                       
                        //Do we still have a valid item?                                 
                        if(dg.dataProvider.getItemAt(j) != undefined && dg.dataProvider.getItemAt(j) != null) {
                             
                              if(dg.columns[k].labelFunction != undefined) {
                                    str += "<td width=\""+Math.ceil(dg.columns[k].width)+"\" "+style+">"+dg.columns[k].labelFunction(dg.dataProvider.getItemAt(j),dg.columns[k])+"</td>";
                                   
                              }else {
                                   
                                    str += "<td width=\""+Math.ceil(dg.columns[k].width)+"\" "+style+">"+dg.dataProvider.getItemAt(j)[dg.columns[k].dataField]+"</td>";
                              }
                        }
                  }
                  str += "</tr>";
            }
            str+="</tbody></table>";
       
            return str;
        }
       
            /**
             * offers the excel download to the user.
             * @params: dg Datagrid The Datagrid that will be loaded into Excel
             */
            private function loadDGInExcel(dg:DataGrid):void {

                  //Pass the htmltable in a variable so that it can be delivered
                  //to the backend script
                  var variables:URLVariables = new URLVariables();
                  variables.htmltable     = convertDGToHTMLTable(dg);
                 
                  //Setup a new request and make sure that we are
                  //sending the data through a post
                  var u:URLRequest = new URLRequest(urlExcelExport);
                  u.data = variables;
                  u.method = URLRequestMethod.POST;

                  //Navigate to the script
            navigateToURL(u,"_blank");
        }      

MY COLDFUSION PAGE
<cfset thsNum = randRange(50,999)>
<cfset thsNum2 = randRange(50,999)>
<cfset thsFile = "PCMSExport" & thsNum & thsNum2 & ".xls">
<cfcontent type="application/msexcel">
<cfheader name="Content-Disposition" value="filename=#thsFile#">
<cfoutput>
#form.htmltable#
</cfoutput>

AND ADDED TO MY MXML
I HAVE A DATAGRID CALLED carrierRes
include "excel.as";
<mx:Button label="Export To Excel

" 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.
Google