CodeSnip: Increasing Speed of ODBC Queries through Linked Servers
page 1 of 1
Published: 25 Jan 2005
Unedited - Community Contributed
Abstract
This article shows a technique to speed up ODBC queries using linked servers in SQL Server to do a bulk join insert into one reporting table from 2 disparate systems.
by Tim Curtin
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 19265/ 48

Bulk Insert to Reporting Table via Linked Servers

I’m working on a consulting assignment using Crystal Reports 9.2x that uses two database connections. One connects to an Access 2000 database that uses one table of 31,000+ records. The other connects to a CacheInterSystems database via ODBC to a Linux box.

 

The Crystal Report uses eight tables from Cache and one from Access. Many of the tables in Cache are 300,000+ records and require a combination of inner and outer joins with an outer join to the Access table.

 

Several Accounting reports are created from these tables. These reports take over thirty minutes to well over an hour to run. So while waiting for these fiscal reports to run, I investigated an alternative.

 

I used the ODBC connections to create two linked servers via Enterprise Manager in SQL Server. Enterprise Manager requires ‘System’ data sources.

 

CONFIGURE LINKED SERVERS

- Open Enterprise Manager to the Server and open the node for Security.
- Right-click ‘Linked Servers’ and select ‘New Linked Server’.

- Enter a name for the linked server. This is arbitrary.

- Select ‘Other Data Source’ in ‘Server Type’ and select ‘Microsoft OLE DB Provider of ODBD Drivers’.

- Enter the name of the DSN connection in the ‘Data source’ box.

- Click OK.

- Repeat for the Access connection.

 

It would be nice if SQL Server had the capability that Access does to use the designer to build the query via a designer. It dosen’t and you have to create the T-SQL like a pass-through query.  Also, linked servers are not defined with a particular database because they are linked to the server.

 

CREATE THE STORED PROCEDURE

Open the node for your database and drill-down to the node for stored procedures. Right-click and select ‘New Stored Procedure’.

 

OPENQUERY()  is the function that distributes the T-SQL via the ODBC connection to the linked server and represents the return result set (derived table) that should be aliased. It takes two arguments. The first argument is the DSN name.  The second is the linked-server-specific DML statement, written as a literal. I tried to create it dynamically and store the T-SQL in a variable and pass it as the second argument but no dice. Thus we need to do "SELECT fields FROM OPENQUERY(DSNName,’hard-coded sql statement’)."

 

Since I was using annual fiscal data, I did some research for as much data as possible required for the A/R reports and stored it in one table that was well indexed. I also used the OPENQUERY(dsn, ‘T-SQL’) as the alias to establish a join operation between the two linked servers. Incidently, it was also fussy with carriage returns, so paste the T-SQL in without them.  The T-SQL was simplified for obvious reasons; it was significantly longer. The insert took well under thirty minutes to run. The subsequent reports took five or so minutes to run after being well indexed. I also added the code to index the table as well.

 

PASSING PARAMS TO OPENQUERY

Next, the issue of executing the open query statement with params.

Once you have built the full select statement, store the whole statement in a variable and do an EXEC.

 

 

Example DML:

CREATE DoDistribQuery(@StartDate dateTime,@EndDate datetime)

as

Set QUOTED_IDENTIFIER OFF

Declare @SQL varchar(5000)

 

IF EXISTS(SELECT *

  FROM sysobjects

  WHERE  name =’tbl_AR_Statistics_ Report’

  AND type = 'U')

Begin

   DROP TABLE tbl_AR_Statistics_Report

end

 

/*//////// 

    do code to add indexes

    truncate table 'tablename' would probably be much faster

/////////*/

 

set @SQL ="select a.*, b.* Into tbl_AR_Statistics_ Report from OpenQuery(apmlive,'SELECT billing_tx_charge_detail.date_of_service,  billing_tx_charge_detail.v_accounting_period FROM   SYSTEM.billing_tx_history billing_tx_history Order By GUARANTOR_ID'

) as a Left Outer Join( 

    select iD,ServCode,Payment from OpenQuery(bhnacctrpt_access,'Select * From tblPaymentPerService')

) b

On a.ID = b.ID

And a.SERVICECODE = b.SerCode

WHERE B.date_of_service Between " + Cast(@StartDate as VarChar)+ " AND " + Cast(@EndDate as VarChar)

 

 

Exec @SQL  

 

 

 

I subsequently went on to create a job that was scheduled for evening updates.



User Comments

Title: Excellent - Thx...   
Name: SS
Date: 2010-02-06 6:04:15 PM
Comment:
5 years after this article was published and it's still relevant. I just changed a stored proc that I use to extract data from MYOB via ODBC. The SP puts the data into a buffer that the reports then run off. Using OPENQUERY doubled the speed of getting the data (MYOB's ODBC driver is utter crap at the best of times).
Title: Did this ever work?   
Name: JM
Date: 2005-03-10 1:48:11 PM
Comment:
Did your solution ever work? How fast were the reports running after your solution?






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2017 ASPAlliance.com  |  Page Processed at 2017-06-22 12:31:15 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search