still not clear

Hennie de Nooijer

hi thank you for this solution Hugo (dutch?) and it's very handy, thank
you but my issue is that we created dozens of views with my function.
This functions returns an integer of a date. This function has to give a
lowerlimit and higherlimit back when dates are out of Calendar range.

But i want to read this from the Calendartable ONCE and store it in a
global variable somewhere in SQL Server. I DON'T want to reference
tables in my function. SO read once the lower- and upperlimit and use
this variable/property for referencing in my function!!
Greetz
Hennie

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jul 23 '05 #1

Subscribe Post Reply

1709

Simon Hayes

"Hennie de Nooijer" <hd********@hotmail.com> wrote in message
news:41**********@127.0.0.1...

hi thank you for this solution Hugo (dutch?) and it's very handy, thank
you but my issue is that we created dozens of views with my function.
This functions returns an integer of a date. This function has to give a
lowerlimit and higherlimit back when dates are out of Calendar range.

But i want to read this from the Calendartable ONCE and store it in a
global variable somewhere in SQL Server. I DON'T want to reference
tables in my function. SO read once the lower- and upperlimit and use
this variable/property for referencing in my function!!
Greetz
Hennie

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I haven't read your previous posts, but there are no global variables in
TSQL, and the only place you can store data is in a table - it's quite
common to have a dbo.Constants (or whatever) table for this purpose.

If you can use stored procs or table-valued functions instead of views you
might be able to do something such as retrieve the correct date values once,
then pass them to each proc/function as parameters, but I have no idea if
that would be a good idea in your situation. If you definitely need to use
views then you might look into replacing your function with a CASE statement
in the view definition.

If that doesn't help, you might want to give a (simplified) set of DDL and
INSERT statements for your table, view, function and sample data which can
be run in QA - with a clear example of your problem, someone may be able to
make a better suggestion. (Apologies if you've already done this in another
post.)

Simon

Jul 23 '05 #2

Hugo Kornelis

On 4 Jan 2005 03:15:22 -0600, Hennie de Nooijer wrote:

hi thank you for this solution Hugo (dutch?) and it's very handy, thank
you but my issue is that we created dozens of views with my function.
This functions returns an integer of a date. This function has to give a
lowerlimit and higherlimit back when dates are out of Calendar range.
Hoi Hennie,

Ja, ik ben inderdaad Nederlander. Jij ook, neem ik aan? :-)
But let's continue in English - this is an English group, after all.

Could you post the current code of your function? I can understand not
wanting to change something used in so many places, but I guess it might
be possible to change the function's code to do the same as it is doing
now, but without the limitations you are experiencing. However, I can only
be sure if I see your code.

But i want to read this from the Calendartable ONCE and store it in a
global variable somewhere in SQL Server. I DON'T want to reference
tables in my function. SO read once the lower- and upperlimit and use
this variable/property for referencing in my function!!

Simon is correct - there are no global variables. Other than that, I also
am not sure if I really understand what you are trying to achieve, so I
will second Simon's request to post table structure (as CREATE TABLE
statements), sample data (as INSERT statements) and expected output along
with the description of the problem you're trying to solve. That might
help me (and others) understand what you're trying to achieve. Check this
site: http://www.aspfaq.com/5006 - it has some valuable tips to help you
assemble the requested data.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Jul 23 '05 #3

Hennie de Nooijer

