[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Optimizing Sub Queries

rh1200la

3/22/2007 12:03:00 AM

Hey All. I have a stored procedure that, in the where clause has 4
subqueries. I just ran a sql trace on the db and it's usinq quite a
bit of CPU (compared to the other queries). Aside from indexing, are
there any other ways to optimize such a query?

Thanks.
6 Answers

Dan Guzman

3/22/2007 12:18:00 AM

0

> Aside from indexing, are
> there any other ways to optimize such a query?

Subqueries can be used in a number of ways. It's difficult to make
recommendations without seeing the actual query and DDL. Consider posting
that information so that we can better help you out.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<rh1200la@gmail.com> wrote in message
news:1174521785.424909.255550@e1g2000hsg.googlegroups.com...
> Hey All. I have a stored procedure that, in the where clause has 4
> subqueries. I just ran a sql trace on the db and it's usinq quite a
> bit of CPU (compared to the other queries). Aside from indexing, are
> there any other ways to optimize such a query?
>
> Thanks.
>

Roy Harvey

3/22/2007 12:24:00 AM

0

On 21 Mar 2007 17:03:05 -0700, rh1200la@gmail.com wrote:

>Hey All. I have a stored procedure that, in the where clause has 4
>subqueries. I just ran a sql trace on the db and it's usinq quite a
>bit of CPU (compared to the other queries). Aside from indexing, are
>there any other ways to optimize such a query?
>
>Thanks.

Not from the information provided. Indexing is really, really
important of course. Someone might be able to suggest some other
things to try if you posted the DDL for the tables (CREATE TABLE
including keys and indexes) as well as a general idea of table sizes
and the nature of the data. And the query, of course.

Roy Harvey
Beacon Falls, CT

rh1200la

3/22/2007 12:39:00 AM

0

Here you go. It's for a leads database. This query is getting the
first lead from a view, but the criteria is against the whole table.
It's doing a duplicate analysis.

Thanks.



select top 1 leadid, createdate, timestamp, AffiliateName from
vwActiveLead where
(DayPhone = (select DayPhone from tblLead where leadid = @LeadID) or
DayPhone = (select WorkPhone from tblLead where leadid = @LeadID)) OR
(WorkPhone = (select DayPhone from tblLead where leadid = @LeadID) or
DayPhone = (select WorkPhone from tblLead where leadid = @LeadID)) OR
Email = (select Email from tblLead where leadid = @LeadID)
order by createdate asc



On Mar 21, 5:18 pm, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.net> wrote:
> > Aside from indexing, are
> > there any other ways to optimize such a query?
>
> Subqueries can be used in a number of ways. It's difficult to make
> recommendations without seeing the actual query and DDL. Consider posting
> that information so that we can better help you out.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> <rh120...@gmail.com> wrote in message
>
> news:1174521785.424909.255550@e1g2000hsg.googlegroups.com...
>
> > Hey All. I have a stored procedure that, in the where clause has 4
> > subqueries. I just ran a sql trace on the db and it's usinq quite a
> > bit of CPU (compared to the other queries). Aside from indexing, are
> > there any other ways to optimize such a query?
>
> > Thanks.

Dan Guzman

3/22/2007 1:11:00 AM

0

> Here you go. It's for a leads database. This query is getting the
> first lead from a view, but the criteria is against the whole table.
> It's doing a duplicate analysis.

I think the following is equivalent to your query but I couldn't test the
behavior or performance without DDL and sample data.

SELECT TOP 1
leadid,
createdate,
timestamp,
AffiliateName
FROM vwActiveLead al
JOIN tblLead l ON
l.leadid = @LeadID
WHERE
(
al.DayPhone IN(l.DayPhone, l.WorkPhone) OR
al.WorkPhone IN(l.DayPhone, l.WorkPhone) OR
al.Email = l.Email
)
ORDER BY al.createdate

--
Hope this helps.

Dan Guzman
SQL Server MVP

<rh1200la@gmail.com> wrote in message
news:1174523931.603694.103250@y66g2000hsf.googlegroups.com...
> Here you go. It's for a leads database. This query is getting the
> first lead from a view, but the criteria is against the whole table.
> It's doing a duplicate analysis.
>
> Thanks.
>
>
>
> select top 1 leadid, createdate, timestamp, AffiliateName from
> vwActiveLead where
> (DayPhone = (select DayPhone from tblLead where leadid = @LeadID) or
> DayPhone = (select WorkPhone from tblLead where leadid = @LeadID)) OR
> (WorkPhone = (select DayPhone from tblLead where leadid = @LeadID) or
> DayPhone = (select WorkPhone from tblLead where leadid = @LeadID)) OR
> Email = (select Email from tblLead where leadid = @LeadID)
> order by createdate asc
>
>
>
> On Mar 21, 5:18 pm, "Dan Guzman" <guzma...@nospam-
> online.sbcglobal.net> wrote:
>> > Aside from indexing, are
>> > there any other ways to optimize such a query?
>>
>> Subqueries can be used in a number of ways. It's difficult to make
>> recommendations without seeing the actual query and DDL. Consider
>> posting
>> that information so that we can better help you out.
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> <rh120...@gmail.com> wrote in message
>>
>> news:1174521785.424909.255550@e1g2000hsg.googlegroups.com...
>>
>> > Hey All. I have a stored procedure that, in the where clause has 4
>> > subqueries. I just ran a sql trace on the db and it's usinq quite a
>> > bit of CPU (compared to the other queries). Aside from indexing, are
>> > there any other ways to optimize such a query?
>>
>> > Thanks.
>
>

xiangyuanbo

3/22/2007 1:26:00 AM

0


rh1200la@gmail.com Wrote:
> Hey All. I have a stored procedure that, in the where clause has 4
> subqueries. I just ran a sql trace on the db and it's usinq quite a
> bit of CPU (compared to the other queries). Aside from indexing, are
> there any other ways to optimize such a query?
>
> Thanks.
what is your subqueries look like?can these subqueries be merge into
FROM clause use any kind of JOIN clauses?


--
xiangyuanbo
------------------------------------------------------------------------
xiangyuanbo's Profile: http://www.xpertgurus.com/member.php?...
View this thread: http://www.xpertgurus.com/showthread.ph...

Gert-Jan Strik

3/23/2007 5:18:00 PM

0

Here is another - complete different approach - to the same query. Make
sure the tables in the view are properly indexed, and make sure there is
an index on tblLead(leadid). Test both your original query, Dan's
version and the version below, and keep the fastest.

SELECT TOP 1 *
FROM (
SELECT leadid, createdate, timestamp, AffiliateName
FROM tblLead L
INNER JOIN vwActiveLead AL
ON AL.DayPhone = L.DayPhone
WHERE leadid = @LeadID

UNION ALL

SELECT leadid, createdate, timestamp, AffiliateName
FROM tblLead L
INNER JOIN vwActiveLead AL
ON AL.DayPhone = L.WorkPhone
WHERE leadid = @LeadID

UNION ALL

SELECT leadid, createdate, timestamp, AffiliateName
FROM tblLead L
INNER JOIN vwActiveLead AL
ON AL.WorkPhone = L.WorkPhone
WHERE leadid = @LeadID

UNION ALL

SELECT leadid, createdate, timestamp, AffiliateName
FROM tblLead L
INNER JOIN vwActiveLead AL
ON AL.WorkPhone = L.DayPhone -- not in original query
WHERE leadid = @LeadID

UNION ALL

SELECT leadid, createdate, timestamp, AffiliateName
FROM tblLead L
INNER JOIN vwActiveLead AL
ON AL.Email = L.Email
WHERE leadid = @LeadID
) AS all_qualifying_leads
ORDER BY createdate

HTH,
Gert-Jan


rh1200la@gmail.com wrote:
>
> Here you go. It's for a leads database. This query is getting the
> first lead from a view, but the criteria is against the whole table.
> It's doing a duplicate analysis.
>
> Thanks.
>
> select top 1 leadid, createdate, timestamp, AffiliateName from
> vwActiveLead where
> (DayPhone = (select DayPhone from tblLead where leadid = @LeadID) or
> DayPhone = (select WorkPhone from tblLead where leadid = @LeadID)) OR
> (WorkPhone = (select DayPhone from tblLead where leadid = @LeadID) or
> DayPhone = (select WorkPhone from tblLead where leadid = @LeadID)) OR
> Email = (select Email from tblLead where leadid = @LeadID)
> order by createdate asc
>
> On Mar 21, 5:18 pm, "Dan Guzman" <guzma...@nospam-
> online.sbcglobal.net> wrote:
> > > Aside from indexing, are
> > > there any other ways to optimize such a query?
> >
> > Subqueries can be used in a number of ways. It's difficult to make
> > recommendations without seeing the actual query and DDL. Consider posting
> > that information so that we can better help you out.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > <rh120...@gmail.com> wrote in message
> >
> > news:1174521785.424909.255550@e1g2000hsg.googlegroups.com...
> >
> > > Hey All. I have a stored procedure that, in the where clause has 4
> > > subqueries. I just ran a sql trace on the db and it's usinq quite a
> > > bit of CPU (compared to the other queries). Aside from indexing, are
> > > there any other ways to optimize such a query?
> >
> > > Thanks.