General information
Forum:
Microsoft SQL Server
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
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only