Bulkload XML Code: application

Showing posts with label application. Show all posts

Sunday, March 25, 2012

calculated fields w/user defined functions

Hi,
In the new application we are extensively using calculated fields that
consist of user defined functions that query multiple tables in the
database. Does anybody know of any limitations or drowbacks of using
user-defined functions in calculated fields (performance, locking, etc) Here
is an example of the most complex user defined function we have:
CREATE FUNCTION dbo.PayrollDetail_Subtotal
(
@.PayrollDetailID int,
@.CoverageType varchar(25)
)
RETURNS decimal(19,4) AS
BEGIN
DECLARE
@.AmountCap AS decimal(19,2),
@.AmountEffective as decimal (19,2),
@.AssignmentID as int,
@.PolicyID1 as int,
@.PolicyID2 as int,
@.PolicyTo1 as datetime,
@.PolicyFrom2 as datetime,
@.WorkCode AS int,
@.Result AS decimal(19,2),
@.PayrollFrom AS datetime,
@.PayrollTo AS datetime,
@.PayrollType AS varchar(50),
@.PayrollHeaderID AS int,
@.Composite1 AS decimal(19,2),
@.Composite2 AS decimal(19,2),
@.Territory1 AS int,
@.TerritoryMod1 as decimal(10,4),
@.WCRateID1 AS int,
@.WCRate1 AS decimal(19,4),
@.WCFactor1 AS varchar(50)
SET @.Result = 0
/* Payroll Header ID and dates*/
/* NULL for PayrollHeader is not allowed in tblPayrollDetail */
SELECT @.PayrollHeaderID = PayrollHeaderID, @.WorkCode=lkWorkCodeID,
@.AmountCap=AmountCap, @.AmountEffective = AmountEffective from
tblPayrollDetail where PayrollDetailID = @.PayrollDetailID;
/* NULL is not allowed for DateFrom, DateTo in tblPayrollHeader */
SELECT @.PayrollFrom = DateFrom, @.PayrollTo = DateTo, @.PayrollType = PayrollType, @.AssignmentID = AssignmentID from tblPayrollHeader WHERE
PayrollHeaderID = @.PayrollHeaderID
SELECT @.PolicyID1=CE.CoverageEntryID, @.Composite1 = CE.CompositeRating,
@.PolicyTo1 = CE.DateExpiration from tblCoverageAssignment A INNER JOIN
tblCoverageEntry CE ON A.CoverageEntryID = CE.CoverageEntryID AND
A.CoverageType=@.CoverageType WHERE
CE.DateEffective<=@.PayrollFrom AND @.PayrollFrom<=CE.DateExpiration
SELECT @.PolicyID2=CE.CoverageEntryID, @.Composite2 = CE.CompositeRating,
@.PolicyFrom2 = CE.DateEffective from tblCoverageAssignment A INNER JOIN
tblCoverageEntry CE ON A.CoverageEntryID = CE.CoverageEntryID AND
A.CoverageType=@.CoverageType WHERE
CE.DateEffective<=@.PayrollTo AND @.PayrollTo<=CE.DateExpiration
/* Composite Rating in tblCoverageEntry has default of 0 */
if (@.PolicyID1 is null) OR (@.Composite1 = 0) OR (@.PolicyID2 is null) OR
(@.Composite2=0)
return 0
ELSE
BEGIN
IF (@.PolicyID1 = @.PolicyID2)
/* ++++++CASE 1 : Payroll dates fall within the same policy year
+++++++*/
BEGIN
/* work code rate */
SELECT @.WCRateID1 = WorkCodeRateID, @.WCRate1 = RateAmount, @.WCFactor1 = Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID1 and
lkWorkCodeID = @.WorkCode
IF (@.WCRateID1 is null)
return 0
ELSE
BEGIN
IF @.WCFactor1 = 'Rate/100'
SELECT @.Result = @.WCRate1*0.01
ELSE
BEGIN
IF @.WCFactor1='Rate/1000'
SELECT @.Result = @.WCRate1*0.001
ELSE
SELECT @.Result = @.WCRate1
END
/* base */
IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
SELECT @.Result = @.Result*@.AmountEffective
IF (@.CoverageType = 'WC')
BEGIN
IF @.AmountCap =0
SELECT @.Result = @.Result * @.AmountEffective
ELSE
SELECT @.Result = @.Result * @.AmountCap
END
/* Territory */
SELECT @.Territory1 = A.lkTerritoryID from tblPayrollHeader PH INNER
JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
PH.PayrollHeaderID = @.PayrollHeaderID
if (@.Territory1 is null)
SELECT @.Result = @.Result * @.Composite1
ELSE
BEGIN
SELECT @.TerritoryMod1= Modifier from tblTerritoryModifier WHERE
lkTerritoryID = @.Territory1 and WorkCodeRateID = @.WCRateID1
IF (@.TerritoryMod1 is NULL) OR (@.TerritoryMod1=0)
SELECT @.Result = @.Result * @.Composite1
ELSE
SELECT @.Result = @.Result * @.Composite1 * @.TerritoryMod1
END
END
END
ELSE
/* IF Payroll falls under 2 policy years */
BEGIN
DECLARE
@.Territory2 AS int,
@.TerritoryMod2 as decimal(10,4),
@.WCRateID2 AS int,
@.WCRate2 AS decimal(19,4),
@.WCFactor2 AS varchar(50),
@.Result1 AS decimal(19,4),
@.Result2 AS decimal(19,4),
@.NoDays1 AS int,
@.NoDays2 AS int,
@.PayrollLength AS int
/* +++POLICY YEAR 1+++ */
/* work code rate */
SELECT @.WCRateID1 = WorkCodeRateID, @.WCRate1 = RateAmount, @.WCFactor1 = Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID1 and
lkWorkCodeID = @.WorkCode
IF (@.WCRateID1 is null)
SELECT @.Result1 = 0
ELSE
BEGIN
IF @.WCFactor1 = 'Rate/100'
SELECT @.Result1 = @.WCRate1*0.01
ELSE
BEGIN
IF @.WCFactor1='Rate/1000'
SELECT @.Result1 = @.WCRate1*0.001
ELSE
SELECT @.Result1 = @.WCRate1
END
/* base */
SELECT @.NoDays1= DATEDIFF(day,@.PayrollFrom,@.PolicyTo1)
SELECT @.NoDays2= DATEDIFF(day,@.PayrollTo,@.PolicyFrom2)
SELECT @.PayrollLength = @.NoDays1+@.NoDays2
/* no of days for Policy Year 1= DateFrom - DateExpiration for Policy
Year 1 */
IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
SELECT @.Result1 = @.Result1*((@.AmountEffective/@.PayrollLength)*@.NoDays1)
IF (@.CoverageType = 'WC')
BEGIN
IF @.AmountCap =0
SELECT @.Result1 = @.Result1*((@.AmountEffective/@.PayrollLength)*@.NoDays1)
ELSE
SELECT @.Result1 = @.Result1* ((@.AmountCap/@.PayrollLength)*@.NoDays1)
END
/* Territory */
SELECT @.Territory1 = A.lkTerritoryID from tblPayrollHeader PH INNER
JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
PH.PayrollHeaderID = @.PayrollHeaderID
if (@.Territory1 is null)
SELECT @.Result1 = @.Result1 * @.Composite1
ELSE
BEGIN
SELECT @.TerritoryMod1= Modifier from tblTerritoryModifier WHERE
lkTerritoryID = @.Territory1 and WorkCodeRateID = @.WCRateID1
IF (@.TerritoryMod1 is NULL) OR (@.TerritoryMod1=0)
SELECT @.Result1 = @.Result1 * @.Composite1
ELSE
SELECT @.Result1 = @.Result1 * @.Composite1 * @.TerritoryMod1
END
END
/* +++POLICY YEAR 2+++ */
/* work code rate */
SELECT @.WCRateID2 = WorkCodeRateID, @.WCRate2 = RateAmount, @.WCFactor2 = Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID2 and
lkWorkCodeID = @.WorkCode
IF (@.WCRateID2 is null)
SELECT @.Result2 = 0
ELSE
BEGIN
IF @.WCFactor2 = 'Rate/100'
SELECT @.Result2 = @.WCRate2*0.01
ELSE
BEGIN
IF @.WCFactor2='Rate/1000'
SELECT @.Result2 = @.WCRate2*0.001
ELSE
SELECT @.Result2 = @.WCRate2
END
/* base */
IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
SELECT @.Result2 = @.Result2*((@.AmountEffective/@.PayrollLength)*@.NoDays2)
IF (@.CoverageType = 'WC')
BEGIN
IF @.AmountCap =0
SELECT @.Result2 = @.Result2*((@.AmountEffective/@.PayrollLength)*@.NoDays2)
ELSE
SELECT @.Result2 = @.Result2* ((@.AmountCap/@.PayrollLength)*@.NoDays2)
END
/* Territory */
SELECT @.Territory2 = A.lkTerritoryID from tblPayrollHeader PH INNER
JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
PH.PayrollHeaderID = @.PayrollHeaderID
if (@.Territory2 is null)
SELECT @.Result2 = @.Result2 * @.Composite2
ELSE
BEGIN
SELECT @.TerritoryMod2= Modifier from tblTerritoryModifier WHERE
lkTerritoryID = @.Territory2 and WorkCodeRateID = @.WCRateID2
IF (@.TerritoryMod2 is NULL) OR (@.TerritoryMod2=0)
SELECT @.Result2 = @.Result2 * @.Composite2
ELSE
SELECT @.Result2 = @.Result2 * @.Composite2 * @.TerritoryMod2
END
END
SELECT @.Result = @.Result1 + @.Result2
END
END
return @.Result
ENDUser defined functions in computed columns perform pretty badly compared to
plain SQL. The reason is that behind the season the function works as a
cursor, all the work is done on a row-by-row basis. You probably won't
notice that much if you only have a few rows, but once you get to a largish
amount (say 1000+) performance will go out of the window.
I did some testing the other day and SELECT * on a table with around 8000
rows had a subsecond return time without a computed column with a UDF, but
20+ seconds with a computed column with a UDF. On that basis I decided to
redesign the solution so it would work without a computed column, which
involved creating a few new tables, and performance is now acceptable.
So, you can use it, but only if your table has a very small number of rows,
or if you have a larger table and you are sure you will never ever have a
table scan on that table.
"ilona" <ieshulman@.sseinc.com> wrote in message
news:etjJPzjRDHA.1688@.TK2MSFTNGP11.phx.gbl...
> Hi,
> In the new application we are extensively using calculated fields that
> consist of user defined functions that query multiple tables in the
> database. Does anybody know of any limitations or drowbacks of using
> user-defined functions in calculated fields (performance, locking, etc)
Here
> is an example of the most complex user defined function we have:
>
> CREATE FUNCTION dbo.PayrollDetail_Subtotal
> (
> @.PayrollDetailID int,
> @.CoverageType varchar(25)
> )
> RETURNS decimal(19,4) AS
> BEGIN
> DECLARE
> @.AmountCap AS decimal(19,2),
> @.AmountEffective as decimal (19,2),
> @.AssignmentID as int,
> @.PolicyID1 as int,
> @.PolicyID2 as int,
> @.PolicyTo1 as datetime,
> @.PolicyFrom2 as datetime,
> @.WorkCode AS int,
> @.Result AS decimal(19,2),
> @.PayrollFrom AS datetime,
> @.PayrollTo AS datetime,
> @.PayrollType AS varchar(50),
> @.PayrollHeaderID AS int,
> @.Composite1 AS decimal(19,2),
> @.Composite2 AS decimal(19,2),
> @.Territory1 AS int,
> @.TerritoryMod1 as decimal(10,4),
> @.WCRateID1 AS int,
> @.WCRate1 AS decimal(19,4),
> @.WCFactor1 AS varchar(50)
>
> SET @.Result = 0
> /* Payroll Header ID and dates*/
> /* NULL for PayrollHeader is not allowed in tblPayrollDetail */
> SELECT @.PayrollHeaderID = PayrollHeaderID, @.WorkCode=lkWorkCodeID,
> @.AmountCap=AmountCap, @.AmountEffective = AmountEffective from
> tblPayrollDetail where PayrollDetailID = @.PayrollDetailID;
> /* NULL is not allowed for DateFrom, DateTo in tblPayrollHeader */
> SELECT @.PayrollFrom = DateFrom, @.PayrollTo = DateTo, @.PayrollType => PayrollType, @.AssignmentID = AssignmentID from tblPayrollHeader WHERE
> PayrollHeaderID = @.PayrollHeaderID
> SELECT @.PolicyID1=CE.CoverageEntryID, @.Composite1 = CE.CompositeRating,
> @.PolicyTo1 = CE.DateExpiration from tblCoverageAssignment A INNER JOIN
> tblCoverageEntry CE ON A.CoverageEntryID = CE.CoverageEntryID AND
> A.CoverageType=@.CoverageType WHERE
> CE.DateEffective<=@.PayrollFrom AND @.PayrollFrom<=CE.DateExpiration
> SELECT @.PolicyID2=CE.CoverageEntryID, @.Composite2 = CE.CompositeRating,
> @.PolicyFrom2 = CE.DateEffective from tblCoverageAssignment A INNER JOIN
> tblCoverageEntry CE ON A.CoverageEntryID = CE.CoverageEntryID AND
> A.CoverageType=@.CoverageType WHERE
> CE.DateEffective<=@.PayrollTo AND @.PayrollTo<=CE.DateExpiration
> /* Composite Rating in tblCoverageEntry has default of 0 */
> if (@.PolicyID1 is null) OR (@.Composite1 = 0) OR (@.PolicyID2 is null) OR
> (@.Composite2=0)
> return 0
> ELSE
> BEGIN
> IF (@.PolicyID1 = @.PolicyID2)
> /* ++++++CASE 1 : Payroll dates fall within the same policy year
> +++++++*/
> BEGIN
> /* work code rate */
> SELECT @.WCRateID1 = WorkCodeRateID, @.WCRate1 = RateAmount, @.WCFactor1 => Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID1 and
> lkWorkCodeID = @.WorkCode
> IF (@.WCRateID1 is null)
> return 0
> ELSE
> BEGIN
> IF @.WCFactor1 = 'Rate/100'
> SELECT @.Result = @.WCRate1*0.01
> ELSE
> BEGIN
> IF @.WCFactor1='Rate/1000'
> SELECT @.Result = @.WCRate1*0.001
> ELSE
> SELECT @.Result = @.WCRate1
> END
> /* base */
> IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
> SELECT @.Result = @.Result*@.AmountEffective
> IF (@.CoverageType = 'WC')
> BEGIN
> IF @.AmountCap =0
> SELECT @.Result = @.Result * @.AmountEffective
> ELSE
> SELECT @.Result = @.Result * @.AmountCap
> END
> /* Territory */
> SELECT @.Territory1 = A.lkTerritoryID from tblPayrollHeader PH INNER
> JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
> PH.PayrollHeaderID = @.PayrollHeaderID
> if (@.Territory1 is null)
> SELECT @.Result = @.Result * @.Composite1
> ELSE
> BEGIN
> SELECT @.TerritoryMod1= Modifier from tblTerritoryModifier WHERE
> lkTerritoryID = @.Territory1 and WorkCodeRateID = @.WCRateID1
> IF (@.TerritoryMod1 is NULL) OR (@.TerritoryMod1=0)
> SELECT @.Result = @.Result * @.Composite1
> ELSE
> SELECT @.Result = @.Result * @.Composite1 * @.TerritoryMod1
> END
> END
> END
> ELSE
> /* IF Payroll falls under 2 policy years */
> BEGIN
> DECLARE
> @.Territory2 AS int,
> @.TerritoryMod2 as decimal(10,4),
> @.WCRateID2 AS int,
> @.WCRate2 AS decimal(19,4),
> @.WCFactor2 AS varchar(50),
> @.Result1 AS decimal(19,4),
> @.Result2 AS decimal(19,4),
> @.NoDays1 AS int,
> @.NoDays2 AS int,
> @.PayrollLength AS int
>
> /* +++POLICY YEAR 1+++ */
> /* work code rate */
> SELECT @.WCRateID1 = WorkCodeRateID, @.WCRate1 = RateAmount, @.WCFactor1 => Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID1 and
> lkWorkCodeID = @.WorkCode
> IF (@.WCRateID1 is null)
> SELECT @.Result1 = 0
> ELSE
> BEGIN
> IF @.WCFactor1 = 'Rate/100'
> SELECT @.Result1 = @.WCRate1*0.01
> ELSE
> BEGIN
> IF @.WCFactor1='Rate/1000'
> SELECT @.Result1 = @.WCRate1*0.001
> ELSE
> SELECT @.Result1 = @.WCRate1
> END
> /* base */
> SELECT @.NoDays1= DATEDIFF(day,@.PayrollFrom,@.PolicyTo1)
> SELECT @.NoDays2= DATEDIFF(day,@.PayrollTo,@.PolicyFrom2)
> SELECT @.PayrollLength = @.NoDays1+@.NoDays2
> /* no of days for Policy Year 1= DateFrom - DateExpiration for Policy
> Year 1 */
> IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
> SELECT @.Result1 => @.Result1*((@.AmountEffective/@.PayrollLength)*@.NoDays1)
> IF (@.CoverageType = 'WC')
> BEGIN
> IF @.AmountCap =0
> SELECT @.Result1 => @.Result1*((@.AmountEffective/@.PayrollLength)*@.NoDays1)
> ELSE
> SELECT @.Result1 = @.Result1* ((@.AmountCap/@.PayrollLength)*@.NoDays1)
> END
> /* Territory */
> SELECT @.Territory1 = A.lkTerritoryID from tblPayrollHeader PH INNER
> JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
> PH.PayrollHeaderID = @.PayrollHeaderID
> if (@.Territory1 is null)
> SELECT @.Result1 = @.Result1 * @.Composite1
> ELSE
> BEGIN
> SELECT @.TerritoryMod1= Modifier from tblTerritoryModifier WHERE
> lkTerritoryID = @.Territory1 and WorkCodeRateID = @.WCRateID1
> IF (@.TerritoryMod1 is NULL) OR (@.TerritoryMod1=0)
> SELECT @.Result1 = @.Result1 * @.Composite1
> ELSE
> SELECT @.Result1 = @.Result1 * @.Composite1 * @.TerritoryMod1
> END
>
> END
> /* +++POLICY YEAR 2+++ */
> /* work code rate */
> SELECT @.WCRateID2 = WorkCodeRateID, @.WCRate2 = RateAmount, @.WCFactor2 => Factor from tblWorkCodeRate where CoverageEntryID = @.PolicyID2 and
> lkWorkCodeID = @.WorkCode
> IF (@.WCRateID2 is null)
> SELECT @.Result2 = 0
> ELSE
> BEGIN
> IF @.WCFactor2 = 'Rate/100'
> SELECT @.Result2 = @.WCRate2*0.01
> ELSE
> BEGIN
> IF @.WCFactor2='Rate/1000'
> SELECT @.Result2 = @.WCRate2*0.001
> ELSE
> SELECT @.Result2 = @.WCRate2
> END
> /* base */
> IF (@.CoverageType = 'Excess' OR @.CoverageType='GL')
> SELECT @.Result2 => @.Result2*((@.AmountEffective/@.PayrollLength)*@.NoDays2)
> IF (@.CoverageType = 'WC')
> BEGIN
> IF @.AmountCap =0
> SELECT @.Result2 => @.Result2*((@.AmountEffective/@.PayrollLength)*@.NoDays2)
> ELSE
> SELECT @.Result2 = @.Result2* ((@.AmountCap/@.PayrollLength)*@.NoDays2)
> END
> /* Territory */
> SELECT @.Territory2 = A.lkTerritoryID from tblPayrollHeader PH INNER
> JOIN tblAssignment A ON PH.AssignmentID = A.AssignmentID WHERE
> PH.PayrollHeaderID = @.PayrollHeaderID
> if (@.Territory2 is null)
> SELECT @.Result2 = @.Result2 * @.Composite2
> ELSE
> BEGIN
> SELECT @.TerritoryMod2= Modifier from tblTerritoryModifier WHERE
> lkTerritoryID = @.Territory2 and WorkCodeRateID = @.WCRateID2
> IF (@.TerritoryMod2 is NULL) OR (@.TerritoryMod2=0)
> SELECT @.Result2 = @.Result2 * @.Composite2
> ELSE
> SELECT @.Result2 = @.Result2 * @.Composite2 * @.TerritoryMod2
> END
> END
> SELECT @.Result = @.Result1 + @.Result2
> END
> END
> return @.Result
>
> END
>sql

