"WebBuilder451" wrote:

> I have two queries and was wondering which would be considered more efficent?
> Please, all oppinions welcome!  as to table structure, well i'll giv it if
> any one wants it, but all i got to say is i didn't create it!
>
>
> (included is a date create function)
>
>
> start
> declare @guid as varchar(50)
> declare @MTD as datetime
> declare @DTD as datetime
> set @guid = '506C4404-992F-4F84-9BCB-578148749DBA'
> set @MTD = dbo.fn_MDate(year(getdate()),month(getdate()),01)
> set @DTD = dbo.fn_MDate(year(getdate()),month(getdate()),day(getdate()))
>
> -- query 1 the way that easy(?)
> select
> (select count(q.szstatus) allopen from quotes q where q.szcreatedby = @guid
> and q.szstatus = 'open' group by q.szstatus) as allopen,
> (select count(q.lquoteid) as newquotes from quotes q where q.szcreatedby =
> @guid and q.dtCreated >= dateadd(dd,-5, getdate())) as newQuotes,
> (select count(q.lquoteid) as mtdcount from quotes q where q.szcreatedby =
> @guid and q.dtCreated >= @MTD) as MTDcount,
> (select count(q.lquoteid) as mtdcount from quotes q where q.szcreatedby =
> @guid and q.dtCreated >= @MTD and q.szstatus = 'Cancelled') as MTDLost,
> (select count(q.lquoteid) as mtdcount from quotes q where q.szcreatedby =
> @guid and q.dtCreated >= @MTD and q.szstatus = 'closed') as MTDWon,
> (select count(q.lquoteid) as dtdquotes from quotes q where q.szcreatedby =
> @guid and q.dtCreated >= @DTD) as DTDQuotes
>
> ---query 2 the way that's better(?)
>
> select sum(case q.szstatus when 'open' then 1 else 0 end) as allopen,
>        sum(case when q.szcreatedby = @guid and q.dtCreated >= dateadd(dd,-5,
> getdate()) then 1 ELSE 0 END) as newQuotes,   
>        sum(case WHEN q.szcreatedby = @guid and q.dtCreated >= @MTD THEN 1 ELSE
> 0 END) as MTDCount,   
>        sum(case WHEN q.dtCreated >= @MTD and q.szstatus = 'Cancelled' THEN 1
> ELSE 0 END) as MTDLost,    
>        sum(case WHEN q.dtCreated >= @MTD AND q.szStatus = 'closed' Then 1 ELSE
> 0 END) as MTDWON,
>        sum(case WHEN q.dtCreated >= @DTD THEN 1 ELSE 0 END) as MTDWon
> from quotes q
> where q.szcreatedby = @guid
>
> --
> thanks (as always)
> some day i''m gona pay this forum back for all the help i''m getting
> kes

"Anith Sen" wrote:

> Based on the initial glance, the second one tend to perform better. For an
> accurate answer, you may need to test it out and compare the execution
> times.
>
> --
> Anith
>
>
>

