Please help speed up my query!

[m3ckon]

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.CGAMain Id,CGAMain.Salu tation,CGAMain. FirstName,

CGAMain.LastNam e,CGAMain.Salut ation2,CGAMain. FirstName2,CGAM ain.LastName
2,
cgamain.telepho ne,CGAMain.[Post Code],
other.dbo.users .UserName, other.dbo.agent s.[agent_name],
LEADSTATUS.CURR ENTSTATUS,leads tatus.activesta tus,Leadstatus.[no
answer],CGAMain.rescod e,
Agent_displayna me As 'Called By'
FROM Leadstatus INNER JOIN CGAMain ON Leadstatus.CGAM ainId =
CGAMain.CGAmain Id
INNER JOIN other.dbo.users ON Leadstatus.LTAi d = other.dbo.users .userref
INNER JOIN other.dbo.agent s ON Leadstatus.agen tid = other.dbo.agent s.id
WHERE
(StatusAssigned = 1)
AND (leadstatus.act ivestatus = 'Active')

please help???

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

[Erland Sommarskog]

m3ckon (an*******@devd ex.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.CGAMain Id,CGAMain.Salu tation,CGAMain. FirstName,

CGAMain.LastNam e,CGAMain.Salut ation2,CGAMain. FirstName2,CGAM ain.LastName
2,
cgamain.telepho ne,CGAMain.[Post Code],
other.dbo.users .UserName, other.dbo.agent s.[agent_name],
LEADSTATUS.CURR ENTSTATUS,leads tatus.activesta tus,Leadstatus.[no
answer],CGAMain.rescod e,
Agent_displayna me As 'Called By'
FROM Leadstatus INNER JOIN CGAMain ON Leadstatus.CGAM ainId =
CGAMain.CGAmain Id
INNER JOIN other.dbo.users ON Leadstatus.LTAi d = other.dbo.users .userref
INNER JOIN other.dbo.agent s ON Leadstatus.agen tid = other.dbo.agent s.id
WHERE
(StatusAssigned = 1)
AND (leadstatus.act ivestatus = '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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Gert-Jan Strik]

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(acti vestatus,CGAMai nID) and/or on
leadstatus(CGAM ainID,activesta tus) 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.CGAMain Id,CGAMain.Salu tation,CGAMain. FirstName,

CGAMain.LastNam e,CGAMain.Salut ation2,CGAMain. FirstName2,CGAM ain.LastName
2,
cgamain.telepho ne,CGAMain.[Post Code],
other.dbo.users .UserName, other.dbo.agent s.[agent_name],
LEADSTATUS.CURR ENTSTATUS,leads tatus.activesta tus,Leadstatus.[no
answer],CGAMain.rescod e,
Agent_displayna me As 'Called By'
FROM Leadstatus INNER JOIN CGAMain ON Leadstatus.CGAM ainId =
CGAMain.CGAmain Id
INNER JOIN other.dbo.users ON Leadstatus.LTAi d = other.dbo.users .userref
INNER JOIN other.dbo.agent s ON Leadstatus.agen tid = other.dbo.agent s.id
WHERE
(StatusAssigned = 1)
AND (leadstatus.act ivestatus = 'Active')

please help???

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