MySQL version of Matt Chotin’s Large Data Sets SQL (Part 2 of 2)
	by Niklas Richardson 4:23 pm Thursday, 26 August 2004.

Yesterday I posted an entry on converting the SQL used in Matt Chotin’s second article on Large Data Sets in Flex to work with MySQL. Today I post the second lot of SQL. Enjoy!

Each block (from yesterday and today) takes about 2.5 seconds to run on MySQL. That is, creating the temporary table, inserting 16,383 sorted records, doing a select and then dropping the table. Now, this performance doesn’t sound great, however I haven’t tried it on SQL Server. I guess we’ll have to wait to MySQL 5.0 to get stored procedures to see if that speeds it up!

MSSQL Stored Proc

Create Procedure sp_GetSortedCensusData
(
@Begin int,
@End int,
@SortSQL varchar(100)
)
As

SET NOCOUNT ON

CREATE TABLE #TempTable
(
rownum int IDENTITY,
age smallint NULL,
classofworker varchar(100) NULL,
education varchar(100) NULL,
maritalstatus varchar(100) NULL,
race varchar(100) NULL,
sex varchar(10) NULL,
id int
)

DECLARE @TempSQL varchar(5000)
SELECT @TempSQL = 'INSERT INTO #TempTable '+
'SELECT age, classofworker, education, maritalstatus, race, sex, id FROM censusincome ORDER BY ' + @SortSQL
EXECUTE(@TempSQL)

SELECT age, classofworker, education, maritalstatus, race, sex, id
FROM #TempTable
WHERE rownum >= @Begin AND rownum < @End ORDER BY rownum ASC

SET NOCOUNT OFF

MySQL

CREATE TEMPORARY TABLE temptable
(
age int(11) default NULL,
classofworker varchar(255) default NULL,
education varchar(255) default NULL,
maritalstatus varchar(255) default NULL,
race varchar(255) default NULL,
sex varchar(255) default NULL,
id int(11) unsigned NOT NULL,
rownum int(11) unsigned NOT NULL auto_increment,
PRIMARY KEY  (rownum)
);

INSERT INTO temptable (age, classofworker, education, maritalstatus, race, sex, id)
SELECT age, classofworker, education, maritalstatus, race, sex, id
FROM censusincome
ORDER BY @SortSQL;

SELECT rownum
FROM temptable
WHERE rownum >= @Begin
AND rownum < @End
ORDER BY @SortSQL;

DROP TABLE temptable;

Comments (1)

---

	Free Seminar : September 30th
	by allen 10:11 am .

As Web developers we often need to worry about displaying and managing large amounts of data. Working in an enterprise environment this is often the case – companies frequently have tens of thousands of business objects like contacts, invoices, work orders etc.

Macromedia’s Matt Chotin has given us some insights into ways of working with large amounts of data in Flex. We will apply these concepts and the patterns he uses in a sample Flex and ColdFusion Application.

Outline

1. Architectural Review
2. Explicit Paging
3. Implicit Paging
4. Sorting
5. CFC Implementation Changes and Limits

Ideas and concepts covered

• CFML
• ColdFusion Components
• Flash Remoting
• MXML
• ActionScript 2.0 in Flex
• SQL Optimisation

Background Material

• Prismix Blog - Contact List (Exploring Large Datasets)
• Matt Chotin’s Blog

Level

Intermediate / Advanced

About The Speaker

Allen Manning, Technical Director, Prismix Ltd.

More information…

Comments (0)

---

	Contact List (Part 1 - Exploring Large Data Sets)
	by allen 6:29 pm Wednesday, 25 August 2004.

Macromedia’s Matt Chotin in his three part series about Large Data Sets in Flex is a informative hands-on tutorial about handling enterprise - level quantities of data between the Flex presentation tier and a services tier. I find Matt’s contributions to both his Blog and the Flexcoders email list a tangible contribution to the community. We have started testing his sample code with a ColdFusion Application server and MySQL database on the server side. Later we aim to compare and constrast the results with J2EE and .Net at MX Europe 2005 in January.

ColdFusion lacks many of the fully OOP features that we would like to see, but it has been traditionally rapid in knocking out web applications. In particular CF lacks polymorphism and the clear seperation between interface and implementation. That being said, with the release of ColdFusion MX developing Web Services became extremely easy to rapdily prototype. We continue to use ColdFusion, but more and more question the usefulness of a 4GL rapid Java develelopment environment against just J2EE direct - or .NET I’m looking forward to seeing if the Blackstone seminar at the UKCFUG tomorrow addresses many of these concerns. But I feel that it may just be the classic trade-off between rapid protyping and robustness of architecture.

We have knocked out a first take at the CensusDataService object that we will be later using as a guide for integrating into our own application.

