469,602 Members | 1,777 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,602 developers. It's quick & easy.

Please help speed up my query!


Hi there,

had to rush some sql and am now going back to it due to a slow db
performance. I have a db for sales leads and have created 3 views based
on the data I need to produce.

However one o the views, which has subqueries to the other views is VERY
slow and it needs to be speeded up, but am unsure how, can anyone
help... below is the sql?

SELECT CGAMain.CGAMainId,CGAMain.Salutation,CGAMain.First Name,

CGAMain.LastName,CGAMain.Salutation2,CGAMain.First Name2,CGAMain.LastName
2,
cgamain.telephone,CGAMain.[Post Code],
other.dbo.users.UserName, other.dbo.agents.[agent_name],
LEADSTATUS.CURRENTSTATUS,leadstatus.activestatus,L eadstatus.[no
answer],CGAMain.rescode,
Agent_displayname As 'Called By'
FROM Leadstatus INNER JOIN CGAMain ON Leadstatus.CGAMainId =
CGAMain.CGAmainId
INNER JOIN other.dbo.users ON Leadstatus.LTAid = other.dbo.users.userref
INNER JOIN other.dbo.agents ON Leadstatus.agentid = other.dbo.agents.id
WHERE
(StatusAssigned = 1)
AND (leadstatus.activestatus = 'Active')

please help???

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #1
2 1735
m3ckon (an*******@devdex.com) writes:
had to rush some sql and am now going back to it due to a slow db
performance. I have a db for sales leads and have created 3 views based
on the data I need to produce.

However one o the views, which has subqueries to the other views is VERY
slow and it needs to be speeded up, but am unsure how, can anyone
help... below is the sql?

SELECT CGAMain.CGAMainId,CGAMain.Salutation,CGAMain.First Name,

CGAMain.LastName,CGAMain.Salutation2,CGAMain.First Name2,CGAMain.LastName
2,
cgamain.telephone,CGAMain.[Post Code],
other.dbo.users.UserName, other.dbo.agents.[agent_name],
LEADSTATUS.CURRENTSTATUS,leadstatus.activestatus,L eadstatus.[no
answer],CGAMain.rescode,
Agent_displayname As 'Called By'
FROM Leadstatus INNER JOIN CGAMain ON Leadstatus.CGAMainId =
CGAMain.CGAmainId
INNER JOIN other.dbo.users ON Leadstatus.LTAid = other.dbo.users.userref
INNER JOIN other.dbo.agents ON Leadstatus.agentid = other.dbo.agents.id
WHERE
(StatusAssigned = 1)
AND (leadstatus.activestatus = 'Active')

please help???


If you help us to help you, you may get help. Just the query alone is
not much to work from. In general, optimizing queries often requires
access to the database, since there is a lot of information that one
might need to know. But some issues can be easy to spot from a distance.
So, please post the following:

o CREATE TABLE statements for the tables in question.
o All CREATE INDEX statements for the tables. (Incluing PK and UNIQUE
constraints.)
o If any of Leadstatus, users, agents are views, please post the source
for the views.
o Approxamite number of rows per table.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Some general advice:
- make sure all tables have a primary key (which automatically means the
key is indexed)
- make sure all foreign keys are indexed
- make sure the foreign keys have exactly the same definition (data type
and size) as the primary key
- you may want to experiment with covering indexes when you access
tables only because of the WHERE clause. In this case, a covering index
on leadstatus(activestatus,CGAMainID) and/or on
leadstatus(CGAMainID,activestatus) might help

HTH,
Gert-Jan
m3ckon wrote:

Hi there,

had to rush some sql and am now going back to it due to a slow db
performance. I have a db for sales leads and have created 3 views based
on the data I need to produce.

However one o the views, which has subqueries to the other views is VERY
slow and it needs to be speeded up, but am unsure how, can anyone
help... below is the sql?

SELECT CGAMain.CGAMainId,CGAMain.Salutation,CGAMain.First Name,

CGAMain.LastName,CGAMain.Salutation2,CGAMain.First Name2,CGAMain.LastName
2,
cgamain.telephone,CGAMain.[Post Code],
other.dbo.users.UserName, other.dbo.agents.[agent_name],
LEADSTATUS.CURRENTSTATUS,leadstatus.activestatus,L eadstatus.[no
answer],CGAMain.rescode,
Agent_displayname As 'Called By'
FROM Leadstatus INNER JOIN CGAMain ON Leadstatus.CGAMainId =
CGAMain.CGAmainId
INNER JOIN other.dbo.users ON Leadstatus.LTAid = other.dbo.users.userref
INNER JOIN other.dbo.agents ON Leadstatus.agentid = other.dbo.agents.id
WHERE
(StatusAssigned = 1)
AND (leadstatus.activestatus = 'Active')

please help???

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Markku Uttula | last post: by
5 posts views Thread by JENS CONSER | last post: by
2 posts views Thread by jphelan | last post: by
27 posts views Thread by SQL Learner | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.