Tuesday, March 20, 2012

Calculate leave days from Startdate to enddate of contract

Can someone plz help me.

I'm working on leave application, have to calculate number of leave days available, starting from Startdate to Enddate of a contract. Where an employee get 1 day leave after 17 days from startdate of contract. How do I calculate the leave days, that accrue every after 17 days by 1.

I'm using ASP and SQL Server 2000 (Query Analyzer)

ndindi22

Quote:

Originally Posted by ndindi22

Can someone plz help me.

I'm using ASP and SQL Server 2000 (Query Analyzer)

ndindi22

get the datediff between startdate to getdate() in days...divide by 17...

select @.numberofleave = datediff(dd, StartDate, getdate())/17 from yourtable

Calculate duration whilst excluding non working time

I have a helpdesk application and would like to calculate the duration of a call excluding non working time.

I already have a calendar table which lists working dates/times and a function as follows:

CREATETABLE [dbo].[iHLPWorkingHours] (

[workfromdt] [datetime] NOTNULL,

[worktodt] [datetime] NOTNULL

Sample data:

WorkFromDt WorkToDt

02/01/2007 08:00:00 02/01/2007 18:00:00
03/01/2007 08:00:00 03/01/2007 18:00:00
04/01/2007 08:00:00 04/01/2007 18:00:00
05/01/2007 08:00:00 05/01/2007 18:00:00
06/01/2007 06/01/2007
07/01/2007 07/01/2007

Non-working days such as weekends and holidays have their times removed in the iHLPWorkingHours table.

To calculate the call duration I use the following function:

CREATE FUNCTION dbo.TotalCallDuration
(
@.fromdt DATETIME,
@.todt DATETIME
)
RETURNS INT

BEGIN

RETURN
(
SELECT CAST((SUM(DATEDIFF(MINUTE,workfromdt,worktodt)) -
DATEDIFF(MINUTE,MIN(workfromdt),@.fromdt) -
DATEDIFF(MINUTE,@.todt,MAX(worktodt))) AS DECIMAL(9,2))
AS working_hours
FROM ihlpWorkingHours
WHERE NOT (@.fromdt >= worktodt OR @.todt <= workfromdt)
HAVING MIN(workfromdt) <= @.fromdt AND MAX(worktodt) >= @.todt
)
END

Then pass in the opening and closing dates from the Helpdesk Call to the function to calculate the duration in minutes:

select callid, openeddatetime,
closeddatetime, dbo.TotalCallDuration(openeddatetime, closeddatetime) as duration
from ihlpcall
statusid='closed'

This works fine when a call has been opened or closed within working hours M-F but for calls that have been opened or closed outside of these times (after 18:00 and before 08:00) M-F or Weekends the function returns a null value for the call duration.

Is there any way the function can be altered to compensate for calls opened or closed outside of working hours.

Thanks in advance.

Paul

I think this will give you the logic you need. The nested query builds a set of all to-from ranges valid for the query. It corrects the from and to dates when a full date hasn't expired. It then calculates the minutes in the ranges and sums them.

There are some more elegant solutions to this, but I think this is probably the most readible.

Please note, if a call is opened and closed outside working hours without spanning a working period, this will still return NULL.

Code Snippet

select sum(datediff(minute, x.fromdt, x.todt))
from (
select
case
when @.fromdt >= workingfromdt then @.fromdt
else workingfromdt
end as fromdt,
case
when @.todt <= workingtodt then @.todt
else workingtodt
end as todt
from ihlpworkinghours
where workingtodt >= @.fromdt AND
workingfromdt <= @.todt
) x

|||

Hi Brian,

many thanks for your response, I have tried pasting the code snippet into my function but it fails the sysntax check, with the following error:

Error 1075: RETURN statements in scalar valued functions must include an argument

am I missing something?

Thanks

Paul

|||

If you are using the code in a function, the function must return some value. Declare a variable of an appropriate type, assign the results of the SELECT statement to that variable, and then return the variable.

To keep things simple, I recommend just testing the results as a simple SELECT statement, verify it's accurate, and then work on migrating it to a function.

Good luck,
Bryan

sql

Calculate duration whilst excluding non working time

I have a helpdesk application and would like to calculate the duration of a call excluding non working time.

I already have a calendar table which lists working dates/times and a function as follows:

CREATE TABLE [dbo].[iHLPWorkingHours] (

[workfromdt] [datetime] NOT NULL ,

[worktodt] [datetime] NOT NULL

Sample data:

WorkFromDt WorkToDt

Non-working days such as weekends and holidays have their times removed in the iHLPWorkingHours table.

To calculate the call duration I use the following function:

CREATE FUNCTION dbo.TotalCallDuration
(
@.fromdt DATETIME,
@.todt DATETIME
)
RETURNS INT

BEGIN

Then pass in the opening and closing dates from the Helpdesk Call to the function to calculate the duration in minutes:

select callid, openeddatetime,
closeddatetime, dbo.TotalCallDuration(openeddatetime, closeddatetime) as duration
from ihlpcall
statusid='closed'

Is there any way the function can be altered to compensate for calls opened or closed outside of working hours.

Thanks in advance.

Paul

There are some more elegant solutions to this, but I think this is probably the most readible.

Please note, if a call is opened and closed outside working hours without spanning a working period, this will still return NULL.

Code Snippet

select sum(datediff(minute, x.fromdt, x.todt))
from (
select
case
when @.fromdt >= workingfromdt then @.fromdt
else workingfromdt
end as fromdt,
case
when @.todt <= workingtodt then @.todt
else workingtodt
end as todt
from ihlpworkinghours
where workingtodt >= @.fromdt AND
workingfromdt <= @.todt
) x

|||

Hi Brian,

many thanks for your response, I have tried pasting the code snippet into my function but it fails the sysntax check, with the following error:

Error 1075: RETURN statements in scalar valued functions must include an argument

am I missing something?

Thanks

Paul

|||

To keep things simple, I recommend just testing the results as a simple SELECT statement, verify it's accurate, and then work on migrating it to a function.

Good luck,
Bryan

Sunday, March 11, 2012

CAL users - 1 or many?

If you have an application that uses a single SQL logon to access the database, but that application is used by many people to access the database, do we require one CAL (for the app) or many (for each user)?For server/CAL licensing the licence agreement requires that you purchase a
licence for each user or device using the server. A user is an actual
person, not a login. That's my understanding.
From the licensing FAQ:
"A user CAL allows a particular user to gain access to licensed server
software from any number of devices."
http://www.microsoft.com/sql/howtobuy/faq.asp
--
David Portas
SQL Server MVP
--

CAL users - 1 or many?

If you have an application that uses a single SQL logon to access the databa
se, but that application is used by many people to access the database, do w
e require one CAL (for the app) or many (for each user)?For server/CAL licensing the licence agreement requires that you purchase a
licence for each user or device using the server. A user is an actual
person, not a login. That's my understanding.
From the licensing FAQ:
"A user CAL allows a particular user to gain access to licensed server
software from any number of devices."
http://www.microsoft.com/sql/howtobuy/faq.asp
David Portas
SQL Server MVP
--

CAL users - 1 or many?

If you have an application that uses a single SQL logon to access the database, but that application is used by many people to access the database, do we require one CAL (for the app) or many (for each user)?
For server/CAL licensing the licence agreement requires that you purchase a
licence for each user or device using the server. A user is an actual
person, not a login. That's my understanding.
From the licensing FAQ:
"A user CAL allows a particular user to gain access to licensed server
software from any number of devices."
http://www.microsoft.com/sql/howtobuy/faq.asp
David Portas
SQL Server MVP

CAL license question

I want to know how many CAL license do I need. I got an application with 100
users to that application adn there are 10 SQL logins. So do I need to
purchase 100 users or 10 users license on SQL 2000. Or only the concurrent
connection users or if the users got two PC to connect to SQL server, do I
buy 200 ...? Please help. Thanks.100
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"00KobeBrian" <a@.b.com> wrote in message
news:OJNyRH9dGHA.5016@.TK2MSFTNGP04.phx.gbl...
>I want to know how many CAL license do I need. I got an application with
>100 users to that application adn there are 10 SQL logins. So do I need to
>purchase 100 users or 10 users license on SQL 2000. Or only the concurrent
>connection users or if the users got two PC to connect to SQL server, do I
>buy 200 ...? Please help. Thanks.
>|||Hi,
Additional information:
For licensing questions, you can call 1-800-426-9400 (select option 4),
Monday through Friday, 6:00 A.M. to 5:30 P.M. (PST) to speak directly to a
Microsoft licensing specialist.
Worldwide customers can use the Guide to Worldwide Microsoft Licensing
Sites http://www.microsoft.com/licensing/index/worldwide.asp
to find contact information in their locations.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================
Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com>
>>References: <OJNyRH9dGHA.5016@.TK2MSFTNGP04.phx.gbl>
>>Subject: Re: CAL license question
>>Date: Sun, 14 May 2006 21:15:22 -0700
>>Lines: 17
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
>>X-RFC2646: Format=Flowed; Response
>>Message-ID: <OvIrtY9dGHA.3484@.TK2MSFTNGP04.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: h-68-167-251-21.sttnwaho.dynamic.covad.net
68.167.251.21
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:431469
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>100
>>--
>>This posting is provided "AS IS" with no warranties, and confers no
rights.
>>Use of included script samples are subject to the terms specified at
>>http://www.microsoft.com/info/cpyright.htm
>>"00KobeBrian" <a@.b.com> wrote in message
>>news:OJNyRH9dGHA.5016@.TK2MSFTNGP04.phx.gbl...
>>I want to know how many CAL license do I need. I got an application with
>>100 users to that application adn there are 10 SQL logins. So do I need
to
>>purchase 100 users or 10 users license on SQL 2000. Or only the
concurrent
>>connection users or if the users got two PC to connect to SQL server, do
I
>>buy 200 ...? Please help. Thanks.
>>
>>

CAL

I know CAL stands for Client access license, does this mean if I have an
application that logs onto SQL as a generic user, is that one CAL? Do I need
a CAL for each of my application generic users? Des this go for each user I
have in SQL?
TIA,
JoeA CAL covers a connection to the DB, so whether your using a generic login
or user specific logins each is a seperate CAL.|||A CAL covers either a user or a device that connects to SQL. The gotcha is
that only end users or devices count. If you have a web server or other
device that collects end users and feeds them through a single device
connections, then you have to license each end user, not just the single
middle-ware device. Licensed users can have multiple connections so there
is not any relationship between licensed users and connections. If you have
a situation where you cannot enumerate each licensed user, such as a
public-facing web site, then you must license SQLnon a per-processor basis.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:DBCCB86D-7367-4AC5-9B9E-440CA72C31C1@.microsoft.com...
>I know CAL stands for Client access license, does this mean if I have an
> application that logs onto SQL as a generic user, is that one CAL? Do I
> need
> a CAL for each of my application generic users? Des this go for each user
> I
> have in SQL?
> TIA,
> Joe
>|||Thank you.
This was very helpful.
Joe
"Geoff N. Hiten" wrote:
> A CAL covers either a user or a device that connects to SQL. The gotcha is
> that only end users or devices count. If you have a web server or other
> device that collects end users and feeds them through a single device
> connections, then you have to license each end user, not just the single
> middle-ware device. Licensed users can have multiple connections so there
> is not any relationship between licensed users and connections. If you have
> a situation where you cannot enumerate each licensed user, such as a
> public-facing web site, then you must license SQLnon a per-processor basis.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "jaylou" <jaylou@.discussions.microsoft.com> wrote in message
> news:DBCCB86D-7367-4AC5-9B9E-440CA72C31C1@.microsoft.com...
> >I know CAL stands for Client access license, does this mean if I have an
> > application that logs onto SQL as a generic user, is that one CAL? Do I
> > need
> > a CAL for each of my application generic users? Des this go for each user
> > I
> > have in SQL?
> >
> > TIA,
> > Joe
> >
>
>|||Thank you,
This was very helpful
Joe
"BenUK" wrote:
> A CAL covers a connection to the DB, so whether your using a generic login
> or user specific logins each is a seperate CAL.

Caching with Dynamic Security

I defined one role in my AS database with dynamic security for each dimension. I am accessing the AS database in an ASP.NET web application which runs under a domain user and uses Form Authentication. Therefore I always connect to the AS database under the same domain user even though security should be based on the user logged into the web application. When I run a MDX query, I pass along the web user's security info as part of the connectionstring and uses dynamic security to get the AllowedSet for each dimension. However, I notice my user defined function is ran only on the first time I run a query, meaning the dimension security is cached based on the domain user in the connection string and not the web user. Sorry if this sounds confusing but I can clarify a bit more if needed. My question boils down to: Is there a way to tell AS database to cache result base on the CustomData property of the connectionstring?

The answer is YES - AS is smart enough to recognize that different values of CustomData were used even though the real identity on the connection is the same. Since the main purpose of CustomData was for custom authentication - it is treated the same as different users. Same is true w.r.t. Roles and UserId properties.

HTH,

Mosha (http://www.mosha.com/msolap)

|||

Mosha,

As always, you are right and thanks for the help. I did a little more testing after my post and realized AS is caching the result base on the CustomData property. Thanks!

Caching UDF

Hi All,

I have an application that reads data from a very slow database link
(like 10 seconds per call) though what I am looking for would be of
generic use for anyone who has long-running queries that are
frequently repeated.

I would like to be able to cache the results of a query so that I do
not have to re-execute that query if it is reissued. Ideally I
believe that this could be implemented by hiding the query inside a
UDF and exposing the UDF through a view. The UDF could then "Check
the cache" and only run the slow query if there wasn't a match (or if
the match was too old). From what I understand the best way to do
this would be for the cache to be an extended stored procedure.

Has anyone done or seen this? Has someone written a copy that I
could purchase? Does anyone care to offer their opinnion of how or if
this could work?

Thanks in Advance,

StevenHi

Caching like this is usually the function of a middle tier rather than the
database.

John

"Steven Ensslen" <ensslen@.planet-save.com> wrote in message
news:73ce0e91.0405141350.716061eb@.posting.google.c om...
> Hi All,
> I have an application that reads data from a very slow database link
> (like 10 seconds per call) though what I am looking for would be of
> generic use for anyone who has long-running queries that are
> frequently repeated.
> I would like to be able to cache the results of a query so that I do
> not have to re-execute that query if it is reissued. Ideally I
> believe that this could be implemented by hiding the query inside a
> UDF and exposing the UDF through a view. The UDF could then "Check
> the cache" and only run the slow query if there wasn't a match (or if
> the match was too old). From what I understand the best way to do
> this would be for the cache to be an extended stored procedure.
> Has anyone done or seen this? Has someone written a copy that I
> could purchase? Does anyone care to offer their opinnion of how or if
> this could work?
> Thanks in Advance,
> Steven|||[posted and mailed, please reply in news]

Steven Ensslen (ensslen@.planet-save.com) writes:
> I have an application that reads data from a very slow database link
> (like 10 seconds per call) though what I am looking for would be of
> generic use for anyone who has long-running queries that are
> frequently repeated.
> I would like to be able to cache the results of a query so that I do
> not have to re-execute that query if it is reissued. Ideally I
> believe that this could be implemented by hiding the query inside a
> UDF and exposing the UDF through a view. The UDF could then "Check
> the cache" and only run the slow query if there wasn't a match (or if
> the match was too old). From what I understand the best way to do
> this would be for the cache to be an extended stored procedure.

