IN Operator v.s. Linked Excel sheets [CR11]

[0sprey]

I often receive Excel spreadsheets containing serial numbers that I use as input for an existing report [CR11].
The SNs are used with the Selection Expert to select the equipment records corresponding to the SNs from within a very large database.

I use two methods for this record selection base on SN (SNs quantities can vary from approx. 5 to 200):

A - Use the Database Expert to link the Excel sheet and select the SNs via the linked sheet

B – Cut and Pate the SNs into a formula that utilizes the IN Operator to perform the selection {table.field} in ["SN1","SN2","SN3"]


Are there performance advantages for one method over the other?
Are the other more efficient methods or techniques to perform this type of task?

 

[briangriffin]

I'd be more than willing to take a reasonable performance hit in order to not have to modify a report every time I use it, so I'd use option A. Linking database tables with Excel tables can sometimes bring the report to a crawl though.

Depending on your environment you may want to consider creating a table in a spare database to hold the SSN's, along with a batch date and record number. Then all you need to do is import the spreadsheet into the table each time (easy to do), or even better would be to create a job that looks for the appearance of the Excel file in a specified directory, then runs the script to copy the data into the table. Then you wouldn't have to do anything except run the report on demand.

 

[pmax9999]

The linked spreadsheet is inefficient because the join needs to be processed locally which means that more data will be returned from the database than is actually required for the report, with excess data dropped based on the join to the spreadsheet.

Having said that, the inefficiency of the report processing is going to be more than offset by the effort required to copy and paste the SNs from the spreadsheet to the record selection formula.

As briangriffin recommends, go with the "linked spreadsheet" approach.

Cheers
Pete

 

[0sprey]

Thanks for this info.
As the large database I am accessing is in another city and used by many others, which method would
be least potentially disruptive for others using the database while I am running my reports ?