Adhoc,prepared,dynamic,what else...

I keep hearing about these different types of SQL statements. Can someone
tell me what they all are vs differences in brief or maybe point me to a
link if one exists ?
Hi Hassan
"Hassan" wrote:

> I keep hearing about these different types of SQL statements. Can someone
> tell me what they all are vs differences in brief or maybe point me to a
> link if one exists ?
>
I am not sure if there is one that specifically exists but you may be able
to piece together something for yourself from
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/aa175762(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/ms998569.aspx
http://msdn2.microsoft.com/pt-br/library/aa175244(SQL.80).aspx
John
sql

Adhoc,prepared,dynamic,what else...

I keep hearing about these different types of SQL statements. Can someone
tell me what they all are vs differences in brief or maybe point me to a
link if one exists ?Hi Hassan
"Hassan" wrote:

> I keep hearing about these different types of SQL statements. Can someone
> tell me what they all are vs differences in brief or maybe point me to a
> link if one exists ?
>
I am not sure if there is one that specifically exists but you may be able
to piece together something for yourself from
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/aa175762(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/ms998569.aspx
http://msdn2.microsoft.com/pt-br/library/aa175244(SQL.80).aspx
John

Adhoc vs PROC

Hi Guru,

When I ran my adhoc script below it generated only 45000 reads or 4 seconds but when I wrapped it into procedure it took about two minutes or millions of reads. The parameters calling both adhoc and proc are indeed the same. I'm pretty 99.9% sure that the proc does not recompile because I don't mix up between DDL and DML, no temp tables or any thing to cause proc to recompile. The big difference is adhoc used index scan for 45% but proc used bookmark lookup for 75%. Why it's so difference since they both returned the same results?

Please help...

Below is my code,

DECLARE @.Mode varchar(10),
@.UserID varchar(36),
@.FromDate smalldatetime,
@.ToDate smalldatetime,
@.Inst tinyint,
@.LocationID smallint,
@.BunitID tinyint,
@.TeamID int

SET @.Mode='TEAM'
SET @.UserID=''
SET @.FromDate='Dec 1 2006 12:00AM'
SET @.ToDate='Dec 31 2006 12:00AM'
SET @.Inst=28
SET @.LocationID=0
SET @.BunitID=2
SET @.TeamID=805

--IF @.Mode = 'TEAM'
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, TeamName, '' As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1 ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND (DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped RIGHT OUTER JOIN
dbo.MyTeamsRpt ON vw_Referrals_Grouped.OfficerID = dbo.MyTeamsRpt.OfficerId
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID = dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (MyTeamID = @.TeamID) AND ((StartDate BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate BETWEEN @.FromDate AND @.ToDate))
GROUP BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName, Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END)>0 Or Active = 1
ORDER BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName, Active
END

IF @.Mode = 'RM'
BEGIN
IF @.BUnitId > 0
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, '' As TeamName, OffBUnitDesc As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1 ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND (DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID = dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @.UserID) AND ((StartDate BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate BETWEEN @.FromDate AND @.ToDate)) AND OffBUnitID = @.BUnitID
GROUP BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc, OfficerName, Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END)>0 Or Active = 1
ORDER BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc, OfficerName, Active
END
--ELSE
IF @.BUnitId = 0
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, '' As TeamName, '' As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1 ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND (DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID = dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @.UserID) AND ((StartDate BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate BETWEEN @.FromDate AND @.ToDate))
GROUP BY OffAffiliateDesc, OffLocationDesc, OfficerName, Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END)>0 Or Active = 1
ORDER BY OffAffiliateDesc, OffLocationDesc, OfficerName, Active
END
END
END

When wrapped in SP have you recompiled for the first time to execute the same, check the estimated execution plan from QA in this case that explains why there is delay.

Adhoc vs PROC

Hi Guru,

When I ran my adhoc script below it generated only 45000 reads or 4
seconds but when I wrapped it into procedure it took about two minutes
or millions of reads. The parameters calling both adhoc and proc are
indeed the same. I'm pretty 99.9% sure that the proc does not recompile
because I don't mix up between DDL and DML, no temp tables or any thing
to cause proc to recompile. The big difference is adhoc used index scan
for 45% but proc used bookmark lookup for 75%. Why it's so difference
since they both returned the same results?

