build view 1

[johnny45]

I'm using th efololowing SQL:

SELECT OESHDT.CUSTOMER, OESHDT.ITEM, OESHDT.YR, OESHDT.PERIOD, OESHDT.TRANDATE, OESHDT.DAYENDSEQ, OESHDT.TRANSSEQ, OESHDT.LINENO, OESHDT.TRANNUM, OESHDT.ORDDATE, OESHDT.SHIPDATE, OESHDT.SALESPER, OESHDT.QTYSOLD, OESHDT.SCSTSALES, OESHDT.FAMTSALES, OESHDT.SAMTSALES, OESHDT.FRETSALES, OESHDT.SRETSALES, OESHDT.PONUMBER, OESHDT.FAMTTRAN, OESHDT.SAMTTRAN, OESHDT.FAMTDISC, OESHDT.SAMTDISC, ARCUS.CUSTTYPE, OEINVH.TERMS, ARRTA.TEXTDESC, ICITEM.ALTSET, 'ICITEM.DESC', ICITEM.CATEGORY, ICITEM.CNTLACCT, ICITEM.STOCKITEM
FROM ICITEM INNER JOIN (((ARCUS INNER JOIN OESHDT ON ARCUS.IDCUST = OESHDT.CUSTOMER) INNER JOIN OEINVH ON OESHDT.ORDNUMBER = OEINVH.ORDNUMBER) INNER JOIN ARRTA ON ARCUS.CODETERM = ARRTA.CODETERM) ON ICITEM.ITEMNO = OESHDT.ITEM
ORDER BY OESHDT.CUSTOMER, OESHDT.SALESPER, OESHDT.PONUMBER

I run this query from Aqua Data Studio and ...well the query is still runnuing.......

I tried creating a view with this code:

Sub Create_View() 
    Dim conn As ADODB.Connection 
    Set conn = CurrentProject.Connection 
    On Error GoTo ErrorHandler 
    conn.Execute "CREATE VIEW vw_com AS "& above sql

ExitHere: 
    If Not conn Is Nothing Then 
        If conn.State = adStateOpen Then conn.Close 
    End If 
    Set conn = Nothing 
    Exit Sub 
ErrorHandler: 
    If Err.Number = -2147217900 Then 
        conn.Execute "DROP VIEW vw_com" 
        Resume 
    Else 
        Debug.Print Err.Number & ":" & Err.Description 
        Resume ExitHere 
    End If 
End Sub

and I get an error...

The reason for trying to build a view is to cut down on the query time......(the query is still running from Aqua Data Studio over 10 minutes)

Any sugestions on this ?

 

[mirtheil]

I've never used Aqua Data Studio. How long does the query take when running though the Pervasive Control Center or ODBC Test?

What error do you get when creating the view?

A quick look at the query shows that it might not be optimized and might be creating a temp file or performing a table scan (reading all of the records in all of the tables).

I would suggest generating a Query Plan and then running the Query Plan Viewer to see what's happening.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[johnny45]

Thank you mirtheil... I will try generating a Query Plan and then running the Query Plan Viewer...