Ok here 's an impression of my view(see below). As you can see i called
the function xxxxx_FN_GetDateString(ISNULL(Intake_Date,'1990-01-01
00:00:00')) multiple times This function returns an integer back :
19900101. Not more than that!!!

The calendar table (see create script below) has information about the
dates, which month, which day, etc. This is standard table in a
datawarehouse. Nothing special. The Calendar_Dim_key has the same key as
my generated date : 19900101 for example.

If i want to return a minimumdate and a maximumdate(because my
calendartable has limited dateinformation)
and if want to use the lowest date and the highest date in the calendar
table i would have to create a SELECT MIN(Calendar_Dim_Key) FROM
SDM_Calendar and a SELECT MAX(Calendar_Dim_Key) FROM SDM_Calendar in my
FUNCTION.

Every time i call this function it would execute this query twice but
this information is retrieved already is by a former call of this
function. And this irritates me. I want a one call, retrieve the
information and use it multiple times....

An example:

My calendar table ranges from 1980 - 2049 and when the sourcetable has a
date like 01-05-249 the function has to return a 19800101 date (249 <
1980) not 2490501 because this a faulty date.

So i hope it's now easier to understand. Thanx.

en ja hugo ik ben ook nederlander;-) ik hoop dat dit wat duidelijker is.

Greetz

Hennie

-------------------------------------------------
This is the create script of the calendar table:
-------------------------------------------------
CREATE TABLE [dbo].[SDM_Calendar] (
[Calendar_Dim_Key] [int] NOT NULL ,
[Full_Date_App] [datetime] NOT NULL ,
[Day_of_Month] [tinyint] NOT NULL ,
[Day_of_Year] [smallint] NOT NULL ,
[Day_Full_Name] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[Week_Number] [tinyint] NOT NULL ,
[Month_Full_Name] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Month_Number] [tinyint] NOT NULL ,
[Quarter] [tinyint] NOT NULL ,
[Quarter_Full_Name] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Calendar_Year] [smallint] NOT NULL
) ON [SAMIS_SDM_Index1]
GO
----------------------------------------------
This is the view
-----------------------------------------------
CREATE VIEW TR_Fact_Event_Action1_V
AS
SELECT
Event_Process_Type as Event_Process_Type_Code,
...........
ISNULL(contract_id,'N/A') as Serv_Contract_Code,
ISNULL(Site_category,'N/A') as Event_Site_Category_Code,
ISNULL(Cause_Code,'N/A') as Event_Cause_Code,
ISNULL(Resolution_Code,'N/A') as Event_Resolution_Code,
..........
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Cl ose_Time,Open_Time))
as FK_Event_Close_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Cl ose_Time,Open_Time))
as FK_Event_Close_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Re open_Time,Open_Time))
as FK_Event_Reopen_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Re open_Time,Open_Time))
as FK_Event_Reopen_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Si te_Visit_Time,Open_Tim
e)) as FK_Event_Site_Visit_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Si te_Visit_Time,Open_Tim
e)) as FK_Event_Site_Visit_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(SL A_Expire_Time,Open_Tim
e)) as FK_Event_SLA_Expire_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(SL A_Expire_Time,Open_Tim
e)) as FK_Event_SLA_Expire_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Re quest_Date,'1990-01-01
00:00:00')) as FK_Event_Request_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Re quest_Date,'1990-01-01
00:00:00')) as FK_Event_Request_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Ne gotiate_Date,'1990-01-
01 00:00:00')) as FK_Event_Negotiate_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Ne gotiate_Date,
'1990-01-01 00:00:00')) as FK_Event_Negotiate_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(In take_Date,'1990-01-01
00:00:00')) as FK_Event_Intake_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(In take_Date,'1990-01-01
00:00:00')) as FK_Event_Intake_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Pl anned_Start_Time,'1990
-01-01 00:00:00')) as FK_Event_Planned_Start_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Pl anned_Start_Time,'1990
-01-01 00:00:00')) as FK_Event_Planned_Start_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Pl anned_End_Time,'1990-0
1-01 00:00:00')) as FK_Event_Planned_End_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Pl anned_End_Time,'1990-0
1-01 00:00:00')) as FK_Event_Planned_End_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Im plement_Start_Time,'19
90-01-01 00:00:00')) as FK_Event_Implement_Start_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Im plement_Start_Time,'19
90-01-01 00:00:00')) as FK_Event_Implement_Start_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Im plement_End_Time,'1990
-01-01 00:00:00')) as FK_Event_Implement_End_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Im plement_End_Time,'1990
-01-01 00:00:00')) as FK_Event_Implement_End_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Ac tion_Open_Time,Open_Ti
me)) as FK_Action_Open_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Ac tion_Open_Time,Open_Ti
me)) as FK_Action_Open_Time,
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(IS NULL(Action_Close_Time
,Close_Time),Open_Time)) as FK_Action_Close_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(IS NULL(Action_Close_Time
,Close_Time),Open_Time)) as FK_Action_Close_Time,
......
CASE
WHEN Close_Time IS NOT NULL THEN
xxxxx_Control.dbo.xxxxx_FN_GetDiffSLA
(ISNULL(SLA_Expire_Time,Close_Time),
Close_Time,ISNULL(service_schedule,'N/A'),'Y','N')
ELSE 0 END,0) as SLA_Exceed_Closed_Time_Min,
'0' AS SLA_Exceed_Closed_Time_Min_2,
ISNULL(
CASE
WHEN Resolved_Time IS NOT NULL THEN
xxxxx_Control.dbo.xxxxx_FN_GetDiffSLA (Open_Time,
Resolved_Time,ISNULL(service_schedule,'N/A'),'Y','N')
WHEN Resolved_Time IS NULL AND Close_Time IS NOT NULL THEN
xxxxx_Control.dbo.xxxxx_FN_GetDiffSLA (Open_Time,
Close_Time,ISNULL(service_schedule,'N/A'),'Y','N')
ELSE 0 END,0) as Event_Duration_Resolved_Time_Min,
ISNULL(
CASE
WHEN Close_Time IS NOT NULL THEN
xxxxx_Control.dbo.xxxxx_FN_GetDiffSLA (Open_Time,
Close_Time,ISNULL(service_schedule,'N/A'),'Y','N')
ELSE 0 END,0) as Event_Duration_Closed_Time_Min,
ISNULL(
xxxxx_Control.dbo.xxxxx_FN_GetDiffSLA (Action_Open_Time,
Action_Close_Time,ISNULL(service_schedule,'N/A'),'Y','N'),0) as
Action_Duration_Min,
CASE
WHEN Event_Process_Type = 'Call'
THEN DATEDIFF (ss , '4000-01-01 00:00:00', call_handle_time) ELSE 0
END as Call_Handling_Sec,
ISNULL(Planned_Hours,0) as Event_Planned_Hours,
ISNULL(Spent_Hours,0) as Event_Spent_Hours,
ISNULL(Remaining_Hours,0) as Event_Remaining_Hours,
ISNULL(sysmodtime,GETDATE()) as Sysmodtime
FROM
SAD_Fact_Event_Action

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jul 23 '05 #4