Please help...

Silaphet,

Below is my code,

DECLARE @.Modevarchar(10),
@.UserIDvarchar(36),
@.FromDatesmalldatetime,
@.ToDatesmalldatetime,
@.Insttinyint,
@.LocationIDsmallint,
@.BunitIDtinyint,
@.TeamIDint

SET @.Mode='TEAM'
SET @.UserID=''
SET @.FromDate='Dec 1 2006 12:00AM'
SET @.ToDate='Dec 31 2006 12:00AM'
SET @.Inst=28
SET @.LocationID=0
SET @.BunitID=2
SET @.TeamID=805

--IF @.Mode = 'TEAM'
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active,
TeamName, '' As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0
END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1
ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND
(DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As
PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped RIGHT OUTER JOIN
dbo.MyTeamsRpt ON
vw_Referrals_Grouped.OfficerID = dbo.MyTeamsRpt.OfficerId
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =
dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (MyTeamID = @.TeamID) AND ((StartDate
BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate BETWEEN @.FromDate
AND @.ToDate))
GROUP BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName,
Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1
ELSE 0 END)>0 Or Active = 1
ORDER BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName,
Active
END

IF @.Mode = 'RM'
BEGIN
IF @.BUnitId 0
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, ''
As TeamName, OffBUnitDesc As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0
END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1
ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND
(DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As
PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =
dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @.UserID)
AND ((StartDate BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate
BETWEEN @.FromDate AND @.ToDate)) AND OffBUnitID = @.BUnitID
GROUP BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc,
OfficerName, Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1
ELSE 0 END)>0 Or Active = 1
ORDER BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc,
OfficerName, Active
END
--ELSE
IF @.BUnitId = 0
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, ''
As TeamName, '' As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0
END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1
ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND
(DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As
PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =
dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @.UserID)
AND ((StartDate BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate
BETWEEN @.FromDate AND @.ToDate))
GROUP BY OffAffiliateDesc, OffLocationDesc, OfficerName, Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1
ELSE 0 END)>0 Or Active = 1
ORDER BY OffAffiliateDesc, OffLocationDesc, OfficerName, Active
END
END
ENDOn 4 Jan 2007 07:12:58 -0800, kmounkhaty@.yahoo.com wrote:

Quote:

Originally Posted by

>Hi Guru,
>
>When I ran my adhoc script below it generated only 45000 reads or 4
>seconds but when I wrapped it into procedure it took about two minutes
>or millions of reads. The parameters calling both adhoc and proc are
>indeed the same. I'm pretty 99.9% sure that the proc does not recompile
>because I don't mix up between DDL and DML, no temp tables or any thing
>to cause proc to recompile. The big difference is adhoc used index scan
>for 45% but proc used bookmark lookup for 75%. Why it's so difference
>since they both returned the same results?
>
>Please help...

Hi Silaphet,

You might suffer from parameter sniffing. Google for this term to find
out what it is and how you can try to deal with it.

You should also consider creating three procedures for the three
versions of the SELECT statement, and change your current stored proc
into a simple IF ELSE tree to call either one of them. This way, each
stored proc can get an execution plan that is otimized for its
parameters.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||kmounkhaty@.yahoo.com (smounkhaty@.bremer.com) writes:

Quote:

Originally Posted by

When I ran my adhoc script below it generated only 45000 reads or 4
seconds but when I wrapped it into procedure it took about two minutes
or millions of reads. The parameters calling both adhoc and proc are
indeed the same. I'm pretty 99.9% sure that the proc does not recompile
because I don't mix up between DDL and DML, no temp tables or any thing
to cause proc to recompile. The big difference is adhoc used index scan
for 45% but proc used bookmark lookup for 75%. Why it's so difference
since they both returned the same results?

Run this:

select objectproperty(object_id('yoursp'), 'ExecIsAnsiNullsOn'),
objectproperty(object_id('yoursp'), 'ExecIsQuotedIdentOn')