Unless I am misunderstanding something, this won't fly at all. The UDF
and the extended stored procedure still executes on the server, so there
is no cache you could retrieve data from. SQL Server maintains a cache, but
that is from disk to local memory, so from your point of view, this is
still on the remote side of your link.

For such a cache to be meaningful, you must have it on your side of the
link. Thus, the typical place to fix this would be in the application
itself (unless there is a separate middle tier between the application
and the database).

If this is an application you cannot modify, you might still be able to
do it, but it will be hairy. In this case you would point your application
to a local SQL Server, which use linked servers to access the remote
server, and this local server would implement a cache. But how you would
load the cache and keep int current is far from trivial. To develop this,
I wold need some more information to proceed.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the replies, but I guess that I haven't explained my idea
clearly enough.

> Unless I am misunderstanding something, this won't fly at all. The UDF
> and the extended stored procedure still executes on the server, so there
> is no cache you could retrieve data from. SQL Server maintains a cache, but
> that is from disk to local memory, so from your point of view, this is
> still on the remote side of your link.
> For such a cache to be meaningful, you must have it on your side of the
> link. Thus, the typical place to fix this would be in the application
> itself (unless there is a separate middle tier between the application
> and the database).

I'm looking for a custom-coded,programmer-activated, server-side
cache. I want to be able to store an arbitrary string so that it
persists for my entire database session and I do not have to execute
the expensive query that generated that string more than once.