>I have two queries and was wondering which would be considered more efficent?
>Please, all oppinions welcome!  as to table structure, well i'll giv it if
>any one wants it, but all i got to say is i didn't create it!
>
>
>(included is a date create function)
>
>
>start
>declare @guid as varchar(50)
>declare @MTD as datetime
>declare @DTD as datetime
>set @guid = '506C4404-992F-4F84-9BCB-578148749DBA'
>set @MTD = dbo.fn_MDate(year(getdate()),month(getdate()),01)
>set @DTD = dbo.fn_MDate(year(getdate()),month(getdate()),day(getdate()))
>
>-- query 1 the way that easy(?)
>select
>(select count(q.szstatus) allopen from quotes q where q.szcreatedby = @guid
>and q.szstatus = 'open' group by q.szstatus) as allopen,
>(select count(q.lquoteid) as newquotes from quotes q where q.szcreatedby =
>@guid and q.dtCreated >= dateadd(dd,-5, getdate())) as newQuotes,
>(select count(q.lquoteid) as mtdcount from quotes q where q.szcreatedby =
>@guid and q.dtCreated >= @MTD) as MTDcount,
>(select count(q.lquoteid) as mtdcount from quotes q where q.szcreatedby =
>@guid and q.dtCreated >= @MTD and q.szstatus = 'Cancelled') as MTDLost,
>(select count(q.lquoteid) as mtdcount from quotes q where q.szcreatedby =
>@guid and q.dtCreated >= @MTD and q.szstatus = 'closed') as MTDWon,
>(select count(q.lquoteid) as dtdquotes from quotes q where q.szcreatedby =
>@guid and q.dtCreated >= @DTD) as DTDQuotes
>
>---query 2 the way that's better(?)
>
>select sum(case q.szstatus when 'open' then 1 else 0 end) as allopen,
>       sum(case when q.szcreatedby = @guid and q.dtCreated >= dateadd(dd,-5,
>getdate()) then 1 ELSE 0 END) as newQuotes,   
>       sum(case WHEN q.szcreatedby = @guid and q.dtCreated >= @MTD THEN 1 ELSE
>0 END) as MTDCount,   
>       sum(case WHEN q.dtCreated >= @MTD and q.szstatus = 'Cancelled' THEN 1
>ELSE 0 END) as MTDLost,    
>       sum(case WHEN q.dtCreated >= @MTD AND q.szStatus = 'closed' Then 1 ELSE
>0 END) as MTDWON,
>       sum(case WHEN q.dtCreated >= @DTD THEN 1 ELSE 0 END) as MTDWon
>from quotes q
>where q.szcreatedby = @guid

"Roy Harvey" wrote:

> It depends.
>
> My immediate reaction was to favor the second version.  But then I
> thought about it a bit more....
>
> If the table is very large, the subqueries all highly selective and
> supported with indexing, and the optimizer recognizes their
> selectivity to the point of using the indexes, then the first version
> would probably be more efficient.
>
> On the other hand, if the subqueries are NOT highly selective the best
> you can hope for from the first query is that the optimizer will be
> smart enough to do them all in one pass - like the second version.
>
> By the way, the GROUP BY in the first subqeury of the first variation
> is not required, as it resolves to a single row anyway.
>
> Roy Harvey
> Beacon Falls, CT
>
> On Wed, 7 Jun 2006 15:51:03 -0700, WebBuilder451
> <WebBuilder***@discussions.microsoft.com> wrote:
>
> >I have two queries and was wondering which would be considered more efficent?
> >Please, all oppinions welcome!  as to table structure, well i'll giv it if
> >any one wants it, but all i got to say is i didn't create it!
> >
> >
> >(included is a date create function)
> >
> >
> >start
> >declare @guid as varchar(50)
> >declare @MTD as datetime
> >declare @DTD as datetime
> >set @guid = '506C4404-992F-4F84-9BCB-578148749DBA'
> >set @MTD = dbo.fn_MDate(year(getdate()),month(getdate()),01)
> >set @DTD = dbo.fn_MDate(year(getdate()),month(getdate()),day(getdate()))
> >
> >-- query 1 the way that easy(?)
> >select
> >(select count(q.szstatus) allopen from quotes q where q.szcreatedby = @guid
> >and q.szstatus = 'open' group by q.szstatus) as allopen,
> >(select count(q.lquoteid) as newquotes from quotes q where q.szcreatedby =
> >@guid and q.dtCreated >= dateadd(dd,-5, getdate())) as newQuotes,
> >(select count(q.lquoteid) as mtdcount from quotes q where q.szcreatedby =
> >@guid and q.dtCreated >= @MTD) as MTDcount,
> >(select count(q.lquoteid) as mtdcount from quotes q where q.szcreatedby =
> >@guid and q.dtCreated >= @MTD and q.szstatus = 'Cancelled') as MTDLost,
> >(select count(q.lquoteid) as mtdcount from quotes q where q.szcreatedby =
> >@guid and q.dtCreated >= @MTD and q.szstatus = 'closed') as MTDWon,
> >(select count(q.lquoteid) as dtdquotes from quotes q where q.szcreatedby =
> >@guid and q.dtCreated >= @DTD) as DTDQuotes
> >
> >---query 2 the way that's better(?)
> >
> >select sum(case q.szstatus when 'open' then 1 else 0 end) as allopen,
> >       sum(case when q.szcreatedby = @guid and q.dtCreated >= dateadd(dd,-5,
> >getdate()) then 1 ELSE 0 END) as newQuotes,   
> >       sum(case WHEN q.szcreatedby = @guid and q.dtCreated >= @MTD THEN 1 ELSE
> >0 END) as MTDCount,   
> >       sum(case WHEN q.dtCreated >= @MTD and q.szstatus = 'Cancelled' THEN 1
> >ELSE 0 END) as MTDLost,    
> >       sum(case WHEN q.dtCreated >= @MTD AND q.szStatus = 'closed' Then 1 ELSE
> >0 END) as MTDWON,
> >       sum(case WHEN q.dtCreated >= @DTD THEN 1 ELSE 0 END) as MTDWon
> >from quotes q
> >where q.szcreatedby = @guid
>