If any of these return 0, recreate the procedure and make sure that
the settings ANSI_NULLS and QUOTED_IDENTIFIER are ON. This matters if
there is an indexed view or an index on a computed column. They can
only be used if these two settings are active, and these two are saved
with the stored procedure.

It could also depend how you pass the parameters, as Hugo discussed,
but we don't that. If the script is your actual ad-hoc script, it
may be that it works better if you copy the parameters to local variables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||First off, I think I see a bug:

"(StartDate BETWEEN @.FromDate-365 AND @.ToDate-365)"

What happens on leap years?

It's impossible to tell what the performance of this query is going to
be like without knowing how all of the tables and views are structured.
I'd imagine that your biggest issue is going to be with the range of
dates that you're dealing with, but there's no way to know for sure
without knowing the table structure, view definitions, and statistical
distribution of data in the tables.

My advice: If it's not a proc that's being used a lot during the day,
try defining it with the RECOMPILE option (see docs) so that the
procedure gets re-optimized each time you run it.

-Dave

Erland Sommarskog wrote:

Quote:

Originally Posted by

kmounkhaty@.yahoo.com (smounkhaty@.bremer.com) writes:

Quote:

Originally Posted by

When I ran my adhoc script below it generated only 45000 reads or 4
seconds but when I wrapped it into procedure it took about two minutes
or millions of reads. The parameters calling both adhoc and proc are
indeed the same. I'm pretty 99.9% sure that the proc does not recompile
because I don't mix up between DDL and DML, no temp tables or any thing
to cause proc to recompile. The big difference is adhoc used index scan
for 45% but proc used bookmark lookup for 75%. Why it's so difference
since they both returned the same results?

>
Run this:
>
select objectproperty(object_id('yoursp'), 'ExecIsAnsiNullsOn'),
objectproperty(object_id('yoursp'), 'ExecIsQuotedIdentOn')
>
If any of these return 0, recreate the procedure and make sure that
the settings ANSI_NULLS and QUOTED_IDENTIFIER are ON. This matters if
there is an indexed view or an index on a computed column. They can
only be used if these two settings are active, and these two are saved
with the stored procedure.
>
It could also depend how you pass the parameters, as Hugo discussed,
but we don't that. If the script is your actual ad-hoc script, it
may be that it works better if you copy the parameters to local variables.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

AdHoc reporting against SQL 2K w/meta data?

Okay... We have a SQL2K database that has about 500 tables or so. It is normalized to a reasonable level and enforces all relationships with PK/FKs, not triggers. Hence, for a database-minded person it is fairly easy to read (as easy as a 500+ table database can be!).

Our users need adhoc query capabilities. Our report writer is simply overwhelmed. He doesn't need to be spending time writing a report that is intended to be run once.

I expect the best alternative would be to use some sort of adhoc reporting tool that is based off meta data. We (the DBAs) could be responsible for maintaining the meta data and STILL have a manhour savings over developing all these reports.

Here's the catch... We are on a TIGHT budget (aerospace industry is still reeling a bit). Is anyone using a product or aware of a product that might be just the ticket for us? We have been investigating a product by LogiXML called LGX AdHoc (http://www.logixml.com/products/AdHoc/adhoc.htm). Looks promising. Anyone use or familar with it?Can you describe what it does (I didn't find their eval)? I am using ActiveReport from DataDynamics, but it does require some knowledge and time to spend designing reports.sql

Adhoc Reporting

Couple of Questions regarding Reporting Services
1) Does SQL Server Reporting Services support adhoc reporting like dynamic
generation of report files rather than basing it on a template rdl
2) How long does it take to do a manual conversion of Crystal report with
say sub reports and say 30 formula fields and 4 paramters?
Thanks in Advance,
RdjeethRdjeeth,
1) Version 1.0 of RS doesn't support ad-hoc reporting features. However, the
next version of RS will come with a Report Builder which will support
comprehensive ad-hoc functionality. I've seen a short demo of the Report
Builder and I can say only good words about it. Meanwhile, you can evaluate
third-party products, such as the Cizer Quick Query.
2) Time will be in reverse proportion to your RS skills. As a side note,
instead of subreports you should gravitate towards using side-by-side
regions for performance reasons.
--
Hope this helps.
----
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"RDJEETH" <RDJEETH@.discussions.microsoft.com> wrote in message
news:1243B393-52ED-4CD4-B61A-F6C129B65912@.microsoft.com...
> Couple of Questions regarding Reporting Services
> 1) Does SQL Server Reporting Services support adhoc reporting like dynamic
> generation of report files rather than basing it on a template rdl
> 2) How long does it take to do a manual conversion of Crystal report with
> say sub reports and say 30 formula fields and 4 paramters?
> Thanks in Advance,
> Rdjeeth

