Let me try again....

SELECT p.*, o.pName, u.name FROM dbo.PrintersJobs p
inner Join Orders o on
p.orderid = o.orderid
left outer join users u on
p.dataperson = u.userid
ORDER BY p.TimeEntered DESC, p.jobid desc

Hi all,
I have a query that it takes to much time to run.
In my PrinterJobs table I have about 2300 records, index on ID (PK),
timeEntered and orderid.
I'm getting only one field from orders (pName), Orders table has about 8000
records.
How can I speed this?  Any ideas??
Thank you in advance.
Rgds

Johnny

[attached file: myScript.sql]

JFB wrote:
Show quoteHide quoteStart by looking at the execution plan, find the most expensive step in
the plan.  I'm guessing the expensive step is going to be a bookmark
lookup or a sort.

A couple of things.  There isn't really a lot of records so I guess you've
got little or no indexing on your tables.
orders.orderid should probably be a primary key, at least make sure it's
clustered.
PrintersJobs.orderid should also be indexed.
SELECT * can be costly.  Do you really need all fields?  Consider specifying
your fields exactly.
LEFT JOINs can be costly, are there really PrintersJobs.dataperson values
not in users.userid?  Consider making this an INNER JOIN
users.userid sounds like it should also be a primary key.  Make sure it is
and index PrintersJobs.dataperson.
Make sure there are relationships between all tables where you join them.
ORDER BYs are costly, do you really need it? However it shouldn't make much
difference given your volumes.
Consider a covering index on TimeEntered and JobId.  Research "covering
index" if you're not sure.

Hope that helps


WBob

Show quoteHide quote

SET/SELECT truncate string
parse out set of numbers from a column
stored procedure stopped working
How is the design for this complex requirement ?
trying to use a cursor to update table..