Application Note for QuNect ODBC for QuickBase

Optimizing SQL Server OPENQUERY with INSERT, UPDATE and DELETE

If you don't already have a linked server called QUICKBASE, create one using the instructions in the Creating a Linked Server for Quickbase in SQL Server Using the UI application note. The advantage of OPENQUERY is that it passes the SQL statement you supply as its second argument to the linked server you supply as its first argument, directly without modification. You can execute the standard INSERT, UPDATE or DELETE statements against a linked server by using the four part name syntax like this:

        UPDATE QUICKBASE."My Quickbase Application".."b6mpfsxfr" SET checkbox = 1 WHERE checkbox = 0
        

However the performance of this kind of statement is poor because SQL Server first does a complete scan of all rows and columns. The exception is the INSERT statement which performs as well using the four part name syntax or the OPENQUERY syntax. In most cases you can use an INSERT statement to accomplish what an UPDATE statement does. Please read this application note to understand how to use INSERT statements to update existing records. Using an INSERT statement to update records is much faster in the SQL Server environment than using an UPDATE statement. UPDATE statements should only be used when the key field value or values is not known or available. The above UPDATE statement can be passed to Quickbase like this:

        UPDATE OPENQUERY(QUICKBASE, 'SELECT checkbox FROM "b6mpfsxfr" WHERE checkbox = 0') SET checkbox = 1
        

An even higher performance way of doing this uses a the special UPDATEOQ capability of QuNect ODDBC for Quickbase

        SELECT * FROM OPENQUERY(QUICKBASE, 'UPDATEOQ "b6mpfsxfr" SET checkbox = 1 WHERE checkbox = 0')
        

You can also use this technique with DELETE statements.

        DELETE OPENQUERY(QUICKBASE, 'SELECT checkbox FROM "b6mpfsxfr" WHERE checkbox = 0')
        

However you will encounter a spurious error: Cannot fetch a row using a bookmark from OLE DB provider "MSDASQL" Instead you can use the higher performance special DELETEOQ capablility of QuNect ODBC for QuickBase:

        SELECT * FROM OPENQUERY(QUICKBASE, 'DELETEOQ FROM "b6mpfsxfr" WHERE checkbox = 0')
        

Here's an example that allows you to pass in parameters at run time

        DECLARE @DateModifiedText nvarchar(128)
        DECLARE @DateModified datetime
        SET @DateModified = DATEADD(d,-60,Convert(datetime,GETDATE()))
        SET @DateModifiedText = (SELECT CONVERT(VARCHAR(23), @DateModified, 121))
        SET @DateModifiedText = '{ts '''''+ @DateModifiedText + '''''}'
        DECLARE @tempsql nvarchar(max)
        set @tempsql = 'SELECT * from OPENQUERY(QUICKBASE,''DELETEOQ from "bc7sd8jg3" WHERE Application_Loan_Status like ''''%ACTIVE%'''' and Date_Created <' + @DateModifiedText  + ' and Type_of_File Not Like ''''%Notarized%'''' '')'
        EXEC(@tempsql)
        

You can also use this technique with INSERT statements. However INSERT statements are not prone to the performance problems encountered with UPDATE and DELETE statements.

You can also use this technique with JOIN statements to join native SQL Server tables with Quickbase tables.

		SELECT *
		FROM [CRM].[dbo].[Projects] a JOIN
		OPENQUERY(QUICKBASE, 'SELECT * FROM bc45c8xjj WHERE Type = ''customer''') b on 
		a.[Project ID] = b.[Project_ID]