Adhoc report with join tables on

Does anyone know if user has the capability to join two entities with
additional field which is not a predefined primary key or foreign key at the
design time? User want to run the report by join tables with certain fields
on the fly. I have not found a way to allow them to do that unless they have
Microsoft visual studio 2005 or SQL server business intelligence development
studio installed. Is SQL server reporting service a right candidate to serve
user's request? Does anyone know any other tool with this capability?
Thanks!1. You can do this if you define the sql statement dynamically and allow the
user parameter choices that provide the ability to specify the tables and
joins expressions. (for how to define the sql statement dynamically,
building it up as an expression, just treat the command as an expression
like you would the expression to display in a text box. IOW, start it with
an = sign and build up the string or invoke a code function)
However, I don't think it's really the best way.
2. Does the user have access and understanding to create views on the
server? It might be best to define the report based on a view, and have the
report basically remain ignorant of the joins and table information. The
report would have a single parameter -- the name of the view to invoke --
and would send that information to a stored procedure which would validate
that the view exists and has appropriate columns, and then run the view or
error-handle as described in choice #3 below.
3. If the user does not have that ability or access, I think I would build
this report to run a stored procedure, passing the parameter information as
described in #1, and have the stored procedure built and execute the sql. I
could do better validation in the stored procedure (for example, validate
that the tables and fields chosen by the user actually exist, assuming these
elements cannot be a dropdown in the report interface). I would have the
sproc send back a default data set of one record with every item showing
appropriate error text (or something) if I couldn't handle it another way.
Basically I think choice #2 is the right way to go here and if the user
doesn't have that ability and access I'm wondering whether that user should
be specifying this information at all...
>L<
"Daisy" <diyfan@.msnews.group.post> wrote in message
news:DE6C8C52-FBE3-48D4-9EB6-F37BBD8C40E4@.microsoft.com...
> Does anyone know if user has the capability to join two entities with
> additional field which is not a predefined primary key or foreign key at
> the
> design time? User want to run the report by join tables with certain
> fields
> on the fly. I have not found a way to allow them to do that unless they
> have
> Microsoft visual studio 2005 or SQL server business intelligence
> development
> studio installed. Is SQL server reporting service a right candidate to
> serve
> user's request? Does anyone know any other tool with this capability?
> Thanks!|||Thank you very much for the idea! I had built the model with report builder
to let user chose any fields from the tables given for the report. Now user
wanted to join the table not based on the primary key field that specified in
the design time. They wanted to join the table by certain non key fields at
the run time. I was stucked. I was only thinking use report builder model to
let user do this. And the report model could not change the key field (join
relation) on the fly. I have not found anything online or in MSDN library
telling me how to define the key at run time. As you suggested by using the
reprot designer and the sql stored procedure it is feasible to achieve the
task. Hope I did not misunderstood your post. If so please let me know.
Thanks!
"Lisa Slater Nicholls" wrote:
> 1. You can do this if you define the sql statement dynamically and allow the
> user parameter choices that provide the ability to specify the tables and
> joins expressions. (for how to define the sql statement dynamically,
> building it up as an expression, just treat the command as an expression
> like you would the expression to display in a text box. IOW, start it with
> an = sign and build up the string or invoke a code function)
> However, I don't think it's really the best way.
> 2. Does the user have access and understanding to create views on the
> server? It might be best to define the report based on a view, and have the
> report basically remain ignorant of the joins and table information. The
> report would have a single parameter -- the name of the view to invoke --
> and would send that information to a stored procedure which would validate
> that the view exists and has appropriate columns, and then run the view or
> error-handle as described in choice #3 below.
> 3. If the user does not have that ability or access, I think I would build
> this report to run a stored procedure, passing the parameter information as
> described in #1, and have the stored procedure built and execute the sql. I
> could do better validation in the stored procedure (for example, validate
> that the tables and fields chosen by the user actually exist, assuming these
> elements cannot be a dropdown in the report interface). I would have the
> sproc send back a default data set of one record with every item showing
> appropriate error text (or something) if I couldn't handle it another way.
> Basically I think choice #2 is the right way to go here and if the user
> doesn't have that ability and access I'm wondering whether that user should
> be specifying this information at all...
> >L<
>
> "Daisy" <diyfan@.msnews.group.post> wrote in message
> news:DE6C8C52-FBE3-48D4-9EB6-F37BBD8C40E4@.microsoft.com...
> > Does anyone know if user has the capability to join two entities with
> > additional field which is not a predefined primary key or foreign key at
> > the
> > design time? User want to run the report by join tables with certain
> > fields
> > on the fly. I have not found a way to allow them to do that unless they
> > have
> > Microsoft visual studio 2005 or SQL server business intelligence
> > development
> > studio installed. Is SQL server reporting service a right candidate to
> > serve
> > user's request? Does anyone know any other tool with this capability?
> >
> > Thanks!
>

