What happens if you run the first select on the QA? I don't see why they would run any differnt. Are you use the front end is getting a return value. Is that string exactally what is being sent, did you look in profiler to see what was actually sent to the back end?

Are you using the same database in both instances, the same server?

You could probably speed it up by not building the field LIKE '%', for example, in your sp, check to see what was sent and only include those parameters in the query...


>Hi,
>
>I am dealing with an application that already uses MS SQL Server for the back-end, but I'm migrating some code from the front-end (VFP) to the back-end. I am using the query analyzer trying to work out some code that I want to be in a stored proc.
>
>Currently the front-end VFP app dynamically builds a SQL string that is passed using SPT.
>
>Here is the SPT statement that gets constructed from VFP:
> "SELECT ContactName,CompanyName,AccountId,zip,Address1,City,cpk FROM account WHERE zip LIKE '55405%' AND CompanyName LIKE '%' AND ContactName LIKE '%' AND AccountId LIKE '%' ORDER BY companyName"
>
>In the above example, a user is searching for matches on a zip code.
>
>This works as expected and returns results virtually instantly.
>
>Here is my problem, when I try to convert this processing to the back-end expecting that the front-end will only send down parameters to a stored procedure, the TRANSACT SQL runs incredibly slow.
>
>Here is the code (from my tests in the Query Analyzer):
>
>DECLARE @tcAccountId VarChar(10)
>DECLARE @tcContactName VarChar(30)
>DECLARE @tcCompanyName VarChar(30)
>DECLARE @tcZip VarChar(10)
>
>SET @tcAccountId = '%'
>SET @tcContactName = '%'
>SET @tcCompanyName = '%'
>SET @tcZip = '55405%'
>
>SELECT ContactName,CompanyName,AccountId,zip,Address1,City,cpk FROM account
> WHERE zip LIKE @tcZip
> AND CompanyName LIKE @tcCompanyName
> AND ContactName LIKE @tcContactName
> AND AccountId LIKE @tcAccountId
> ORDER BY companyName
>
>
>The account table has about 150,000 names and this query takes about 18 seconds, even though the SPT from VFP takes only an instant. All four fields in the WHERE clause are indexed.
>
>The analyzer indicates that the sort consumes 75% of the cost. Only ten rows are returned. Does this mean that a sort is occurring on the whole table instead of the 10 rows returned by the zipcode criteria? If so, can I force a change on this execution?
>
>What's different here that would cause this code to run significantly slower than virtually the same code coming in via SPT from Visual FoxPro?
>
>Guy