Faster way to retrieve this data set?

[grtammi]

Hey everyone,

Wasn't sure where to put this question, so I figured this topic may suit it the best.

Here's the situation: I have a table (table x) with fields name, phone, recnum, product, etc etc etc. recnum is PK (autonumber, etc) in this table.

hmm ... on second thought, here's a visual of "sample data" that this table may contain:

name          phone          product       recnum

bob           1234567        Acme-X        4
bob           1234567        Acme-Y        51
bill          1239876        Acme-Z        120
bob           1234567        Jelly Beans   8

So, as you can see above, name / phone / product may be duplicated in records.

What I am looking to do with the above is have each name / phone combination display on screen, and then all the products / other fields listed underneath, such as this:

bob           1234567        
   - Acme-X        
   - Acme-Y        
   - Jelly Beans   
bill          1239876        
   - Acme-Z

What I am doing right now is this:

1) Using a "SELECT DISTINCT ..." statement, I am pulling a distinct set of records from the database containing ONLY a unique name and phone number combination.

2) I am populating a Treeview control with this data as the parent nodes in the tree.

3) I am then running another SELECT statement against the same table to pull all the product transactions from it, and am posting those as child nodes to the parent nodes above.

However, we are upgrading all of our databases to SQL Server (above was an Access platform), and are also moving the front ends to a web-based solution (don't ask ... long story), and I don't think that the above solution I am using is the fastest method of retrieving data.

Soooo .... I guess what I'm really asking is this: Can anyone suggest a faster method for retrieving this data? Or, even better, how about an alternative method that doesn't use a Treeview control, but maybe a constructed Tree structure in say ASP or PHP?

Thanks,

Greg

 

[johnwm]

Try the SHAPE keyword in MSSQL Books on Line:

http://www.microsoft.com/sql/

techinfo/productdoc/2000/books.asp

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[grtammi]

I have been playing with the data shaping (found info for it on 4guysfromrolla.com) ... seems like a messy syntax, but anyways ...

Does it work in PHP? I would prefer to use PHP over ASP if I could, as I am a stronger programmer with PHP.

PS: Love the signature ... A fingers ... heh ... I'm a nerd.

Greg

"for me, the action is the juice.

 

[ceh4702]

Might be nice to know if all the info is in the same table and how the database structure is set up. This looks like it might be like an order table.

Microsoft Simplified Shape Syntax (Is this simple?)

SHAPE {parent-statement}
APPEND Aggregate
| ({child-statement} [As Alias]
RELATE parent-field TO child-field | parameter-marker
[, parent-field TO child-field | parameter-marker ...])
[, Aggregate | ({child statement})...]

SHAPE {non-normalized-statement} [AS Alias]
COMPUTE Aggregate
| Alias
| ({child-statement} [As Alias] RELATE parent-field TO
child-field | parameter-marker)
[, Aggregate | Alias | ({child-statement}...)]
[BY grouping-field [, grouping-field]]

SHAPE {non-normalized-statement} [AS Alias]
BY grouping-field [, grouping-field]

Looks like fun....


If you do not like my post feel free to point out your opinion or my errors.

 

[grtammi]

I understand data shaping, and your post will be of help to those that can use it.

However, my question is whether or not data shaping can be used in conjunction with PHP or not. I prefer to use PHP for all my database related issues now, instead of pulling a separate recordset for each user id passed.

As per your request, here is a code snippet used in the current Access module to pull data and load it into a TreeView control:

Public Sub WriteTree()

On Error GoTo Tree_Error

' ************************************************************************
' subroutine writes out landline service information for the customers
' ************************************************************************
' ADO variable declarations and sets
    Dim conn As ADODB.Connection: Set conn = CurrentProject.Connection
    Dim rs As ADODB.Recordset:    Set rs = CreateObject("ADODB.Recordset")
    Dim rsPromo As ADODB.Recordset: Set rsPromo = CreateObject("ADODB.Recordset")
' miscellaneous variable declarations
    ' declare and initialize variables
    Dim tSQL As String: tSQL = ""
    Dim treSQL As String
    Dim sKeyString As String
    Dim sDisplay As String
    Dim sNameLookup As String: sNameLookup = "tLookup('[name]', 'tblCustomers', '[id]=' & id)"
    ' get sales person id, if selected
    If cmbSales = "" Or IsNull(cmbSales) Then
        SalesPaid = ""
    Else
        SalesPaid = " AND salesperson=" & cmbSales.Column(0)
    End If
' -----------------------------------------------
' set up SQL string to retrieve data, based upon user selections
    ' does user want express vu transactions?
    If chkBev = True Then
        If Not tSQL = "" Then
            tSQL = tSQL & " UNION "
        End If
        tSQL = tSQL & "SELECT DISTINCT id, phone_num, " & sNameLookup & " AS cName FROM tblBEV WHERE trans_date " _
            & "BETWEEN #" & dteStart & "# AND #" & dteEnd & "#" & SalesPaid
    End If
    ' does user want feature transactions?
    If chkLLF = True Then
        If Not tSQL = "" Then
            tSQL = tSQL & " UNION "
        End If
        tSQL = tSQL & "SELECT DISTINCT id, phone_num, " & sNameLookup & " AS cName FROM tblLLFeature WHERE trans_date " _
            & "BETWEEN #" & dteStart & "# AND #" & dteEnd & "#" & SalesPaid
    End If

' more code goes here ....

' clear existing tree nodes
    Me!treCust.Nodes.Clear
' -----------------------------------------------
' open recordset
    rs.Open tSQL, conn
' ************************************************************************
' the add method used below utilizes the following syntax
' <treeview control name>.Nodes.Add 1, 2, 3, 4 where:
' 1 = identifying key node back to the parent (relates to #3 of parent node); if parent, leave blank
' 2 = type of node being added ; blank = parent, tvwChild = child node.
' 3 = unique key name for this node - usually use primary key of recordset
' 4 = text description of node (what user sees)
' I haven't yet found anything good to describe this tree node thingy, so this
' is what I've come up with - seems to work
' -----------------------------------------------
' write parent nodes out to tree, using the phone number as the key value,
' and display the customer phone number and name as node text
    While Not rs.EOF
        ' set node key string
        sKeyString = CStr(rs(1))
        ' set node display text
        sDisplay = rs(1) & " :: [ " & rs("cName") & " ]"
        ' add node
        Me!treCust.Nodes.Add , , "node" & sKeyString, sDisplay
        rs.MoveNext
    Wend
    rs.Close
' -----------------------------------------------
' are we retrieving Feature Transactions?
    If chkLLF = True Then
        ' get all landline feature transactions for each customer, and write to the tree (prefix: LLF)
        treSQL = "SELECT id, trans_date, salesperson, feature, order_num, ATScomm, phone_num, ATSStatus, recNum FROM " _
            & "tblLLFeature WHERE trans_date BETWEEN #" & dteStart & "# AND #" & dteEnd & "#" & SalesPaid
        ' determine if we're restricting these records or not
        treSQL = treSQL & GetRestrictions("tblLLFeature", sTreeClause)
        ' open recordset
        rs.Open treSQL, conn
        ' iterate recordset
        While Not rs.EOF
            ' set node key string
            sKeyString = "LLF," & rs(6) & "," & rs(0) & "," & rs(1) & "," & rs(2) & "," & rs(3) & "," & rs(5) & "," & rs(8)
            ' set node display text
            sDisplay = "LLF: " & rs(1) & " -> " & rs(3) & " for: $" & CCur(rs(5))
            ' also display status, if it exists
            If Not IsNull(rs("ATSStatus")) Then
                sDisplay = sDisplay & " :: " & DLookup("[status]", "tblCommStatus", "[id]=" & rs("ATSStatus"))
            End If
            Me!treCust.Nodes.Add "node" & CStr(rs(6)), tvwChild, sKeyString, sDisplay
            rs.MoveNext
        Wend
        rs.Close
    End If

' pretty much same code as above for each different transaction type

Confusing? Good. Was for me also.

Anyhow, once again, any assistance would be greatly appreciated.


Greg

"for me, the action is the juice.

 

[gol4]

Since it is not normalized data have you looked into
XML, XSL, XPath.

Just a suggestion.

 

[grtammi]

Thanks for the reply gol4.

That's what I was thinking about using, but I really don't understand the XML technology "suite".

Anyone know of any good sites that have tutorials for pulling data from a database and transforming to an XML file?


Greg

"for me, the action is the juice.

 

[gol4]

look at c#(or VB.net) and XmlReader. If you have no knowledge of XML at all then this is one site that will get you started.

http://www.w3schools.com/default.asp

 

[harebrain]

Moving to SQL Server, you say? You can use the "output for XML" clause in "select" statements.

 

[johpje]

Why don't you just execute

SELECT [name], phone, product FROM TableX ORDER BY [name], phone

Then when you fill whatever visual control you are using, just check when name and/or phone number change to create another parent object.

regards,
Johan

 

[TimBiesiek]

3 words....

Normalize, Normalize, Normalize!!!!!!!!!!!!!!!!!!!!!!!!