Adhoc Query Tool for Sql Server

Hi !!
We are developing an application where we need a Query tool which allows customer to do Ad-Hoc or random query.. something similar to lets sayhttp://salebyowner.com/advancedSearch.php
We have few more options than this on which customer can do search.
I don't think dynamic query in C# code or something like that is going to help me. Am I right? Do we have to use any type of query tool or something for doing this?Hi love_luv,
Your example link shows a reasonably sophisticated query form written in PHP. But that doesn't mean PHP is a better database query tool than C# (or any other language for that matter).
PHP is just an application layer over top of some database. That database requires a completely different query language known as SQL (that has no direct link to PHP, C# or any other language).
The programmer in your example is simply using PHP to dyanmically costruct a SQL query statement (in text form) and passing it to their database to retrieve a result. C# (i.e., ASP.NET) can do that too.
In fact, with C# you can utilizeparameterized SQL queries for cleaner and safer SQL construction and execution.
The real trick behind sophisticated queries, of the type you're hoping to achieve, is a good familiarity with the SQL language (of the type native to your database brand,e.g., MS SQL Server uses a form of SQL known as Transact-SQL (a.k.a. TSql), and the knowledge to transform the user input from a webpage into said SQL.
Hope that helps.|||know, I understand that, its sophisticated query. I thought do we need to get some tool like Cognos etc etc for doing such query? More than parameterized query, I think its Dynamic or Adhoc query.|||You don't "need" a query tool at all, if you are familiar with SQL - regardless of how complex a query is. Visual Studio also query tools built in.

A special query tool or not, sophisticated or simple requirements - it all still comes down to a SQL statement to get your database results.
I found query tools useful once-upon-a-time for learning SQL, but I generally don't use them in my day-to-day programming now. You might find a tool useful to get an example of what your SQL text should look like, but once you have that then you use a language like C# to dynamically build the SQL text.
As an aside, "nearly" all database queries are dynamic (or adhoc) in nature. And using a parameterized query is just an optional format to make your dynamic (i.e., adhoc) queries easier to build (and safer to execute).
Hope that helps.|||So mean to say such a complex query is Dynamic Query written in C# code?
We really don't need any Commercial Query tool for Client end? We can use Web Forms to generate such query?
In couple of projects we implemented complex dynamic query, and that made us think if we need any tool or what? In our dynamic query, most of the time (99%) Select clause returns fix number of columns however, From Clause has base table and based on Where Conditions it may or may not join with other tables in data base.
In our version of dynamic query we builded WHERE clause in C#.
But how can we implement Join and WHERE using some proper structure.
Thanks|||

How one can handle dynamic SQL which has to perform JOIN with other tables.

Example I showed in my first post say has 2 - 3 tables:

PropertyListings and PropertyFeatures, PropertyLotFeatures

PropertyListings table gives me basic information I need to display.
PropertyFeatures and PropertyLotFeatures has information I need for advance search. I don't know which table to use and Join till user selects some features. based on Features selected for advance search I may have to Join:
1) PropertyListings with PropertyFeatures
2) PropertyListings with PropertyLotFeatures
3) PropertyListings with propertyFeatures and PropertyLotFeatures
I know once can right such query in C#, but is that how they do it or there is a better way? Am I missing something?

