Re: Using Function Table ? using Sql Server 2008


As I have no sound knowledge about performance and tuning of queries, however I have heard that performance can be evaluated using Execution Plan, is it true? if so, how ?

I think Erland's suggested methodology is solid and pretty much what I do during normal development. I applied it here and discovered an sub-optimal plan in the function I posted. Below is the corrected version, which is basically a datetime version of your original.

One thing I would like to add is that performance rules change when you have a high-volume OLTP type queries. The difference between 1 and 100 milliseconds may be imperceptible in cursory testing but can make an enormous difference in production performance when hundreds or thousands of users run the query. Special attention to execution plan detail is a good practice in such cases to ensure the query and indexes are as optimal as possible.

ALTER FUNCTION dbo.GetDates
(
@StartDate datetime,
@EndDate datetime
)

RETURNS @ret TABLE(DateValue datetime)
AS
BEGIN

WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1),
L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),
L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),
L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),
L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),
num AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)
INSERT INTO @ret(DateValue)
SELECT DATEADD(day, N-1, @StartDate)
FROM NUM
WHERE N <= DATEDIFF(day, @StartDate, @EndDate)
RETURN

END
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Luqman" <pearlsoft@xxxxxxxxxxxx> wrote in message news:efK9ar09JHA.4560@xxxxxxxxxxxxxxxxxxxxxxx
Hi,

Thanks very much for the help, it really worked out.

As I have no sound knowledge about performance and tuning of queries, however I have heard that performance can be evaluated using Execution Plan, is it true? if so, how ?

I will appreciate if you please point me to the right direction.

Take care!

Luqman

"Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:Od0oAQ09JHA.1492@xxxxxxxxxxxxxxxxxxxxxxx
I want to retrieve all Employees who are missing in particular Dates.

I suggest you change the function to use strongly-typed dates as this will make the query clearer. Better yet, create a permanent Calendar table to facilitate these types of queries. Below is a refactored example that I think will provide the desired results and also perform better:

ALTER FUNCTION dbo.GetDates
(
@StartDate datetime,
@EndDate datetime
)

RETURNS @ret TABLE(DateValue datetime)
AS
BEGIN

WITH
L0 AS (SELECT 0 AS N UNION ALL SELECT 1),
L1 AS (SELECT A.N+(B.N*2) AS N FROM L0 AS A, L0 AS B),
L2 AS (SELECT A.N+(B.N*4) AS N FROM L1 AS A, L1 AS B),
L3 AS (SELECT A.N+(B.N*16) AS N FROM L2 AS A, L2 AS B),
NUM AS (SELECT A.N+(B.N*256) AS N FROM L3 AS A, L3 AS B)
INSERT INTO @ret(DateValue)
SELECT DATEADD(day, N, @StartDate)
FROM NUM
WHERE DATEADD(day, N, @StartDate) BETWEEN @StartDate AND @EndDate
RETURN

END
GO

DECLARE
@FirstDate datetime,
@LastDate datetime
SELECT
@FirstDate = '20090601',
@LastDate = '20090630'
SELECT
A.EMPLOYEEID,
B.DateValue
FROM EMPLOYEE A
CROSS JOIN dbo.GetDates(@FirstDate,@LastDate) B
WHERE NOT EXISTS(
SELECT *
FROM transactionold
WHERE
employeeid = A.employeeid
AND transactionold.cDate = B.DateValue
)

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Luqman" <pearlsoft@xxxxxxxxxxxx> wrote in message news:%23wAZmZz9JHA.4692@xxxxxxxxxxxxxxxxxxxxxxx
I have created a function to find missing Dates.

The function name is GetDates.

CREATE FUNCTION dbo.GetDates
(
@Start BIGINT,
@End BIGINT
)

RETURNS @ret TABLE(Number BIGINT)
AS
BEGIN
WITH
L0 AS (SELECT 1 AS C UNION ALL SELECT 1), L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),
L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),
L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),
L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),
L5 AS (SELECT 1 AS C FROM L4 AS A, L4 AS B),
num AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L5)

INSERT INTO @ret(Number) SELECT N FROM NUM WHERE N BETWEEN @Start AND @End
RETURN
END



I want to retrieve all Employees who are missing in particular Dates.

I tried following query but could not get the desired results.

SELECT EMPLOYEEID,CAST(@nfirstdate AS DATETIME) + Number-1
FROM EMPLOYEE A, dbo.GetDates(@nFirstDay,@nLastDay) B
WHERE NOT EXISTS
(SELECT * FROM transactionold
WHERE employeeid=A.employeeid and cDate=CAST(@nfirstdate AS DATETIME) + Number-1)

Any idea please ?

Thanks and Regards,

Luqman




.

Relevant Pages

  • Re: Passing DateDiff rather than Actual Dates as Parameters
    ... So for instance you could pass a date in international format. ... Declare @real_date datetime ... > SELECT @StartDate = DATEDIFF ... > SELECT @EndDate = DATEDIFF ...
    (comp.databases.ms-sqlserver)
  • Re: Using Function Table ? using Sql Server 2008
    ... As I have no sound knowledge about performance and tuning of queries, however I have heard that performance can be evaluated using Execution Plan, is it true? ... @StartDate datetime, ... SELECT DATEADD(day, N, @StartDate) ... @Start BIGINT, ...
    (microsoft.public.sqlserver.programming)
  • Re: Overlapping and duplicate records
    ... work management type #1 ... - startdate = min startdate of the 2 original records ... , CAST(0x00009C2E00000000 AS DateTime) ...
    (microsoft.public.sqlserver.programming)
  • Union Query?
    ... DECLARE @StartDate DateTime ... AND wwRetrievalMode = 'Cyclic' ... AND wwRowCount = 1000 ...
    (microsoft.public.sqlserver.datamining)
  • Re: DateTime
    ... I would like to select jobs that fall between Monday - Friday 8am to ... StartDate (Datetime datatype) - Job start date and time ... DECLARE @startdate datetime,@enddate datetime ...
    (comp.databases.ms-sqlserver)