> If this is an application you cannot modify, you might still be able to
> do it, but it will be hairy. In this case you would point your application
> to a local SQL Server, which use linked servers to access the remote
> server, and this local server would implement a cache. But how you would
> load the cache and keep int current is far from trivial. To develop this,
> I wold need some more information to proceed.

You're correct that I can't modify the application. So I'd like the
local server to implement a cache of the remote server.

Has anyone done this? Does anyone have an example or know of a 3rd
party program/extension that will perform this function?

Steven|||Steven Ensslen (ensslen@.planet-save.com) writes:
> I'm looking for a custom-coded,programmer-activated, server-side
> cache. I want to be able to store an arbitrary string so that it
> persists for my entire database session and I do not have to execute
> the expensive query that generated that string more than once.

I'm afraid that I don't really follow. Can you give an overview the
architecture of the application as it works now? I mean which boxes
you have, and where the slow link is.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Caching stored procedures

I've recently been told by developers that some of our
SQL 2000 stored procedures an application is using take
longer to run the first time they are ran in a while. If
they run them again, they seem to be quicker. Does
anyone have any suggestions as to where to start looking
to resolve this? Does this have to do with the execution
plan not staying in the cache? Is there a way to fix
this?Stored procedures are compiled when the execution plan is not in cache
but the performance hit is usually not significant for occasional
compiles. The more likely cause is that data is retrieved from disk
when the proc is first run and remains in cache for subsequent access.
You can see if this is the case by running the following test:
USE MyDatabase
GO
CHECKPOINT
DBCC DROPCLEANBUFFERS
GO
SELECT GETDATE()
EXECUTE MyProcedure
SELECT GETDATE()
EXECUTE MyProcedure
SELECT GETDATE()
GO
If the second execution is noticeably faster, you might take a look at
the execution plan to see if you can optimize the queries and/or add
indexes. This can reduce both physical and logical i/o.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Bill" <bill4390@.hotmaillcom> wrote in message
news:286f01c3673b$06480360$7d02280a@.phx.gbl...
> I've recently been told by developers that some of our
> SQL 2000 stored procedures an application is using take
> longer to run the first time they are ran in a while. If
> they run them again, they seem to be quicker. Does
> anyone have any suggestions as to where to start looking
> to resolve this? Does this have to do with the execution
> plan not staying in the cache? Is there a way to fix
> this?
>

