Try to create stored procedure with 'WITH RECOMPILE' option.

>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