First steps are to get Matt’s examples running in our current environment. Then, we would like to integrate these patterns into our Contacts area, where the user will be managing possibly thousands of contacts and companies - CRM - style. Finally, we would like to explore new ways of improving the Contacts interface through many well known Outlook paradigms, in particular inline searching and filtering.

Below is a snippet which is our CFC (very slow and being improved with some nice SQL from Nik ) implementation of Matt’s ValueList and SortedValueList services. We will be exploring these concepts at a Free seminar on Flex and ColdFusion next month.

<cfcomponent displayname="CensusService" hint="">

<!---

Used for testing paging

--->
<cffunction name="getElements" access="remote">
<cfargument name="begin" required="yes">
<cfargument name="count" required="yes">
<cfargument name="sortField" required="no" default="">
<cfargument name="sortAscending" required="no" default="">

<cfset results = arrayNew(1)>

<cfquery name="qGetCensus" datasource="CensusData">
SELECT
id, age, classofworker, education, maritalstatus, race, sex
FROM
censusincome
WHERE
id > #arguments.begin#
AND
id <= #arguments.count# + #arguments.begin#

<!---

Needs to be cleaned up...

--->
<cfif len(arguments.sortField) AND len(arguments.sortAscending)>
Order By
#arguments.sortField# #iif(arguments.sortAscending,de('ASC'),de('DESC'))#
</cfif>
</cfquery>

<cfreturn qGetCensus>
</cffunction>

<cffunction name="getNumElements" access="remote">
<cfquery name="qGetNumRows" datasource="CensusData">
SELECT
id
FROM
censusincome
</cfquery>
<cfreturn qGetNumRows.recordCount>
</cffunction>

<!---
/**
* Return a range of elements sorted in the order specified
* @param begin the position of the first element to return
* @param count how many elements to return
* @param sortField the field on which to sort
* @param sortAscending true if the sort should be in ascending order
* @return a sorted List of elements
*/
--->
<cffunction name="getSortedElements" access="remote">

<cfargument name="begin">
<cfargument name="count">
<cfargument name="sortField" required="no" default="id">
<cfargument name="sortAscending" required="no" default="">

<!--- 

Build a temporary sorted table which has a new rownum primary key column,
then insert sorted data into temp table.  Use the new rownum column to obtain
the specified 'page'.

--->

<cfquery name="qTemp" datasource="CensusData">
CREATE TEMPORARY TABLE temptable
(
age int(11) default NULL,
classofworker varchar(255) default NULL,
education varchar(255) default NULL,
maritalstatus varchar(255) default NULL,
race varchar(255) default NULL,
sex varchar(255) default NULL,
id int(11) unsigned NOT NULL,
rownum int(11) unsigned NOT NULL auto_increment,
PRIMARY KEY  (rownum)
);
</cfquery>

<cfquery name="qTemp" datasource="CensusData">
INSERT INTO temptable (age, classofworker, education, maritalstatus, race, sex, id)
SELECT age, classofworker, education, maritalstatus, race, sex, id
FROM censusincome
<cfif len(arguments.sortField) AND len(arguments.sortAscending)>
ORDER BY
#arguments.sortField# #iif(arguments.sortAscending,de('ASC'),de('DESC'))#
</cfif>
</cfquery>

<cfquery name="qGetOrderedCensus" datasource="CensusData">
SELECT *
FROM temptable
WHERE rownum >= #arguments.begin#
AND rownum < #arguments.begin# + #arguments.count#
<cfif len(arguments.sortField) AND len(arguments.sortAscending)>
ORDER BY
#arguments.sortField# #iif(arguments.sortAscending,de('ASC'),de('DESC'))#
</cfif>
</cfquery>

<cfquery name="qTemp" datasource="CensusData">
DROP TABLE temptable;
</cfquery>
<cfreturn qGetOrderedCensus>
</cffunction>

<!---
/**
* Return the 0-based index of the passed in element if a List were sorted according to the parameters
* @param searchObject the element we're searching for
* @param sortField the field on whcih to sort
* @param sortAscending true if the sort should be in ascending order
* @return the 0-based index of the searchObject
*/
--->
<cffunction name="getSortedElementPosition" access="remote">
<cfargument name="searchObject">
<cfargument name="sortField">
<cfargument name="sortAscending">

<!--- 

Build a temporary sorted table which has a new rownum primary key column,
then insert sorted data into temp table.  Use the new rownum column to obtain
the location of the selected object.

ToDo: can we reuse the other getSortedElements bit here?
--->
<cfquery name="qTemp" datasource="CensusData">
CREATE TEMPORARY TABLE temptable
(
age int(11) default NULL,
classofworker varchar(255) default NULL,
education varchar(255) default NULL,
maritalstatus varchar(255) default NULL,
race varchar(255) default NULL,
sex varchar(255) default NULL,
id int(11) unsigned NOT NULL,
rownum int(11) unsigned NOT NULL auto_increment,
PRIMARY KEY  (rownum)
);
</cfquery>