Thursday, March 8, 2012

caching problem in sql reporting services

Hi all,
I have developed a web application which displays records stored in the database based on certain criteria supplied by the user. I have used SQL Reporting services for generating a report coresponding to the displayed records. The problem I faces is as follows.
I generated a report for the records displayed in the application. Then, I updated one of the records and again generated the report. But the change I made is not reflected in it. The report is the same as the one previously generated. But the change is reflected if I logged out of the application, again logged in and generated the report.
I checked the 'Execution properties' of the report in the Report Manager and I found that the 'Do not cache temporary copies of this report' option remains selected. But the caching problem exists. Please help if anyone knows a solution. I am desperately in need of a solution for the above problem.

Thanks,

Renju.

Had similar problem with SRS 2000 (are you using same version).

Resolved by creating a dummy parameter that passed the current date/time - forcing a new dataset everytime...

Caching of stored procedure

Hello all,

I've got an application that calls a really simple stored procedure - just selects all records from the dbase. The problem is this seems to cache every now and then and as the same table is updated very frequently by other users this means the data returned isn't up to date. I thought it was Sql Server caching the results of the stored procedure, but I can do an iisreset and it will be up to date again. And unless I'm missing some point iisreset has no bearing on Sql Server. So is there an application data cache somewhere that I should be clearing to ensure the recordset is always up to date?

