|
Ok, I am racking my head over how to efficently do this in Sql Server 2000. This code is going slow.......very slow.......and direction would be appreciated.
dim rs1
dim rs2
dim cn
dim sSql
dim cmd
' instantiate the ADO objects
set cn = CreateObject("ADODB.Connection")
cn.Open = "Provider=SQLOLEDB;Data Source=(Local);Initial Catalog=Ave;User ID=sa;Password=bigmak10"
'Find distinct express numbers in sales table
sSql = "SELECT DISTINCT EXPRESS FROM SMART_SALES ; "
set rs1 = CreateObject("ADODB.Recordset")
rs1.open sSql , cn
do until rs1.eof
sSql = "SELECT SMART_SALES.EXPRESS, SMART_FUND_LIST.[Fund Long Name], SUM(SMART_SALES.FY_SALES) AS GROSS_SALES "& _
"FROM SMART_SALES INNER JOIN "& _
"SMART_FUND_LIST ON SMART_SALES.FUND_NUM = SMART_FUND_LIST.[Fund TA Fund Num] "& _
"GROUP BY SMART_SALES.EXPRESS, SMART_FUND_LIST.[Fund Long Name] "& _
"HAVING (SUM(SMART_SALES.FY_SALES) > 5000) AND (SMART_SALES.EXPRESS = '" & rs1("express").value & "') "& _
"ORDER BY SUM(SMART_SALES.FY_SALES) DESC "
set rs2 = CreateObject("ADODB.Recordset")
rs2.Open sSql, cn
i = 0
Do Until rs2.EOF
i = i + 1
set cmd = createobject("ADODB.COMMAND")
cmd.activeconnection = cn
cmd.commandtype = adCmdText
cmd.commandtext = "insert into SMART_SALES_EXPRESS_FUND ( express, [Fund Long Name], Gross_sales, rank ) "& _
"values ('" & rs1("express").value & "', '" & rs2("Fund Long Name").value & "', " & rs2("Gross_sales").value & ", " & i & " ) "
cmd.execute, adAsyncExecute
set cmd = nothing
rs2.movenext
Loop
rs2.Close
Set rs2 = Nothing
rs1.movenext
loop
rs1.close
set rs1 = nothing
cn.close
set cn = nothing
Main = DTSTaskExecResult_Success
End Function
| 
