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!

re: Please help speed up my query!

m3ckon (anonymous@devdex.com) writes:[color=blue]
> 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???[/color]

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, esquel@sommarskog.se

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

re: Please help speed up my query!

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:[color=blue]
>
> 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![/color]