The question that comes immediately to my mind is.. Are you storing the data from the database in some state bags.. like Session state or Application State or.. simply any application caching?

The problem obviously lies with your application... not any sql server caching.. So, how do you retrieve the data that your application use? Make a fresh database call everytime you read that data?

|||

Hi,
I was being stupid - I wasn't storing the data in state but I was using paged data and my page number was referenced statically so whenever one user moved the page on everyone saw the older pages!

Doh!

Caching Application Block and SQL 2005 SQL Dependency

I am building a web app using VS2005 and SQL 2005 I would like to use the Caching Application Block to cache objects from my BLL. I was wondering if there is a way of utilizing the build in SQLDependency in SQL 2005 with the Caching Application Block? Does anybody have tried this, are there any samples on the web?

Thanks,

Newbie

You can take a look at

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

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

caching a view?

I've got a web application that does queries against a very large
product database (SQL Server 2000). The data that needs to be returned
is such that the query is HUGE, and very expensive.
Thus far, I've had great success by caching that big query (returning
all rows) in my web application server, and searching against that to
perform product searches. This works great, but now the data is
changing more rapidly and a cache solution is not as ideal as before
So I'd like to go back to querying the database for each product
search. I've moved the big query into a view in SQL Server, but that
of course doesn't do anything for performance. The query is simply too
large and complex to do these product search queries. I was thinking I
could flatten the data out into one big table for product search
queries only, or something like that - kind of effectively caching a
view in SQL Server, and using triggers to update it when relevant data
has changed.
Is this a realistic endeavor? Is there a canned method of doing this,
or will I have to do it through brute force? Any pointers or
suggestions would be much appreciated.
Thanks,
ErikErik - please take a look in BOL for "Indexed Views" or check out this
article for more info:
http://www.microsoft.com/technet/pr...in/indexvw.mspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Erik
"voldengen@.gmail.com" wrote:

> I've got a web application that does queries against a very large
> product database (SQL Server 2000). The data that needs to be returned
> is such that the query is HUGE, and very expensive.
I am not sure why this would be the case, a user may only view a finite
amount of information! Is this information paged or are you selecting
information that is subsequently dropped?
> So I'd like to go back to querying the database for each product
> search. I've moved the big query into a view in SQL Server, but that
> of course doesn't do anything for performance. The query is simply too
> large and complex to do these product search queries. I was thinking I
> could flatten the data out into one big table for product search
> queries only, or something like that - kind of effectively caching a
> view in SQL Server, and using triggers to update it when relevant data
> has changed.
>
Have you considered partitioning the data and using a partitioned view?
Posting DDL and sample data may help understand exactly what you are trying
to do.

> Thanks,
> Erik
>
John|||how many rows do you have?
what type of search do you do?
do you do free text searches? have your try to create a server side full
text catalog?
(full text catalogs are great to index text content and support queries like
google search)
do you use storeprocedure to access your database or ad-hoc queries?
how the table is indexed?
you talk about a web application, do you use ASP.NET? do you use SQL
dependent caching in your web application? (you keep in cache the result of
a search until the database is updated, you don't keep to entire table in
memory)
and also, what is your SQL Server hardware?
<voldengen@.gmail.com> wrote in message
news:1167380092.221925.65670@.a3g2000cwd.googlegroups.com...
> I've got a web application that does queries against a very large
> product database (SQL Server 2000). The data that needs to be returned
> is such that the query is HUGE, and very expensive.
> Thus far, I've had great success by caching that big query (returning
> all rows) in my web application server, and searching against that to
> perform product searches. This works great, but now the data is
> changing more rapidly and a cache solution is not as ideal as before
> So I'd like to go back to querying the database for each product
> search. I've moved the big query into a view in SQL Server, but that
> of course doesn't do anything for performance. The query is simply too
> large and complex to do these product search queries. I was thinking I
> could flatten the data out into one big table for product search
> queries only, or something like that - kind of effectively caching a
> view in SQL Server, and using triggers to update it when relevant data
> has changed.
> Is this a realistic endeavor? Is there a canned method of doing this,
> or will I have to do it through brute force? Any pointers or
> suggestions would be much appreciated.
> Thanks,
> Erik
>|||Thanks everyone for those suggestions. I will research indexed and
partitioned views to see how that works.
The number of rows is about 200K.
An overly simplified recordset would look like:
-id (int)
-supplier (varchar)
-category (int)
-subcategory (int)
-description (varchar)
-notes (text)
-quantity (int)
My product queries return all columns, searching against any of them.
Most queries are based on supplier, category, and subcategory.
I've considered setting up a verity index for doing full text searches,
but that only covers a fraction of the queries I'm doing.
SQL hardware is a beefy P4 with 2gigs of ram, win2k3...Dell Poweredge
server.
Thanks again for the suggestions.
-Erik
On Dec 29, 4:39 am, "Jeje" <willg...@.hotmail.com> wrote:[vbcol=seagreen]
> how many rows do you have?
> what type of search do you do?
> do you do free text searches? have your try to create a server side full
> text catalog?
> (full text catalogs are great to index text content and support queries li
ke
> google search)
> do you use storeprocedure to access your database or ad-hoc queries?
> how the table is indexed?
> you talk about a web application, do you use ASP.NET? do you use SQL
> dependent caching in your web application? (you keep in cache the result o
f
> a search until the database is updated, you don't keep to entire table in
> memory)
> and also, what is your SQL Server hardware?
> <volden...@.gmail.com> wrote in messagenews:1167380092.221925.65670@.a3g2000
cwd.googlegroups.com...
>
>
>
>
>|||voldengen@.gmail.com wrote:
> Thanks everyone for those suggestions. I will research indexed and
> partitioned views to see how that works.
> The number of rows is about 200K.
> An overly simplified recordset would look like:
> -id (int)
> -supplier (varchar)
> -category (int)
> -subcategory (int)
> -description (varchar)
> -notes (text)
> -quantity (int)
> My product queries return all columns, searching against any of them.
> Most queries are based on supplier, category, and subcategory.
> I've considered setting up a verity index for doing full text searches,
> but that only covers a fraction of the queries I'm doing.
> SQL hardware is a beefy P4 with 2gigs of ram, win2k3...Dell Poweredge
> server.
> Thanks again for the suggestions.
>
How are you facilitating the flexible searching? If you're using
something like
WHERE (@.Category IS NULL OR category = @.Category)
You might want to consider using dynamic SQL instead. The above syntax
will prevent your query from using indexes efficiently.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||My query uses a few outer joins, indexed views probably won't work.
I'll check out partitioned views now...
Thanks again,
Erik
On Dec 29, 2:00 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> Erik - please take a look in BOL for "Indexed Views" or check out this
> article for more info:http://www.microsoft.com/technet/pr...
tain/indexv...
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459541F1.8030507@.realsqlguy.com...
> voldengen@.gmail.com wrote:
> How are you facilitating the flexible searching? If you're using
> something like
> WHERE (@.Category IS NULL OR category = @.Category)
> You might want to consider using dynamic SQL instead. The above syntax
> will prevent your query from using indexes efficiently.
>
Indeed. The suggestions you have received assume that your query really is
too expensive to run every time. Before going to indexed views, caching or
any other workaround, you should be sure that you can't optimize your query
so it is cheap enough to run every time.
If you post the query and DDL here someone might suggest an efficient
approach.
David|||<voldengen@.gmail.com> wrote in message
news:1167413897.388996.88880@.a3g2000cwd.googlegroups.com...
> My query uses a few outer joins, indexed views probably won't work.
> I'll check out partitioned views now...
> Thanks again,
> Erik
> On Dec 29, 2:00 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>
I can't imagine how partitioned views would help.
David|||On 29 Dec 2006 00:14:52 -0800, voldengen@.gmail.com wrote:

>I've got a web application that does queries against a very large
>product database (SQL Server 2000). The data that needs to be returned
>is such that the query is HUGE, and very expensive.
>Thus far, I've had great success by caching that big query (returning
>all rows) in my web application server, and searching against that to
>perform product searches. This works great, but now the data is
>changing more rapidly and a cache solution is not as ideal as before
>So I'd like to go back to querying the database for each product
>search. I've moved the big query into a view in SQL Server, but that
>of course doesn't do anything for performance. The query is simply too
>large and complex to do these product search queries. I was thinking I
>could flatten the data out into one big table for product search
>queries only, or something like that - kind of effectively caching a
>view in SQL Server, and using triggers to update it when relevant data
>has changed.
>Is this a realistic endeavor? Is there a canned method of doing this,
>or will I have to do it through brute force? Any pointers or
>suggestions would be much appreciated.
>Thanks,
>Erik
Hi Erik,
You have already gotten a few good answers, including the very sound
advise to try tuning "that big query" first.
Failing that, indexed views would be my second choice, but I understand
that the use of outer joins precludes that option. That leaves you with
one other option - to build your own indexed view: create a table to
store the results from the big query, AND create triggers on all tables
used in the big query to change the results in the "cached view" as the
base data is changed. (Note that this is essentially what happpens under
the covers when you create an indexed view - but because of the outer
join, deducing the correct updates to the "cached view" from the updates
to the base tables becomes too hard for SQL Server).
Hugo Kornelis, SQL Server MVP

caching a view?

I've got a web application that does queries against a very large
product database (SQL Server 2000). The data that needs to be returned
is such that the query is HUGE, and very expensive.
Thus far, I've had great success by caching that big query (returning
all rows) in my web application server, and searching against that to
perform product searches. This works great, but now the data is
changing more rapidly and a cache solution is not as ideal as before
So I'd like to go back to querying the database for each product
search. I've moved the big query into a view in SQL Server, but that
of course doesn't do anything for performance. The query is simply too
large and complex to do these product search queries. I was thinking I
could flatten the data out into one big table for product search
queries only, or something like that - kind of effectively caching a
view in SQL Server, and using triggers to update it when relevant data
has changed.
Is this a realistic endeavor? Is there a canned method of doing this,
or will I have to do it through brute force? Any pointers or
suggestions would be much appreciated.
Thanks,
ErikErik - please take a look in BOL for "Indexed Views" or check out this
article for more info:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/indexvw.mspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||Hi Erik
"voldengen@.gmail.com" wrote:
> I've got a web application that does queries against a very large
> product database (SQL Server 2000). The data that needs to be returned
> is such that the query is HUGE, and very expensive.
I am not sure why this would be the case, a user may only view a finite
amount of information! Is this information paged or are you selecting
information that is subsequently dropped?
> So I'd like to go back to querying the database for each product
> search. I've moved the big query into a view in SQL Server, but that
> of course doesn't do anything for performance. The query is simply too
> large and complex to do these product search queries. I was thinking I
> could flatten the data out into one big table for product search
> queries only, or something like that - kind of effectively caching a
> view in SQL Server, and using triggers to update it when relevant data
> has changed.
>
Have you considered partitioning the data and using a partitioned view?
Posting DDL and sample data may help understand exactly what you are trying
to do.
> Thanks,
> Erik
>
John|||how many rows do you have?
what type of search do you do?
do you do free text searches? have your try to create a server side full
text catalog?
(full text catalogs are great to index text content and support queries like
google search)
do you use storeprocedure to access your database or ad-hoc queries?
how the table is indexed?
you talk about a web application, do you use ASP.NET? do you use SQL
dependent caching in your web application? (you keep in cache the result of
a search until the database is updated, you don't keep to entire table in
memory)
and also, what is your SQL Server hardware?
<voldengen@.gmail.com> wrote in message
news:1167380092.221925.65670@.a3g2000cwd.googlegroups.com...
> I've got a web application that does queries against a very large
> product database (SQL Server 2000). The data that needs to be returned
> is such that the query is HUGE, and very expensive.
> Thus far, I've had great success by caching that big query (returning
> all rows) in my web application server, and searching against that to
> perform product searches. This works great, but now the data is
> changing more rapidly and a cache solution is not as ideal as before
> So I'd like to go back to querying the database for each product
> search. I've moved the big query into a view in SQL Server, but that
> of course doesn't do anything for performance. The query is simply too
> large and complex to do these product search queries. I was thinking I
> could flatten the data out into one big table for product search
> queries only, or something like that - kind of effectively caching a
> view in SQL Server, and using triggers to update it when relevant data
> has changed.
> Is this a realistic endeavor? Is there a canned method of doing this,
> or will I have to do it through brute force? Any pointers or
> suggestions would be much appreciated.
> Thanks,
> Erik
>|||Thanks everyone for those suggestions. I will research indexed and
partitioned views to see how that works.
The number of rows is about 200K.
An overly simplified recordset would look like:
-id (int)
-supplier (varchar)
-category (int)
-subcategory (int)
-description (varchar)
-notes (text)
-quantity (int)
My product queries return all columns, searching against any of them.
Most queries are based on supplier, category, and subcategory.
I've considered setting up a verity index for doing full text searches,
but that only covers a fraction of the queries I'm doing.
SQL hardware is a beefy P4 with 2gigs of ram, win2k3...Dell Poweredge
server.
Thanks again for the suggestions.
-Erik
On Dec 29, 4:39 am, "Jeje" <willg...@.hotmail.com> wrote:
> how many rows do you have?
> what type of search do you do?
> do you do free text searches? have your try to create a server side full
> text catalog?
> (full text catalogs are great to index text content and support queries like
> google search)
> do you use storeprocedure to access your database or ad-hoc queries?
> how the table is indexed?
> you talk about a web application, do you use ASP.NET? do you use SQL
> dependent caching in your web application? (you keep in cache the result of
> a search until the database is updated, you don't keep to entire table in
> memory)
> and also, what is your SQL Server hardware?
> <volden...@.gmail.com> wrote in messagenews:1167380092.221925.65670@.a3g2000cwd.googlegroups.com...
> > I've got a web application that does queries against a very large
> > product database (SQL Server 2000). The data that needs to be returned
> > is such that the query is HUGE, and very expensive.
> > Thus far, I've had great success by caching that big query (returning
> > all rows) in my web application server, and searching against that to
> > perform product searches. This works great, but now the data is
> > changing more rapidly and a cache solution is not as ideal as before
> > So I'd like to go back to querying the database for each product
> > search. I've moved the big query into a view in SQL Server, but that
> > of course doesn't do anything for performance. The query is simply too
> > large and complex to do these product search queries. I was thinking I
> > could flatten the data out into one big table for product search
> > queries only, or something like that - kind of effectively caching a
> > view in SQL Server, and using triggers to update it when relevant data
> > has changed.
> > Is this a realistic endeavor? Is there a canned method of doing this,
> > or will I have to do it through brute force? Any pointers or
> > suggestions would be much appreciated.
> > Thanks,
> > Erik|||voldengen@.gmail.com wrote:
> Thanks everyone for those suggestions. I will research indexed and
> partitioned views to see how that works.
> The number of rows is about 200K.
> An overly simplified recordset would look like:
> -id (int)
> -supplier (varchar)
> -category (int)
> -subcategory (int)
> -description (varchar)
> -notes (text)
> -quantity (int)
> My product queries return all columns, searching against any of them.
> Most queries are based on supplier, category, and subcategory.
> I've considered setting up a verity index for doing full text searches,
> but that only covers a fraction of the queries I'm doing.
> SQL hardware is a beefy P4 with 2gigs of ram, win2k3...Dell Poweredge
> server.
> Thanks again for the suggestions.
>
How are you facilitating the flexible searching? If you're using
something like
WHERE (@.Category IS NULL OR category = @.Category)
You might want to consider using dynamic SQL instead. The above syntax
will prevent your query from using indexes efficiently.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||My query uses a few outer joins, indexed views probably won't work.
I'll check out partitioned views now...
Thanks again,
Erik
On Dec 29, 2:00 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
> Erik - please take a look in BOL for "Indexed Views" or check out this
> article for more info:http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/indexv...
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:459541F1.8030507@.realsqlguy.com...
> voldengen@.gmail.com wrote:
>> Thanks everyone for those suggestions. I will research indexed and
>> partitioned views to see how that works.
>> The number of rows is about 200K.
>> An overly simplified recordset would look like:
>> -id (int)
>> -supplier (varchar)
>> -category (int)
>> -subcategory (int)
>> -description (varchar)
>> -notes (text)
>> -quantity (int)
>> My product queries return all columns, searching against any of them.
>> Most queries are based on supplier, category, and subcategory.
>> I've considered setting up a verity index for doing full text searches,
>> but that only covers a fraction of the queries I'm doing.
>> SQL hardware is a beefy P4 with 2gigs of ram, win2k3...Dell Poweredge
>> server.
>> Thanks again for the suggestions.
> How are you facilitating the flexible searching? If you're using
> something like
> WHERE (@.Category IS NULL OR category = @.Category)
> You might want to consider using dynamic SQL instead. The above syntax
> will prevent your query from using indexes efficiently.
>
Indeed. The suggestions you have received assume that your query really is
too expensive to run every time. Before going to indexed views, caching or
any other workaround, you should be sure that you can't optimize your query
so it is cheap enough to run every time.
If you post the query and DDL here someone might suggest an efficient
approach.
David|||<voldengen@.gmail.com> wrote in message
news:1167413897.388996.88880@.a3g2000cwd.googlegroups.com...
> My query uses a few outer joins, indexed views probably won't work.
> I'll check out partitioned views now...
> Thanks again,
> Erik
> On Dec 29, 2:00 am, "Paul Ibison" <Paul.Ibi...@.Pygmalion.Com> wrote:
>> Erik - please take a look in BOL for "Indexed Views" or check out this
>> article for more
>> info:http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/indexv...
>> Cheers,
>> Paul Ibison SQL Server MVP,www.replicationanswers.com.
>
I can't imagine how partitioned views would help.
David|||On 29 Dec 2006 00:14:52 -0800, voldengen@.gmail.com wrote:
>I've got a web application that does queries against a very large
>product database (SQL Server 2000). The data that needs to be returned
>is such that the query is HUGE, and very expensive.
>Thus far, I've had great success by caching that big query (returning
>all rows) in my web application server, and searching against that to
>perform product searches. This works great, but now the data is
>changing more rapidly and a cache solution is not as ideal as before
>So I'd like to go back to querying the database for each product
>search. I've moved the big query into a view in SQL Server, but that
>of course doesn't do anything for performance. The query is simply too
>large and complex to do these product search queries. I was thinking I
>could flatten the data out into one big table for product search
>queries only, or something like that - kind of effectively caching a
>view in SQL Server, and using triggers to update it when relevant data
>has changed.
>Is this a realistic endeavor? Is there a canned method of doing this,
>or will I have to do it through brute force? Any pointers or
>suggestions would be much appreciated.
>Thanks,
>Erik
Hi Erik,
You have already gotten a few good answers, including the very sound
advise to try tuning "that big query" first.
Failing that, indexed views would be my second choice, but I understand
that the use of outer joins precludes that option. That leaves you with
one other option - to build your own indexed view: create a table to
store the results from the big query, AND create triggers on all tables
used in the big query to change the results in the "cached view" as the
base data is changed. (Note that this is essentially what happpens under
the covers when you create an indexed view - but because of the outer
join, deducing the correct updates to the "cached view" from the updates
to the base tables becomes too hard for SQL Server).
--
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:nkfdp2ll6cjckrl0h997dvttjt1ov5ppe2@.4ax.com...
> On 29 Dec 2006 00:14:52 -0800, voldengen@.gmail.com wrote:
>>I've got a web application that does queries against a very large
>>product database (SQL Server 2000). The data that needs to be returned
>>is such that the query is HUGE, and very expensive.
>>Thus far, I've had great success by caching that big query (returning
>>all rows) in my web application server, and searching against that to
>>perform product searches. This works great, but now the data is
>>changing more rapidly and a cache solution is not as ideal as before
>>So I'd like to go back to querying the database for each product
>>search. I've moved the big query into a view in SQL Server, but that
>>of course doesn't do anything for performance. The query is simply too
>>large and complex to do these product search queries. I was thinking I
>>could flatten the data out into one big table for product search
>>queries only, or something like that - kind of effectively caching a
>>view in SQL Server, and using triggers to update it when relevant data
>>has changed.
>>Is this a realistic endeavor? Is there a canned method of doing this,
>>or will I have to do it through brute force? Any pointers or
>>suggestions would be much appreciated.
>>Thanks,
>>Erik
> Hi Erik,
> You have already gotten a few good answers, including the very sound
> advise to try tuning "that big query" first.
> Failing that, indexed views would be my second choice, but I understand
> that the use of outer joins precludes that option.
> ...
Not entirely. If the query has, say, four inner joins and three outer joins
you can create an indexed view on the inner joins and add the outer joins in
a non-indexed view.
David|||Great, thanks very much for the suggestions. I like these last two
quite a bit - flatten the nasty stuff with a table, updated by
triggers, and join to an indexed view. Wonderful!