|||

Let me give an Example :

How about when there is Join Present. How whould you build Dynamic SQL? Do you need Dynamic SQL or there is a Better Way?
For Eg.
Table1 = Customers
Table2 = Interests
Table3 = Customers_Interests
Table4 = Activities
Table5 = Customers_Activities
Table3 holds Customers Interests.
Table5 holds Customers Activities
Lets say Customers Table has Data: C1, C2, C3, C4
Interests Table has Data: I1, I2, I3, I4
Cusomters_Interests has Data:
C1, I1
C1, I3
C1, I4
C2, I2
C2, I4
C3, I3
Activities Table has Data: A1, A2, A3, A4
Customers_Activities has Data:
C1, A1
C1, A4
C2, A3
C4, A1
Now lets say on my webpage I represent Interests and Activities as CheckBoxes.
Case 1:
I select Interests I1, I3, I4 but No Activities Selected. In this case my query has to join Customers, Interests and Customers_Interests Table
So query will be: Give me list of Customers with Interests I1, I3, I4
Case 2:
If I select Activities A1, A3 and No Interests, query whas to join Customers, Activities and Customers_Activities Table
Case 2:
If I select Interests I1 and Activities A1, in this case Query has to join Customers, Interests, Customers_Interests, Activities and Customers_Activities tables(all tables)
How would you write Query?
Looking for help!!!!!!!!!!!!!!!!!!!!!!!!!!!

AdHoc Query faster than Stored Proc?

Yesterday i face a strange SQL Server 2000 behaviour :-(

I had a query that was wrapped inside a stored procedure, as usual.
Suddenly, the stored procedure execution time raised from 9 secs to 80.

So to understand where the problem was i cut and pasted the sp body's into a new query analyzer window an then executed it again. Speed back to 9 secs.
Tried stored procedure again, and speed again set to 80 secs.

Tried to recompile sp. Nothing. Tried to restart SQL Server. Nothing. Tried to DROP & RE-CREATE sp. Done! Speed again at 9 secs.

My collegue asked me "why?", but i had no words. :confused: Do you have any explanation?the stored proc was past its "best before" date

like, if you look in your fridge and the milk says april 2003, you have to throw it out and buy some more

;)|||Well, that was what i exactly did. Now i can drink milk safely, but i'd like to prevent this situation again if it's possibile.

It's always frustrating when things happen and I cannot understand why :(|||It's always frustrating when things happen and I cannot understand why :(boy, i know that feeling

switch from milk to bourbon -- keeps forever :)

trouble is, i always finish it, and have to go out for more...|||See Bart Duncan's notes on parameter sniffing here http://www.examnotes.net/article48335.html|||Thanks for the link. Though very interesting i was already aware of that problem, and in fact i tried to run the body of the stored procedure using parameters to closely simulate the behaviour of the stored procedure.

Since the sp was executed with WITH RECOMPILE i expected the same execution plan of the ad-hoc query (that is what normally happens), but this time SQL Server produced two different execution plans.

UPDATE:
mmm...i've read more deeply the info at the link i've told me and seems that the problem was really parameter sniffing. a BIG thanks! :)

adhoc queries

Hello

We got third party software, we are using adhoc queries more for the Applications (i am checking the DB Dashboard, it is showing as 97% CPU for adhoc queries). Could any one suggest how to tune SQL Server (at server level) for adhoc queries, we can not change the code now.

Let me know how to improve the SQL Server, whenever 3rd party applications use adhoc queries more?

Thanks in advance.

You can use DTA for this.

http://msdn2.microsoft.com/en-us/library/ms186354.aspx

You will want to use sql profiler to capture a trace/workload for DTA.