Hennie de Nooijer

Jul 23 '05 #5

Hugo Kornelis

On 5 Jan 2005 05:20:22 -0600, Hennie de Nooijer wrote:

Ok here 's an impression of my view(see below). As you can see i called
the function xxxxx_FN_GetDateString(ISNULL(Intake_Date,'1990-01-01
00:00:00')) multiple times This function returns an integer back :
19900101. Not more than that!!!

(snip)

Hi Hennie,

Thanks for posting the code. Unfortunately, you forgot to include the code
of the function itself. But based on what you wrote, I'd say you have
several options:

IF the function currently has a datetime as input parameter and an integer
as output, you could replace the function:

ALTER FUNCTION dbo.xxxxx_FN_GetDateString (@InDate datetime)
RETURNS int
AS
BEGIN
DECLARE @OutDate int
SET @OutDate = CAST(CONVERT(varchar, @InDate, 112) AS int)
RETURN (@OutDate)
END

The above assumes that you only want the special handling to prevent
errors based on your calendar table not having all dates and would be
happy to convert dates outside of that range if possible.

If there are other reasons to impose a minimum and maximum date as well,
then you'd have to tweak it a little:

ALTER FUNCTION dbo.xxxxx_FN_GetDateString (@InDate datetime)
RETURNS int
AS
BEGIN
DECLARE @OutDate int,
@MinDate datetime,
@MaxDate datetime
SET @MinDate = '19800101'
SET @MaxDate = '20491231'
SET @OutDate = CASE
WHEN @InDate < @MinDate
THEN CAST(CONVERT(varchar, @MinDate, 112) AS int)
WHEN @InDate > @MaxnDate
THEN CAST(CONVERT(varchar, @MaxDate, 112) AS int)
ELSE CAST(CONVERT(varchar, @InDate, 112) AS int)
END
RETURN (@OutDate)
END

If this doesn't do what you need, then it's time I ask you to post a repro
script (a script containing all CREATE TABLE, INSERT, CREATE PROC, CREATE
VIEW, CREATE FUNCTION and other statements needed to create a simplified
version of your situation - simplified as much as possible, to make it as
small as possible while still showing what you want to get done).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Jul 23 '05 #6

Hennie de Nooijer

Hi Hugo,

As you post in your message you put a hardcoded minimum and maximumdate
in your function.

SET @MinDate = '19800101'
SET @MaxDate = '20491231'

I want it to make it flexible and dynamic

I want to make it something like this:

SET @MinDate = (SELECT MIN(Calendar_dim_key) From Calendar)
SET @MaxDate = (SELECT MAX(Calendar_dim_key) From Calendar)

But if i do that it will query the calendar table twice and that for
every time i use this function in the views and i don't want that
because of performance issues. So i want a sort of global variable which
i could fill in once and use it many times. Or maybe with a trick in the
query in which i call this function?

Greetz

Hennie
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jul 23 '05 #7

Hugo Kornelis

On 5 Jan 2005 09:20:24 -0600, Hennie de Nooijer wrote:

Hi Hugo,

As you post in your message you put a hardcoded minimum and maximumdate
in your function.

SET @MinDate = '19800101'
SET @MaxDate = '20491231'

I want it to make it flexible and dynamic

I want to make it something like this:

SET @MinDate = (SELECT MIN(Calendar_dim_key) From Calendar)
SET @MaxDate = (SELECT MAX(Calendar_dim_key) From Calendar)

But if i do that it will query the calendar table twice and that for
every time i use this function in the views and i don't want that
because of performance issues. So i want a sort of global variable which
i could fill in once and use it many times. Or maybe with a trick in the
query in which i call this function?

Hi Hennie,

You can reduce the number of queries to the table to one:

SELECT @MinDate = MIN(Calendar_dim_key),
@MaxDate = MAX(Calendar_dim_key)
FROM Calendar

This will still have to be done for each execution of the user-defined
function, as there are no global variable in SQL Server. All executions
after the will take the results from cache, so they won't be terribly
slow, but they will have some slowness. Do make sure that Calendar_dim_key
is indexed, otherwise you'll have a table scan which DEFINITELY is slow.

Another possible improvement is to store min_date and max_date in a
special one-row table of constants:
CREATE TABLE Constants
(LimitToOne char(1) NOT NULL PRIMARY KEY DEFAULT 'X'
CHECK (LimitToOne = 'X'),
min_date datetime NOT NULL,
max_date datetime NOT NULL,
.....)
You could use triggers on Calendar to keep min_date and max_date in sync
with the Calendar table. This might give a small performance improvement
over using MIN() and MAX() as above, but not much.

If you really want to reduce the number of accesses to the Calendar table,
your only option is to change the function and supply min_date and
max_date as parameters. This will force you to change all code where the
function os called. If you decide to take this route, your function will
look something like this (omitting the details for brevity):

CREATE FUNCTION xxxxx_FN_GetDateString
(@DateIn datetime, @DateMin datetime, @DateMax datetime)
RETURNS int
AS
.....

And your query will become something like the following:

SELECT
Event_Process_Type as Event_Process_Type_Code,
...........
xxxxx_Control.dbo.xxxxx_FN_GetDateString(ISNULL(Cl ose_Time,Open_Time),
md.MinDate,md.MaxDate)
as FK_Event_Close_Date,
xxxxx_Control.dbo.xxxxx_FN_GetTimeString(ISNULL(Cl ose_Time,Open_Time),
md.MinDate,md.MaxDate)
as FK_Event_Close_Time,
...........
FROM SAD_Fact_Event_Action
CROSS JOIN (SELECT MIN(Calendar_dim_key) AS MinDate,
MAX(Calendar_dim_key) AS MaxDate
FROM Calendar) AS md

Now, the minimum and maximum date will only be fetched once from the
Calendar table, then passed as argument to the function.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Jul 23 '05 #8

Erland Sommarskog

Hennie de Nooijer (hd********@hotmail.com) writes:

I want to make it something like this:

SET @MinDate = (SELECT MIN(Calendar_dim_key) From Calendar)
SET @MaxDate = (SELECT MAX(Calendar_dim_key) From Calendar)

But if i do that it will query the calendar table twice and that for
every time i use this function in the views and i don't want that
because of performance issues. So i want a sort of global variable which
i could fill in once and use it many times. Or maybe with a trick in the
query in which i call this function?

There are no global variables, and, assuming that the Calender_dim_key
column is indexed, neither is there any performance issue. Would this
table frequently be updated, you could have contention issues, but, then
again, if the table is frequently updated, you probably need to query
the table each time to get the max/min values.

What you are looking for a place to keep a cache of these values. But
this cache is already there! Ever wondered why SQL Server takes so much
memory? Because it likes to have huge cache. Since this table would
be queried frequently, it would be cache constantly, so access to it as
quick your global variables.

There are many ways to make things go really slow in a database, but
this is one one of them.

Again, I like to stress, assuming that the key is indexed.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

Jul 23 '05 #9

Hennie de Nooijer

Haha thanx for your reply Erland. Trust me i don't want things to go
slow. hmmm Should i use the proposed solution of Hugo with the
crossjoin? I think this a very good solution.

And secondly, is there information available about what is cached and
what not? And can i see this somewhere in SQL Server?

Hennie

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jul 23 '05 #10

Hugo Kornelis

On 7 Jan 2005 04:30:21 -0600, Hennie de Nooijer wrote:

Haha thanx for your reply Erland. Trust me i don't want things to go
slow. hmmm Should i use the proposed solution of Hugo with the
crossjoin? I think this a very good solution.
Hi Hennie,

If I were you, I'd postpone that and first try how things go if you just
fetch min and max date at the start of the function. At most one read will
not be from cache, all others will. (And - it just can't be stressed
enough - you REALLY need to have an index on Calendar_dim_key!). The cross
join I proposed would require you to change all your code. Fine if you
really need to squeeze the last bit of performance out of your queries,
but a waste of your time otherwise.

And secondly, is there information available about what is cached and
what not?
Every bit of data read by SQL Server goes in the cache. The maximum cache
size can be configured; by default, SQL Server will simply use all memory
it can get, unless other applications need it. Once no more memory is
available, SQL Server will remove the data from cache that was accessed
last. Therefor, any data that is frequently accessed will stay in ccache
forever.

And can i see this somewhere in SQL Server?

If you run your queries with SET STATISTICS IO ON, you'll the number of
logical reads (from cache or disk) and the number of physical reads (from
disk only). The difference between these numbers is an indication of how
much was taken from cache. You can easily check this by running a query
against a table not used often, then directly running the query again.

use Northwind
set statistics io on
select * from Categories
select * from Categories

I'm not sure though if reads from within a function are properly
reflected. If not, you'll have to use Profiler instead.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Jul 23 '05 #11

Erland Sommarskog

Hennie de Nooijer (hd********@hotmail.com) writes:

Haha thanx for your reply Erland. Trust me i don't want things to go
slow. hmmm Should i use the proposed solution of Hugo with the
crossjoin? I think this a very good solution.
I don't remember Hugo's CROSS JOIN, but he seems to know SQL a bit, so
it's probably a good bet.
And secondly, is there information available about what is cached and
what not? And can i see this somewhere in SQL Server?

I say: just don't worry about it. If the table is queried often enough
the data will be in the cache. If the table is only queried every
second Wednesday morning, the data will drop out of the cache by
Wednesday noon. But in that case, it's probably not critical that data
is in cache.

There is actually a way to force a table to be resident in memory, but
usage of this possibility is not recommended, and if I recall correctly,
that feature has been dropped in SQL2005, because it caused more harm than
good.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

Jul 23 '05 #12

Similar topics