I already posted this in the VFP community, but this piece really belongs in here.

I have a batch that I converted to a stored procedure. As a batch (against 17,711,230 records) in Analyzer (and VFP) it runs within 2-5 secs retrieving around 1500 records. As a stored procedure it clocks in at a hefty 2:30-3:00 retrieving the same record count. I have no clue as to where to even begin. Below is a brief list of parameters, and the gist of what's going on.

Parameters:
@FiscalYear char(4)
@Accounts varchar(600)
@SWDNs varchar(1100)
@PrimaryDocs varchar(700)
@GLCs varchar(50)
@Sites varchar(200)

The batch and sp both take the last five params and determine from that 5 table variables (not temp tables) and populates them from the above params. Next it merges the 5 tables into 1 temp table to match the keys of two DB tables. The it performs the selection using an inner join on the DB Tables.

Example of the params:
(This is not a real one.)
@Accounts is set to (no quotes): "60102000326609001010004000088X60202261015609001010004000088X"
This combined with the FiscalYear param determines a table variable @AccountTable with the form (char(4),char(29),char(1))
The Accounts are 29 characters with a special indicator of 1 char(the 'X' in the above)
I use a While loop combined with an insert...Select substring() to populate it.

The Table variables have primary keys set.

TIA,

Richard