Link to home
Start Free TrialLog in
Avatar of Rick
Rick

asked on

SQL Server performance

Hello,

I remote into into a SQL 2008 server, open the Management Studio and run a query similar to this:

SELECT
l.itemnumber AS 'sku',
CASE l.opttype
WHEN 3 then l.quantity
WHEN 4 then l.quantity * -1
END AS 'Quantity',
h.recdate AS 'Date'
FROM  ON30200 h WITH (NOLOCK)
JOIN ON30300 l WITH (NOLOCK) ON h.optnumber= l.optnumberand h.opttype= l.opttype
WHERE isvoid = 0
AND h.opttype in (3, 4)
AND h.optnumber like 'MA%'
AND h.recdate > '12/26/2009'
AND l.locationcode = ('JPN')
AND l.quantity > 0

it takes about a 1.5 seconds to run.



From an office that is about 15 miles away, using a T1 connection, I run the same query from a SQL 2008 server that has the server above set up as a linked server.
This time the query takes about 1.5 minutes to run.

SELECT
l.itemnumber AS 'sku',
CASE l.opttype
WHEN 3 then l.quantity
WHEN 4 then l.quantity * -1
END AS 'Quantity',
h.recdate AS 'Date'
FROM  WWC..ON30200 h WITH (NOLOCK)
JOIN WWC..ON30300 l WITH (NOLOCK) ON h.optnumber= l.optnumberand h.opttype= l.opttype
WHERE isvoid = 0
AND h.opttype in (3, 4)
AND h.optnumber like 'MA%'
AND h.recdate > '12/26/2009'
AND l.locationcode = ('JPN')
AND l.quantity > 0


I know I should expect some latency, but 1.48 minutes difference?
How can I improve performance?

Thank you.
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rick
Rick

ASKER

I'm getting an error, "incorrect syntax near 'sku'".

Also, is openquery a SQL language keyword or is it recognized Management Studio only?
The reason I'm asking is because this query will be used in a web application (in codebehind).

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
openquery is a sql keyword

can you up this query in a sproc and then call the sproc?
Avatar of Rick

ASKER

I will try both approaches.
Keep the suggestions coming, please.
with the open query, it acts as a pass through, running the entire query at linked server and then bring the results across.  You want to use openquery.
Avatar of Rick

ASKER

Chapmandew, that sounds good, but I'm getting an error, "incorrect syntax near 'sku'".
I am voting for the view by I agree to try both approaches.
put the code in a procedure rather than putting it inline in your code
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Try the following:
SELECT *
FROM
OPENQUERY
   (
      WWC,
      '
   SELECT l.itemnumber [sku],
      CASE l.opttype     WHEN 3
         THEN l.quantity WHEN 4
         THEN l.quantity * -1
      END AS Quantity,
      h.recdate AS [Date]
   FROM ON30200 h WITH (NOLOCK)
   JOIN ON30300 l WITH (NOLOCK)
   ON h.optnumber= l.optnumberand h.opttype= l.opttype
   WHERE isvoid = 0
      AND h.opttype IN (3,
                        4)
      AND h.optnumber LIKE ''MA%''
      AND h.recdate > ''12/26/2009''
      AND l.locationcode = (''JPN'')
      AND l.quantity > 0 '
   )

Open in new window

I've had SQL Server whine when I used AS while aliasing column names and I've also had it whine when I quote-wrapped column aliases. ;-)
Remove the double qoutes for sku, Quantity, Date
GSGDBA,
The double quotes will be needed if the author is to replace the code in the snippet provided with the code I provided; however, if the SQL is to be executed stand-alone, you are correct.