"Arnie" wrote:

> "WebBuilder451" <WebBuilder***@discussions.microsoft.com> wrote
> in message
> news:9D0C9AF3-347D-45FC-B850-92346514C827@microsoft.com...
> > thanks for the reply and esp the head's up on the group by. It
> > may have been
> > a hold over from the mod to make this one. I did a check and
> > the second is
> > faster by 50%. for reasons you may have noted.
> > I grew up in Monroe by the way. Been to your area many time. Is
> > the park
> > still there?
> Which one?
>
> Small world.  I grew up in Trumbull, moved to Huntington and now
> live in Newtown.
>
> - Arnie
>
>
>

"Arnie" wrote:

> "WebBuilder451" <WebBuilder***@discussions.microsoft.com> wrote
> in message
> news:9D0C9AF3-347D-45FC-B850-92346514C827@microsoft.com...
> > thanks for the reply and esp the head's up on the group by. It
> > may have been
> > a hold over from the mod to make this one. I did a check and
> > the second is
> > faster by 50%. for reasons you may have noted.
> > I grew up in Monroe by the way. Been to your area many time. Is
> > the park
> > still there?
> Which one?
>
> Small world.  I grew up in Trumbull, moved to Huntington and now
> live in Newtown.
>
> - Arnie
>
>
>

"Arnie" wrote:

> "WebBuilder451" <WebBuilder***@discussions.microsoft.com> wrote
> in message
> news:2970E48E-DDB9-4DF6-8E68-D37AE10A9E80@microsoft.com...
> > what year did you grad. from Trumbull High (or St Joe's)?
> >
> Trumbul High, '65.
>
> - Arnie
>
>
>

"Roy Harvey" wrote:

> On Thu, 8 Jun 2006 06:27:03 -0700, WebBuilder451
> <WebBuilder***@discussions.microsoft.com> wrote:
>
> >I grew up in Monroe by the way. Been to your area many time. Is the park
> >still there?
>
> If you mean Mathies Park - with the house on the island in middle of
> the pond - the answer is yes, most of it.  A bit of it, mostly outside
> the park proper, became the regional high school a few years ago.
> Before that we had to export grades 9-12 to Naugatuck, Seymour, and
> (for the last few years of that era) Masuk.  I live just up the hill
> from there.
>
> Roy
>

"Roy Harvey" wrote:

> On Thu, 8 Jun 2006 06:27:03 -0700, WebBuilder451
> <WebBuilder***@discussions.microsoft.com> wrote:
>
> >I grew up in Monroe by the way. Been to your area many time. Is the park
> >still there?
>
> If you mean Mathies Park - with the house on the island in middle of
> the pond - the answer is yes, most of it.  A bit of it, mostly outside
> the park proper, became the regional high school a few years ago.
> Before that we had to export grades 9-12 to Naugatuck, Seymour, and
> (for the last few years of that era) Masuk.  I live just up the hill
> from there.
>
> Roy
>