sql - Coldfusion 从第二个数据源获取数据

Question

我有一个从第二个数据源获取数据的查询。这是非常缓慢的。有没有更有效的方法来做到这一点？

我认为慢的部分是 ListQualify。它会生成一个包含 40,000 个值的列表。但是我不确定另一种方法。

<cfif GetExtractionData.recordcount gt 0 >

            <cfquery name="queryVPOCar" datasource="Web_Applications">
                Select [strStockNumber] as "STOCK",
                        field1 as "field_1",
                        field2 as "field_2",
                        field3 as "field_3",
                        field4 as "field_4",
                        field5 as "field_5",
                        field6 as "field_6"
                From ExtractionAggregates
                WHERE [strStockNumber] IN (#ListQualify(StockList,"'",",","ALL")# )
            </cfquery>

        <!--- Select an empty Row --->
        <cfquery name="joinQuery" dbtype="query" >
            SELECT *
            FROM queryVPOCar
            WHERE queryVPOCar.STOCK = ''
        </cfquery>

        <!--- Add empty row to table, so we have column names  ---> 
        <cfset QueryAddRow(joinQuery) />

        <!--- if results exist, add them to the query --->
        <cfif queryVPOCar.recordcount gt 1 >
            <cfquery name="GetExtractionData" dbtype="query">

                SELECT * FROM 
                GetExtractionData, queryVPOCar
                WHERE GetExtractionData.STOCK = queryVPOCar.STOCK

                UNION 

                SELECT GetExtractionData.*, joinQuery.*
                FROM GetExtractionData, joinQuery
                WHERE GetExtractionData.STOCK  NOT IN (#ListQualify(ValueList(queryVPOCar.STOCK),"'",",","ALL")# ) 

                ORDER BY STOCK 
            </cfquery>
        </cfif>

    </cfif>

Answer 1

更好的方法是使用 sql server 来处理尽可能多的数据库工作。第 1 步将安装 linked server到您的 sql server 上的 oracle 数据库。第 2 步是编写一个存储过程:

• 使用 openquery从 oracle 获取数据并将该数据放入sql server 数据库的临时表。
• 从加入临时表的 sql server 数据库表中获取数据

第 3 步是从 ColdFusion 调用存储过程并显示结果。