caching a view?

Wednesday, March 7, 2012

Cache problem in Report Services

Hi All,
We are using RS for our application to show report. We are calling URL
for all report from ASP with some parameters, like on click of a button
with some filters we are using report link as
http://localhost/reportserver?/rptParameter/rdlTestParameter&rs:Command=Render&rc:Parameters=false&CID=2067
When we click on button, report gets fired, but it shows old data and
when we press refresh button of RS it shows updated data. We changed
the data and fired the above url again, it doesnt show updated data and
when we press refresh button it will shows updated data. May be this is
due to cache but in Report Manager i have checked the Do not cache
reports.., but problem still persist, can anybody please tell me how to
solve this problem.
Thanks
Regards
RajeshHi,
when calling your report, you can add the following parameter:
http://localhost/reportserver?/rptParameter/rdlTestParameter&rs:Command=Render&rc:Parameters=false&rs:ClearSession=true&CID=2067
Laurent
"Raj" <kapse_rajesh@.rediffmail.com> a écrit dans le message de news:
1141463990.435176.137140@.i40g2000cwc.googlegroups.com...
> Hi All,
> We are using RS for our application to show report. We are calling URL
> for all report from ASP with some parameters, like on click of a button
> with some filters we are using report link as
> http://localhost/reportserver?/rptParameter/rdlTestParameter&rs:Command=Render&rc:Parameters=false&CID=2067
> When we click on button, report gets fired, but it shows old data and
> when we press refresh button of RS it shows updated data. We changed
> the data and fired the above url again, it doesnt show updated data and
> when we press refresh button it will shows updated data. May be this is
> due to cache but in Report Manager i have checked the Do not cache
> reports.., but problem still persist, can anybody please tell me how to
> solve this problem.
>
> Thanks
> Regards
> Rajesh
>|||Thanks Laurent, this solved my problem.
Thanks
Rajesh

Sunday, March 25, 2012

calculated fields w/user defined functions

Tuesday, March 20, 2012

Calculate leave days from Startdate to enddate of contract

Calculate duration whilst excluding non working time

Calculate duration whilst excluding non working time

Sunday, March 11, 2012

CAL users - 1 or many?

CAL users - 1 or many?

CAL users - 1 or many?

CAL license question

CAL license question

CAL

Caching with Dynamic Security

Caching UDF

Caching stored procedures

Thursday, March 8, 2012

caching problem in sql reporting services

Caching of stored procedure

Caching Application Block and SQL 2005 SQL Dependency

caching a view?

caching a view?

caching a view?

Wednesday, March 7, 2012

Cache problem in Report Services

Bulkload XML Code

Blog Archive

About Me