<cfquery name="qTemp" datasource="CensusData">
INSERT INTO temptable (age, classofworker, education, maritalstatus, race, sex, id)
SELECT age, classofworker, education, maritalstatus, race, sex, id
FROM censusincome
<cfif len(arguments.sortField) AND len(arguments.sortAscending)>
ORDER BY
#arguments.sortField# #iif(arguments.sortAscending,de('ASC'),de('DESC'))#
</cfif>
</cfquery>

<cfquery name="qGetSortedElementPosition" datasource="CensusData">
SELECT rownum
FROM temptable
WHERE id = #searchObject.id#;
</cfquery>

<cfquery name="qTemp" datasource="CensusData">
DROP TABLE temptable;
</cfquery>

<cfreturn qGetSortedElementPosition.rownum>
</cffunction>
</cfcomponent>

Comments (0)

---

	MySQL version of Matt Chotin’s Large Data Sets SQL (Part 1 of 2)
	by Niklas Richardson 6:12 pm .

Matt Chotin wrote three really great articles on managing large data sets in Flex. Part 2 of his series used MSSQL stored procedures. As we are using MySQL I decided to figure out the SQL for MySQL.

Below is the MSSQL and MySQL version of the SQL. MySQL doesn’t support stored procedures yet, however you can still use these MySQL SQL directly.

I would love to hear your feedback, as I’m not a DBA by trade!

MSSQL Stored Proc

Create Procedure sp_GetSortedCensusDataPosition
(
@SearchId int,
@SortSQL varchar(100)
)
As

SET NOCOUNT ON

CREATE TABLE #TempTable
(
rownum int IDENTITY,
age smallint NULL,
classofworker varchar(100) NULL,
education varchar(100) NULL,
maritalstatus varchar(100) NULL,
race varchar(100) NULL,
sex varchar(10) NULL,
id int
)

DECLARE @TempSQL varchar(5000)
SELECT @TempSQL = 'INSERT INTO #TempTable '+
'SELECT age, classofworker, education, maritalstatus, race, sex, id FROM censusincome ORDER BY ' + @SortSQL
EXECUTE(@TempSQL)

SELECT rownum FROM #TempTable WHERE id = @SearchId

SET NOCOUNT OFF

MySQL

CREATE TEMPORARY TABLE temptable
(
age int(11) default NULL,
classofworker varchar(255) default NULL,
education varchar(255) default NULL,
maritalstatus varchar(255) default NULL,
race varchar(255) default NULL,
sex varchar(255) default NULL,
id int(11) unsigned NOT NULL,
rownumber int(11) unsigned NOT NULL auto_increment,
PRIMARY KEY  (rownumber)
);

INSERT INTO temptable (age, classofworker, education, maritalstatus, race, sex, id)
SELECT age, classofworker, education, maritalstatus, race, sex, id
FROM censusincome
ORDER BY @SortSQL;

SELECT rownumber
FROM temptable
WHERE id = @SearchId;

DROP TABLE temptable;

Comments (2)

---

	CFDUMP for AS2 and Flex
	by allen 4:50 pm Tuesday, 24 August 2004.

As much as we love FlexBuilder, MXML and ActionScript 2.0 as a development platform we continue to run into the age old problem of trying to visulize exactly what data we are working on. So much of debugging seems to be centred around resolving a misconception of what structure of data you are working on. In the world of ActionScript, this is even more the case. Error reporting is less than verbose with most run-time errors failing silently.

Years ago when we ran into this problem with early versions of ColdFusion, I came up with a little utility called CF_DumpStruct(). This simple custom tag takes any ColdFusion struct and dumps a visualisation of the contents into the browser. This improved our need to have a visual understanding of the contents of the structure. Peter Muzila, then lead architect of Spectra, which later became Farcry (not officially of course) expanded on this and made cf_dump. This later became CFDUMP which everyone knows and loves(?) today.

Excuse my digression as I point the reader out to this little class that we are using to visualise our ActionScript objects. Instead of writing our own utility, we have decided to use the NetConnection Debugger and Flash Remoting to visualise it for us. Essentially, we make a fake call with our object out to some unknown remote method and then examine the contents of the call to see what it is we are working on.

It is very simple, but I use it every day. Much easier than examining a log file when dealing with large structures of data and object trees.

Enjoy!

<!-- First establish a stubbed gateway to call somwhere -->
<mx:RemoteObject
id="dumper"
encoding="AMF"
protocol="http"
endpoint="http://mysite/flashservices/gateway"
result=""
fault=""
source="net.ourclient.business.dumper"/>

/*
Then use mangled names on the label to get a visual map of your variable
*/
static function dump(lbl : String, obj) : Void{

 services.dumper['!!DEBUG!!' + lbl](obj);

}

Comments (0)

---

Older Posts »